How do I grant access to entire database at once(schemas,tables,sequences,...)?

[Ulrich Meis]

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



---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Peter Eisentraut]

Post by Ulrich Meis
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.

Post by Ulrich Meis
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.


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ***@postgresql.org so that your
message can get through to the mailing list cleanly

[Randall Perry]

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?

Post by Peter Eisentraut

Post by Ulrich Meis
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/



---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Oliver Elphick]

Post by Randall Perry
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 ***@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


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ***@postgresql.org so that your
message can get through to the mailing list cleanly

[Randall Perry]

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.

Post by Oliver Elphick

Post by Randall Perry
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?

\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/



---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ***@postgresql.org so that your
message can get through to the mailing list cleanly

[Sam Barnett-Cormack]

Post by Randall Perry
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.

Post by Randall Perry

Post by Oliver Elphick

Post by Randall Perry
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?

\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.)

[Tom Lane]

Post by Sam Barnett-Cormack
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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ***@postgresql.org so that your
message can get through to the mailing list cleanly

[Randall Perry]

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...

Post by Tom Lane
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/



---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ***@postgresql.org

[Jean-Luc Lachance]

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?

Post by Randall Perry
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.

Post by Oliver Elphick

Post by Randall Perry
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?

\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]

Post by Jean-Luc Lachance
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

Post by Jean-Luc Lachance

Post by Randall Perry
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.

Post by Oliver Elphick

Post by Randall Perry
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?

\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