Re: Default privileges not working

Greetings,

* atiris(at)gmail(dot)com (atiris(at)gmail(dot)com) wrote:
> Thanks for the reply.
[...]
> alter default privileges in schema public grant select on tables to
> "test_readonly", "test_readwrite", "test_power";
> alter default privileges in schema public grant insert, update, delete on
> tables to "test_readwrite", "test_power";
> alter default privileges in schema public grant all on tables to
> "test_power";

Default privileges are assigned to roles. In other words, you can only
say "tables created by user X have default privileges Y." If you omit
the user from the ALTER DEFAULT PRIVILEGES command, then the
CURRENT_USER is used.

Use: \ddp
in psql to see the default privileges created and which user they are
associated with. My guess is that in the above scenario, default
privileges were only set up for the 'postgres' user.

> -- CONNECT AS USER: user_power
> select * from a;
> create table b (x numeric); -- ok, created
> insert into a values (3);
> insert into b values (4); -- ok, everything like expected

> -- CONNECT AS USER: user_readwrite
> select * from b; -- SQL Error [42501]: ERROR: permission denied for relation
> b
> -- why? according to grant default privileges on tables for insert update
> delete and select this user can do any selection insertion or deletion from
> tables in public schema
> insert into b values (5); -- SQL Error [42501]: ERROR: permission denied for
> relation b

There were no default privileges set up for the "user_power" role and,
therefore, when that role created a table, no privileges were set for
it. That's why the query by user_readwrite failed.

Try doing this first:

ALTER DEFAULT PRIVILEGES FOR user_power IN SCHEMA PUBLIC GRANT ...

And then creating a table as the "user_power" role.

> -- So no one except user who create table b can read from it.
> -- But with tables created as USER: postgres, everything is ok.
> -- How can I use default privileges to grant read to any new tables created
> to USER readonly.
> -- And grant all CRUD operations to USER readwrite, and grant delete table
> by USER power?

Assign default privileges for all roles which will be creating objects.

Thanks!

Stephen