grant usage & privileges on future created schema in PostgreSQL

Question

The application I am integrating now will create new schemas. (each customer has its owned schema, eg. schema1, schema2, schema3 ....etc) To grant usage and read-only access to the new created schema and specific tables in the schema, I execute these commands:

GRANT USAGE ON SCHEMA schema1 TO read_only_user;
GRANT SELECT ON schema1.talbe1 TO read_only_user;
GRANT SELECT ON schema1.table2 TO read_only_user;

GRANT USAGE ON SCHEMA schema2 TO read_only_user;
GRANT SELECT ON schema2.talbe1 TO read_only_user;
GRANT SELECT ON schema2.table2 TO read_only_user;

(......and so on.....)

I just wonder if I could grant usage & privileges on future created schema in PostgreSQL. Could only find ways to alter default privileges on future created tables but not future created schemas.

Patrick Patrick · Accepted Answer · 2015-07-26T10:13:51

There are no default privileges for schemas. But since you are using a model whereby every user has its own schema you can automate the full process, including creating the user and setting a password, if needed:

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;

You can then create the user, create the schema and set up default privileges with a simple command:

SELECT new_user_schema('new_user', 'secret');

grant usage & privileges on future created schema in PostgreSQL

1 Answers