Re: Restricted access on DataBases

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.
> >
>
> 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;

Here you are telling PostgreSQL to grant those privileges to u_tr_db on tables created by user postgres.

> ---- 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".

This is not correct. You issued the ALTER DEFAULT PRIVILEGES statement as user postgres. So u_tr_db is granted privileges only on tables created by user postgres. Since you created the table as user u_tr_main the default privileges don't apply, because there are none defined.

> 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!

Yes, because the owner of the table is u_tr_db. With set role user u_tr_main is impersonating user u_tr_db.

> 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;

Here you are telling PostgreSQL to grant privileges on tables created by u_tr_main 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;

They do if the user creating the table is the user that issued the statement. In the case above postgres.

> 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?

Yes. You may also choose to restrict the privileges, instead of granting all of them.

> 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;

In 1 the rule apply for tables created by the user that created the default privileges. Specifically the current_user is the one used for authorization checks.
In 2 you say explicitly that the rule applies to tables created by user u_tr_main.

> Why the second works and first not?

They both work. In the first statement it works if you create tables as the user who was the current_user when you issued the alter default privileges statement. In the second it works if you create a table as user u_tr_main.

> ---
>
>
> 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)

Here you see in different form what I already mentioned above.
Bye
Charles

>
> 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
>