Re: [HACKERS] Granting all tables in schema by one SQL command
Dnia 19-10-2006 o 23:14:07 Merlin Moncure [EMAIL PROTECTED] 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/REVOKE permissions 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 [EMAIL PROTECTED] -- Używam programu pocztowego Opery: http://www.opera.com/mail/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] 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
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Granting all tables in schema by one SQL command
looks to me that NEW TABLES are the tables created AFTER the GRANT :)Is that?[]'s- WalterOn 10/19/06, Krycek
[EMAIL PROTECTED] wrote:HelloIm 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 withone commandThe 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 schemaandREVOKE [ 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 schemaPlease review is it ok.
And I have question about what author of point in TODO list has on mindwhen was writingGRANT SELECT ON NEW TABLES IN public TO phpuser;?What does NEW TABLES mean in this context?
Thanx for reply.Sorry for my english.--Best RegardsPrzemyslaw Kantyka[EMAIL PROTECTED]---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Granting all tables in schema by one SQL command
On 10/19/06, Krycek [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
