grant usage & privileges on future created schema in PostgreSQL
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');