Configuring Trusted Extensions Without Superuser

How to let a non-superuser automation role install and update the extensions your fleet actually needs — using PostgreSQL’s trusted-extension mechanism and a scoped grant model — so a CI/CD pipeline never has to run as a superuser.

Up: Security Boundaries & Permissions — this page is the concrete non-superuser install recipe for that boundary, under the PostgreSQL Extension Architecture & Lifecycle Fundamentals area of the site.

Context & When This Applies

Since PostgreSQL 13, an extension’s control file can carry trusted = true, which lets a role holding CREATE on the database install it without being a superuser — the server performs the install as the bootstrap superuser but hands ownership back to the invoking role. This is the mechanism that makes a least-privilege promotion pipeline possible: most common extensions (hstore, pg_trgm, citext, pgcrypto, pg_stat_statements, uuid-ossp) ship trusted, so a scoped deployer role can manage them end to end. This applies to PostgreSQL 13–17; on 12 and earlier every CREATE EXTENSION requires superuser, and you must fall back to the wrapper pattern below.

Configuring this correctly is what turns the security implications of superuser extension installation from an accepted risk into an eliminated one. It also resolves the most common 42501 blocker a gated pipeline hits, detailed in resolving 42501 insufficient-privilege errors.

Trusted direct install versus a SECURITY DEFINER wrapper for untrusted extensions A non-superuser deployer role with CREATE on the database takes one of two paths. For a trusted extension, it runs CREATE EXTENSION directly and the server installs it as bootstrap superuser then returns ownership; no elevated role is exposed. For an untrusted extension, the deployer instead calls a SECURITY DEFINER wrapper function owned by a controlled installer role with a pinned search_path; the deployer holds only EXECUTE on that function, so the elevated capability is contained to one audited install path. Deployer role non-superuser CREATE on database trusted? CREATE EXTENSION direct server installs as bootstrap superuser ownership returns to deployer SECURITY DEFINER wrapper owned by controlled installer pinned search_path deployer holds only EXECUTE yes no

Concept: Trust Is a Property of the Extension, Delegation Is Yours to Design

Two mechanisms combine here. Trust is declared by the extension author in the control file (trusted = true) and means the server is willing to run the install with elevated privilege on behalf of a database-CREATE holder — you cannot make an untrusted extension trusted, and you should not try. Delegation is the pattern you design for the extensions that are genuinely untrusted (those installing an untrusted procedural language or a C library with filesystem access): a SECURITY DEFINER function, owned by a tightly controlled installer role, that performs the specific install and exposes only EXECUTE to the pipeline.

The security property that makes both safe is containment. A trusted install never exposes a superuser session to the pipeline. A SECURITY DEFINER wrapper contains the elevated capability to a single, audited code path with a pinned search_path, so the deployer can install this extension and nothing else — it cannot pivot the grant into arbitrary superuser action. This is the same containment discipline the compatibility validation pipeline applies to privilege as a gate rather than an afterthought.

Runnable Implementation

The SQL below provisions a least-privilege deployer for trusted extensions, then defines a contained wrapper for one specific untrusted extension. Run the provisioning once, as an admin, during cluster setup.

-- === One-time provisioning (run as an admin/superuser at setup) ===

-- 1. A non-superuser role the pipeline authenticates as.
CREATE ROLE ci_deployer LOGIN PASSWORD :'deployer_pw' NOSUPERUSER NOCREATEROLE;

-- 2. Let it install TRUSTED extensions: CREATE on the database is enough.
GRANT CREATE ON DATABASE appdb TO ci_deployer;

-- 3. Give it a home schema it owns, so relocatable extensions land somewhere
--    the deployer controls without touching public.
CREATE SCHEMA ext AUTHORIZATION ci_deployer;

-- 4. For ONE genuinely untrusted extension, define a contained installer.
--    Owned by a controlled role; pins search_path so it cannot be hijacked.
CREATE ROLE ext_installer NOLOGIN;
CREATE OR REPLACE FUNCTION ext.install_plpython()
  RETURNS void
  LANGUAGE sql
  SECURITY DEFINER
  SET search_path = pg_catalog, pg_temp
AS $$
  CREATE EXTENSION IF NOT EXISTS plpython3u;
$$;
ALTER FUNCTION ext.install_plpython() OWNER TO ext_installer;
REVOKE ALL ON FUNCTION ext.install_plpython() FROM PUBLIC;
GRANT EXECUTE ON FUNCTION ext.install_plpython() TO ci_deployer;

With that in place, the pipeline authenticates as ci_deployer and runs CREATE EXTENSION hstore directly for trusted extensions, or SELECT ext.install_plpython() for the one contained untrusted case — driving both through ALTER EXTENSION automation.

Expected Output & Verification

A trusted install by the deployer succeeds without any superuser grant:

appdb=> \c appdb ci_deployer
appdb=> CREATE EXTENSION hstore SCHEMA ext;
CREATE EXTENSION

Verify the deployer holds exactly the privileges it needs and nothing more:

-- Deployer can create in the database, is NOT a superuser, and owns its schema.
SELECT rolname, rolsuper, rolcreatedb,
       has_database_privilege('ci_deployer', 'appdb', 'CREATE') AS can_create_ext
FROM   pg_roles WHERE rolname = 'ci_deployer';

-- Which installed extensions are trusted (installable this way)?
SELECT name, default_version FROM pg_available_extensions
WHERE  name IN ('hstore','pg_trgm','citext','pgcrypto') ORDER BY name;

rolsuper must be false while can_create_ext is true. Record the role grants under version control and branching so the privilege model is reviewed like code.

Edge Cases & Gotchas

A trusted extension still needs schema CREATE for its objects. CREATE on the database lets the deployer install the extension, but if it targets a schema the role cannot write, object creation fails. Install into a schema the deployer owns (the ext schema above), or grant CREATE on the target schema explicitly.

SECURITY DEFINER without a pinned search_path is exploitable. An installer function that resolves unqualified names can be tricked into executing an attacker’s object. Always SET search_path = pg_catalog, pg_temp on the wrapper, exactly as Security Boundaries & Permissions requires — the single most important line in the wrapper.

ALTER EXTENSION UPDATE on a trusted extension follows the same rule. The trust property covers updates too, so the deployer can update trusted extensions without superuser. But an update that newly requires an untrusted dependency will fail — re-check the requires graph with dependency tree analysis before assuming an update stays in-trust.

Do not grant the deployer CREATEROLE to “simplify” things. CREATEROLE can be escalated toward superuser in several ways and defeats the whole point. Keep the deployer NOSUPERUSER NOCREATEROLE and add narrow grants only for the specific objects it must manage.