Trusted vs Untrusted Extensions

Extensions marked trusted can be installed by non-superusers with the right grants. Untrusted extensions run C code with superuser privileges during install and demand security review before any CREATE EXTENSION in production.

Recipe

-- Check trust flag (PostgreSQL 13+)
SELECT name,
       default_version,
       CASE WHEN trusted THEN 'trusted' ELSE 'untrusted' END AS trust_level
FROM pg_available_extensions
WHERE name IN ('vector', 'postgis', 'plpython3u', 'pgcrypto')
ORDER BY name;
 
-- Grant install to a role (trusted extensions only)
GRANT CREATE ON DATABASE appdb TO deploy_role;
GRANT USAGE ON SCHEMA extensions TO deploy_role;
-- On PostgreSQL 15+: also grant on the extension object
GRANT vector TO deploy_role;  -- extension name as object

When to reach for this: Onboarding a new extension, auditing who can install what, or hardening a multi-tenant managed cluster.

Working Example

-- Security review checklist query pack
-- 1) All installed extensions
SELECT e.extname,
       e.extversion,
       a.trusted,
       pg_get_userbyid(e.extowner) AS owner,
       n.nspname AS schema
FROM pg_extension e
JOIN pg_available_extensions a ON a.name = e.extname
JOIN pg_namespace n ON n.oid = e.extnamespace
ORDER BY e.extname;
 
-- 2) Roles that can create extensions (superuser or createrole)
SELECT rolname,
       rolsuper,
       rolcreaterole,
       rolcreatedb
FROM pg_roles
WHERE rolsuper OR rolcreatedb
ORDER BY rolname;
 
-- 3) Untrusted extension install attempt (should fail for app role)
SET ROLE app_role;
CREATE EXTENSION plpython3u;  -- expect ERROR: permission denied
RESET ROLE;

What this demonstrates:

• trusted column in pg_available_extensions drives install policy
• Inventory joins owner and schema for audit
• Untrusted extensions stay superuser-only in hardened environments
• Role testing validates least privilege before go-live

Deep Dive

Trusted Extensions

Trusted extensions are reviewed by the PostgreSQL community (or vendor) as safe for non-superuser install. They still add capabilities (new types, functions) but are constrained compared to arbitrary C modules.

Common trusted extensions on PostgreSQL 18:

Untrusted Extensions

Untrusted extensions can expose filesystem, network, or arbitrary code execution paths. Treat each as a supply-chain decision.

Hardening Patterns

-- Revoke public CREATE on database
REVOKE CREATE ON DATABASE appdb FROM PUBLIC;
 
-- Allowlist: only specific extensions get GRANT
-- (run as superuser after security review)
GRANT postgis TO dba_deploy_role;
GRANT vector TO dba_deploy_role;
-- Do NOT grant plpython3u

On managed cloud Postgres, the provider's allowlist is the first gate. Your ADR is the second.

Gotchas

• Assuming cloud = safe - Managed hosts offer plpython3u on some tiers. Fix: Disable via parameter group or never grant install rights.
• Trusted flag changes between versions - An extension may become trusted in a newer PG release. Fix: Re-read pg_available_extensions after major upgrades.
• Extension in public schema - Even trusted extensions widen the attack surface if functions are callable by PUBLIC. Fix: REVOKE ALL ON FUNCTION ... FROM PUBLIC; and grant to app roles explicitly.
• SUPERUSER deploy pipelines - CI uses superuser creds "just for extensions." Fix: Dedicated deploy_role with minimal grants; superuser only for break-glass.
• Shadow IT installs - Developer runs CREATE EXTENSION on a staging clone that mirrors prod privileges. Fix: Periodic pg_extension audit job with alerting.
• CASCADE dependencies - Untrusted extension pulled in as dependency. Fix: Review pg_depend chain before approving parent extension.

Alternatives

FAQs

Related

• Extensions Basics - install and versioning
• Extension Allowlist Policy - governance ADR template
• Security Basics - threat model
• Roles Basics - least privilege patterns
• Extensions Best Practices - operational checklist

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PostGIS 3.5+, pgbouncer 1.x, and Patroni 3.x.