Dear Charles!
Sorry for late answer. Now I got a little time to check this again... 2016-09-14 18:43 GMT+02:00 Charles Clavadetscher <clavadetsc...@swisspug.org >: > Hello > > > > > > Also try this: > > > > ALTER DEFAULT PRIVILEGES FOR ex_mainuser GRANT INSERT, SELECT, UPDATE, > DELETE, TRUNCATE, REFERENCES, TRIGGER ON TABLES TO ex_dbuser; > > > > You execute the ALTER DEFAULT PRIVILEGES as su, so the grant applies to > objects created by su and not ex_mainuser, unless you specify it with FOR > ex_mainuser. > > > > Besides, if the objects in the table will not be created by the owner, but > by your admin, then I don’t very much see the point in giving ownership. > That could be done anyway in the public schema, unless you changed that. > So... I repeated the test. *--- login with postgres:* *CREATE DATABASE db_testrole WITH ENCODING='UTF8' TEMPLATE=template0 CONNECTION LIMIT=-1;* *CREATE ROLE u_tr_db LOGIN NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION; * *CREATE ROLE u_tr_main LOGIN NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION; GRANT u_tr_db TO u_tr_main; * *ALTER DATABASE db_testrole OWNER TO u_tr_db;* *REVOKE ALL ON DATABASE db_testrole FROM public; GRANT CREATE, TEMPORARY ON DATABASE db_testrole TO public; GRANT ALL ON DATABASE db_testrole TO u_tr_db; ALTER DEFAULT PRIVILEGES GRANT INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER ON TABLES TO u_tr_db; * *---- login with u_tr_main: * *create table t_canyouseeme_1 (k int); * *---- login with u_tr_db: * *select * from t_canyouseeme_1; ERROR: permission denied for relation t_canyouseeme_1 SQL state: 42501 * As you see before, u_tr_db got all default privileges on future tables, so I don't understand why he don't get to "t_canyouseeme_1". If I try to use these things they would work: *A.) **---- login with u_tr_main:* *set role u_tr_db; create table t_canyouseeme_2 (k int); * *---- login with u_tr_db: * *select * from t_canyouseeme_2; -- OK! * *B.) * *---- login with su: * *ALTER DEFAULT PRIVILEGES FOR role u_tr_main GRANT INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER ON TABLES TO u_tr_db; * *---- login with u_tr_main:* *create table t_canyouseeme_3 (k int); * *---- login with u_tr_db: * *select * from t_canyouseeme_3; -- OK!* A.) is because I can set role to u_tr_db and then he is the creator, he get all rights. B.) I don't understand this statement... :-( :-( :-( So the main questions. Why the default privilege settings aren't affected on newly created table? See: *ALTER DEFAULT PRIVILEGES GRANT INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER ON TABLES TO u_tr_db;* What are the meaning of this statement if they won't usable for object created by another users? U_TR_DB is owner, so they have all privileges for next tables he will create. So I supposed that "default privileges" is for future objects created by different users. But this not works here. I don't understand case B. U_TR_MAIN gives all privileges to U_TR_DB for all newly created table? What are the differences between? 1. *ALTER DEFAULT PRIVILEGES GRANT INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER ON TABLES TO u_tr_db;* 2. *ALTER DEFAULT PRIVILEGES FOR role u_tr_main GRANT INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER ON TABLES TO u_tr_db;* Why the second works and first not? --- db_testrole-# \ddp Default access privileges Owner | Schema | Type | Access privileges -----------+--------+-------+----------------------------- postgres | | table | postgres=arwdDxt/postgres + | | | u_tr_db=arwdDxt/postgres u_tr_main | | table | u_tr_db=arwdDxt/u_tr_main + | | | u_tr_main=arwdDxt/u_tr_main (2 rows) db_testrole-# \d List of relations Schema | Name | Type | Owner --------+-----------------+-------+----------- public | t_canyouseeme_1 | table | u_tr_main public | t_canyouseeme_2 | table | u_tr_db public | t_canyouseeme_3 | table | u_tr_main (3 rows) --- Thank you for your help! Best wishes dd