[ckan-dev] postgres permissions

Mon Oct 10 10:00:43 UTC 2011

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.

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
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.

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
>>
>>