This is a role-boundary lab, not a “become superuser from SQL” note. Target is PostgreSQL 16 in Docker, databases lab with roles alice (login, no inherit of dba) and appdba (owns functions). Goal: \du the cluster, write a safe SECURITY DEFINER function that only returns alice’s row count, and show alice fail to CREATE EXTENSION. I do not COPY TO PROGRAM, I do not load an untrusted C extension, I do not plant search_path gadgets that wrap public.

1Figure 1. SECURITY DEFINER runs as the owner. That is a privilege boundary, not a convenience flag.
2alice  --EXECUTE-->  my_balance() DEFINER appdba
3                     search_path pinned; session_user bind
4alice  CREATE EXTENSION => permission denied

Lab layout

1labs/pg_lab/
2  docker-compose.yml     # postgres:16-alpine, port 127.0.0.1:5432
3  seed.sql

 1-- seed.sql
 2CREATE ROLE alice LOGIN PASSWORD 'lab';
 3CREATE ROLE appdba LOGIN PASSWORD 'lab';
 4CREATE DATABASE lab OWNER appdba;
 5\c lab
 6REVOKE ALL ON SCHEMA public FROM PUBLIC;
 7GRANT USAGE ON SCHEMA public TO alice, appdba;
 8GRANT CREATE ON SCHEMA public TO appdba;   -- not to alice
 9CREATE TABLE public.accounts (
10  id int PRIMARY KEY,
11  owner name NOT NULL,
12  balance int NOT NULL
13);
14INSERT INTO accounts VALUES (1, 'alice', 100), (2, 'bob', 100);
15ALTER TABLE accounts OWNER TO appdba;
16GRANT SELECT ON accounts TO alice;         -- column-level later

1$ psql -h 127.0.0.1 -U postgres -f seed.sql

Artifact: \du and object owners

 1lab=# \du
 2                                   List of roles
 3 Role name |                         Attributes                         | Member of
 4-----------+------------------------------------------------------------+-----------
 5 alice     |                                                            | {}
 6 appdba    |                                                            | {}
 7 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 8
 9lab=# \dt+
10 Schema |   Name   | Type  | Owner  |  Size   | Description
11--------+----------+-------+--------+---------+-------------
12 public | accounts | table | appdba | 16 kB   |

alice has no Superuser, no Create DB, no Create role. Member of {}. That is the baseline. A role with CREATE ROLE is a nearly-superuser in practice; I do not grant it.

1$ psql -h 127.0.0.1 -U alice -d lab -c 'SELECT current_user, session_user, current_setting(''is_superuser'')'
2 current_user | session_user | current_setting
3--------------+--------------+-----------------
4 alice        | alice        | off

Safe SECURITY DEFINER example

The dangerous pattern is SECURITY DEFINER plus a dynamic SQL string plus a search_path the caller can influence. The safe pattern: fixed SQL, SET search_path on the function, owner is appdba, grant execute only to alice, function does not touch other owners’ rows.

 1-- as appdba
 2CREATE OR REPLACE FUNCTION public.my_balance()
 3RETURNS int
 4LANGUAGE sql
 5SECURITY DEFINER
 6SET search_path = pg_catalog, public
 7AS $$
 8  SELECT balance FROM public.accounts
 9   WHERE owner = session_user;
10$$;
11
12REVOKE ALL ON FUNCTION public.my_balance() FROM PUBLIC;
13GRANT EXECUTE ON FUNCTION public.my_balance() TO alice;

session_user is the login role, even inside DEFINER. current_user would be appdba during the call. Using session_user is the authorization bind.

 1$ psql -h 127.0.0.1 -U alice -d lab -c 'SELECT public.my_balance();'
 2 my_balance
 3------------
 4        100
 5
 6$ psql -h 127.0.0.1 -U alice -d lab -c "SELECT balance FROM accounts WHERE owner='bob';"
 7 balance
 8---------
 9     100
10# alice has table SELECT in this seed — too broad; next tick tightens it

Tighten:

1REVOKE SELECT ON public.accounts FROM alice;
2-- alice can still:
3SELECT public.my_balance();  -- 100
4SELECT * FROM public.accounts;
5-- ERROR:  permission denied for table accounts

That pair is the DEFINER contract: a narrow function, table not directly readable. I did not write a function that takes a table name or an owner argument from the caller.

Unsafe shape I only show as a comment, not as a created object:

1-- NOT CREATED. Reviewer grep target.
2-- SECURITY DEFINER function that does EXECUTE format('SELECT * FROM %I', user_arg)
3-- with search_path left default → classic definer gadget.
4-- Fix: no dynamic SQL, SET search_path, bind session_user.

Extension trust

1$ psql -h 127.0.0.1 -U alice -d lab -c 'CREATE EXTENSION IF NOT EXISTS adminpack;'
2ERROR:  permission denied to create extension "adminpack"
3HINT:  Must be superuser to create this extension.
4
5$ psql -h 127.0.0.1 -U postgres -d lab -c '\dx'
6                 List of installed extensions
7  Name   | Version |   Schema   |         Description
8---------+---------+------------+------------------------------
9 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language

adminpack / file_fdw / untrusted PLs are superuser territory. CREATE EXTENSION from alice failing is the artifact. On the host, superuser can COPY TO PROGRAM — I do not run it. The OS-adjacent lesson: superuser is root of the postgres process, which in this lab is the container; on a host install it is the postgres OS user.

1$ docker exec pg_lab capsh --print | head -2
2# the postgres *container* still has whatever Docker gave it
3# see container-escape note; do not run postgres --privileged

shared_preload_libraries and local_preload_libraries are extra load paths. I dump:

1lab=# SHOW shared_preload_libraries;
2 shared_preload_libraries
3--------------------------
4 (empty)

Non-empty on a box I do not own is an inventory item, not automatically bad (pg_stat_statements is fine). A library path outside the package dir is a finding.

Sanitized reproduction (denied / crash only)

alice tries to become owner:

1$ psql -h 127.0.0.1 -U alice -d lab -c 'ALTER TABLE public.accounts OWNER TO alice;'
2ERROR:  must be owner of table accounts

1$ psql -h 127.0.0.1 -U alice -d lab -c 'CREATE ROLE bob LOGIN;'
2ERROR:  permission denied to create role

Failed-auth:

1$ psql -h 127.0.0.1 -U alice -d lab -c 'SELECT 1'
2# password prompt, wrong:
3psql: error: connection to server at "127.0.0.1", port 5432 failed: FATAL:  password authentication failed for user "alice"
4# log:
5# 2025-11-29 12:18:01.441 UTC [42] FATAL:  password authentication failed for user "alice"
6# 2025-11-29 12:18:01.441 UTC [42] DETAIL:  Connection matched pg_hba.conf line 1: "host all all 127.0.0.1/32 scram-sha-256"

ASAN analog — a tiny C client that overflows a stack buffer while building SQL (the app, not Postgres):

1/* q.c */
2#include <stdio.h>
3#include <string.h>
4int main(int argc, char **argv) {
5    char q[32];
6    snprintf(q, sizeof(q), "SELECT %s", argv[1]);
7    puts(q);
8}

1$ clang -fsanitize=address -g -o q q.c
2$ ./q $(python3 -c 'print("A"*80)')
3AddressSanitizer: stack-buffer-overflow WRITE of size 81 at 0x[REDACTED]
4    #0 snprintf
5    #1 main q.c:6
6# Postgres never saw the string. App builder bug.

DoS-shaped: I do not SELECT pg_terminate_backend as alice (she cannot). alice running SELECT pg_sleep(60) is a resource issue; I statement_timeout=2s in the lab.

1ALTER ROLE alice SET statement_timeout = '2s';

Mitigation

  • Least privilege: login roles without SUPERUSER, CREATEDB, CREATEROLE, REPLICATION.
  • REVOKE CREATE ON SCHEMA public FROM PUBLIC (Postgres 15+ default; I still set it).
  • SECURITY DEFINER: fixed SQL, SET search_path = pg_catalog, public, session_user for authz, REVOKE FROM PUBLIC, grant execute per role.
  • Extensions: only superuser, inventory \dx, no untrusted languages (plpythonu) on this cluster.
  • Authn: scram-sha-256, pg_hba.conf not trust on TCP. Lab log line above is the shape.
  • Host: run postgres not as OS root, not --privileged, data dir 700.
  • RLS if tenant tables share a relation; owner and superuser bypass RLS unless FORCE ROW LEVEL SECURITY.
1ALTER TABLE accounts ENABLE ROW LEVEL SECURITY;
2CREATE POLICY accounts_self ON accounts
3  FOR SELECT TO alice USING (owner = session_user);
4-- still keep the REVOKE SELECT if the function is the only API

What I file after this lab

  • \du: alice (no attrs), appdba (no attrs), postgres superuser
  • accounts owner appdba; alice SELECT revoked after the first demo
  • my_balance() SECURITY DEFINER, search_path pinned, returns 100 for alice, table permission denied
  • CREATE EXTENSION as alice: permission denied
  • Wrong password: FATAL scram, pg_hba line [REDACTED]
  • App q.c ASAN overflow — not a Postgres bug
  • Fix: no PUBLIC create, narrow DEFINER, no superuser for apps, scram, timeout
  • Out of scope: COPY TO PROGRAM, C extension, search_path planting against a DBA session

Commands appendix

1psql -h 127.0.0.1 -U postgres -c '\du'
2psql -h 127.0.0.1 -U alice -d lab -c 'SELECT public.my_balance();'
3psql -h 127.0.0.1 -U alice -d lab -c 'CREATE EXTENSION adminpack;'
4psql -h 127.0.0.1 -U postgres -d lab -c '\dx'