Database security at the database level.

Поиск
Список
Период
Сортировка
Искать

Database security at the database level.

От:
"Eric Naujock " <naujocke@abacusii.com>
Дата:
Вложения:
I am looking for a way I can make an individual user or group of users be 
able to have free reign over a specific database in the postgresql system 
but not be able to see or modify any other of the databases installed on 
the system.

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

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



--------------------------------------------------------------------------------
Eric Naujock  CCNA, CCDA, A+, Network +, I-Net +
Abacus II
5610 Monroe St.
Sylvania, Ohio 43560

E-mail - naujocke@abacusii.com
Phone - 419-885-0082  X 241
Fax : 419-885-2717
AOL IM: erlic

Re: Database security at the database level.

От:
Tim Frank <tfrank@registrar.uoguelph.ca>
Дата:
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@abacusii.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
> 
> E-mail - naujocke@abacusii.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@abacusii.com
> TEL;WORK:419-885-0082 X241
> ORG:;Support
> TEL;PREF;FAX:419-885-2717
> EMAIL;WORK;PREF:naujocke@abacusii.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@abacusii.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--
FAQ