Postgres SECURITY DEFINER Functions: Privilege Escalation by Design

Postgres has two ways a function can run: with the privileges of whoever called it (the default, called SECURITY INVOKER) or with the privileges of whoever defined it (SECURITY DEFINER). The second mode is privilege escalation by design. A user who would not normally be allowed to perform some operation can be granted EXECUTE on a SECURITY DEFINER function that performs that operation on their behalf, and the function runs with the owner's permissions rather than the caller's.

This is one of the more useful and one of the more dangerous features Postgres offers. Used carefully it lets you build narrow, auditable interfaces to operations that would otherwise require broad grants. Used carelessly it produces SQL-injectable backdoors that bypass the rest of your access control. The mechanics are straightforward; the failure modes deserve attention.

What SECURITY DEFINER actually does

A function declared SECURITY DEFINER runs with the privileges of the role that owns the function, not the role that called it. From the caller's perspective, the function appears to do something the caller could not do directly. From inside the function, queries see the owner's permissions: tables the owner can read are readable, tables only the owner can write are writable, and so on.

The standard use case is the controlled-operation pattern. You have a sensitive table (a webhook_signing_secrets table, say) that the application role should not be able to query directly. You create a SECURITY DEFINER function that takes a webhook_id, looks up the current secret, and uses it to validate or sign a payload. You grant EXECUTE on the function to the application role and never grant SELECT on the underlying table. The application can sign and verify webhooks; it cannot dump the secrets.

The function ownership matters. SECURITY DEFINER uses the owner's privileges, not a fixed role name. If you transfer ownership of the function, the privileges change accordingly. The recommendation is to own SECURITY DEFINER functions with a dedicated role that has only the specific privileges the function needs, not with a high-privilege role that has more than the function requires.

The search_path attack

The classic SECURITY DEFINER vulnerability is the search_path attack. A SECURITY DEFINER function that references an object by unqualified name (just my_table rather than my_schema.my_table) resolves the name using the caller's search_path. A malicious caller can set their search_path to include a schema they control, create an object there with the same name as the one the function expects, and the function will call their object instead with the owner's privileges.

This is not a hypothetical attack. It was the basis of a long-running series of CVEs in Postgres extensions and applications, and Postgres now warns at function-creation time if a SECURITY DEFINER function has no explicit search_path set. The fix is to set search_path explicitly in the function definition:

CREATE FUNCTION sign_webhook(webhook_id BIGINT, payload TEXT)
RETURNS TEXT
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = pg_catalog, app_schema
AS $$
DECLARE
  secret TEXT;
BEGIN
  SELECT signing_secret INTO secret
  FROM app_schema.webhook_signing_secrets
  WHERE id = webhook_id;
  RETURN encode(hmac(payload::bytea, secret::bytea, 'sha256'), 'hex');
END;
$$;

Note both the SET search_path clause and the fully-qualified table reference. Either alone is a partial defense. Both together close the attack.

The SET ROLE alternative for arbitrary code paths

SECURITY DEFINER is appropriate when the function's behavior is fixed and the privilege escalation is narrowly scoped. It is inappropriate when the function performs arbitrary operations or when the privilege escalation is broad. The pattern that should not be used is creating a SECURITY DEFINER function that takes a SQL string and executes it via EXECUTE: this gives the caller arbitrary access at the owner's privilege level, which defeats the point of privilege separation.

For cases where you genuinely need to switch identity within a session (typically for connection-pooled applications that authenticate at the application layer and want database-level role attribution), the right primitive is SET ROLE or SET SESSION AUTHORIZATION, executed at the start of each transaction. This is what PostgreSQL's RLS-with-tenant-context pattern uses. It does not produce the same trust shape as SECURITY DEFINER and does not have the same attack surface.

The audit trail problem

SECURITY DEFINER functions complicate the audit trail. The default Postgres logging records the database user that executed each statement, but inside a SECURITY DEFINER function the effective user is the owner. If you have audit triggers on tables, they see the owner-as-actor unless you explicitly capture the caller's identity inside the function and pass it through.

The pattern that works: inside a SECURITY DEFINER function, capture session_user (the role the session authenticated as) and pass it explicitly to any audit-logging or INSERT path. Do not rely on current_user inside SECURITY DEFINER because current_user returns the owner, not the caller. The distinction between session_user and current_user is one of the corners of Postgres role identity that catches people, and the audit trail is where the bugs surface.

The EXECUTE grant policy

By default in Postgres, the PUBLIC role has EXECUTE permission on functions. This means a newly-created SECURITY DEFINER function is callable by everyone unless you explicitly revoke. The standard discipline is to REVOKE EXECUTE FROM PUBLIC immediately after creating any SECURITY DEFINER function and grant explicitly only to the roles that should call it:

CREATE FUNCTION sign_webhook(BIGINT, TEXT) RETURNS TEXT ... ;
REVOKE EXECUTE ON FUNCTION sign_webhook(BIGINT, TEXT) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION sign_webhook(BIGINT, TEXT) TO application_role;

The same applies to schema-level access. If the function lives in a schema that PUBLIC can USE, the GRANT and REVOKE on the function itself is sufficient. If you want to harden further, revoke USAGE on the schema from PUBLIC as well, which prevents enumeration of the available functions.

Where SECURITY DEFINER earns its weight

Four canonical patterns where SECURITY DEFINER produces a meaningful improvement over alternatives:

Sensitive-column access. A table has a column that should be readable only via specific operations (a password hash that is verified but never returned, a webhook secret that is used to sign but never exposed). A SECURITY DEFINER function that takes inputs and returns derived outputs lets the application use the column without being able to see it.

Cross-tenant aggregation. A query that aggregates across tenants in a multi-tenant database requires bypassing row-level security. A SECURITY DEFINER function owned by a role with BYPASSRLS lets specific aggregations run without granting BYPASSRLS to the application role generally.

Maintenance operations. An operation that requires elevated privileges (creating a partition, refreshing a materialized view, vacuuming a specific table) can be wrapped in a SECURITY DEFINER function that the application can call on schedule. The application role does not have the privileges to do these directly; the function lets it perform exactly the operations needed.

Multi-step transactional integrity. An operation that must update several tables in a specific order, possibly across schemas with different ownership, can be encapsulated in a single SECURITY DEFINER function. The function holds the consistency invariant; the application calls it without needing the granular grants that direct access would require.

Anti-patterns

Three patterns that should not be used:

SECURITY DEFINER on functions that take SQL strings as input. This creates an arbitrary-code-execution gadget at the owner's privilege level.

SECURITY DEFINER owned by the superuser role. The owner role should have exactly the privileges the function needs, not more. If the function needs superuser-only operations (very rare in application code), that is itself a sign that the design needs reconsideration.

SECURITY DEFINER without explicit search_path. This is the classic vulnerability described above. Postgres warns about it, and the warning should be treated as an error.

Our use

Across the four products, the SQLite baseline does not have a SECURITY DEFINER concept; SQLite has a single-process trust model where the application is the database. The Postgres migration plan includes SECURITY DEFINER functions for the webhook signing path on WebhookVault, where we genuinely want the application to be able to sign and verify without being able to dump the secrets. The function will be owned by a dedicated role with SELECT only on the signing_secrets table and INSERT only on the audit_log table, with explicit search_path and no EXECUTE grant to PUBLIC. The audit_log will capture session_user for caller attribution.

For FlagBit's flag-evaluation path, SECURITY DEFINER is not appropriate because the operation is fundamentally read-only and the secrets-vs-application-data separation does not apply. The application role will have SELECT on the flags table directly. For CronPing's monitor-ping path, the same is true. For DocuMint's invoice-generation path, the same. Only WebhookVault has the structural fit.

The deeper observation: SECURITY DEFINER is a tool for cases where you genuinely have a privilege gap and want a narrow, auditable interface across it. It is not a general-purpose mechanism for application-database integration. The cases where it earns its weight are specific, and the failure modes when it is used carelessly are severe. The current safety landscape with mandatory search_path and explicit grants is much better than it was a decade ago, but the underlying primitive is still privilege escalation by design and should be used with that framing in mind.

Our products: DocuMint (PDF invoice generation API), CronPing (cron job monitoring with status pages), FlagBit (feature flags API for modern teams), and WebhookVault (webhook capture and replay) keep the lights on.