Re: Restricted access on DataBases

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 <clavadetscher(at)swisspug(dot)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