Best Practices for Isolating Experimental PostgreSQL Extensions

This page shows how to install an unvetted PostgreSQL extension into a dedicated schema and non-superuser role so a failed experiment can never shadow a pg_catalog function, escalate privilege, or contaminate a production catalog — and how to tear it back down atomically when the experiment ends.

Up: Fallback Routing Strategies — the routing gate that decides whether an upgrade proceeds or diverts to a safe version; isolating the candidate first is what makes that gate safe to run against real workloads.

Context & When This Applies

Reach for this technique whenever you need to evaluate an extension that has not yet earned production trust: a community build with no signed release, a fork carrying an unreviewed patch, a pgvector or timescaledb release candidate, or an internal C-language extension mid-review. It applies to PostgreSQL 12 and newer, where the transactional-DDL guarantees the teardown relies on are dependable, and it assumes you can connect as a role permitted to CREATE SCHEMA and CREATE ROLE.

The core hazard is that PostgreSQL resolves unqualified object references through search_path, and the default path front-loads public and pg_catalog. An extension that creates a function named like a built-in — or registers an implicit cast or operator — can silently intercept calls that other sessions expected the server to answer. Sandboxing removes that ambiguity by giving the experiment its own namespace, its own owner, and no reach into shared schemas.

Schema-level isolation of an experimental extension Two side-by-side namespaces. On the left, an untouched production namespace holds the public schema and pg_catalog, which every production session reaches through its search_path so the first matching name wins. On the right, a dashed disposable namespace called ext_sandbox is owned by ext_sandbox_owner, a NOLOGIN role with no inherited superuser rights; it contains the experimental extension and its functions, bound only to ext_sandbox with EXECUTE revoked from PUBLIC, and it sits off every production search_path. A REVOKE ALL FROM PUBLIC barrier and a no-entry symbol between the two namespaces show that objects the experiment creates cannot shadow public or pg_catalog. A single DROP SCHEMA ext_sandbox CASCADE statement removes the whole experiment at once. Production session search_path = public, pg_catalog Production namespace untouched by the experiment public shared application objects pg_catalog built-in functions & operators first match on search_path wins REVOKE ALL FROM PUBLIC cannot shadow ext_sandbox isolated · disposable namespace ext_sandbox_owner NOLOGIN · no inherited superuser experimental_ext experimental_f · experimental_g bound to ext_sandbox only EXECUTE revoked from PUBLIC off every production search_path DROP SCHEMA ext_sandbox CASCADE one statement removes the whole experiment
The experiment lives in its own owner role and namespace, off every production search_path and revoked from PUBLIC, so it can neither shadow a built-in nor outlive a single CASCADE drop.

Concept: search_path Resolution and Role Scope

Two catalog mechanics make isolation work. First, search_path is an ordered list of schemas the planner walks to resolve an unqualified name; the first match wins. If an experimental function lives only in ext_sandbox and ext_sandbox is not on any production session’s path, that function is unreachable from production code — exactly what you want during evaluation. Second, object creation is governed by the CREATE privilege on the target schema, and execution by USAGE on the schema plus EXECUTE on the function. Revoking those from PUBLIC and granting them only to a scoped owner role means the blast radius of the experiment is a single schema you can drop in one statement.

This is the same privilege model formalised in Security Boundaries & Permissions; here we apply the narrowest possible slice of it to a throwaway namespace. Because CREATE EXTENSION and the catalog portions of DROP EXTENSION run inside the calling transaction, the whole provisioning-and-teardown cycle is atomic for catalog-only extensions — a ROLLBACK or a clean DROP ... CASCADE leaves no residue.

Runnable Implementation

Provision the sandbox as a single guarded transaction. Run it as a role that can create schemas and roles; the extension itself is bound to the isolated schema and owned by a role with no reach beyond it.

BEGIN;

-- 1. A throwaway owner role with no login and no inherited superuser rights.
CREATE ROLE ext_sandbox_owner NOLOGIN;

-- 2. A dedicated namespace, stripped of the default PUBLIC grants.
CREATE SCHEMA IF NOT EXISTS ext_sandbox AUTHORIZATION ext_sandbox_owner;
REVOKE ALL ON SCHEMA ext_sandbox FROM PUBLIC;
GRANT USAGE, CREATE ON SCHEMA ext_sandbox TO ext_sandbox_owner;

-- 3. Pre-empt function shadowing: nothing the extension creates is
--    executable by PUBLIC unless you later grant it explicitly.
ALTER DEFAULT PRIVILEGES IN SCHEMA ext_sandbox
    REVOKE ALL ON FUNCTIONS FROM PUBLIC;

-- 4. Pin resolution to the sandbox for this session only, then install.
--    SCHEMA binds every object the extension creates into ext_sandbox.
SET LOCAL search_path = ext_sandbox, pg_catalog;
CREATE EXTENSION IF NOT EXISTS experimental_ext SCHEMA ext_sandbox;

COMMIT;

Before wiring that into a pipeline, gate it with a Python preflight that confirms the target is actually available and not already installed cluster-wide. This is the same shape of check the router in the parent guide runs, narrowed to the isolation decision:

import psycopg2
from psycopg2.extras import RealDictCursor


def sandbox_readiness(dsn: str, target_ext: str) -> dict:
    """Decide whether target_ext can be safely sandboxed.

    Returns a status dict the caller can branch on:
      not_found  -> package missing from $SHAREDIR; do not proceed
      installed  -> already present cluster-wide; sandbox would collide
      ready      -> available and uninstalled; safe to provision
    """
    query = """
        SELECT name, default_version, installed_version
        FROM pg_available_extensions
        WHERE name = %s
    """
    with psycopg2.connect(dsn) as conn:
        with conn.cursor(cursor_factory=RealDictCursor) as cur:
            cur.execute(query, (target_ext,))
            row = cur.fetchone()

    if row is None:
        return {"status": "not_found", "target_version": None}
    if row["installed_version"] is not None:
        return {"status": "installed",
                "current_version": row["installed_version"]}
    return {"status": "ready", "target_version": row["default_version"]}

Map the version-to-artifact source this reads against with Extension Registry Mapping so a ready verdict points at a signed release rather than whatever a package feed happens to serve, and resolve the extension’s requires chain up front with Dependency Tree Analysis — an unmapped prerequisite will otherwise abort the CREATE EXTENSION inside the transaction.

Expected Output & Verification

A clean provisioning run commits silently. Verify the objects actually landed in the sandbox and nowhere else:

SELECT proname, pronamespace::regnamespace AS schema
FROM pg_proc
WHERE pronamespace = 'ext_sandbox'::regnamespace
ORDER BY proname;
    proname     |   schema
----------------+-------------
 experimental_f | ext_sandbox
 experimental_g | ext_sandbox
(2 rows)

Then confirm the extension registered against the isolated namespace, not public:

SELECT e.extname, n.nspname AS extnamespace
FROM pg_extension e
JOIN pg_namespace n ON n.oid = e.extnamespace
WHERE e.extname = 'experimental_ext';
     extname      | extnamespace
------------------+--------------
 experimental_ext | ext_sandbox
(1 row)

If the extension registers casts or operators, audit pg_cast and pg_operator for entries that resolve into pg_catalog — those bypass search_path entirely and are the one class of object a schema boundary cannot contain on its own.

Edge Cases & Gotchas

A missing prerequisite aborts the transaction. If the extension declares requires = 'pgcrypto' and that dependency is absent, CREATE EXTENSION raises:

ERROR:  required extension "pgcrypto" is not installed
HINT:  Use CREATE EXTENSION ... CASCADE to install required extensions too.

Resolve the full graph before provisioning rather than reaching for CASCADE, which would install prerequisites into the current search_path and can leak them into public. Stage each dependency into ext_sandbox explicitly, or add CASCADE only after confirming the path resolves to the sandbox.

A relocatable extension silently ignores your SCHEMA clause when the control file forbids it. If the .control file sets relocatable = false and hard-codes a schema, CREATE EXTENSION ... SCHEMA ext_sandbox fails with:

ERROR:  extension "experimental_ext" must be installed in schema "pg_catalog"

That is a signal to stop: an extension that demands pg_catalog cannot be sandboxed at the schema level and must instead be evaluated in a disposable database or container. Route those candidates to a throwaway instance per Test Environment Routing rather than forcing them into a shared cluster.

Teardown fails while a session still holds objects. Dropping the sandbox with dependents attached raises:

ERROR:  cannot drop schema ext_sandbox because other objects depend on it

Tear down in dependency order inside one transaction, and confirm zero residual references before dropping the schema:

BEGIN;
DROP EXTENSION IF EXISTS experimental_ext CASCADE;
-- Must return 0 before the schema drop is safe.
SELECT count(*)
FROM pg_depend
WHERE refclassid = 'pg_namespace'::regclass
  AND refobjid = 'ext_sandbox'::regnamespace;
DROP SCHEMA IF EXISTS ext_sandbox CASCADE;
DROP ROLE IF EXISTS ext_sandbox_owner;
COMMIT;

Not every extension is transaction-safe. An experiment that spawns a background worker, allocates shared memory, or edits shared_preload_libraries acts below the transaction boundary, so a ROLLBACK or DROP cannot fully undo it — a restart may be required to clear the worker. Classify the candidate before trusting the atomic teardown, and treat any shared_preload_libraries requirement as a mandatory postmaster restart under change control, tracked the same way as any other catalog-affecting change through Version Control Branching.