Re: Default privileges not working

Hi Stephen,

thanks for your help, I tried it, but without success.
I think there is small typo in your proposal, you need to set
USER or ROLE keyword in ALTER DEFAULT PRIVILEGES:

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

FOR { ROLE | USER } target_role

But even after adjustment I can not achieve the desired state.
After I rewrite script according your proposal. I still get the same
errors. Now I enclose with errors also output from dds command.
You can here find again full script to avoid any misunderstandings
what I was run. I run only this commands in this order and my
DB version is: PostgreSQL 9.5.4 on x86_64-pc-linux-gnu

Again thanks for your reply.

-- CONNECT AS USER: postgres
create database test;

-- CONNECT AS USER: postgres -- ON DATABASE: test
-- revoke all
revoke connect on database test from public;
revoke all on schema public from public;
revoke all on all tables in schema public from public;

-- create readonly role
create role "test_readonly" nologin noinherit;
grant connect on database test to "test_readonly";

-- schema
grant usage on schema public to "test_readonly";
-- tables
grant select on all tables in schema public to "test_readonly";

-- create readonly user
create role "user_readonly" login encrypted password 'user_readonly' in
role "test_readonly";

-- create rw role
create role "test_readwrite" nologin inherit;
grant "test_readonly" to "test_readwrite";

-- schema
-- from readonly
-- tables
grant insert, update, delete on all tables in schema public to
"test_readwrite";

-- create readwrite user
create role "user_readwrite" login encrypted password 'user_readwrite' in
role "test_readwrite";

-- create power role
create role "test_power" nologin inherit;
grant "test_readwrite" to "test_power";

-- schema
grant all privileges on schema public to "test_power";
-- tables
grant all on all tables in schema public to "test_power";

-- create readwrite user
create role "user_power" login encrypted password 'user_power' in role
"test_power";

-- grant for new tables
-- only users "postgres" and "test_power" can create tables;
alter default privileges for role "test_power" in schema public grant
select on tables to "test_readonly", "test_readwrite", "test_power";
alter default privileges for role "test_power" in schema public grant
insert, update, delete on tables to "test_readwrite", "test_power";
alter default privileges for role "test_power" in schema public grant all
on tables to "test_power";

alter default privileges for user "postgres" in schema public grant select
on tables to "test_readonly", "test_readwrite", "test_power";
alter default privileges for user "postgres" in schema public grant insert,
update, delete on tables to "test_readwrite", "test_power";
alter default privileges for user "postgres" in schema public grant all on
tables to "test_power";

-- I tried also this (both roles in one command) with the same result:
-- alter default privileges for role "test_power", "postgres" in schema
-- public grant select on tables to "test_readonly", "test_readwrite",
"test_power";

-- as postgres i can create table in public schema and insert into it
create table a (x numeric);
insert into a values (1);
select * from a; -- everything ok

-- CONNECT AS USER: user_readwrite
select * from a; -- ok
insert into a values (2); -- ok
delete from a where x = 1; -- ok
create table b (x numeric); -- ok: permission denied
drop table a; -- ok: permission denied

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

-- CONNECT AS USER: user_readwrite
select * from b; -- wrong -- SQL Error [42501]: ERROR: permission denied
for relation b
insert into b values (5); -- wrong -- SQL Error [42501]: ERROR: permission
denied for relation b

-- Output from console:
-- psql -d test
-- \ddp
--
-- Default access privileges
-- Owner | Schema | Type | Access privileges
-- ------------+--------+-------+--------------------------------
-- postgres | public | table | test_readonly=r/postgres +
-- | | | test_readwrite=arwd/postgres +
-- | | | test_power=arwdDxt/postgres
-- test_power | public | table | test_readonly=r/test_power +
-- | | | test_readwrite=arwd/test_power+
-- | | | test_power=arwdDxt/test_power
-- (2 rows)

-- CONNECT AS USER: user_readonly
select * from a; -- ok
insert into a values (6); -- ok: permission denied
select * from b; -- wrong -- SQL Error [42501]: ERROR: permission denied
for relation b

-- CONNECT AS USER: user_power
drop table a; -- wrong -- SQL Error [42501]: ERROR: must be owner of
relation a
-- user_power has granted from test_power: all privileges on schema public
and all on all tables in schema public
-- so why he can not drop table a?
alter table a owner to "user_power"; -- and he can not set new owner also.
drop table b; -- ok

-- What is wrong?
-- No one except user who create table b can read from it.
-- But with tables created as USER "postgres", everything is ok.
-- Even "user_power" can not remove tables created by "postgres".
-- 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?

-- Clean up
-- CONNECT AS USER: postgres

drop database test;

drop role user_readonly;
drop role user_readwrite;
drop owned by user_power;
reassign owned by user_power to postgres;
drop role user_power;
reassign owned by test_readonly to postgres;
drop owned by test_readonly;
drop role test_readonly;
reassign owned by test_readwrite to postgres;
drop owned by test_readwrite;
drop role test_readwrite;
reassign owned by test_power to postgres;
drop owned by test_power;
drop role test_power;

Regards
Jozef