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
Last activity: Jan 21, 2025, 06:48 PM