Day 2: Databases, Schemas, Roles and Privileges
Goal: You create databases with a template and owner, organize objects in schemas, build group roles and grant privileges so that each role can do exactly what it needs – including for tables that don’t exist yet. · Duration: approx. 90 min · Prerequisite: Day 1
Briefing
- A cluster contains databases, a database contains schemas, a schema contains tables, sequences, functions. Roles are cluster-wide.
- “User” and “group” are both roles;
LOGINturns a role into a user. - Whoever creates an object owns it. Everyone else needs privileges –
on every level: database (
CONNECT), schema (USAGE), object (SELECT…). PUBLICmeans “all roles, including future ones” and has more privileges out of the box than you might think.
role --CONNECT--> database --USAGE--> schema --SELECT/INSERT--> table
--USAGE---------> sequence
Everything on pg1 unless it says pg2.
Database tag02, roles with the prefix
tag02_.
Drill 1: Database from a template with an owner
CREATE DATABASE copies a template, by default
template1. Your own templates carry a basic setup into
every new database:
psql <<'EOF'
CREATE ROLE tag02_owner NOLOGIN;
CREATE DATABASE tag02_vorlage;
\c tag02_vorlage
CREATE TABLE einstellungen (schluessel text PRIMARY KEY, wert text);
INSERT INTO einstellungen VALUES ('version', '1');
\c postgres
CREATE DATABASE tag02 TEMPLATE tag02_vorlage OWNER tag02_owner;
EOF
psql -d tag02 -c '\dt' -c 'TABLE einstellungen'Expected (abridged):
CREATE DATABASE
Schema | Name | Type | Owner
--------+---------------+-------+--------
public | einstellungen | table | ubuntu
schluessel | wert
------------+------
version | 1
The database belongs to tag02_owner, but the copied
table still belongs to ubuntu – OWNER applies
only to the database itself. A template must not have any connections
while it is copied:
psql -d tag02_vorlage -c 'SELECT pg_sleep(12)' >/dev/null &
sleep 1
psql -c 'CREATE DATABASE tag02_kopie TEMPLATE tag02_vorlage'
wait
psql -c 'ALTER DATABASE tag02_vorlage IS_TEMPLATE true'
psql -c 'DROP DATABASE tag02_vorlage'Expected:
ERROR: source database "tag02_vorlage" is being accessed by other users
DETAIL: There is 1 other session using the database.
ALTER DATABASE
ERROR: cannot drop a template database
CREATE DATABASE waits five seconds, then gives up.
template0 is the untouched reserve
(datallowconn = false) – you use it for a different
encoding or locale and for restoring a pg_dump (Day 8).
IS_TEMPLATE protects against accidental deletion and lets
non-superusers with CREATEDB use the database as a
template.
Save your progress? Sign up for free
Drill 2: Schemas and search_path
SET ROLE switches identity – a superuser may assume any
role. That way the owner role creates its objects itself:
psql -d tag02 <<'EOF'
SET ROLE tag02_owner;
SHOW search_path;
CREATE SCHEMA app;
CREATE TABLE app.einstellungen (schluessel text PRIMARY KEY, wert text);
INSERT INTO app.einstellungen VALUES ('version', '2');
SELECT wert FROM einstellungen;
EOFExpected:
SET
search_path
-----------------
"$user", public
(1 row)
CREATE SCHEMA
CREATE TABLE
INSERT 0 1
ERROR: permission denied for table einstellungen
As database owner, tag02_owner may create schemas – but
not read the table from the template, since it belongs to
ubuntu. Transfer ownership, then change the search
path:
psql -d tag02 <<'EOF'
ALTER TABLE public.einstellungen OWNER TO tag02_owner;
SET ROLE tag02_owner;
SELECT wert FROM einstellungen;
SET search_path = app, public;
SELECT wert FROM einstellungen;
\dt *.einstellungen
EOFExpected (abridged):
wert
------
1
SET
wert
------
2
Schema | Name | Type | Owner
--------+---------------+-------+-------------
app | einstellungen | table | tag02_owner
public | einstellungen | table | tag02_owner
PostgreSQL looks up unqualified names in order along the
search_path; the first hit wins, new objects land in the
first existing schema. "$user" stands for a schema with the
role’s name. Set the path for all logins to this database:
psql -d tag02 -c 'ALTER DATABASE tag02 SET search_path = app, public' -c '\drds'Expected:
ALTER DATABASE
List of settings
Role | Database | Settings
------+----------+-------------------------
| tag02 | search_path=app, public
(1 row)
This takes effect from the next login.
ALTER ROLE … IN DATABASE … SET works per role – but only on
a real login, not with SET ROLE.
Save your progress? Sign up for free
Drill 3: Roles, groups and INHERIT
You grant privileges to group roles; people and applications become members:
psql -d tag02 <<'EOF'
CREATE ROLE tag02_lesen NOLOGIN;
CREATE ROLE tag02_schreiben NOLOGIN IN ROLE tag02_lesen;
CREATE ROLE tag02_alice LOGIN PASSWORD 'lab-alice-123' IN ROLE tag02_lesen;
CREATE ROLE tag02_bob LOGIN NOINHERIT PASSWORD 'lab-bob-123' IN ROLE tag02_schreiben;
\du tag02*
\drg tag02*
EOFExpected (abridged):
Role name | Attributes
-----------------+----------------
tag02_alice |
tag02_bob | No inheritance
tag02_lesen | Cannot login
tag02_owner | Cannot login
tag02_schreiben | Cannot login
Role name | Member of | Options | Grantor
-----------------+-----------------+--------------+----------
tag02_alice | tag02_lesen | INHERIT, SET | postgres
tag02_bob | tag02_schreiben | SET | postgres
tag02_schreiben | tag02_lesen | INHERIT, SET | postgres
The passwords are lab values. \drg (since PostgreSQL 16)
shows each membership with its options: INHERIT – the
group’s privileges apply automatically; SET – the role may
become the group via SET ROLE. tag02_bob
inherits nothing, he has to assume the group deliberately. This also
works per membership:
GRANT tag02_lesen TO x WITH INHERIT FALSE, SET TRUE.
Save your progress? Sign up for free
Drill 4: GRANT on schema, table, sequence
Now tag02_alice really logs in – over TCP on
127.0.0.1, where scram-sha-256 applies:
export PGPASSWORD=lab-alice-123
A="host=127.0.0.1 dbname=tag02 user=tag02_alice"
psql "$A" -c 'SELECT * FROM app.einstellungen'
psql -d tag02 -c 'GRANT USAGE ON SCHEMA app TO tag02_lesen'
psql "$A" -c 'SELECT * FROM app.einstellungen'
psql -d tag02 -c 'GRANT SELECT ON ALL TABLES IN SCHEMA app TO tag02_lesen'
psql "$A" -c 'SHOW search_path' -c 'SELECT wert FROM einstellungen'
unset PGPASSWORDExpected (abridged):
ERROR: permission denied for schema app
LINE 1: SELECT * FROM app.einstellungen
^
GRANT
ERROR: permission denied for table einstellungen
GRANT
search_path
-------------
app, public
wert
------
2
Level by level: first the schema, then the table. The
search_path from Drill 2 takes effect without
tag02_alice having to do anything. Now
tag02_bob writes to a table with a serial
column:
psql -d tag02 <<'EOF'
SET ROLE tag02_owner;
CREATE TABLE app.protokoll (id serial PRIMARY KEY, eintrag text NOT NULL);
GRANT INSERT ON app.protokoll TO tag02_schreiben;
EOF
export PGPASSWORD=lab-bob-123
B="host=127.0.0.1 dbname=tag02 user=tag02_bob"
psql "$B" -c "INSERT INTO protokoll (eintrag) VALUES ('bob war da')"
psql "$B" -c 'SET ROLE tag02_schreiben' \
-c "INSERT INTO protokoll (eintrag) VALUES ('bob war da')"Expected:
ERROR: relation "protokoll" does not exist
LINE 1: INSERT INTO protokoll (eintrag) VALUES ('bob war da')
^
SET
ERROR: permission denied for sequence protokoll_id_seq
Without INHERIT, tag02_bob doesn’t even
have USAGE on app – name lookup skips schemas
without USAGE, hence “does not exist” instead of
“permission denied”. After SET ROLE, the privilege to
advance the sequence is still missing:
psql -d tag02 -c 'GRANT USAGE ON SEQUENCE app.protokoll_id_seq TO tag02_schreiben'
psql "$B" -c 'SET ROLE tag02_schreiben' \
-c "INSERT INTO protokoll (eintrag) VALUES ('bob war da')" \
-c 'SELECT current_user, session_user' -c 'RESET ROLE' -c 'SELECT current_user'
unset PGPASSWORDExpected (abridged):
SET
INSERT 0 1
current_user | session_user
-----------------+--------------
tag02_schreiben | tag02_bob
RESET
current_user
--------------
tag02_bob
session_user remains the logged-in user,
current_user determines the privileges. Identity columns
(GENERATED … AS IDENTITY) don’t need a separate sequence
privilege – one more reason to use them instead of
serial.
Save your progress? Sign up for free
Drill 5: ALTER DEFAULT PRIVILEGES
GRANT … ON ALL TABLES applies only to tables that exist
now:
export PGPASSWORD=lab-alice-123
psql -d tag02 -c 'SET ROLE tag02_owner' \
-c 'CREATE TABLE app.kunden (id int PRIMARY KEY, name text)'
psql "$A" -c 'SELECT count(*) FROM kunden'
psql -d tag02 <<'EOF'
ALTER DEFAULT PRIVILEGES FOR ROLE tag02_owner IN SCHEMA app
GRANT SELECT ON TABLES TO tag02_lesen;
ALTER DEFAULT PRIVILEGES FOR ROLE tag02_owner IN SCHEMA app
GRANT INSERT, UPDATE, DELETE ON TABLES TO tag02_schreiben;
ALTER DEFAULT PRIVILEGES FOR ROLE tag02_owner IN SCHEMA app
GRANT USAGE ON SEQUENCES TO tag02_schreiben;
SET ROLE tag02_owner;
CREATE TABLE app.auftraege (id int PRIMARY KEY, kunde int);
\ddp
EOF
psql "$A" -c 'SELECT count(*) FROM auftraege'
psql -d tag02 -c 'CREATE TABLE app.von_ubuntu (x int)'
psql "$A" -c 'SELECT count(*) FROM von_ubuntu'
psql -d tag02 -c 'DROP TABLE app.von_ubuntu'
unset PGPASSWORDExpected (abridged):
ERROR: permission denied for table kunden
ALTER DEFAULT PRIVILEGES
...
Default access privileges
Owner | Schema | Type | Access privileges
-------------+--------+----------+---------------------------------
tag02_owner | app | sequence | tag02_schreiben=U/tag02_owner
tag02_owner | app | table | tag02_lesen=r/tag02_owner +
| | | tag02_schreiben=awd/tag02_owner
count
-------
0
CREATE TABLE
ERROR: permission denied for table von_ubuntu
Default privileges are tied to the role that creates
the object (FOR ROLE). If someone else creates the table –
here ubuntu – they don’t apply. That’s why migrations
always run as the same owner role. The abbreviations: r
SELECT, a INSERT, w UPDATE, d
DELETE, U USAGE.
Save your progress? Sign up for free
Drill 6: The PUBLIC trap
A fresh role without any membership:
psql -d tag02 <<'EOF'
CREATE ROLE tag02_gast LOGIN PASSWORD 'lab-gast-123';
SELECT has_database_privilege('tag02_gast', 'tag02', 'CONNECT') AS connect,
has_database_privilege('tag02_gast', 'tag02', 'TEMP') AS temp,
has_schema_privilege('tag02_gast', 'public', 'USAGE') AS usage_public,
has_schema_privilege('tag02_gast', 'public', 'CREATE') AS create_public;
\dn+ public
EOF
export PGPASSWORD=lab-gast-123
G="host=127.0.0.1 dbname=tag02 user=tag02_gast"
psql "$G" -c 'CREATE TABLE public.muell (x int)' -c 'CREATE TEMP TABLE t (x int)'Expected (abridged):
connect | temp | usage_public | create_public
---------+------+--------------+---------------
t | t | t | f
Name | Owner | Access privileges | ...
--------+-------------------+----------------------------------------+ ...
public | pg_database_owner | pg_database_owner=UC/pg_database_owner+| ...
| | =U/pg_database_owner | ...
ERROR: permission denied for schema public
LINE 1: CREATE TABLE public.muell (x int)
^
CREATE TABLE
Out of the box, PUBLIC may enter every database
(CONNECT), create temporary tables (TEMP) and
execute all functions (EXECUTE). Since PostgreSQL 15, the
schema public belongs to the role
pg_database_owner (that is, the database owner), and
PUBLIC only has USAGE there (=U),
no CREATE anymore. Up to 14, anyone could create tables in
public. You lock down database access yourself:
psql -d tag02 <<'EOF'
REVOKE CONNECT, TEMPORARY ON DATABASE tag02 FROM PUBLIC;
GRANT CONNECT ON DATABASE tag02 TO tag02_lesen, tag02_owner;
EOF
psql "$G" -c 'SELECT 1'
PGPASSWORD=lab-alice-123 psql "$A" -Atc 'SELECT current_user'
PGPASSWORD=lab-bob-123 psql "$B" -Atc 'SELECT current_user'Expected:
REVOKE
GRANT
psql: error: connection to server at "127.0.0.1", port 5432 failed: FATAL:
permission denied for database "tag02"
DETAIL: User does not have CONNECT privilege.
tag02_alice
psql: error: connection to server at "127.0.0.1", port 5432 failed: FATAL:
permission denied for database "tag02"
DETAIL: User does not have CONNECT privilege.
NOINHERIT also applies to CONNECT:
tag02_bob doesn’t inherit it from his group and needs it
directly.
psql -d tag02 -c 'GRANT CONNECT ON DATABASE tag02 TO tag02_bob' \
-Atc "SELECT datacl FROM pg_database WHERE datname = 'tag02'"
PGPASSWORD=lab-bob-123 psql "$B" -Atc 'SELECT current_user'Expected:
GRANT
{tag02_owner=CTc/tag02_owner,tag02_lesen=c/tag02_owner,tag02_bob=c/tag02_owner}
tag02_bob
c is CONNECT, C CREATE, T
TEMPORARY. No entry for PUBLIC (=c/…)
anymore.
Save your progress? Sign up for free
Drill 7: Predefined roles and the notes-api problem
PostgreSQL ships roles for typical tasks. Instead of distributing
privileges on every schema, pg_read_all_data grants read
access to everything, pg_monitor insight into
statistics:
psql -Atc "SELECT rolname FROM pg_roles WHERE rolname LIKE 'pg\_%' ORDER BY 1" \
| paste -sd' ' | fold -s -w 80
psql -d tag02 -c 'GRANT CONNECT ON DATABASE tag02 TO tag02_gast'
psql "$G" -c 'SELECT count(*) FROM app.kunden'
psql -d tag02 -c 'SELECT pg_sleep(3)' >/dev/null &
sleep 1
psql "$G" -Atc "SELECT usename, query FROM pg_stat_activity WHERE usename = 'ubuntu'"
wait
psql -d tag02 -c 'GRANT pg_read_all_data, pg_monitor TO tag02_gast'
psql "$G" -c 'SELECT count(*) FROM app.kunden' \
-c "INSERT INTO app.kunden VALUES (1, 'x')"
psql -d tag02 -c 'SELECT pg_sleep(3)' >/dev/null &
sleep 1
psql "$G" -Atc "SELECT usename, query FROM pg_stat_activity WHERE usename = 'ubuntu'"
wait
unset PGPASSWORDExpected (abridged):
pg_checkpoint pg_create_subscription pg_database_owner
pg_execute_server_program pg_maintain pg_monitor pg_read_all_data
pg_read_all_settings pg_read_all_stats pg_read_server_files
pg_signal_autovacuum_worker pg_signal_backend pg_stat_scan_tables
pg_use_reserved_connections pg_write_all_data pg_write_server_files
GRANT
ERROR: permission denied for schema app
ubuntu|<insufficient privilege>
GRANT ROLE
count
-------
0
ERROR: permission denied for table kunden
ubuntu|SELECT pg_sleep(3)
pg_read_all_data reads everything, writes nothing
(that’s what pg_write_all_data is for). Without
pg_monitor, a role sees only its own queries in
pg_stat_activity – ideal for a monitoring user like the
postgres_exporter on Day 7. pg_maintain (since
17) allows VACUUM, ANALYZE,
REINDEX without a superuser.
A problem you will run into with notes-api:
notes-api migrate fails as notes_app. On
pg2, the same statement the application sends:
psql service=notes -c 'CREATE TABLE IF NOT EXISTS notes (id uuid PRIMARY KEY,
text text NOT NULL, created_at timestamptz NOT NULL DEFAULT now())'Expected:
ERROR: permission denied for schema public
LINE 1: CREATE TABLE IF NOT EXISTS notes (id uuid PRIMARY KEY,
^
The table has long existed, yet PostgreSQL first checks the
CREATE privilege on the schema. Grant the privilege on
pg1, repeat on pg2:
psql -d notes -c 'GRANT CREATE ON SCHEMA public TO notes_app'psql service=notes -c 'CREATE TABLE IF NOT EXISTS notes (id uuid PRIMARY KEY,
text text NOT NULL, created_at timestamptz NOT NULL DEFAULT now())'Expected:
NOTICE: relation "notes" already exists, skipping
CREATE TABLE
That’s the narrowest fix for an application that creates its own tables. Cleaner is a dedicated migration role as owner of the schema and tables, with the application running under a pure read/write role (default privileges from Drill 5). Part 2 implements this in CloudNativePG.
Save your progress? Sign up for free
Drill 8: Row Level Security and proving privileges
RLS filters rows per role – on top of table privileges:
psql -d tag02 <<'EOF'
SET ROLE tag02_owner;
CREATE TABLE app.notizen (
id serial PRIMARY KEY,
besitzer name NOT NULL DEFAULT current_user,
text text NOT NULL);
INSERT INTO app.notizen (besitzer, text) VALUES
('tag02_alice', 'Notiz von Alice'), ('tag02_bob', 'Notiz von Bob');
ALTER TABLE app.notizen ENABLE ROW LEVEL SECURITY;
CREATE POLICY nur_eigene ON app.notizen USING (besitzer = current_user);
SELECT count(*) FROM app.notizen;
EOF
PGPASSWORD=lab-alice-123 psql "$A" -c 'SELECT besitzer, text FROM notizen'
PGPASSWORD=lab-gast-123 psql "$G" -Atc 'SELECT count(*) FROM app.notizen'Expected (abridged):
count
-------
2
besitzer | text
-------------+-----------------
tag02_alice | Notiz von Alice
(1 row)
0
The owner sees everything (RLS applies to the owner only with
FORCE ROW LEVEL SECURITY), tag02_alice sees
only her row – the SELECT privilege came via the default
privileges. pg_read_all_data doesn’t bypass RLS either;
only the BYPASSRLS attribute can do that. Proving the
privileges:
psql -d tag02 -c '\dp app.notizen'
psql -d tag02 -c '\d app.notizen' | sed -n '/Policies/,$p'
psql -d tag02 -c "SELECT r.rolname,
has_table_privilege(r.rolname, 'app.notizen', 'SELECT') AS lesen,
has_table_privilege(r.rolname, 'app.notizen', 'INSERT') AS schreiben
FROM pg_roles r WHERE r.rolname LIKE 'tag02\_%' ORDER BY 1"Expected (abridged):
Schema | Name | Type | Access privileges | Column privileges ...
--------+---------+-------+----------------------------------+------------------ ...
app | notizen | table | tag02_owner=arwdDxtm/tag02_owner+| ...
| | | tag02_lesen=r/tag02_owner +| ...
| | | tag02_schreiben=awd/tag02_owner | ...
Policies:
POLICY "nur_eigene"
USING ((besitzer = CURRENT_USER))
rolname | lesen | schreiben
-----------------+-------+-----------
tag02_alice | t | f
tag02_bob | f | f
tag02_gast | t | f
tag02_lesen | t | f
tag02_owner | t | t
tag02_schreiben | t | t
has_table_privilege answers “may role X do this?”
including inherited privileges – tag02_bob gets
f because he inherits nothing without
SET ROLE. For the owner, D stands for
TRUNCATE, x REFERENCES, t TRIGGER and
m MAINTAIN (since 17).
Save your progress? Sign up for free
Exercise: Speed Drills
- Check via SQL whether
tag02_alicemay read and write inapp.kunden. Target time: 30 s - Show all default privileges of the database
tag02. Target time: 10 s - Take the role
pg_monitoraway fromtag02_gastagain. Target time: 15 s - Show who owns the schema
publicintag02. Target time: 10 s
Solution
psql -d tag02 -c "SELECT has_table_privilege('tag02_alice', 'app.kunden', 'SELECT'),
has_table_privilege('tag02_alice', 'app.kunden', 'INSERT')"
psql -d tag02 -c '\ddp'
psql -d tag02 -c 'REVOKE pg_monitor FROM tag02_gast'
psql -d tag02 -c '\dn public'Expected (abridged):
has_table_privilege | has_table_privilege
---------------------+---------------------
f | f
...
REVOKE ROLE
Name | Owner
--------+-------------------
public | pg_database_owner
app.kunden was created after the
GRANT … ON ALL TABLES and before the default privileges –
hence no read privilege for tag02_alice.
Save your progress? Sign up for free
Checkpoint
psql -d tag02 -Atc "SELECT pg_get_userbyid(datdba) FROM pg_database
WHERE datname = 'tag02'"Expected: tag02_owner
psql -d tag02 -Atc "SELECT has_database_privilege('tag02_gast', 'tag02', 'TEMP')"Expected: f – PUBLIC no
longer has TEMPORARY.
PGPASSWORD=lab-alice-123 psql "host=127.0.0.1 dbname=tag02 user=tag02_alice" \
-Atc 'SELECT count(*) FROM notizen'Expected: 1 – RLS shows only her own
note.
psql -d tag02 -Atc "SELECT count(*) FROM pg_default_acl"Expected: 2 (tables and sequences for
tag02_owner in app).
psql -d notes -Atc "SELECT has_schema_privilege('notes_app', 'public', 'CREATE')"Expected: t – until cleanup.
Challenge
Remove the group role tag02_schreiben.
DROP ROLE alone fails – find out why and clean up so that
no trace of the role remains afterwards.
Hint: A role with privileges or objects can only be dropped once those are gone; there is a command that does exactly that per database.
Solution
psql -d tag02 -c 'DROP ROLE tag02_schreiben'
psql -d tag02 -c 'DROP OWNED BY tag02_schreiben' -c 'DROP ROLE tag02_schreiben' \
-c '\ddp'Expected:
ERROR: role "tag02_schreiben" cannot be dropped because some objects depend on it
DETAIL: privileges for sequence protokoll_id_seq
privileges for table protokoll
privileges for default privileges on new relations belonging to role tag02_owner in
schema app
privileges for default privileges on new sequences belonging to role tag02_owner in
schema app
privileges for table auftraege
privileges for sequence notizen_id_seq
privileges for table notizen
DROP OWNED
DROP ROLE
Default access privileges
Owner | Schema | Type | Access privileges
-------------+--------+-------+---------------------------
tag02_owner | app | table | tag02_lesen=r/tag02_owner
DROP OWNED BY drops all of the role’s objects in the
current database and revokes all its privileges, including default
privileges. If it owns objects that should stay, run
REASSIGN OWNED BY role TO other_role first. If the role has
privileges in several databases, you have to do this in each one.
Save your progress? Sign up for free
Cleanup
Revoke the schema privilege for notes_app again – the
following days assume the state from Day 0. Then drop the database,
template and roles:
psql -d notes -c 'REVOKE CREATE ON SCHEMA public FROM notes_app'
psql -c 'DROP DATABASE tag02'
psql -c 'ALTER DATABASE tag02_vorlage IS_TEMPLATE false'
psql -c 'DROP DATABASE tag02_vorlage'
psql -c 'DROP ROLE tag02_alice, tag02_bob, tag02_gast, tag02_lesen, tag02_owner'Expected: REVOKE,
DROP DATABASE twice, ALTER DATABASE,
DROP ROLE.
Interview Note
- “A new user gets
GRANT SELECT ON ALL TABLESbut doesn’t see new tables – why?” – TheGRANTonly affects existing tables; for future ones useALTER DEFAULT PRIVILEGES FOR ROLE <creating role>. - “What changed about
publicin PostgreSQL 15?” –PUBLICno longer hasCREATEon the schemapublic, the owner ispg_database_owner. - “How do you build a read-only user?” – A group role with
CONNECT,USAGE,SELECTplus default privileges – or simplypg_read_all_data, which however applies to all databases.
Enterprise Note
- Three levels of roles: owner (migrations,
NOLOGIN), groups (_ro,_rw), logins for people and services – people never with direct privileges on tables. REVOKE ALL ON DATABASE … FROM PUBLICbelongs in the baseline configuration of every new database, as doesREVOKE EXECUTE ON FUNCTION … FROM PUBLICforSECURITY DEFINERfunctions.- Privileges as code: roles and grants in migrations or tools such as
Terraform, declaratively in CloudNativePG (
managed.roles, Day 17). - Regular privilege reviews with
has_*_privilegeand\dp; audit logging (e.g. pgaudit) for changes to roles. - RLS for tenant isolation only with tests: owners and
BYPASSRLSroles see everything,SECURITY DEFINERfunctions can bypass policies.