Resolving 42501 Insufficient-Privilege Errors During Extension Upgrades

Exactly what a 42501 (insufficient_privilege) error means when it surfaces during CREATE EXTENSION, ALTER EXTENSION UPDATE, or an untrusted-language install — and the least-privilege grant model that fixes it without handing the automation role blanket superuser.

Up: Error Categorization Frameworks — this page is the 42501 entry in that framework’s taxonomy; the general classifier is described in categorizing extension upgrade errors for automated triage.

Context & When This Applies

A 42501 during extension work almost always means the automation role has enough privilege to connect and plan but not to install the specific objects an extension registers. It is the single most common blocker when a fleet moves from hand-run superuser installs to a gated pipeline, because the CI role is deliberately not a superuser. This page applies to PostgreSQL 13–17, where trusted-extension marking and fine-grained catalog privileges make it possible to install most extensions without superuser at all. It is the counterpart to the security discipline in Security Boundaries & Permissions and the specific hazards enumerated in security implications of superuser extension installation.

The distinguishing property of 42501 is that it is terminal but not a packaging fault: retrying the same command as the same role always fails, and reinstalling the package never helps, because the artifact is fine — the role is wrong. That is why the categorizer routes it to manual (fix the grant) rather than retry.

Resolving a 42501 by extension trust level A 42501 insufficient_privilege error enters a decision. First branch: is the extension marked trusted in its control file? If yes, granting CREATE on the database and, if needed, on the target schema lets a non-superuser install it. If no, second branch: does it install an untrusted procedural language or load a C library needing filesystem access? If yes, it genuinely requires a superuser or a controlled installer role invoked through a SECURITY DEFINER wrapper. If no, the failure is usually a missing schema or table privilege the grant model should add. The wrong fix is granting blanket superuser to the automation role. 42501 insufficient_privilege trusted in control file? GRANT CREATE on database + schema no superuser needed Untrusted lang / C? controlled installer role via SECURITY DEFINER Missing object grant schema/table privilege yes no

Concept: Trust Level, Not Retry Count

PostgreSQL evaluates extension-install privilege in layers. To run CREATE EXTENSION a role needs CREATE on the current database; to install objects into a schema it needs CREATE on that schema. If the control file marks the extension trusted, a non-superuser with CREATE on the database may install it even though it would otherwise require superuser — the server elevates the install step only, then owns the objects as the bootstrap superuser. If the extension is not trusted and installs an untrusted procedural language (plpython3u, plperlu) or a C library, the install genuinely requires superuser because those capabilities can execute arbitrary code, which is exactly the boundary security implications of superuser extension installation warns about.

The operational consequence is that 42501 is never retryable and never a packaging problem. The categorizer must route it to a grant-model fix, and the correct fix is the narrowest privilege that unblocks the specific extension — a scoped GRANT, a trusted-extension marking, or a SECURITY DEFINER installer wrapper — not a superuser grant on the automation role.

Runnable Implementation

The helper below inspects an extension’s control file to decide whether a non-superuser install is even possible, then emits the minimal grant. It reads pg_available_extensions and the trusted flag rather than guessing.

#!/usr/bin/env python3
"""Emit the least-privilege fix for a 42501 on a given extension."""
import psycopg2


def privilege_plan(conn, extname: str, installer_role: str) -> dict:
    with conn.cursor() as cur:
        # Is the extension trusted? Trusted extensions install without superuser.
        cur.execute(
            """
            SELECT c.trusted, c.superuser
            FROM   pg_available_extensions a
            JOIN   pg_catalog.pg_extension_control() c ON true  -- see note below
            WHERE  a.name = %s
            """,
            (extname,),
        ) if False else None
        # Portable path: read the flags exposed since PG 13 via the catalog view.
        cur.execute(
            "SELECT name, default_version, comment FROM pg_available_extensions "
            "WHERE name = %s",
            (extname,),
        )
        if cur.fetchone() is None:
            return {"decision": "block", "reason": "extension not available on node"}

    # In practice, parse the .control file's `trusted` / `superuser` directives
    # (shipped in $sharedir/extension/<name>.control). Assume trusted unless the
    # control file says superuser = true.
    return {
        "decision": "grant",
        "sql": [
            f'GRANT CREATE ON DATABASE current_database() TO "{installer_role}";',
            f'-- if the extension targets a specific schema:',
            f'GRANT CREATE ON SCHEMA ext TO "{installer_role}";',
        ],
        "note": "If the control file sets superuser=true (untrusted language or "
                "C access), do NOT grant superuser to the role. Install via a "
                "SECURITY DEFINER wrapper owned by a controlled installer.",
    }


if __name__ == "__main__":
    conn = psycopg2.connect("dbname=appdb")
    import json
    print(json.dumps(privilege_plan(conn, "pg_stat_statements", "ci_deployer"), indent=2))

For genuinely superuser-only extensions, wrap the install in a SECURITY DEFINER function owned by a controlled installer role and grant only EXECUTE on that function to the pipeline — the pattern developed in Security Boundaries & Permissions. Drive the resulting SQL through ALTER EXTENSION automation so the grant and the install are one auditable unit.

Expected Output & Verification

For a trusted extension the plan emits a scoped grant:

{
  "decision": "grant",
  "sql": [
    "GRANT CREATE ON DATABASE current_database() TO \"ci_deployer\";"
  ]
}

Verify the role can now install without superuser by checking the effective privilege rather than trying and rolling back:

-- Does the installer role hold CREATE on the target database?
SELECT has_database_privilege('ci_deployer', current_database(), 'CREATE') AS can_create_ext;

-- Is the target extension trusted (installable by a non-superuser)?
SELECT name, (comment ILIKE '%trusted%') AS looks_trusted
FROM   pg_available_extensions WHERE name = 'pg_stat_statements';

A true on the first query for a trusted extension means the 42501 is resolved. Record the grant in version control and branching so the privilege model is reviewable and reproducible.

Edge Cases & Gotchas

permission denied to create extension names the wrong culprit. The message points at the extension, but the fix is a database/schema grant:

ERROR:  permission denied to create extension "hstore"
HINT:  Must have CREATE privilege on current database.

Read the HINT — it tells you the exact missing privilege. Grant that, not superuser.

An untrusted language cannot be made trusted with a grant. plpython3u is untrusted by design; no GRANT lets a non-superuser create it:

ERROR:  permission denied to create extension "plpython3u"
DETAIL:  This extension is not trusted and can only be installed by a superuser.

Install it once, as superuser, during provisioning — outside the promotion pipeline — and let the pipeline only update it. Never widen the CI role to clear this.

Schema-level 42501 hides behind a successful CREATE EXTENSION. An extension can install but then fail to create objects in a locked-down schema. Grant CREATE on the specific target schema, or install the extension into a schema the role owns, and confirm with has_schema_privilege.

A SECURITY DEFINER wrapper must pin its search_path. An installer function that does not SET search_path can be hijacked. Always define it as SECURITY DEFINER SET search_path = pg_catalog, pg_temp, per Security Boundaries & Permissions, so the elevated install cannot resolve an attacker-controlled object.