Thread: user manage their own pg_hba entries
Hi How do all you out there in postgres land who allow users who can create thier own databases, also allow them to then add their db to pg_hba.conf and then have postgres reload those permissions Glenn
If this is the way you are going to set things up, then I would just set up all the users to connect to all databases. Then, users can set permissions for their databases to limit privileges. Doing what you suggest means giving everyone in your group access to the postgres account, with ability to edit the configuration file, pg_hba, and even to potentially delete files. This is not a very safe (as in keeping things running smoothly) way to work. Make one person in charge of adding new users to pg_hba would be my two-cents worth of advice. Sean On 7/24/06 22:10, "Glenn Davy" <glenn@tangelosoftware.net> wrote: > Hi > How do all you out there in postgres land who allow users who can create > thier own databases, also allow them to then add their db to pg_hba.conf and > then have > postgres reload those permissions > > Glenn > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly
hi sean On Tue, 2006-07-25 at 07:52 -0400, Sean Davis wrote: > If this is the way you are going to set things up does this imply you have another setup scenario that would achieve the same net result? > , then I would just set up > all the users to connect to all databases. Then, users can set permissions > for their databases to limit privileges. yeah, ok - will look into that... it just seems wrong though > Doing what you suggest means > giving everyone in your group access to the postgres account, with ability > to edit the configuration file, pg_hba, and even to potentially delete > files. yes it does - and obviously thats not an option- but this _must_ be a common enough need that there is a viable safe solution. For example some sort of ability to import files from users home directory, or use of wildcards in pg_hba.conf that means a users database can be prefixed and permission be alloctaed accordinlgy, or _something_.? > This is not a very safe (as in keeping things running smoothly) way > to work. Make one person in charge of adding new users to pg_hba would be > my two-centsnd worth of advice. safe, but hardly viable - i dont want to have to drop what im doing and edit pg_hba and reload every time someone adds a database, and my users dont want to have to wait for me. I cant be the only person with this dilema - so i guess im wondering how oher people handle it? glenn > > Sean > > > On 7/24/06 22:10, "Glenn Davy" <glenn@tangelosoftware.net> wrote: > > > Hi > > How do all you out there in postgres land who allow users who can create > > thier own databases, also allow them to then add their db to pg_hba.conf and > > then have > > postgres reload those permissions > > > > Glenn > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 1: if posting/reading through Usenet, please send an appropriate > > subscribe-nomail command to majordomo@postgresql.org so that your > > message can get through to the mailing list cleanly >
Sean Davis <sdavis2@mail.nih.gov> writes:
> If this is the way you are going to set things up, then I would just set up
> all the users to connect to all databases. Then, users can set permissions
> for their databases to limit privileges.
This will work a lot better as of 8.2; we've added a CONNECT privilege
for databases that can be manipulated with ordinary GRANT/REVOKE commands.
Unfortunately that's not in any released version :-(
For the moment, the easiest way to handle it is via groups. The trick
is to create a group associated with each database (and named the same
as the database), then use the "samegroup" option in pg_hba.conf to show
that people can log into databases if they are members of the
corresponding group. Then granting and revoking group membership gets
the job done without any changes in pg_hba.conf.
regards, tom lane
On 7/25/06 11:41 AM, "Tom Lane" <tgl@sss.pgh.pa.us> wrote: > Sean Davis <sdavis2@mail.nih.gov> writes: >> If this is the way you are going to set things up, then I would just set up >> all the users to connect to all databases. Then, users can set permissions >> for their databases to limit privileges. > > This will work a lot better as of 8.2; we've added a CONNECT privilege > for databases that can be manipulated with ordinary GRANT/REVOKE commands. > Unfortunately that's not in any released version :-( This will be a nice change. Thanks for letting us know, Tom. Sean
Delightful - thanks for the news. in mean times seans comments will get me out of trouble, so thanks again sean glenn On Tue, 2006-07-25 at 11:41 -0400, Tom Lane wrote: > Sean Davis <sdavis2@mail.nih.gov> writes: > > If this is the way you are going to set things up, then I would just set up > > all the users to connect to all databases. Then, users can set permissions > > for their databases to limit privileges. > > This will work a lot better as of 8.2; we've added a CONNECT privilege > for databases that can be manipulated with ordinary GRANT/REVOKE commands. > Unfortunately that's not in any released version :-( > > For the moment, the easiest way to handle it is via groups. The trick > is to create a group associated with each database (and named the same > as the database), then use the "samegroup" option in pg_hba.conf to show > that people can log into databases if they are members of the > corresponding group. Then granting and revoking group membership gets > the job done without any changes in pg_hba.conf. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org
On 7/25/06 19:48, "Glenn Davy" <glenn@tangelosoftware.net> wrote: > Delightful - thanks for the news. in mean times seans comments will get > me out of trouble, so thanks again sean No problem. And just in case you are NOT running a mission-critical installation, the 8.2 cvs code will probably install and work just fine. I have installed the cvs code for the past two major versions without major incident, in case you wanna "try it". Sean > On Tue, 2006-07-25 at 11:41 -0400, Tom Lane wrote: >> Sean Davis <sdavis2@mail.nih.gov> writes: >>> If this is the way you are going to set things up, then I would just set up >>> all the users to connect to all databases. Then, users can set permissions >>> for their databases to limit privileges. >> >> This will work a lot better as of 8.2; we've added a CONNECT privilege >> for databases that can be manipulated with ordinary GRANT/REVOKE commands. >> Unfortunately that's not in any released version :-( >> >> For the moment, the easiest way to handle it is via groups. The trick >> is to create a group associated with each database (and named the same >> as the database), then use the "samegroup" option in pg_hba.conf to show >> that people can log into databases if they are members of the >> corresponding group. Then granting and revoking group membership gets >> the job done without any changes in pg_hba.conf. >> >> regards, tom lane >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 4: Have you searched our list archives? >> >> http://archives.postgresql.org > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match
hi all, esp tom and sean... just following up from this chat re allowing users to create databases with out having to have permissions to edit pg_hba and issue a reload... i've allowed all 'host' users to connect from 127.0.0.1/32 to all databases, then used grant/revoke to limit what they can do - except I cant seem to see how I can limit user A from creating tables in database owned by user B? is this a 'wait till 8.2' thing, or is it do-able now? glenn On Tue, 2006-07-25 at 12:36 -0400, Sean Davis wrote: > > > On 7/25/06 11:41 AM, "Tom Lane" <tgl@sss.pgh.pa.us> wrote: > > > Sean Davis <sdavis2@mail.nih.gov> writes: > >> If this is the way you are going to set things up, then I would just set up > >> all the users to connect to all databases. Then, users can set permissions > >> for their databases to limit privileges. > > > > This will work a lot better as of 8.2; we've added a CONNECT privilege > > for databases that can be manipulated with ordinary GRANT/REVOKE commands. > > Unfortunately that's not in any released version :-( > > This will be a nice change. Thanks for letting us know, Tom. > > Sean >
Glenn Davy <glenn@tangelosoftware.net> writes:
> i've allowed all 'host' users to connect from 127.0.0.1/32 to all
> databases, then used grant/revoke to limit what they can do - except I
> cant seem to see how I can limit user A from creating tables in database
> owned by user B?
It's not a database-level privilege, it's a schema-level privilege.
Revoke CREATE on the public schema.
regards, tom lane
I guess this is why its the novice forum - thanks tom On Sun, 2006-07-30 at 12:02 -0400, Tom Lane wrote: > Glenn Davy <glenn@tangelosoftware.net> writes: > > i've allowed all 'host' users to connect from 127.0.0.1/32 to all > > databases, then used grant/revoke to limit what they can do - except I > > cant seem to see how I can limit user A from creating tables in database > > owned by user B? > > It's not a database-level privilege, it's a schema-level privilege. > Revoke CREATE on the public schema. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org