Security Implications of Superuser Extension Installation

Running CREATE EXTENSION as a superuser hands an extension’s control file and SQL initialization scripts unrestricted catalog access for the duration of install — this page shows exactly what that privilege buys an attacker and how to audit, isolate, and revoke it before the elevated context becomes a standing backdoor.

Up: Security Boundaries & Permissions — the least-privilege role model, dry-run gating, and runtime isolation patterns this page hardens against are governed there, and the broader loader and catalog mechanics live in PostgreSQL Extension Architecture & Lifecycle Fundamentals.

When This Applies

This technique is for DBAs and database SREs who provision extensions through automation and need to prove that install-time privilege never leaks into runtime. It applies whenever any of the following is true:

  • You install extensions as postgres or another SUPERUSER. Direct CREATE EXTENSION requires SUPERUSER, so most fleets install this way by default, inheriting every escalation surface below.
  • You rely on trusted extensions (PostgreSQL 13+). An extension marked trusted in its .control file can be installed by any role holding CREATE on the target database and schema — no superuser needed. The objects it creates are still owned by the bootstrap superuser, so a non-privileged role can trigger the installation of superuser-owned SECURITY DEFINER functions.
  • You provision C-language extensions (pg_stat_statements, postgis, pg_partman, timescaledb). These load compiled .so files into the backend and cannot be sandboxed by the SQL engine.
  • Your pipeline shares one superuser credential across install, upgrade, and rollback. Coupling those phases is the single most common way install-time privilege becomes a permanent liability.

The audit and hardening steps below assume PostgreSQL 11 or newer (for the pg_read_server_files / pg_write_server_files predefined roles) and target 13+ where trusted-extension semantics matter.

How Elevated Installation Actually Works

CREATE EXTENSION is not a single privileged statement — it is an interpreter that executes an attacker-supplied SQL script under the caller’s authority. When that caller is a superuser, the script runs with unrestricted DDL and catalog-modification rights, bypassing object-level ACLs entirely during provisioning. Four distinct surfaces carry the risk:

  • SECURITY DEFINER function injection. Extensions routinely install wrapper functions that execute under the extension owner’s context. Installed by postgres, these functions inherit superuser privileges permanently, letting a non-privileged application role bypass row-level security or reach OS-level operations long after install completes. The SECURITY DEFINER routine, not the CREATE EXTENSION call, is the durable escalation vector.
  • Shared library loading. CREATE EXTENSION triggers a LOAD of the compiled .so into the backend process. Compiled C runs outside the PL/pgSQL sandbox, so a tampered library can execute arbitrary code, corrupt shared memory, or intercept syscalls. Verifying artifact provenance against a signed Extension Registry Mapping before install is the only defense that acts before the code runs.
  • Hook registration. Extensions register ProcessUtility_hook, ExecutorStart_hook, or override GUCs like client_min_messages and search_path. An improperly scoped hook can intercept, log, or rewrite queries across every session, silently exfiltrating parameters or altering isolation levels.
  • Transitive dependency chains. A single requires directive in a .control file cascades install-time privilege across multiple packages. Resolving that graph with Dependency Tree Analysis reveals when a lightweight utility silently pulls in a heavy auditing or replication module, expanding the superuser footprint — and the blast radius of one compromise — far beyond what the operator requested.
Four escalation surfaces of a superuser CREATE EXTENSION A single superuser CREATE EXTENSION call at the top fans downward into four escalation surfaces — SECURITY DEFINER functions, shared-library LOAD, hook registration, and transitive requires. Each arrow pierces a dashed runtime least-privilege boundary, showing that object-level ACLs are bypassed during install. SUPERUSER · CREATE EXTENSION runs supplied SQL with unrestricted catalog rights runtime least-privilege boundary · object ACLs bypassed 1 SECURITY DEFINER wrappers run as the superuser owner — permanently 2 Shared-library LOAD compiled .so runs outside the PL/pgSQL sandbox 3 Hook registration intercepts and rewrites queries across every session 4 Transitive requires one .control directive cascades privilege across packages Each surface outlives the install call — the escalation becomes standing, not transient.

Auditing the Escalation Surface

Before granting or renewing superuser access for extension deployment, enumerate exactly which surfaces are already live. This audit correlates system-catalog metadata with on-disk artifacts to detect privilege drift. The primary catalog query surfaces every SECURITY DEFINER function an extension installed, joined to its owning role:

-- Every SECURITY DEFINER function installed by an extension, with its owner.
-- A prosecdef=true row owned by a superuser is a standing escalation path.
SELECT e.extname,
       e.extversion,
       e.extowner::regrole      AS extension_owner,
       p.proname                AS function_name,
       p.prosecdef              AS security_definer,
       p.provolatile
FROM pg_extension e
JOIN pg_proc p ON p.pronamespace = e.extnamespace
WHERE p.prosecdef = true
ORDER BY e.extname, p.proname;

Wrap that query, a filesystem integrity scan, and a role check into one runnable audit so a pipeline can gate on the result rather than a human reading psql output:

#!/usr/bin/env python3
"""
Audit the superuser-install escalation surface for PostgreSQL extensions.
Reports SECURITY DEFINER functions owned by superusers, extensions whose
owner is still a superuser, and control/SQL files carrying risky directives.
Requires: psycopg2-binary (pip install psycopg2-binary)
Exit code 0 = clean, 1 = findings that need review.
"""

import json
import subprocess
import sys
import psycopg2

# SECURITY DEFINER functions whose owner holds rolsuper: a durable escalation path.
DEFINER_SQL = """
SELECT e.extname, p.proname, r.rolname AS owner, r.rolsuper
FROM pg_extension e
JOIN pg_proc p  ON p.pronamespace = e.extnamespace
JOIN pg_roles r ON r.oid = e.extowner
WHERE p.prosecdef = true;
"""

# Extensions still owned by a superuser: future ALTER EXTENSION UPDATE inherits it.
OWNER_SQL = """
SELECT e.extname, r.rolname AS owner
FROM pg_extension e
JOIN pg_roles r ON r.oid = e.extowner
WHERE r.rolsuper = true;
"""


def scan_control_files() -> list[str]:
    """Grep the extension sharedir for directives that widen the blast radius."""
    sharedir = subprocess.check_output(
        ["pg_config", "--sharedir"], text=True).strip() + "/extension"
    hits = subprocess.run(
        ["grep", "-rlE", r"SECURITY DEFINER|SET ROLE|ALTER ROLE|LOAD '",
         sharedir],
        capture_output=True, text=True)
    return [ln for ln in hits.stdout.splitlines() if ln]


def audit(dsn: str) -> int:
    findings = {"definer_functions": [], "superuser_owned": [],
                "risky_files": scan_control_files()}
    with psycopg2.connect(dsn) as conn, conn.cursor() as cur:
        cur.execute(DEFINER_SQL)
        for ext, fn, owner, is_super in cur.fetchall():
            if is_super:  # only superuser-owned definers are escalations
                findings["definer_functions"].append(
                    {"extension": ext, "function": fn, "owner": owner})
        cur.execute(OWNER_SQL)
        findings["superuser_owned"] = [
            {"extension": ext, "owner": owner} for ext, owner in cur.fetchall()]

    print(json.dumps(findings, indent=2))
    has_findings = any(findings[k] for k in findings)
    return 1 if has_findings else 0


if __name__ == "__main__":
    if len(sys.argv) != 2:
        print("usage: audit_superuser_install.py <db-dsn>", file=sys.stderr)
        sys.exit(2)
    sys.exit(audit(sys.argv[1]))

The filesystem half of the audit — confirming the scripts on disk match what you approved — runs standalone and needs no database connection:

# Flag any installed extension script carrying a privilege-widening directive.
find "$(pg_config --sharedir)/extension" -name "*.sql" \
     -exec grep -lE "SECURITY DEFINER|SET ROLE|ALTER ROLE|LOAD '" {} \;

Expected Output & Verification

A clean fleet returns empty finding arrays and exit code 0. A fleet carrying standing escalation paths returns a structured report your pipeline can archive as a deploy artifact and alert on:

{
  "definer_functions": [
    { "extension": "pg_audit_wrap", "function": "log_and_run", "owner": "postgres" }
  ],
  "superuser_owned": [
    { "extension": "pg_audit_wrap", "owner": "postgres" }
  ],
  "risky_files": [
    "/usr/share/postgresql/16/extension/pg_audit_wrap--1.0.sql"
  ]
}

Verify three things before you trust the result:

  • Cross-check counts against pg_stat_user_functions. Unexpected spikes in calls or total_time for a newly provisioned, superuser-owned function often indicate a hook firing on every statement rather than the intended narrow use.
  • Validate checksums, not just paths. A file appearing in risky_files is expected for legitimately privileged extensions; confirm its SHA-256 matches the signed release in your artifact store so a risky_file never means a tampered file.
  • Confirm exit code propagation. The script returns 1 on any finding — wire that into the CI gate so a non-zero audit blocks promotion instead of scrolling past in a log.

Hardening Procedure: Isolating an Over-Privileged Extension

When the audit confirms a superuser-owned extension or an unnecessary SECURITY DEFINER function, remediate in a strict, reversible sequence. Run each step inside a maintenance window and capture a pre-change snapshot first.

  1. Snapshot and map the blast radius. Capture SELECT extname, extversion, extowner::regrole FROM pg_extension and every object in the extension’s namespace as a deploy artifact. This is the state your rollback restores to; drive any restore through Snapshot & Point-in-Time Recovery.
  2. Isolate the namespace. Create a dedicated schema and move the extension into it with ALTER EXTENSION <name> SET SCHEMA ext_sandbox; so its objects can no longer leak ACLs into public.
  3. Reassign ownership away from the superuser. Run ALTER EXTENSION <name> OWNER TO ext_manager; where ext_manager is a dedicated NOSUPERUSER role. This severs superuser inheritance for every future ALTER EXTENSION Automation update.
  4. Harden the functions. Rewrite any SECURITY DEFINER function that does not need elevation as SECURITY INVOKER. For functions that must keep elevated context, REVOKE ALL ON FUNCTION ... FROM PUBLIC; and then GRANT EXECUTE only to the specific roles that require it.
  5. Roll back unauthorized hooks and validate. Patch the .sql initialization script to drop any SET directive that overrides search_path or client_min_messages, tracking the edit through Version Control & Branching so the change is reviewable. Then diff pg_dump --schema-only against the known-good baseline and re-run the audit script; a clean exit 0 confirms the escalation surface is closed.

For pipeline provisioning going forward, decouple the superuser credential entirely: install through a dedicated ext_deployer role holding only CREATE on the target schema plus, where strictly required, membership in pg_read_server_files — never CREATEROLE, which is itself an escalation vector. Wrap CREATE EXTENSION in an explicit transaction and DROP EXTENSION on any post-install assertion failure so a partial install never commits.

Edge Cases & Gotchas

  • A non-superuser install still creates superuser-owned objects. Because a trusted extension’s objects are owned by the bootstrap superuser, an unprivileged role holding CREATE can install SECURITY DEFINER functions that run as superuser. Do not treat “installed without superuser” as “installed without escalation” — the audit query flags these regardless of who ran CREATE EXTENSION.
  • ERROR: permission denied to create extension "hstore" / HINT: Must have CREATE privilege ... or be superuser. (SQLSTATE 42501). The extension is untrusted and the deploy role lacks the grant. Do not resolve this by promoting the role to superuser; classify and route the failure through Error Categorization Frameworks, then either mark the extension trusted upstream or run the install through a time-bound elevated session.
  • ERROR: cannot drop extension pg_audit_wrap because other objects depend on it (SQLSTATE 2BP01). A rollback that issues a bare DROP EXTENSION fails when application objects reference extension-owned functions. Reach for DROP EXTENSION ... CASCADE only after the snapshot in step 1 confirms exactly which dependents will be dropped — CASCADE on a superuser-owned extension can silently remove far more than intended.
  • ALTER EXTENSION ... OWNER TO fails unless you hold membership in the new owner role. Reassigning ownership requires the current role to be a member of ext_manager (or be superuser). Grant that membership first, or the hardening step 3 aborts with ERROR: must be able to SET ROLE "ext_manager".