skip to content

In PostgreSQL, why must a SECURITY DEFINER function that lets tellers write to a protected audit table also SET search_path, and what else must its owner lock down?

level: seniorimportance: should knowfreq 46%

answer

  1. owner's privileges, caller's name lookup
  2. temp schema searched first for relations
  3. pg_temp written last
  4. EXECUTE granted to PUBLIC by default
  5. session_user versus current_user

basics

~20 s

A SECURITY DEFINER function runs with its owner's privileges but resolves unqualified names through the caller's search_path, so a caller's temporary table can hijack it. Pin SET search_path = audit, pg_temp, revoke EXECUTE from PUBLIC, and use a minimally privileged owner.

solid answer

~50 s

`SECURITY DEFINER` makes the function run as its owner, so tellers can insert into `audit.audit_log` without holding `INSERT` themselves. But unqualified names in the body are resolved through the `search_path` in force at call time, which the caller controls. The temporary schema is searched first for relations unless it is listed explicitly, so a teller can create a temporary table named `audit_log` and capture the writes, and any schema the teller can write that sits on the path can shadow functions and operators. The fix is a `SET search_path = audit, pg_temp` clause on the function, with `pg_temp` last. Then lock down the rest: `REVOKE ALL ... FROM PUBLIC` and `GRANT EXECUTE` to the teller role in the same transaction as the `CREATE`, have a `NOLOGIN` role that holds only `INSERT` on the audit table own the function, and record `session_user`, because `current_user` is the owner inside the call.

code

sql · 19 lines
sql
CREATE ROLE audit_owner NOLOGIN;
-- audit_owner holds INSERT on audit.audit_log and nothing more

BEGIN;
CREATE FUNCTION audit.record_event(p_account_id bigint, p_action text)
RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = audit, pg_temp
AS $$
BEGIN
    INSERT INTO audit_log (account_id, action, actor, logged_at)
    VALUES (p_account_id, p_action, session_user, now());
END;
$$;
ALTER FUNCTION audit.record_event(bigint, text) OWNER TO audit_owner;
REVOKE ALL ON FUNCTION audit.record_event(bigint, text) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION audit.record_event(bigint, text) TO teller;
COMMIT;

go deeper

for a junior

Recall that SECURITY DEFINER runs a function with its owner's privileges and that SECURITY INVOKER is the default.

for a middle

Explain how unqualified names resolve at call time, why the temporary schema is searched first unless pg_temp is listed, and what the SET clause pins.

for a senior

Show the full hardening checklist: pinned search_path with pg_temp last, REVOKE from PUBLIC in the creating transaction, a minimal NOLOGIN owner, and session_user for the actor.

for a principal

Weigh SECURITY DEFINER gateways against narrow direct grants or row-level policies, considering audit integrity, review burden, and how privilege escalation paths are inventoried.

## What SECURITY DEFINER changes By default a PostgreSQL function is `SECURITY INVOKER`: it runs with the privileges of whoever calls it. `SECURITY DEFINER` (stored as `pg_proc.prosecdef`) makes it run with the privileges of its **owner** for the duration of the call. That is how a bank lets tellers append to `audit.audit_log` while denying them direct `INSERT`, `UPDATE` or `DELETE` on it: the function is the only door. During the call, `current_user`, the user that permission checks use, becomes the owner. `session_user` still names the role that opened the connection. ## The search_path hole The owner's privileges are borrowed, but **name resolution is not**. Unless the function pins it, every unqualified table, function, operator or type name in a PL/pgSQL or string-quoted body is resolved through the `search_path` in force when the function is called, and the caller can set that freely. 1. The default `search_path` is `"$user", public`, and any role can change its own with `SET`. 2. The session's **temporary schema** is always searched. If `pg_temp` is not listed in the path, it is searched **first** for relations and data types, even before `pg_catalog`. It is never searched for functions or operators. 3. A teller can therefore run `CREATE TEMP TABLE audit_log (...)`. An unqualified `INSERT INTO audit_log` in the function now writes into the teller's private table, and the real audit trail silently receives nothing. 4. Any schema the caller can write that appears in the path can also hold a function or operator that shadows the one the body meant, and that code would run with the owner's privileges. The documented cure is a `SET` clause on the function itself, which applies on entry and is restored on exit: | Form | Effect | |---|---| | `SET search_path = audit, pg_temp` | trusted schema first, temporary schema explicitly last; the usual choice | | `SET search_path = audit` | **not enough**: unlisted `pg_temp` is still searched first for relations | | `SET search_path = ''` plus schema-qualified names | the strictest form; `pg_catalog` is still searched implicitly | A `LANGUAGE sql` function written with a `RETURN` or `BEGIN ATOMIC` body binds its names when it is created, which narrows the exposure, but the `SET` clause remains the standard protection for every language. The rules for `search_path` and schema privileges in general belong to the roles and schemas topic. ## Everything else the owner locks down - **Who may call it.** New functions grant `EXECUTE` to `PUBLIC` by default. Run `REVOKE ALL ON FUNCTION ... FROM PUBLIC` and `GRANT EXECUTE ... TO teller` in the **same transaction** as `CREATE FUNCTION`, so there is no window in which everyone can call it. `ALTER DEFAULT PRIVILEGES ... REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC` changes the default for future functions. - **Who owns it.** The owner's privileges are what callers borrow, so the owner should be a dedicated `NOLOGIN` role holding only `INSERT` on the audit table, never a superuser or the schema-owning migration role. - **Whose name is recorded.** Inside the function `current_user` returns the owner, so an audit row that records it blames the owner for everything. Record `session_user`, and never accept the actor's name as a free-text argument a caller can forge. - **What the arguments may do.** Validate them, and never splice them into dynamic SQL without proper quoting; the PL/pgSQL topic covers `format()` with `%I` and `%L`. ## Side effects of these attributes - A `LANGUAGE sql` function that is `SECURITY DEFINER` or carries a `SET` clause is **never inlined** into the calling query, so it runs as an opaque call. - A `SECURITY DEFINER` procedure, and any procedure with a `SET` clause, runs in an atomic context and **cannot `COMMIT`**; keep the privileged part a function and let an unprivileged caller own the transaction. ## Verifying the hardening - In psql, `\df+ audit.record_event` shows `definer` in the `Security` column, and the function's configuration settings appear in `pg_proc.proconfig`. - `has_function_privilege('teller', 'audit.record_event(bigint, text)', 'EXECUTE')` should be true, and the same check for an unrelated role should be false; if it is true, the `PUBLIC` grant is still in place. - A periodic query over `pg_proc` for `prosecdef` functions whose `proconfig` is empty finds every privileged function still exposed to the caller's `search_path`.

  • Why does SET search_path = audit, without pg_temp, still leave the function exploitable?
    When `pg_temp` is not listed, the session's temporary schema is searched first for relations and types, ahead of every listed schema. A teller can create a temporary table named `audit_log`, and the function's unqualified `INSERT` lands there instead of in the real table. Listing `pg_temp` last moves it to the end of the lookup.
  • Every audit row shows audit_owner as the actor. Why, and what should the function record instead?
    In a `SECURITY DEFINER` function, `current_user` (and its synonym `current_role`) is the effective user, the owner, for the whole call. `session_user` still names the role that opened the connection, so record that. Never take the actor as a free-text argument, since any caller could forge it.
  • Could the audit writer be a procedure that commits after each insert?
    Not as `SECURITY DEFINER`. PostgreSQL runs a `SECURITY DEFINER` procedure, and any procedure with a `SET` clause, in an atomic context, so `COMMIT` raises `invalid transaction termination` (`2D000`). Keep the privileged part a function and let an unprivileged procedure or the client control commits.

  • SECURITY DEFINER also makes the body resolve names with the owner's search_path.
  • SET search_path = audit alone keeps temporary tables out of the lookup.
  • A new function is private to its owner until EXECUTE is granted.
  • Letting a superuser own the function is the simplest safe choice.
  • current_user inside the function identifies the teller who called it.