Thread: Granting all tables in schema by one SQL command

Hello

Im new to PostgreSQL development and I would like to make "introduce"  
patch that will satisfied this point of TODO:
"%Allow GRANT/REVOKE permissions to be applied to all schema objects with  
one command

The proposed syntax is:

GRANT SELECT ON ALL TABLES IN public TO phpuser; GRANT SELECT ON NEW  
TABLES IN public TO phpuser;"

My proposal of SQL syntax is:

GRANT { { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER }
[,...] | ALL [ PRIVILEGES ] }
ON ALL TABLES IN schema_name[,...]
TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]

for granting all tables/views in schema

and

REVOKE [ GRANT OPTION FOR ]
{ { SELECT | INSERT | UPDATE | DELETE | RULE | REFERENCES | TRIGGER }
[,...] | ALL [ PRIVILEGES ] }
ON ALL TABLES IN schema_name[,...]FROM { username | GROUP groupname | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]

for revokeing all tables/views in schema

Please review is it ok.

And I have question about what author of point in TODO list has on mind  
when was writing

"GRANT SELECT ON NEW TABLES IN public TO phpuser;"?

What does "NEW TABLES" mean in this context?

Thanx for reply.

Sorry for my english.

-- 
Best Regards
Przemyslaw Kantyka
krycek6@wp.pl

looks to me that NEW TABLES are the tables created AFTER the GRANT :)

Is that?

[]'s
- Walter

On 10/19/06, Krycek <krycek6@wp.pl> wrote:
> The proposed syntax is:
>
> GRANT SELECT ON ALL TABLES IN public TO phpuser; GRANT SELECT ON NEW
> TABLES IN public TO phpuser;"

> "GRANT SELECT ON NEW TABLES IN public TO phpuser;"?
>
> What does "NEW TABLES" mean in this context?

the point is to allow tables to inherit permissions from the parent
schema.  this is actually imo much more important than multiple table
grant, because it is already trivially easy to do that with a pl/pgsql
function doing 'grant' in dynamic sql over information schema for
example.  There was considerable debate on this topic at the time that
todo was written up.

the 'new tables' bit also raises the difficulty up a notch.

merlin

Dnia 19-10-2006 o 23:14:07 Merlin Moncure <mmoncure@gmail.com> napisał:
>> What does "NEW TABLES" mean in this context?
>
> the point is to allow tables to inherit permissions from the parent
> schema.  this is actually imo much more important than multiple table
> grant [...] There was considerable debate on this topic at the time that
> todo was written up.

Thanx for reply.

Can You give me some link to this discussion?

But I still have some doubts wich I want to dispel.

If construct with "NEW TABLES" mean "allow tables to inherit permissions from the parent  schema" so why point "Allow
GRANT/REVOKEpermissions to be inherited by
 
objects based on schema permissions"
is separated in TODO list. On my mind I think its the same and please
correct me if I'm wrong.

To sum up:

"GRANT SELECT ON ALL TABLES IN schema_name TO phpuser" mean that all
tables in schema will by grated ot once (the same
way as one GRANT sql command per relation is schema).

"GRANT SELECT ON NEW TABLES IN schema_name TO phpuser" mean that schema
"schema_name" will be marked with SELECT privilage and
every table or view that will be created in that schema after that command
will have SELECT privilage granted to phpuser.
I understant that this command doesn't have influence on tables that
exists in schema at the moment of its execution.


"REVOKE SELECT ON ALL TABLES IN schema_name FROM phpuser" will be the same
as "REVOKE SELECT ON table1, table2, table3... FROM phpuser"
on all tables in schema.

"REVOKE SELECT ON NEW TABLES IN schema_name FROM phpuser" will not exactly
revoke any privilage from tables in schema but unmark schema_name default
permission for any new table.

If so - maby better alternative then construct with "NEW TABLES" is
"ALTER SCHEMA schema_name SET NEW_TABLE_PERMISSONS TO privilages TO roles"
or
"GRANT SELECT, UPDATE... ON SCHEMA schema_name TO roles" (the same as
GRANT CREATE for schema)

Last is in my opinion the best becouse it uses existing SQL syntax.
Moreover due to meaning of "GRANT ... NEW TABLES" that doesn't
exactly gives any permission to any table in schema at the moment of its
execution but only set default privilage for new object in schema
it (in my humble opinion) is more logical.

Thanx for reply.

--
Best Regards
Przemyslaw Kantyka
krycek6@wp.pl
-- 
Używam programu pocztowego Opery: http://www.opera.com/mail/