Re: Database security at the database level.

Eric,

The pg_hba.conf file is what you would want to use. There are good docs
on it within the file with examples and in the 7.1 online docs. I will
throw a couple quick examples here for you.

Trust all users to any database from any connection

host all 0.0.0.0 0.0.0.0 trust

Trust all users to the "testdb" database from any connection

host testdb 0.0.0.0 0.0.0.0 trust

Trust all users to the "testdb" database from the 192.168.0.1 IP

host testdb 192.168.0.1 255.255.255.255 trust

Use password authentication for all users to the "testdb" from
192.168.0.1 IP

host testdb 192.168.0.1 255.255.255.255 password

Use password authentication for users that are in the "testusers.pwd"
file from the 192.168.0.1 IP

host testdb 192.168.0.1 255.255.255.255 password testusers.pwd

Those are quick and dirty and probably not labelled the best, but they
give you an idea of the progression. You can limit specific users to
connect to specific databases using this method. You can also
tighten/loosen the IP/Mask restrictions as it suits you, but no matter
that line would only allow users in testusers.pwd to connect to testdb
using password authentication.

What you have to be careful of is that there is no other authentication
scheme BEFORE or AFTER that line that would still allow those users to be
able to connect to any database on the system. For example doing this in
your pg_hba.conf

host testdb 192.168.0.1 255.255.255.255 password testusers.pwd
host all 0.0.0.0 0.0.0.0 trust

Would not give you the desired effect because if the first authentication
fails it will keep looking through the file and will match the "trust
all" scheme.

As far as giving them "free reign" over the database, I'm not sure what
you had in mind. I wouldn't let them be superusers in case you needed to,
otherwise they should be able to have "free reign" if they own the
database/tables they are working on. As you said, GRANT will let you do
this.

Hope that helps, and I think I got my examples right, but they were from
memory so excuse me if there is a typo or something.

Tim Frank

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

On 21/04/01, 8:47:29 PM, naujocke(at)abacusii(dot)com ("Eric Naujock ") wrote
regarding Database security at the database level.:

> This is a MIME message. If you are reading this text, you may want to
> consider changing to a mail reader or gateway that understands how to
> properly handle MIME multipart messages.

> --=_C09B1559.A8C9A1AD
> Content-Type: text/plain; charset=US-ASCII
> Content-Transfer-Encoding: quoted-printable
> Content-Disposition: inline

> I am looking for a way I can make an individual user or group of users
be=
> =20
> able to have free reign over a specific database in the postgresql
system=
> =20
> but not be able to see or modify any other of the databases installed
on=20
> the system.

> Does anyone have any docs on how one would go about doing this. I have=20
> already found the ability to control access by machine but I have not
as=20
> yet found one for users and databases.

> I know you can use the grant function to give users rights to certain=20
> tables but I am looking to give the user rights to the full database
but=20
> only to that one database.

>
--------------------------------------------------------------------------
-=
> -----
> Eric Naujock CCNA, CCDA, A+, Network +, I-Net +
> Abacus II
> 5610 Monroe St.
> Sylvania, Ohio 43560
> <http://www.abacusii.com>
> E-mail - naujocke(at)abacusii(dot)com
> Phone - 419-885-0082 X 241
> Fax : 419-885-2717
> AOL IM: erlic

> --=_C09B1559.A8C9A1AD
> Content-Type: text/x-vcard
> Content-Transfer-Encoding: quoted-printable
> Content-Disposition: attachment; filename="Eric Naujock .vcf"

> BEGIN:VCARD
> VERSION:2.1
> X-GWTYPE:USER
> FN:379698.GWIA:naujocke(at)abacusii(dot)com
> TEL;WORK:419-885-0082 X241
> ORG:;Support
> TEL;PREF;FAX:419-885-2717
> EMAIL;WORK;PREF:naujocke(at)abacusii(dot)com
> N:Naujock ;Eric
> TITLE:Networking Eng.
> X-GWUSERID:naujocke
> END:VCARD

> BEGIN:VCARD
> VERSION:2.1
> X-GWTYPE:USER
> FN:Eric Naujock=20
> TEL;WORK:419-885-0082 X241
> ORG:;Support
> TEL;PREF;FAX:419-885-2717
> EMAIL;WORK;PREF;NGW:naujocke(at)abacusii(dot)com
> N:Naujock ;Eric
> TITLE:Networking Eng.
> X-GWUSERID:naujocke
> END:VCARD

> --=_C09B1559.A8C9A1AD
> Content-Type: text/plain
> Content-Disposition: inline
> Content-Transfer-Encoding: binary
> MIME-Version: 1.0

> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?

> http://www.postgresql.org/users-lounge/docs/faq.html

> --=_C09B1559.A8C9A1AD--