Re: Restricted access on DataBases - Mailing list pgsql-general

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
privilegesonly on tables created by user postgres. Since you created the table as user u_tr_main the default privileges
don'tapply, 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
isthe 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
issuedthe 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
>