Jozef, * Jozef Pažin (ati...@gmail.com) wrote: > -- grant for new tables > -- only users "postgres" and "test_power" can create tables; > alter default privileges for role "test_power" in schema public grant > select on tables to "test_readonly", "test_readwrite", "test_power"; > alter default privileges for role "test_power" in schema public grant > insert, update, delete on tables to "test_readwrite", "test_power"; > alter default privileges for role "test_power" in schema public grant all > on tables to "test_power"; > > alter default privileges for user "postgres" in schema public grant select > on tables to "test_readonly", "test_readwrite", "test_power"; > alter default privileges for user "postgres" in schema public grant insert, > update, delete on tables to "test_readwrite", "test_power"; > alter default privileges for user "postgres" in schema public grant all on > tables to "test_power";
Above, you set default privileges for the 'postgres' and the 'test_power' roles, however... > -- CONNECT AS USER: user_power Here, you are connecting as the 'user_power' role, for which no default privileges were set. > select * from a; > create table b (x numeric); -- ok This table is created as the 'user_power' role and, since there were no default privileges set for this role, it is created with no privileges granted. Leading to... > -- CONNECT AS USER: user_readwrite > select * from b; -- wrong -- SQL Error [42501]: ERROR: permission denied > for relation b > insert into b values (5); -- wrong -- SQL Error [42501]: ERROR: permission > denied for relation b These permission denied errors, which are entirely correct because no default privileges were set for the case where the 'user_power' role creates objects in the 'public' schema. Please use \dp to see what the privileges are after object creation, and use \ddp to see what the default privileges will be for objects created by which roles in which schemas. > -- How can I use default privileges to grant read to any new tables > -- created to USER readonly. And grant all CRUD operations > -- to USER readwrite, and grant delete table by USER power? You must set up default privileges for all roles which will be creating objects. Above, you only set them for the 'postgres' role and the 'test_power' role, but then the 'user_power' role created objects. One approach to dealing with this is to have fewer roles which can create objects and then require users to do a 'SET ROLE' prior to creating an object, eg: CONNECT AS USER: user_power SET ROLE test_power; CREATE TABLE b (a int); The above action creates the table as the 'test_power' role and therefore the default privileges for the 'test_power' role will be applied to all newly created objects. Thanks! Stephen
signature.asc
Description: Digital signature
