PostgreSQL - Grant select on all tables (and future tables), in *all schemas*

Question

My database uses schemas to separate tenant-specific data for an application - each customer has a brand new schema created when they sign up to hold their data.

This obviously means I don't know the name of all schemas ahead of time.

I'd like to create a readonly role that has read access to all of these schemas, and those created in the future.

All questions along these lines I've found require knowing the name of the schemas, e.g.:

-- Create a group
CREATE ROLE readaccess;

-- Grant access to existing tables
GRANT USAGE ON SCHEMA a_given_schema TO readaccess;
GRANT SELECT ON ALL TABLES IN SCHEMA a_given_schema TO readaccess;

-- Grant access to future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA a_given_schema GRANT SELECT ON TABLES TO readaccess;

-- Create user with password
CREATE USER readonly WITH PASSWORD 'a_secret';
GRANT readaccess TO readonly;

Is there a way to do something like this, but for all schemas in the future?

Answer 1

You can't make that, as said here : grant usage & privileges on future created schema in PostgreSQL

The best is to think the other way: every time you create a schema, you GRANT the role at the same time: (Take a look at the link for more information)

CREATE FUNCTION new_user_schema (user text, pwd text) RETURNS void AS $$
DECLARE
  usr name;
  sch name;
BEGIN
  -- Create the user
  usr := quote_identifier(user);
  EXECUTE format('CREATE ROLE %I LOGIN PASSWORD %L', usr, quote_literal(pwd));

  -- Create the schema named after the user and set default privileges
  sch := quote_identifier('sch_' || user);
  EXECUTE format('CREATE SCHEMA %I', sch);
  EXECUTE format('ALTER SCHEMA %I OWNER TO %L', sch, usr);
  EXECUTE format('ALTER DEFAULT PRIVILEGES IN SCHEMA %I
                    GRANT SELECT ON TABLES TO %L', sch, usr);
END; $$ LANGUAGE plpgsql STRICT;