[ckan-dev] postgres permissions

[Florian Marienfeld]

Great :-) just to make sure: I was trying to say that if you apply the
patch, then you dont need the additional config any more.

Best regards,

FLO


On 12.10.11 13:17, David Read wrote:
> 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
>>>>
>>>>
>>
>>