Skip to content
Hands-on IT IT Drills

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; LOGIN turns a role into a user.
  • Whoever creates an object owns it. Everyone else needs privileges – on every level: database (CONNECT), schema (USAGE), object (SELECT …).
  • PUBLIC means “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;
EOF

Expected:

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
EOF

Expected (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*
EOF

Expected (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 PGPASSWORD

Expected (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 PGPASSWORD

Expected (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 PGPASSWORD

Expected (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 PGPASSWORD

Expected (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

  1. Check via SQL whether tag02_alice may read and write in app.kunden. Target time: 30 s
  2. Show all default privileges of the database tag02. Target time: 10 s
  3. Take the role pg_monitor away from tag02_gast again. Target time: 15 s
  4. Show who owns the schema public in tag02. 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 TABLES but doesn’t see new tables – why?” – The GRANT only affects existing tables; for future ones use ALTER DEFAULT PRIVILEGES FOR ROLE <creating role>.
  • “What changed about public in PostgreSQL 15?” – PUBLIC no longer has CREATE on the schema public, the owner is pg_database_owner.
  • “How do you build a read-only user?” – A group role with CONNECT, USAGE, SELECT plus default privileges – or simply pg_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 PUBLIC belongs in the baseline configuration of every new database, as does REVOKE EXECUTE ON FUNCTION … FROM PUBLIC for SECURITY DEFINER functions.
  • 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_*_privilege and \dp; audit logging (e.g. pgaudit) for changes to roles.
  • RLS for tenant isolation only with tests: owners and BYPASSRLS roles see everything, SECURITY DEFINER functions can bypass policies.