Dear Charles!
I checked your solution. For example:
db - database
dbuser, mainuser
1. dbuser own the database, and the objects in it.
2. mainuser member of dbuser.
3. public connection revoked.
Ok.
Then dbuser can see all tables, and mainuser too.
Ok.
The operation (overlord):
1. set role to mainuser (or login).
2. create table test_mainuser(id integer);
3. set role to dbuser (or login).
4. select * from test_mainuser;
Result: Permission denied.
Hmmm... the owner of test_mainuser is mainuser...
Then I dropped the test_mainuser table.
I tried to use default privileges. They are for future, so they must be affected on newly created table.
I set them all.
ALTER DEFAULT PRIVILEGES GRANT INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER ON TABLES to dbuser;
I thought this makes all rights to the newly generated table.
I do the test again, but I got same result.
Why? What I do wrong? (Maybe only my mistake).
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!
Regards
dd