Add privileges to group role without knowing schema name

Question

How to add privileges to a role (act as group) without specifying particular schema. Basically I have a database with a schema (my_first_schema). I create multiple new schemas by cloning my_first_schema. Then create a new database user and GRANT my group privileges to this user.

How to add privileges to group role without knowing schema name (because I create schema dynamically)?

Update I tried to revoke all privileges on all schemas in database and grant select privilege to a dynamically created schema,so that the user can access the a particular schema only. But it revokes all privileges on all tables in all database and I got Permission denied error message after grant SELECT privilege to particular schema for the user.

ALTER DEFAULT PRIVILEGES REVOKE ALL ON TABLES FROM PUBLIC;

ALTER DEFAULT PRIVILEGES IN SCHEMA john_smith_gmail_com GRANT SELECT ON TABLES TO john_smith_gmail_com;

Answer 1

There is ALTER DEFAULT PRIVILEGES, but it's only available for tables, sequences, functions and types. Not for schemas. More in this related answer:
Grant all on a specific schema in the db to a group role in PostgreSQL

However, if you create a schema dynamically, you can add privileges to the role dynamically as well. For instance, if you are doing all of this in a plpgsql function, incorporate this (without the DO wrapper):

DO
$$
BEGIN
EXECUTE format('GRANT ALL ON SCHEMA %I TO mygroup', new_schema);
END
$$

format() requires Postgres 9.1 or later.
Be sure to properly escape identifiers for dynamic SQL.