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

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)

> 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