Re: database access - Mailing list pgsql-general

Daniel,

    I have recently been revisiting my database security (or lack thereof
you could say) and trying to find the right combination of pg_hba.conf
settings to make it work. I have NOT TESTED this solution, but in theory
I think it should work based on the reading I have done.
    If you want to restrict access to a database to a single user then this
should do it.  In pg_hba.conf create an entry like

host    userdb1    (IP)        (MASK)    password    userdb1.pwd

which would require password authentication for the database userdb1 and
use an external password file called userdb1.pwd for authentication
(Change IP and MASK to valid values for your situation of course).
    Now, in the userdb1.pwd file you would want to make a list of ALL VALID
USERS that are allowed to connect to that database.  From my reading the
most basic format would be

user1:+

which would then (in theory) ONLY allow user1 to validly connect to the
database called userdb1 as desired.  User2, user3, etc would not be
authenticated to connect to userdb1. You could repeat the process for
each database you have in order to restrict it per user.  The development
docs are really good for pg_hba.conf settings so I suggest taking a look
at those.  I don't think there has been a rewrite of how it works so all
the settings should work with most recent versions of PostgreSQL.

    As I said I do not claim this WILL WORK, but it is the only way I can
think of based on the valid settings in pg_hba.conf.  I am also not sure
if you would have to add your superusers into the userdb1.pwd file in
order for them to connect to userdb1 (I assume you would).  It may not be
practical to maintain a separate password file for each DB, but it may be
the only way to currently make that restriction.

    I am going to be testing some of my ideas soon so any other suggestions
from anyone out there would be great.  I also welcome any corrections to
my logic as I have been mulling these ideas over for days now.  I also
would like some suggestions on the topic of running pg_dump/pg_dumpall
when you use password/crypt authentication for databases.

Thanks,

Tim Frank

>>>>>>>>>>>>>>>>>> Original Message <<<<<<<<<<<<<<<<<<

On 07/03/01, 8:49:12 AM, daniel <dk@wol.nl> wrote regarding database
access:


> Hello,

> Howcome that all users i create have permission to view and edit all
> databases that i create. I want 1 user for each database. Anyone?

> Thanks,

> Daniel