What you will learn
The lab uses an owner role app_owner and read-only role app_reader. Default privileges belong to the role that actually creates objects; they do not retroactively alter existing tables.
Before you begin
Connect to a disposable database as an administrator allowed to create roles and set role to app_owner. Use new role/schema names or adapt the script if they already exist. The owner can be a NOLOGIN role used by a migration account.
1. Create the ownership boundary
CREATE ROLE app_owner NOLOGIN;
CREATE ROLE app_reader NOLOGIN;
CREATE SCHEMA app AUTHORIZATION app_owner;
GRANT USAGE ON SCHEMA app TO app_reader;
Schema USAGE lets a role resolve objects; it does not itself allow SELECT. Grant CONNECT on the database if your database policy has revoked it from PUBLIC.
2. Grant access to existing objects
GRANT SELECT ON ALL TABLES IN SCHEMA app TO app_reader;
This covers tables present now. It does not create a rule for future objects. A writer role would need a separately reviewed set of DML grants and, for relevant sequence-backed inserts, sequence privileges.
3. Define future grants for the creator
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
GRANT SELECT ON TABLES TO app_reader;
Run as app_owner or an authorized administrator. If a migration creates tables as another role, that role’s default privileges apply instead. Membership alone does not make every creator use app_owner’s defaults.
4. Prove the rule with a newly created table
SET ROLE app_owner;
CREATE TABLE app.privilege_probe (id integer PRIMARY KEY);
RESET ROLE;
SET ROLE app_reader;
SELECT * FROM app.privilege_probe;
RESET ROLE;
The empty SELECT should succeed. An attempted INSERT as app_reader should be denied; test that separately so an expected failure does not abort an unrelated transaction.
Example output
The following is an illustrative, normalized lab result, not output captured from a live customer system. Your versions, addresses, timings, and row counts will differ.
-- SELECT as app_reader on the empty probe table
id
----
(0 rows)
-- A separately attempted INSERT as app_reader
ERROR: permission denied for table privilege_probe
An empty successful SELECT and a denied INSERT are both expected for the read-only role. Run the negative test separately so its expected error does not invalidate an unrelated transaction.
Verify the result
Inspect \dp app.* and \ddp in psql. Verify a pre-existing table and a newly created table using the actual application login/membership. Check that read access works and write access remains denied.
Troubleshooting
“Permission denied for schema” points to USAGE; “permission denied for table” points to object grants. If only new tables fail, inspect the creator role. If only old tables fail, apply the explicit existing-table grant.
Recovery and next steps
Reverse an unintended existing grant with REVOKE on the affected objects and adjust ALTER DEFAULT PRIVILEGES for future objects separately. Do not drop shared roles just to undo one grant. Retest using the application role after every permission change.