0

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.

0

Your ALTER DEFAULT PRIVILEGES needs to be for the role who will be creating the schema, so it needs to be FOR ROLE other_role.

1
  • My CREATE SCHEMA will get executed by yet another role, say admin_role, so the ALTER DEFAULT PRIVILEGES actually needs to be issued for the role that will be the owner (as opposed to the creator) of the new_schema, but your answer works perfectly nonetheless. – otterrisk Dec 30 '20 at 18:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.