Thread: Question Two: DB access

Question Two: DB access

From
The BOFH
Date:
Since I'm used to the MySQL security paradigm/model, I'm having a little
difficulty understanding the security with pgsql.

I noticed that once a db is created, any user able to log in to the server
can create tables within a database.  The docs indicate that I can create a
file containing username:[password] combos to allow only listed users
access to a database, but apparently it's a one file/one database scheme.

         "To restrict the set of users that are allowed to connect to certain
         databases, list the set of users in a separate file (one user name
per
         line) in the same directory that pg_hba.conf is in, and mention
the (base)
         name of the file after the password or crypt keyword,
respectively, in
         pg_hba.conf. If you do not use this feature, then any user that is
known
         to the database system can connect to any database (so long as he
         passes password authentication, of course). "

If I want to allow users access to only their databases, do I create a
separate file for each database, and then include the allowed users in that
file?  I'm really after by-database security, as opposed to by-table so it
doesn't appear that using groups would help.

The question then arises:  Do I then need to add a separate line in
pg_hba.conf for each database under this kind of control?

Thanks



Re: Question Two: DB access

From
Tim Frank
Date:
Not necessarily,  try using the "sameuser" parameter as a DBNAME.

host sameuser xxx.xxx.xxx.xxx 255.255.255.255 password
which would let a user connect to a database equivalent to the username
they are logging in as from the specified IP/mask.  Depending on what
users connect from where you might have to repeat this line with
different IP/mask combinations.  But it would then only allow users to
connect to a database having their username.  It worked for me in my
testing even though I don't actually use this authentication method in my
environment since most DBNAME's don't match with users.  In those
instances I have had to use the external password files to help control
this (which is much nicer to do in 7.1 since the password entry in the
external file is option and can be set to use the password in the
database).

Hope that helps.

Tim Frank

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

On 18/04/01, 4:39:17 PM, TheBOFH@nc.rr.com (The BOFH) wrote regarding
Question Two: DB access:


> Since I'm used to the MySQL security paradigm/model, I'm having a little
> difficulty understanding the security with pgsql.

> I noticed that once a db is created, any user able to log in to the
server
> can create tables within a database.  The docs indicate that I can create
a
> file containing username:[password] combos to allow only listed users
> access to a database, but apparently it's a one file/one database scheme.

>          "To restrict the set of users that are allowed to connect to
certain
>          databases, list the set of users in a separate file (one user
name
> per
>          line) in the same directory that pg_hba.conf is in, and mention
> the (base)
>          name of the file after the password or crypt keyword,
> respectively, in
>          pg_hba.conf. If you do not use this feature, then any user that
is
> known
>          to the database system can connect to any database (so long as
he
>          passes password authentication, of course). "

> If I want to allow users access to only their databases, do I create a
> separate file for each database, and then include the allowed users in
that
> file?  I'm really after by-database security, as opposed to by-table so
it
> doesn't appear that using groups would help.

> The question then arises:  Do I then need to add a separate line in
> pg_hba.conf for each database under this kind of control?

> Thanks



> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?

> http://www.postgresql.org/search.mpl