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:
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;
-- begin; create table t_dbuser (id integer);commit;
-- login as ex_mainuser
-- begin; create table t_mainuser (id integer); commit;
-- begin; create table t_mainuser (id integer); commit;
-- login as ex_dbuser
-- select * from t_mainuser; -- ERROR!
-- select * from t_mainuser; -- ERROR!
-- login as su
-- ALTER DEFAULT PRIVILEGES GRANT INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER ON TABLES TO ex_dbuser;
-- 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;
-- begin; create table t_mainuser2 (id integer); commit;
-- login as ex_dbuser
-- select * from t_mainuser2; -- ERROR!
-- 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;
-- 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;
-- begin; create table t_mainuser3 (id integer); commit;
-- login as ex_dbuser
-- select * from t_mainuser3; -- ERROR!
-- select * from t_mainuser3; -- ERROR!
Thanks: dd
2016-09-14 16:52 GMT+02:00 Adrian Klaver <adrian.klaver@xxxxxxxxxxx>:
On 09/14/2016 06:52 AM, Durumdara wrote:
Dear Charles!
I thought before this test that mainuser get all rights as dbuser, so it
have rights to the next (future) objects too.
So mainuser and dbuser have equivalent rights in db database.
Thanks for your every info!
In my previous post I mentioned using \dp or \z. The output from those commands can be hard to understand without a key, which I forgot to mention. The key can be found here:
https://www.postgresql.org/docs/9.5/static/sql-grant.html
in the Notes section.
