I need a role to access a schema owned by another role, which can be dropped and re-created in the future. For the first part of my requirement, i.e. for an existing schema, the following would do:
GRANT USAGE ON SCHEMA existing_schema TO my_role;
For the second part of the requirement, i.e. for schemas that don't exist yet but could be created in the future, the ALTER DEFAULT PRIVILEGES command, as documented here, looked promising:
ALTER DEFAULT PRIVILEGES FOR ROLE my_role
GRANT USAGE ON SCHEMAS TO my_role;
but it doesn't work as expected, i.e. after creating a new schema owned by another user:
CREATE SCHEMA new_schema AUTHORIZATION other_role;
and trying to access it as my_role I get:
my_role=> SELECT * FROM new_schema.some_table;
ERROR: permission denied for schema new_schema
What am I doing wrong here?
PS I am aware of this answer but modifying system databases, such as template1 is not an option for me since I am neither the owner nor the only user of the database cluster in question.
