0

We've setup an AWS Database Migration Service which migrates schema and tables from multiple sources.

The rule for migrating from source can be a wildcard on schema, meaning a schema could 'pop up' arbitrarily as it is created in the source.

From all the various granting of privileges I've seen in PostgreSQL, all seem to specify a specific schema, or loops through each existing schema.

Is there not a way to grant certain privileges to every current and future schema in a given database?

Ideally something like this would be great:

ALTER DEFAULT PRIVILEGES FOR ROLE my_role IN SCHEMA "*" GRANT SELECT ON TABLES TO my_role;

I'm aware the super_user role can be granted to a user to make this happen, but that seems awfully dirty for someone who should only have read permissions.

Appreciate the help.

1

Just omit the IN SCHEMA clause, and it applies to all schemas, so no wildcard capability for schemas is needed.

ALTER DEFAULT PRIVILEGES FOR ROLE my_role GRANT SELECT ON TABLES TO my_role;

Your real problem is likely to be the FOR ROLE clause. The above is kind of useless, as it applies only to tables (in any schema) created by my_role. But the creator of tables automatically has select privileges, so this doesn't do anything useful. Your FOR ROLE clause needs to specify who will be creating the tables (if you omit it, it means the current user). If you have multiple such creating roles, you have little option but to repeat the ALTER DEFAULT PRIVILEGES for each one. This is where the wildcard capability is really needed, but sadly does not exist.

| improve this answer | |

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.