Re: Restricted access on DataBases

Durumdara wrote:
[...]
> --- login with postgres:
[...]
>     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".

You should have written

   ALTER DEFAULT PRIVILEGES FOR ROLE u_tr_main ...

The way you did it, you effectively wrote "FOR ROLE postgres" because
you were connected as that user.

Than means that all future tables created *by postgres* will have
privileges for user "u_tr_db" added.  But you want tables created
*by u_tr_main* to get the privileges.

Yours,
Laurenz Albe