[ckan-dev] postgres permissions

David Read david.read at okfn.org
Wed Oct 12 11:17:31 UTC 2011


On 10 October 2011 11:00, Florian Marienfeld
<florian.marienfeld at fokus.fraunhofer.de> wrote:
> Hi,
>
> In the current apt release ckan and postgres are not interacting
> consistently. this raises two issues:
> - paster db dump does not work
> - ckan connects to postgres through loopback interface rather than unix
> socket.
>
> There is a quick fix that I suggested in this pull request:
> https://bitbucket.org/okfn/ckan/pull-request/2/explicitly-pass-localhost-to-pg_dump
> It tells pg_dump to also use the loopback interface.

You've persuaded me :-) The command-line tools should access the db
with the same method as ckan. I'll put the change in now.

> I see two options to this: a) ask users to add this line
> local   std         std                          md5
> to /etc/postgresql/8.4/main/pg_hba.conf

Sounds like a good suggestion. I'll put this in the docs now and we
can see if we run into any problems. Ticket:
http://trac.ckan.org/ticket/1385

> b) change ckan to use connect to postgres using "ident", i.e.
> unix-user=postgres-user, i.e. set www-data as postgres-user, which would
> make all the password hassle unnacessary.

Interesting idea - I'd not considered it. Maybe it's not preferable
though, because it means all apache processes on the machine can
freely access all ckan databases, so no great default security.

Cheers for these suggestions,

David

> My pull request entails little work and an improved user experience, but
> adding the pg_hba.conf hint would also help people. option b is more a
> long term solution right?
>
> Opinions?
>
> Best regards,
>
> FLO
>
>
> On 05.10.11 17:33, David Read wrote:
>> On 5 October 2011 16:09, Florian Marienfeld
>> <florian.marienfeld at fokus.fraunhofer.de> wrote:
>>> Ok, thats an explanation. but that would mean that ckan in general uses
>>> the loopback interface right? I'm no db expert, either, but I kind of
>>> tend to trust the debian default config. so you are suggesting the
>>> default postgres config is wrong?
>>
>> I'm just saying that's what I do and it works. Good point about ckan
>> using loopback - I'll follow it up when I get a chance.
>>
>> David
>>
>>>
>>> On 05.10.11 17:04, David Read wrote:
>>>> Hi Florian,
>>>>
>>>> I understand from your messages on Bitbucket that you're having
>>>> problems with postgres permissions.
>>>> https://bitbucket.org/okfn/ckan/pull-request/2/add-h-localhost-to-postgres-command-for#comment-301
>>>>
>>>> You did:
>>>> $ paster --plugin=ckan db dump ckan.dump --config=/etc/ckan/std/std.ini
>>>> which runs pg_dump like this:
>>>> $ pg_dump --username=std -W std > ckan.dump
>>>> but you get this error:
>>>> FATAL:  Ident authentication failed for user "std"
>>>>
>>>> Whereas you get success if you add "-h localhost" to the pg_dump command.
>>>>
>>>> The error is due to your postgres permissions setup, I believe
>>>> (/etc/postgresql/8.4/main/pg_hba.conf) and I suggest you fix this,
>>>> rather than add the loopback complexity to the command line interface
>>>> code.
>>>>
>>>> I'm no expert on postgres permissions, and wish I knew more about
>>>> them, but from what I can work out:
>>>>
>>>> If you don't specify -h then it goes straight to the postgres process
>>>> - Unix domain socket, known as "local" in pg_hba.conf.
>>>>
>>>> Whereas "-h localhost" uses the loopback interface and has different
>>>> permissions in pg_hba.conf. In my conf it is known by CIDR
>>>> "127.0.0.1/32".
>>>>
>>>> I hope that helps. Of course, if you can shed any more light on the
>>>> general area, then it would be much appreciated.
>>>>
>>>> David
>>>
>>>
>
>




More information about the ckan-dev mailing list