1

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?

  • 1
    Grant proper permissions in event trigger? – Akina May 6 at 10:42
  • I suppose it will works but it's looks like hard solution for such simple problem. – Maxim Dobryakov May 6 at 13:31
  • Are there some existing schemas for which future tables must not be granted on? – jjanes May 6 at 15:10
  • No, read-only user should have read-only access to all schemas and all tables. – Maxim Dobryakov May 7 at 5:22
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;

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.