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
