How to create read-only user for schemas which can be created in the future?

Question

SO and Internet contains a lot of answers to question how to create read only user (for example see this article), BUT all of them promote the following way:

1. Create user
2. Grant USAGE privilege to all existing schemas
3. Grant SELECT privilege to all existing tables in all existing schemas
4. Grant default SELECT privilegie to all tables (which can be created in the future) for all existing schemas

But what to do if some schemas can be created in the future too?

I mean I want to grant read only permissions to these schemas (and tables in these schemans) created in the future without any additional configuration. Is it possible?

Answer 1

You can modify template1 to grant read only permissions to all future schemas.

/connect template1
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO username;

Now all future CREATE DATABASE statements will also have the read only permissions applied to them.

CREATE DATABASE dbname; // is the same as CREATE DATABASE dbname TEMPLATE template1;