Sample Header Ad - 728x90

Best practice for modern (pg16+) role/schema management

0 votes
0 answers
72 views
I'm more of a data engineer than administrator and my role/schema management knowledge is more or less frozen in time since 8.4. I'm aware that pg15 tightened public schema restrictions, and pg14 introduced "predefined roles " as well as scram-sha-256 password encryption by default. This is how I have been creating DBs and roles so far (typically 1:1 name/ownership).
-- as superuser role
CREATE USER test;
ALTER USER test WITH PASSWORD test;
CREATE DATABASE test WITH OWNER test;
\c test
REVOKE ALL ON DATABASE test FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM PUBLIC;
ALTER SCHEMA public OWNER TO test;
I also sometimes create read-only users, which typically involves granting fine-grained SELECT permission on specific objects. I am now using pg16 and trying to get up to speed on the recommended role management strategies. I have three questions: 1) How much of my pre-pg16 DB creation script (see above) is still relevant? Can it be simplified? 2) How can I create a new role with read-only access to public and read-write access to its own schema? For example editor has read-only access to test.public and read-write to test.editor 3) How can I create another role with read-only access to _all_ schemas in a given DB? I don't need anything further, such as row-level security, etc. Thanks in advance!
Asked by Jeff (130 rep)
Jan 20, 2025, 02:34 PM
Last activity: Jan 21, 2025, 06:48 PM