Thread: user manage their own pg_hba entries

user manage their own pg_hba entries

From
Glenn Davy
Date:
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

Re: user manage their own pg_hba entries

From
Sean Davis
Date:
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


Re: user manage their own pg_hba entries

From
Glenn Davy
Date:
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
>

Re: user manage their own pg_hba entries

From
Tom Lane
Date:
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

Re: user manage their own pg_hba entries

From
Sean Davis
Date:


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


Re: user manage their own pg_hba entries

From
Glenn Davy
Date:
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

Re: user manage their own pg_hba entries

From
Sean Davis
Date:


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


Re: user manage their own pg_hba entries

From
Glenn Davy
Date:
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
>

Re: user manage their own pg_hba entries

From
Tom Lane
Date:
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

Re: user manage their own pg_hba entries

From
Glenn Davy
Date:
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