Tom Lane wrote:
Madison Kelly <linux@xxxxxxxxxxx> 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
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
