pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
How do I grant access to entire database at once(schemas,tables,sequences,...)?
11+ messages / 7 participants
[nested] [flat]

* How do I grant access to entire database at once(schemas,tables,sequences,...)?
@ 2004-05-17 15:08  Ulrich Meis <kenobi@halifax.rwth-aachen.de>
  0 siblings, 1 reply; 11+ messages in thread

From: Ulrich Meis @ 2004-05-17 15:08 UTC (permalink / raw)
  To: pgsql-admin

Hi !

I administrate a development server for a little team, and people want 
to be able to grant access to an entire database to other 
developers/freelancers.
Up till now, I see two possibilities :

1. Write a script that queries the postgres internal tables for all 
tables,schemas,sequences,views,functions,... and then executes grant 
statements for each one of them. This would have to repeated each time a 
new object is created.

2.(Not sure if this works) Insert a trigger on postgres's internal 
tables in template1 that grants permission to a group say <dbname>_group 
to the created object. Developers that need access to the database can 
then be added to that group.

Both solutions require a serious amount of work compared to the simple task.
Is there a simpler or better way to do this?

Thanks for any ideas and comments!

greetings,

Uli





^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* How do I grant access to entire database at once(schemas,tables,sequences,...)?
@ 2004-05-17 16:53  Ulrich Meis <kenobi@halifax.rwth-aachen.de>
  0 siblings, 0 replies; 11+ messages in thread

From: Ulrich Meis @ 2004-05-17 16:53 UTC (permalink / raw)
  To: pgsql-admin

Hi !

I administrate a development server for a little team, and people want
to be able to grant access to an entire database to other
developers/freelancers.

Up till now, I see two possibilities :

1. Write a script that queries the postgres internal tables for all
tables,schemas,sequences,views,functions,... and then executes grant
statements for each one of them. This would have to be repeated each 
time a new object is created.

2.(Not sure if this works) Insert a trigger on postgres's internal
tables in template1 that grants permission to a group, say 
<dbname>_group, to the created object. Developers that need access to 
the database can then be added to that group.

Both solutions require a serious amount of work compared to the simple task.
Is there a simpler or better way to do this?

Thanks for any ideas and comments!

greetings,

Uli






^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: How do I grant access to entire database at once(schemas, tables, sequences, ...)?
@ 2004-05-17 19:10  Peter Eisentraut <peter_e@gmx.net>
  parent: Ulrich Meis <kenobi@halifax.rwth-aachen.de>
  0 siblings, 1 reply; 11+ messages in thread

From: Peter Eisentraut @ 2004-05-17 19:10 UTC (permalink / raw)
  To: Ulrich Meis <kenobi@halifax.rwth-aachen.de>; pgsql-admin

Ulrich Meis wrote:
> 1. Write a script that queries the postgres internal tables for all
> tables,schemas,sequences,views,functions,... and then executes grant
> statements for each one of them. This would have to repeated each
> time a new object is created.

Yes, that's the most popular method so far.  You could also write a 
stored procedure.

> 2.(Not sure if this works) Insert a trigger on postgres's internal
> tables in template1 that grants permission to a group say
> <dbname>_group to the created object. Developers that need access to
> the database can then be added to that group.

Triggers on system tables don't work.




^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: How do I grant access to entire database at
@ 2004-07-18 19:52  Randall Perry <rgp@systame.com>
  parent: Peter Eisentraut <peter_e@gmx.net>
  0 siblings, 1 reply; 11+ messages in thread

From: Randall Perry @ 2004-07-18 19:52 UTC (permalink / raw)
  To: Peter Eisentraut <peter_e@gmx.net>; Ulrich Meis <kenobi@halifax.rwth-aachen.de>; pgsql-admin

This is a pain. Couldn't we gave something simple like
GRANT ALL ON database.* TO JOE;

Which would grant full access to all objects in the database to JOE for all
time?

> Ulrich Meis wrote:
>> 1. Write a script that queries the postgres internal tables for all
>> tables,schemas,sequences,views,functions,... and then executes grant
>> statements for each one of them. This would have to repeated each
>> time a new object is created.
> 
> Yes, that's the most popular method so far.  You could also write a
> stored procedure.
> 

-- 
Randall Perry
sysTame

Xserve Web Hosting/Co-location
Website Design/Development
WebObjects Hosting
Mac Consulting/Sales

http://www.systame.com/





^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: How do I grant access to entire database at
@ 2004-07-18 20:41  Oliver Elphick <olly@lfix.co.uk>
  parent: Randall Perry <rgp@systame.com>
  0 siblings, 1 reply; 11+ messages in thread

From: Oliver Elphick @ 2004-07-18 20:41 UTC (permalink / raw)
  To: Randall Perry <rgp@systame.com>; +Cc: pgsql-admin

On Sun, 2004-07-18 at 20:52, Randall Perry wrote:
> This is a pain. Couldn't we gave something simple like
> GRANT ALL ON database.* TO JOE;
> 
> Which would grant full access to all objects in the database to JOE for all
> time?

You can do it like this in psql:

\a
\t
\o /tmp/grant.sql
SELECT      'GRANT ALL ON ' || n.nspname || '.' || c.relname ||
            ' TO joe;'
  FROM      pg_catalog.pg_class AS c 
            LEFT JOIN pg_catalog.pg_namespace AS n
                 ON n.oid = c.relnamespace
  WHERE     c.relkind IN ('r','v','S') AND
            n.nspname NOT IN ('pg_catalog', 'pg_toast') AND
            pg_catalog.pg_table_is_visible(c.oid)
  ORDER BY  n.nspname, c.relname;
\o
\i /tmp/grant.sql


The above could be put in a script and run from a Unix command prompt.

(The SQL used above is adaated from that used by psql's \d command.)

-- 
Oliver Elphick                                          olly@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA  92C8 39E7 280E 3631 3F0E  1EC0 5664 7A2F A543 10EA
                 ========================================
     "For God so loved the world, that he gave his only 
      begotten Son, that whosoever believeth in him should 
      not perish, but have everlasting life."     John 3:16 




^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: How do I grant access to entire database at
@ 2004-07-26 18:50  Randall Perry <rgp@systame.com>
  parent: Oliver Elphick <olly@lfix.co.uk>
  0 siblings, 2 replies; 11+ messages in thread

From: Randall Perry @ 2004-07-26 18:50 UTC (permalink / raw)
  To: olly@lfix.co.uk; +Cc: pgsql-admin

Thanks, I'll use it.

But, if the developer's are listening -- this is really obtuse. MySQL
administration is much easier. Please consider simplifying the GRANT process
for future revs.

BTW, I prefer postgresql for all my own development.


on 7/18/04 4:41 PM, Oliver Elphick at olly@lfix.co.uk wrote:

> On Sun, 2004-07-18 at 20:52, Randall Perry wrote:
>> This is a pain. Couldn't we gave something simple like
>> GRANT ALL ON database.* TO JOE;
>> 
>> Which would grant full access to all objects in the database to JOE for all
>> time?
> 
> You can do it like this in psql:
> 
> \a
> \t
> \o /tmp/grant.sql
> SELECT      'GRANT ALL ON ' || n.nspname || '.' || c.relname ||
>           ' TO joe;'
> FROM      pg_catalog.pg_class AS c
>           LEFT JOIN pg_catalog.pg_namespace AS n
>                ON n.oid = c.relnamespace
> WHERE     c.relkind IN ('r','v','S') AND
>           n.nspname NOT IN ('pg_catalog', 'pg_toast') AND
>           pg_catalog.pg_table_is_visible(c.oid)
> ORDER BY  n.nspname, c.relname;
> \o
> \i /tmp/grant.sql
> 
> 
> The above could be put in a script and run from a Unix command prompt.
> 
> (The SQL used above is adaated from that used by psql's \d command.)

-- 
Randall Perry
sysTame

Xserve Web Hosting/Co-location
Website Design/Development
WebObjects Hosting
Mac Consulting/Sales

http://www.systame.com/





^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: How do I grant access to entire database at
@ 2004-07-26 19:26  Sam Barnett-Cormack <s.barnett-cormack@lancaster.ac.uk>
  parent: Randall Perry <rgp@systame.com>
  1 sibling, 1 reply; 11+ messages in thread

From: Sam Barnett-Cormack @ 2004-07-26 19:26 UTC (permalink / raw)
  To: Randall Perry <rgp@systame.com>; +Cc: olly@lfix.co.uk; pgsql-admin

On Mon, 26 Jul 2004, Randall Perry wrote:

> Thanks, I'll use it.
>
> But, if the developer's are listening -- this is really obtuse. MySQL
> administration is much easier. Please consider simplifying the GRANT process
> for future revs.

I do agree with this, actually - in fact, let me expand.

GRANT out to have a varying degree of granularity, IMO. So you can
specify individual sequences or tables, a glob (handy for
table+sequence), possibly an intelligent way of including anything
requisite for a given table, a whole DB, a whole namespace, a whole
cluster (although then you could just make the user a superuser), all of
the members of a DB/namespace that are of a certain type (say,
tables+sequences, or functions-only, or whatever).

This isn't just useful for easing the way one grants access to prevent
unauthorised access, but also for creating users that prevent an
individual from doing things accidentally - so a given developer might
have several accounts with different privs - a bit like the way we only
su to root, not log in as root.

It's one of these little niggles that, for me, prevents Postgres being
unassailably the best FOSS database.

> on 7/18/04 4:41 PM, Oliver Elphick at olly@lfix.co.uk wrote:
>
> > On Sun, 2004-07-18 at 20:52, Randall Perry wrote:
> >> This is a pain. Couldn't we gave something simple like
> >> GRANT ALL ON database.* TO JOE;
> >>
> >> Which would grant full access to all objects in the database to JOE for all
> >> time?
> >
> > You can do it like this in psql:
> >
> > \a
> > \t
> > \o /tmp/grant.sql
> > SELECT      'GRANT ALL ON ' || n.nspname || '.' || c.relname ||
> >           ' TO joe;'
> > FROM      pg_catalog.pg_class AS c
> >           LEFT JOIN pg_catalog.pg_namespace AS n
> >                ON n.oid = c.relnamespace
> > WHERE     c.relkind IN ('r','v','S') AND
> >           n.nspname NOT IN ('pg_catalog', 'pg_toast') AND
> >           pg_catalog.pg_table_is_visible(c.oid)
> > ORDER BY  n.nspname, c.relname;
> > \o
> > \i /tmp/grant.sql
> >
> >
> > The above could be put in a script and run from a Unix command prompt.
> >
> > (The SQL used above is adaated from that used by psql's \d command.)
>
>

-- 

Sam Barnett-Cormack
Software Developer                           |  Student of Physics & Maths
UK Mirror Service (http://www.mirror.ac.uk)  |  Lancaster University



^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: How do I grant access to entire database at
@ 2004-07-26 19:29  Jean-Luc Lachance <jllachan@sympatico.ca>
  parent: Randall Perry <rgp@systame.com>
  1 sibling, 1 reply; 11+ messages in thread

From: Jean-Luc Lachance @ 2004-07-26 19:29 UTC (permalink / raw)
  To: Randall Perry <rgp@systame.com>; +Cc: olly@lfix.co.uk; pgsql-admin

I have been thinking about this problem for quite a while.

Proper administration require creation of groups.
Adding a new user to a database is as simple as adding the user to the 
group that has the required privileges to the database.

But, I think one new command would be very usefull.

CREATE GROUP <group> FROM USER <user>

where the privileges would be derived from the user's.

What do you think syntax gurus?






Randall Perry wrote:

> Thanks, I'll use it.
> 
> But, if the developer's are listening -- this is really obtuse. MySQL
> administration is much easier. Please consider simplifying the GRANT process
> for future revs.
> 
> BTW, I prefer postgresql for all my own development.
> 
> 
> on 7/18/04 4:41 PM, Oliver Elphick at olly@lfix.co.uk wrote:
> 
> 
>>On Sun, 2004-07-18 at 20:52, Randall Perry wrote:
>>
>>>This is a pain. Couldn't we gave something simple like
>>>GRANT ALL ON database.* TO JOE;
>>>
>>>Which would grant full access to all objects in the database to JOE for all
>>>time?
>>
>>You can do it like this in psql:
>>
>>\a
>>\t
>>\o /tmp/grant.sql
>>SELECT      'GRANT ALL ON ' || n.nspname || '.' || c.relname ||
>>          ' TO joe;'
>>FROM      pg_catalog.pg_class AS c
>>          LEFT JOIN pg_catalog.pg_namespace AS n
>>               ON n.oid = c.relnamespace
>>WHERE     c.relkind IN ('r','v','S') AND
>>          n.nspname NOT IN ('pg_catalog', 'pg_toast') AND
>>          pg_catalog.pg_table_is_visible(c.oid)
>>ORDER BY  n.nspname, c.relname;
>>\o
>>\i /tmp/grant.sql
>>
>>
>>The above could be put in a script and run from a Unix command prompt.
>>
>>(The SQL used above is adaated from that used by psql's \d command.)
> 
> 




^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: How do I grant access to entire database at
@ 2004-07-26 20:05  Sam Barnett-Cormack <s.barnett-cormack@lancaster.ac.uk>
  parent: Jean-Luc Lachance <jllachan@sympatico.ca>
  0 siblings, 0 replies; 11+ messages in thread

From: Sam Barnett-Cormack @ 2004-07-26 20:05 UTC (permalink / raw)
  To: Jean-Luc Lachance <jllachan@sympatico.ca>; +Cc: Randall Perry <rgp@systame.com>; olly@lfix.co.uk; pgsql-admin

On Mon, 26 Jul 2004, Jean-Luc Lachance wrote:

> I have been thinking about this problem for quite a while.
>
> Proper administration require creation of groups.
> Adding a new user to a database is as simple as adding the user to the
> group that has the required privileges to the database.
>
> But, I think one new command would be very usefull.
>
> CREATE GROUP <group> FROM USER <user>
>
> where the privileges would be derived from the user's.

I ain't no guru, but I would say:

1) I'd've thought that'd be simple enough to implement, and it is nice
and would be very handy

2) It's not as good or as flexible as what I suggested - it'd be nice to
have both, but group from user is much more likely to be forthcoming, I
expect

3) You're right about groups, and I don't think enough people use them
enough or appropriately.

Sam


> Randall Perry wrote:
>
> > Thanks, I'll use it.
> >
> > But, if the developer's are listening -- this is really obtuse. MySQL
> > administration is much easier. Please consider simplifying the GRANT process
> > for future revs.
> >
> > BTW, I prefer postgresql for all my own development.
> >
> >
> > on 7/18/04 4:41 PM, Oliver Elphick at olly@lfix.co.uk wrote:
> >
> >
> >>On Sun, 2004-07-18 at 20:52, Randall Perry wrote:
> >>
> >>>This is a pain. Couldn't we gave something simple like
> >>>GRANT ALL ON database.* TO JOE;
> >>>
> >>>Which would grant full access to all objects in the database to JOE for all
> >>>time?
> >>
> >>You can do it like this in psql:
> >>
> >>\a
> >>\t
> >>\o /tmp/grant.sql
> >>SELECT      'GRANT ALL ON ' || n.nspname || '.' || c.relname ||
> >>          ' TO joe;'
> >>FROM      pg_catalog.pg_class AS c
> >>          LEFT JOIN pg_catalog.pg_namespace AS n
> >>               ON n.oid = c.relnamespace
> >>WHERE     c.relkind IN ('r','v','S') AND
> >>          n.nspname NOT IN ('pg_catalog', 'pg_toast') AND
> >>          pg_catalog.pg_table_is_visible(c.oid)
> >>ORDER BY  n.nspname, c.relname;
> >>\o
> >>\i /tmp/grant.sql
> >>
> >>
> >>The above could be put in a script and run from a Unix command prompt.
> >>
> >>(The SQL used above is adaated from that used by psql's \d command.)
> >
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faqs/FAQ.html
>

-- 

Sam Barnett-Cormack
Software Developer                           |  Student of Physics & Maths
UK Mirror Service (http://www.mirror.ac.uk)  |  Lancaster University



^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: How do I grant access to entire database at
@ 2004-07-26 20:46  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Sam Barnett-Cormack <s.barnett-cormack@lancaster.ac.uk>
  0 siblings, 1 reply; 11+ messages in thread

From: Tom Lane @ 2004-07-26 20:46 UTC (permalink / raw)
  To: Sam Barnett-Cormack <s.barnett-cormack@lancaster.ac.uk>; +Cc: Randall Perry <rgp@systame.com>; olly@lfix.co.uk; pgsql-admin

Sam Barnett-Cormack <s.barnett-cormack@lancaster.ac.uk> writes:
> GRANT out to have a varying degree of granularity, IMO. So you can
> specify individual sequences or tables, a glob (handy for
> table+sequence), possibly an intelligent way of including anything
> requisite for a given table, a whole DB, a whole namespace, a whole
> cluster (although then you could just make the user a superuser), all of
> the members of a DB/namespace that are of a certain type (say,
> tables+sequences, or functions-only, or whatever).

All but the last are nonstarters for the simple reason that different
kinds of objects have different sets of possible GRANT rights.  Only
"GRANT ALL" could possibly be common across different object kinds, and
it doesn't seem to me that wholesale GRANT ALL would be a particularly
common thing to want to do.

It is perhaps interesting to do something like
	GRANT SELECT ON TABLE foo.* TO user;
but I'm not sure this is so useful as to be worth enshrining in the
syntax.  You could also argue that it's a potential security hole since
it'd be mighty easy to grant rights you didn't intend to on objects you
didn't realize would match the wildcard.  (And that'd be true in spades
if the effect of the command were to automatically grant the same rights
on matching objects created in the future, which is what I think some of
the people asking for this sort of thing wanted.  But I'm outright
scared of that idea.)

As long as you're not after the implicit-effects-on-future-objects
behavior, it's easy enough to write custom functions that do exactly
what you want in this line.  I'm inclined to leave it at that for the
moment.  But perhaps we could put some examples of such functions on
techdocs, or set up a pgfoundry project for them.  If your long-term
goal is to get this functionality migrated into the core server, having
a popular pgfoundry project that embodies a specific set of features
would go a long way towards convincing people that those particular
features were right and useful.  Without any pre-existing standard
to follow, it'll be hard to get consensus on "the right thing"
otherwise.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 11+ messages in thread

* Re: How do I grant access to entire database at
@ 2004-07-28 18:14  Randall Perry <rgp@systame.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 11+ messages in thread

From: Randall Perry @ 2004-07-28 18:14 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; Sam Barnett-Cormack <s.barnett-cormack@lancaster.ac.uk>; +Cc: olly@lfix.co.uk; pgsql-admin

What might be nice is a deductive syntax, so you can GRANT ALL and then
remove privileges for certain objects:

    GRANT ALL ON DATABASE foo TO user EXCEPT...


> It is perhaps interesting to do something like
> GRANT SELECT ON TABLE foo.* TO user;
> but I'm not sure this is so useful as to be worth enshrining in the
> syntax.  You could also argue that it's a potential security hole since
> it'd be mighty easy to grant rights you didn't intend to on objects you
> didn't realize would match the wildcard.  (And that'd be true in spades
> if the effect of the command were to automatically grant the same rights
> on matching objects created in the future, which is what I think some of
> the people asking for this sort of thing wanted.  But I'm outright
> scared of that idea.)

-- 
Randall Perry
sysTame

Xserve Web Hosting/Co-location
Website Design/Development
WebObjects Hosting
Mac Consulting/Sales

http://www.systame.com/





^ permalink  raw  reply  [nested|flat] 11+ messages in thread


end of thread, other threads:[~2004-07-28 18:14 UTC | newest]

Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-05-17 15:08 How do I grant access to entire database at once(schemas,tables,sequences,...)? Ulrich Meis <kenobi@halifax.rwth-aachen.de>
2004-05-17 19:10 ` Re: How do I grant access to entire database at once(schemas, tables, sequences, ...)? Peter Eisentraut <peter_e@gmx.net>
2004-07-18 19:52   ` Re: How do I grant access to entire database at Randall Perry <rgp@systame.com>
2004-07-18 20:41     ` Re: How do I grant access to entire database at Oliver Elphick <olly@lfix.co.uk>
2004-07-26 18:50       ` Re: How do I grant access to entire database at Randall Perry <rgp@systame.com>
2004-07-26 19:26         ` Re: How do I grant access to entire database at Sam Barnett-Cormack <s.barnett-cormack@lancaster.ac.uk>
2004-07-26 20:46           ` Re: How do I grant access to entire database at Tom Lane <tgl@sss.pgh.pa.us>
2004-07-28 18:14             ` Re: How do I grant access to entire database at Randall Perry <rgp@systame.com>
2004-07-26 19:29         ` Re: How do I grant access to entire database at Jean-Luc Lachance <jllachan@sympatico.ca>
2004-07-26 20:05           ` Re: How do I grant access to entire database at Sam Barnett-Cormack <s.barnett-cormack@lancaster.ac.uk>
2004-05-17 16:53 How do I grant access to entire database at once(schemas,tables,sequences,...)? Ulrich Meis <kenobi@halifax.rwth-aachen.de>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox