Security Boundaries & Permissions in PostgreSQL Extension Lifecycle Automation #

Installing or upgrading a PostgreSQL extension is not ordinary DDL — it is a privilege event. Many extensions register C-language functions, untrusted procedural languages, background workers, or event triggers, and they execute their installation SQL with the privileges of whoever runs CREATE EXTENSION. That is why a naive automation pipeline that simply hands a superuser connection to every ALTER EXTENSION UPDATE quietly turns each routine patch into an unbounded remote-code-execution surface. This page is for platform engineers and database SREs who need extension upgrades to run unattended while keeping the superuser blast radius closed: it shows how to decouple the install-time privilege requirement from the runtime execution context, provision a least-privilege deploy role, wrap the privileged step in an auditable guard, and gate the whole thing behind a zero-side-effect dry-run.

Up: PostgreSQL Extension Architecture & Lifecycle Fundamentals — the control-file superuser flag, shared library loading, and catalog registration mechanics that make extension installation a privilege boundary are governed there, and understanding them is a prerequisite for the enforcement patterns below.

Privilege Enforcement Flow #

The pipeline treats privilege as a gate, not an afterthought. A read-only role audits the plan and proves the operation is safe; only then does a single, guarded, time-bound elevation execute the catalog mutation, and every branch emits an audit record. The privileged connection is never exposed to the resolver, the diff, or the dry-run.

Prerequisites #

The enforcement model below fails closed: any privilege it cannot positively verify is treated as insufficient rather than assumed benign.

• PostgreSQL version: 12 or newer. pg_has_role, has_database_privilege, has_schema_privilege, and the NOBYPASSRLS role attribute all predate 12, but the transactional rollback the dry-run relies on for catalog-only updates is only dependable from 12 onward. Row-level security (used here to reason about NOBYPASSRLS) requires 9.5+.
• Python packages: Python 3.8+ with psycopg2-binary (pip install psycopg2-binary). The dry-run harness uses only the standard library plus psycopg2; if your automation stack is asynchronous, the same guard logic ports to asyncpg, and the driver trade-offs are covered under ALTER EXTENSION Automation.
• Required roles: two distinct roles. A read-only role for the audit and dry-run gate (needs only CONNECT and catalog read access), and a deploy role that owns the guarded wrapper function. The wrapper’s definer — not the caller — holds the privilege the ALTER EXTENSION actually needs. Never wire a superuser DSN into the resolver or the dry-run.
• Artifact provenance: control files, versioned .sql scripts, and shared libraries must originate from version-pinned, signed packages, verified against the canonical manifest from Extension Registry Mapping. A privilege boundary is only as trustworthy as the bytes it executes — an unsigned .so on $libdir defeats every role restriction downstream.

Core Concept: Install-Time Privilege vs Runtime Context #

The single idea that makes secure extension automation possible is that the privilege required to install an extension is separable from the identity that triggers the install. PostgreSQL gives you three mechanisms to enforce that separation, and confusing them is the usual source of over-privileged pipelines.

The control-file superuser flag decides who may install. When an extension’s .control file sets superuser = true (the default), only a superuser can run CREATE EXTENSION or ALTER EXTENSION UPDATE for it, because its scripts create C functions or untrusted languages. Extensions marked trusted (PostgreSQL 13+) can be installed by a role holding CREATE on the target database without full superuser. Your automation must read this flag per version — it can change between releases — rather than assuming a fixed privilege class for a given extension.

SECURITY DEFINER relocates the privilege from caller to owner. A function declared SECURITY DEFINER executes with the privileges of the role that owns it, not the role that calls it. This is the lever that lets a low-privilege deploy role trigger a privileged catalog mutation without ever holding superuser itself: the function is owned by a controlled, audited role, contains an explicit membership check, and performs exactly one narrowly-scoped ALTER EXTENSION. The critical hardening detail is SET search_path: a mutable search path on a SECURITY DEFINER function lets a caller shadow built-in objects with their own, hijacking the definer’s privileges, so the path is always pinned to pg_catalog first.

Role attributes cap what a compromised deploy role can do. NOSUPERUSER prevents the role from ever bypassing permission checks; NOBYPASSRLS keeps it subject to row-level security policies even during a deploy; NOCREATEROLE and NOCREATEDB stop lateral privilege creation. These attributes are the containment wall — even if the wrapper had a bug, the calling role cannot escalate on its own.

Why this matters beyond tidiness: an unbounded superuser session during a routine patch bypasses row-level security, can disable audit triggers, and can read or rewrite any catalog. The detailed threat model for that scenario — and why the default superuser = true extensions are the highest-value target on a database host — is developed under Security Implications of Superuser Extension Installation.

Step-by-Step Implementation #

The following procedure builds the enforcement stack bottom-up: a contained deploy role, a guarded execution wrapper, an auditing hook, and a zero-side-effect dry-run harness. Each block is complete and copy-pasteable.

Step 1 — Provision a contained, least-privilege deploy role #

Create the deploy role with every escalation attribute explicitly denied, and grant it only the schema privileges a create/upgrade actually needs. The block is idempotent, so it is safe to re-run across a fleet.

-- Idempotent least-privilege deploy-role provisioning.
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'ext_deployer') THEN
        -- NOLOGIN: the role is assumed via membership, never logged into directly.
        -- Every escalation vector is denied explicitly.
        CREATE ROLE ext_deployer
            NOLOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE
            NOBYPASSRLS NOREPLICATION;
    END IF;

    -- Grant only what CREATE / ALTER EXTENSION needs on the target schema.
    GRANT USAGE  ON SCHEMA public TO ext_deployer;
    GRANT CREATE ON SCHEMA public TO ext_deployer;
END $$;

Step 2 — Create the guarded execution wrapper #

The wrapper is the only object that carries the privilege to mutate the catalog. It is owned by a controlled admin role, verifies caller membership before doing anything, pins its search path, and executes exactly one ALTER EXTENSION. Nothing else runs under the definer’s privilege.

-- SECURITY DEFINER wrapper: relocates privilege from caller to a controlled owner.
CREATE OR REPLACE FUNCTION admin.apply_extension_upgrade(
    p_ext_name      TEXT,
    p_target_version TEXT
) RETURNS VOID
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = pg_catalog, public   -- pinned: blocks search_path hijack
AS $$
BEGIN
    -- Explicit authorization: only members of ext_deployer may proceed.
    IF NOT pg_has_role(current_user, 'ext_deployer', 'MEMBER') THEN
        RAISE EXCEPTION 'permission denied: % lacks ext_deployer membership',
            current_user
            USING ERRCODE = '42501';
    END IF;

    -- Execute the single privileged mutation under the definer's rights.
    -- Do NOT SET ROLE to ext_deployer here: it is NOSUPERUSER and cannot run
    -- ALTER EXTENSION, so switching to it would drop the privilege the definer
    -- provides. %I / %L quote the identifier and literal to block injection.
    EXECUTE format('ALTER EXTENSION %I UPDATE TO %L', p_ext_name, p_target_version);
END;
$$;

-- The wrapper is callable by the deploy role, but owned by a privileged role.
REVOKE ALL ON FUNCTION admin.apply_extension_upgrade(TEXT, TEXT) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION admin.apply_extension_upgrade(TEXT, TEXT) TO ext_deployer;

Step 3 — Turn on catalog-mutation auditing #

Every privileged call must be attributable. With pgaudit loaded, scope its logging to the DDL class so each ALTER EXTENSION is recorded with the effective role, timestamp, and exact statement — the evidence trail a post-incident review depends on.

-- Requires pgaudit in shared_preload_libraries (a restart-level change).
ALTER SYSTEM SET pgaudit.log = 'ddl, role';
ALTER SYSTEM SET pgaudit.log_catalog = on;
SELECT pg_reload_conf();

Step 4 — Wire the guarded call behind a read-only gate #

The pipeline connects with the read-only role to audit and dry-run, and only invokes the wrapper through the deploy role once the gate has passed. The privileged path is never reached on a failed check.

# Gate: audit + dry-run with a read-only role (zero side effects).
python3 secure_upgrade.py \
  --db-uri "postgresql://ci_readonly@staging:5432/appdb" \
  --extension pg_partman --target-version 5.1.0 --dry-run

# Apply: the deploy role calls the guarded wrapper only if the gate exited 0.
python3 secure_upgrade.py \
  --db-uri "postgresql://ext_deployer@prod:5432/appdb" \
  --extension pg_partman --target-version 5.1.0

Dry-Run & Validation Gate #

The dry-run proves two things before any write transaction opens: that the target upgrade path exists, and that the acting role holds exactly the privileges the operation needs — no more, no less. It performs the ALTER EXTENSION inside a transaction that is unconditionally rolled back, so the catalog is never mutated.

import psycopg2
from psycopg2 import sql


def dry_run_extension_upgrade(conn, ext_name: str, target_version: str) -> bool:
    """Simulate an extension upgrade and roll it back — zero catalog mutation."""
    with conn.cursor() as cur:
        try:
            # psycopg2 opens a transaction implicitly on the first statement;
            # let the connection manage it rather than issuing raw BEGIN.
            cur.execute(
                sql.SQL("ALTER EXTENSION {} UPDATE TO {}").format(
                    sql.Identifier(ext_name), sql.Literal(target_version)
                )
            )

            # Assert the catalog reached the expected version inside the txn.
            cur.execute(
                "SELECT extversion FROM pg_extension WHERE extname = %s;",
                (ext_name,),
            )
            row = cur.fetchone()
            assert row is not None and row[0] == target_version, (
                f"version mismatch: expected {target_version}, "
                f"got {row[0] if row else 'None'}"
            )

            # Unconditional rollback guarantees no side effect on disk.
            conn.rollback()
            return True
        except Exception as exc:
            conn.rollback()
            raise RuntimeError(f"dry-run failed for {ext_name}: {exc}") from exc

Pair the transactional simulation with a read-only privilege audit that maps required grants without touching state. This is the query the gate blocks on — a INSUFFICIENT_PRIVILEGE row is a hard stop:

-- Pre-flight dependency & privilege validation (read-only).
WITH required_deps AS (
    SELECT name AS extname, default_version AS extversion
    FROM pg_available_extensions
    WHERE name IN ('pgcrypto', 'uuid-ossp', 'hstore')
),
current_state AS (
    SELECT extname, extversion FROM pg_extension
),
missing_or_stale AS (
    -- Flag anything not installed, or whose installed version differs from the
    -- available default. Use <> (not <) to avoid unreliable text-based
    -- version comparison.
    SELECT r.extname, r.extversion
    FROM required_deps r
    LEFT JOIN current_state c ON r.extname = c.extname
    WHERE c.extversion IS NULL OR c.extversion <> r.extversion
)
SELECT
    extname,
    extversion,
    CASE
        WHEN has_database_privilege(current_user, current_database(), 'CREATE')
        THEN 'OK'
        ELSE 'INSUFFICIENT_PRIVILEGE'
    END AS deployment_role_status
FROM missing_or_stale;

A clean gate emits a machine-readable verdict your pipeline archives as a deploy artifact and diffs across runs:

{
  "extension": "pg_partman",
  "target_version": "5.1.0",
  "update_path_exists": true,
  "dry_run_rolled_back": true,
  "deploy_role": "ext_deployer",
  "role_is_superuser": false,
  "role_bypassrls": false,
  "required_grants": ["USAGE:public", "CREATE:public"],
  "verdict": "SAFE_TO_APPLY"
}

Gate the apply on three conditions: dry_run_rolled_back is true, role_is_superuser is false (the deploy role must never be a superuser), and no privilege audit row reports INSUFFICIENT_PRIVILEGE. Any other state routes to the recovery logic in Fallback Routing Strategies rather than proceeding.

Failure Modes & Error Taxonomy #

Privilege failures during extension automation surface as a small, well-defined set of SQLSTATEs. Each has a distinct meaning and a specific remediation; feeding them into a structured classifier turns them into automated triage signals, as developed in Error Categorization Frameworks.

The distinction between 42501 (a genuine privilege gap the operator must close) and 2BP01 (a dependency safety interlock working as designed) is the one to encode first — the former means “grant carefully,” the latter means “stop and inspect the dependency graph.”

Rollback & Recovery Path #

A privileged mutation that fails mid-flight must return the node to a known state without widening privilege to do so. The recovery path branches on whether the update was transaction-safe.

1. Catalog-only update: the wrapper runs inside a transaction, so a failed ALTER EXTENSION UPDATE that only touched the catalog is undone by ROLLBACK — the node is untouched and no elevated session lingers.
2. Non-transactional update (registered a background worker, altered shared_preload_libraries, or allocated shared memory): a plain ROLLBACK is insufficient. Halt dependent workloads, run the extension’s shipped downgrade script ALTER EXTENSION X UPDATE TO '<prior_version>' through the same guarded wrapper, and restart the node if a preload library changed.
3. No downgrade path: restore from a verified pre-upgrade snapshot rather than hand-editing the catalog under superuser — driving the restore through Snapshot & Point-in-Time Recovery keeps the recovery itself auditable and avoids an ad-hoc privileged session.
4. Revoke the elevation. Whatever the path, confirm no temporary superuser membership or pooler-level role switch outlived the operation — a stranded elevation is itself the incident.

Capture the pre-upgrade version set and effective role as a deploy artifact so the rollback target is reproducible; pairing that snapshot with Version Control & Branching makes every privileged change a reviewable, revertible one.

Performance & Scale Considerations #

Privilege enforcement adds negligible runtime cost, but the way it is deployed across a fleet has real operational weight.

• Lock and elevation windows: keep each guarded ALTER EXTENSION in its own short transaction so both the lock hold time and the window during which elevated privilege is live are bounded. A long-running definer transaction is both a contention risk and a larger audit gap.
• Ephemeral credentials over standing superusers: at fleet scale, prefer connection-pooler role switching or short-lived injected credentials for the privileged step over a permanent superuser account. Rotating, time-bound elevation shrinks the window an exposed credential is useful, and it scales better than auditing a shared superuser across hundreds of nodes.
• Parallel audit, serialized apply: run the read-only pre-flight audit and dry-run across every node in parallel — it never mutates state — but roll the guarded apply out in controlled waves so a bad privileged change is caught on a canary before it reaches the whole fleet.
• Staging fidelity: validate the privilege model on a replica whose roles and grants mirror production, using Test Environment Routing; a staging role that happens to hold a grant production lacks is the classic reason a gate passes and the apply fails.

FAQ #

Related Pages #

• Up: PostgreSQL Extension Architecture & Lifecycle Fundamentals
• Security Implications of Superuser Extension Installation
• Dependency Tree Analysis
• Extension Registry Mapping
• Fallback Routing Strategies
• Version Control & Branching