Dear Adrian and Charles!
I tried to create a step by step instruction.
The real commands are separated by ";"
Other commands are: "login as username", "use db"
I ran them in PGAdmin with changing the connection to simulate what I feel as problem.
I suppused the ex_dbuser have owner rights to the DB, and with "default privileges" it must see the new tables created by ex_mainuser. Without them I would understand why (inherited role would have diffferent rights on creation).
If I want to represent this in other way, I would say:
- ex_mainuser have all rights as ex_dbuser, but it could have more
- but when ex_dbuser got all rights to future objects, it must see what ex_mainuser created on his database
If this not happened then my idea crashes, because we must login with ex_dbuser to create objects, or we must create all objects by ex_mainuser WITH ONLY OWNER SETTING (as ex_dbuser).
The example:
-- login as su
-- CREATE DATABASE ct_db WITH OWNER = ex_dbuser ENCODING = 'UTF8' TABLESPACE = pg_default template = template0;
-- use ct_db
-- login as ex_dbuser
-- begin; create table t_dbuser (id integer);commit;
-- login as ex_mainuser
-- begin; create table t_mainuser (id integer); commit;
-- login as ex_dbuser
-- select * from t_mainuser; -- ERROR!
-- login as su
-- ALTER DEFAULT PRIVILEGES GRANT INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER ON TABLES TO ex_dbuser;
-- login as ex_mainuser
-- begin; create table t_mainuser2 (id integer); commit;
-- login as ex_dbuser
-- select * from t_mainuser2; -- ERROR!
-- login as su
-- ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER ON TABLES TO ex_dbuser;
-- login as ex_mainuser
-- begin; create table t_mainuser3 (id integer); commit;
-- login as ex_dbuser
-- select * from t_mainuser3; -- ERROR!
Thanks: dd