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