Re: Getting a DB password to work without editing pg_hba.conf,

Tom Lane wrote:
> Madison Kelly <linux(at)alteeve(dot)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
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-