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 2016-09-14 16:52 GMT+02:00 Adrian Klaver <adrian.kla...@aklaver.com>: > 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. > > > >> Regards >> dd >> >> >> >> >> >> >> >> >> > > -- > Adrian Klaver > adrian.kla...@aklaver.com >
