Grant privileges to tables in all (dynamic) schemas 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.
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.