Grant privileges to tables in all (dynamic) schemas

Question

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.

Answer 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.

Answer 2

If your role does not have USAGE permission on the schema beforehand, granting SELECT ON TABLES will not work. Schema permissions need to be settled first.

A user should have schema usage permissions by default if they created the schema, otherwise, they will have to be granted the permission either explicitly for existing schemas, or in a future-proof way by altering their default permissions (per jjanes` answer).

As of Postgres 10, you can grant usage on future schemas as follows: ALTER DEFAULT PRIVILEGES GRANT USAGE ON SCHEMAS to [YOUR_ROLE or YOUR_USER];

Once you've squared that away, you can grant the SELECT permission on all tables for the entity.

ALTER DEFAULT PRIVILEGES GRANT SELECT ON TABLES to [YOUR_ROLE or YOUR_USER];

Here are the docs for v13