Re: Getting a DB password to work without editing pg_hba.conf, - Mailing list pgsql-general

Tom Lane wrote:
> Madison Kelly <linux@alteeve.com> writes:
>
>>Oh shoot, I really wasn't very verbose, was I? Sorry about that.
>>[ default pg_hba.conf with only "ident" lines ]
>
>
> Ah, that explains your question about whether passwords were good for
> anything at all.  With this pg_hba.conf they aren't --- the server will
> never ask for one.  You'd want to replace some of the "ident sameuser"
> entries with "password" (or more likely "md5") if you want password
> challenges instead of checks on the user's Unix login identity.  See
> the PG administrator docs at
> http://www.postgresql.org/docs/8.1/static/client-authentication.html
> (adjust version as needed)

I've played with the MD5 and I think I will write a little howto or
something similar to explain the options to a user who wants more
security but for now I will default to leaving things as-is.

>>   So ultimately my question becomes; How can I prevent other valid
>>postgres database users from connecting to the 'tle-bu' database
>>('postgres' being the obvious exception)? Can I do this with some
>>combination of GRANT and/or REVOKE?
>
>
> At the moment you have to do that by adjusting the pg_hba.conf entries.
> One possibility is to use "sameuser" in the database field, eg,
>
> # TYPE  DATABASE    USER        IP-ADDRESS        IP-MASK           METHOD
> # Database administrative login by UNIX sockets
> local   all         postgres                                        ident sameuser
> #
> # All other connections by UNIX sockets
> local   sameuser    all                                             ident sameuser
>
> This will let "postgres" connect to anything but other users can only
> connect to the database named after them.  If you need more flexibility
> that that, consider setting up groups named for databases and using
> "samegroup" --- then you grant or revoke group membership to let people
> into databases or not.
>
> It'd be an obvious extension to provide a direct "LOGIN" privilege
> on databases and grant or revoke that, but given the samegroup
> workaround it's not a real high-priority feature ...
>
>             regards, tom lane

   Many thanks for your help clearing that up! If I can vote for the
extension being created, consider this it. Mainly for the reasons I've
mentioned; trying to handle security programatically instead of relying
on the end-user (who may be less technically enclined) doing it. I know
that I could have my program handle the editing of the 'pg_hba.conf'
file but I don't trust myself with doing that write given that order is
important and the wide number of possible configurations.

Madison

--
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
           Madison Kelly (Digimer)
    TLE-BU; The Linux Experience, Back Up
Main Project Page:  http://tle-bu.org
Community Forum:    http://forum.tle-bu.org
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-