Usage and Privileges for Future Created Schema in PostgreSQL
The application I'm currently integrating will create new schemas. (each client has its own schema, like schema1, schema2, schema3 .... etc.) To provide read-only access to the newly created schema and specific tables in the schema, I run the following 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'm just wondering if I can provide usage and privileges for a future generated schema in PostgreSQL. Could find ways to change the default privileges for future created tables, but not future created schemas.
source to share
There are no default privileges for schemas. But since you are using a model where each user has their own schema, you can automate the full process, including creating a 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;
Then you can create a user, create a schema, and set up default privileges with a simple command:
SELECT new_user_schema('new_user', 'secret');
source to share