Thread: How do I grant access to entire database at once(schemas,tables,sequences,...)?
How do I grant access to entire database at once(schemas,tables,sequences,...)?
From
Ulrich Meis
Date:
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
Re: How do I grant access to entire database at once(schemas,tables,sequences,...)?
From
Peter Eisentraut
Date:
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.
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/
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
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/
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
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.)
>
>
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
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
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/