I was familiarizing myself with how PostgreSQL (9.6) role management works and ran into a case that seemed to go against my understanding of the documentation.
I have an RDS instance and a master user with the rds_superuser role. I used this role to create a "group" role called 'readwritegroup'. This role has NOLOGIN and CREATEDB granted to it so that anyone who is added to the group may inherit those permissions but cannot actually be used to do things on.
Using my master user I created a database and a new schema inside of it. I granted CREATE and USAGE on the schema to the 'readwritegroup" role. I then created and added 2 separate users to the 'readwritegroup' with only INHERIT as their grants.
I then used the master user to run the following on the schema:
ALTER DEFAULT PRIVILEGES IN SCHEMA test
GRANT ALL PRIVILEGES ON TABLES TO "readwritegroup";
Using these two users I successfully created 1 table for each user and then attempted to SELECT from each user's table using the other user. So they are the owner of their respective tables. They are unable to see each other's tables however. (permission denied)
In fact, even my master user is unable to GRANT any permissions on the tables that they own (though I can change the ownership to my master user).
Is there any way to circumvent the authority of object ownership? I don't really understand why the ALTER DEFAULT PRIVILEGES bit does not correctly modify the behavior to allow other users in the group to SELECT (or whatever grants are used).
