Thread: 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
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
>> 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?
The "sameuser" option in pg_hba.conf may offer a solution. This allows
access only to a database named the same as the user.
regards, tom lane
Tom,
Your response puzzled me slightly until I combed the docs with a
slightly finer toothed comb than before. The only references where I
remembered seeing "sameuser" were in reference to the ident
authentication type, but you are correct, it is also a possible value for
a database.
My question would be if I used "sameuser" as the database then would my
database superusers be allowed to also connect to databases different
from their usernames? My first guess would be that they wouldn't because
their names wouldn't match all the DB names. The thinking for using
individual password files to name valid users was that I would need a way
to also allow my DB superusers to connect, and would therefore have to
name them in the separate password file.
While we are on this "permissions" topic, I have another related
question. I am contemplating creating a "backup user" with a separate
user/pass that is not a superuser. The reason being if I want password
authentication for my localhost then I would have to provide a user/pass
in order to do backup with a dump. I am not too keen on using a
superuser account if it is going to be stored in a script, or environment
variable, or stuck at the top of the dump when using echo, as other
suggestions on the list have led me to believe.
So, my question is, is there any way to automatically grant certain
rights (read) on any new tables that are created in any database? If I
could automatically grant read to my backup user on all new tables then
in theory a backup could be run with this account without requiring
superuser privledges.
Sorry this turned out to be a long question, but I know these issues
come up again and again without a solid answer from what I could find on
the list archives. Maybe I'm just talking silly with these ideas, so
please let me know if you wouldn't mind.
Thanks in advance to everyone.
Tim Frank
>>>>>>>>>>>>>>>>>> Original Message <<<<<<<<<<<<<<<<<<
On 13/03/01, 12:09:51 AM, tgl@sss.pgh.pa.us (Tom Lane) wrote regarding
Re: [GENERAL] Re: database access:
> >> 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?
> The "sameuser" option in pg_hba.conf may offer a solution. This allows
> access only to a database named the same as the user.
> regards, tom lane
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
Tim Frank <tfrank@registrar.uoguelph.ca> writes:
> My question would be if I used "sameuser" as the database then would my
> database superusers be allowed to also connect to databases different
> from their usernames?
You're looking at this the wrong way. Whether you are superuser or not
is immaterial as far as connection privileges go --- that fact is
determined *after* you have connected.
The sort of thing you can set up with the current privilege model is
like this:
host sameuser hostip hostmask ident
host all hostip hostmask passwd
This essentially says that for connections originating from the
hostip/hostmask part of cyberspace, "ident" authentication will
be used if you are trying to connect to the database named after
your username, otherwise "passwd" authentication will be used.
Obviously you can use two other forms of authentication if you
choose, but that's the basic model. The first line in pg_hba.conf
that matches your target database and connection origination address
determines the authentication method that is used. Generally you'd
make the earlier lines describe a tighter match and a looser auth
method than the later lines, but the machine doesn't care...
regards, tom lane