2

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?

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

1 Answer 1

3

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 and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.