Extension Packaging, CREATE EXTENSION, Versioning, Dependencies, and Trust Boundaries

Learning outcomes

ServiceHub wants fuzzy matching, key/value utilities, cross-database access, and possibly third-party observability features. PostgreSQL deliberately supports such extensibility, but CREATE EXTENSION is not an app-store install button. An extension is a database-level package whose control and SQL files—and sometimes a native shared library—must already be installed on the server. Installing one can execute privileged database code, so package provenance and trust boundaries are part of database security.

1. Available on the server is not installed in this database

SELECT current_setting('server_version') AS server_version;SELECT name, default_version, installed_version, commentFROM pg_available_extensionsWHERE name IN ('hstore','postgres_fdw','pg_trgm','plpgsql')ORDER BY name;SELECT e.extname, e.extversion,       n.nspname AS schema_name,       pg_get_userbyid(e.extowner) AS extension_owner,       e.extrelocatableFROM pg_extension AS eJOIN pg_namespace AS n ON n.oid = e.extnamespaceORDER BY e.extname;

pg_available_extensions reflects control files the running server can discover. pg_extension shows what this specific database has registered. If a module such as hstore is absent from the available view, install the PostgreSQL 18 matching supplied/contrib package first; do not download an arbitrary binary built for another major version.

2. Connect catalog availability to the server installation

The available-extension views are database-facing evidence that the running server discovered control files. When package provenance matters, also record the PostgreSQL installation paths and package-manager identity for the same server build. PostgreSQL 18 can search its configured extension_control_path; pg_config reports paths associated with a PostgreSQL installation when that utility is installed.

SELECT current_setting('extension_control_path') AS extension_control_path,       current_setting('server_version') AS server_version;

pg_config --versionpg_config --sharedirpg_config --pkglibdir

Do not assume the pg_config found first in a shell PATH describes the same server process. Compare its major/version and installation provenance with the running server and the operating-system package inventory.

3. Inspect the control-file contract before installation

SELECT name, version, installed,       superuser, trusted, relocatable, schema, requires, commentFROM pg_available_extension_versionsWHERE name IN ('hstore','postgres_fdw')ORDER BY name, version;

These columns expose selected control-file properties. superuser=true normally means only a superuser can install/update the extension. When trusted=true is also declared, PostgreSQL permits a non-superuser with CREATE on the current database to install it; the install/update script itself executes as the bootstrap superuser. This is why marking an extension trusted is a strong security promise, not a convenience flag.

The requires array declares prerequisite extensions. CREATE EXTENSION ... CASCADE can install missing dependencies recursively, but it selects their default versions. Production change review should normally resolve dependency versions deliberately instead of using CASCADE as discovery.

4. Secure the destination schema

CREATE SCHEMA IF NOT EXISTS ch19_ext AUTHORIZATION servicehub_owner;REVOKE CREATE ON SCHEMA ch19_ext FROM PUBLIC;DROP ROLE IF EXISTS ch19_extension_installer;CREATE ROLE ch19_extension_installer NOLOGIN;GRANT CREATE ON DATABASE servicehub_lab TO ch19_extension_installer;GRANT USAGE, CREATE ON SCHEMA ch19_ext TO ch19_extension_installer;

A secure install schema is not cosmetic. Extension installation/update scripts resolve object names through a controlled search_path; an untrusted user's trojan-horse objects in writable installation/dependency schemas can turn a careless privileged extension script into privilege escalation.

5. Install a trusted supplied extension as a non-superuser role

hstore is a PostgreSQL-supplied trusted extension in a default installation. First verify its row in pg_available_extension_versions. If unavailable, install the matching PostgreSQL 18 contrib package and repeat the preflight.

SET ROLE ch19_extension_installer;CREATE EXTENSION hstore  SCHEMA ch19_ext;RESET ROLE;REVOKE CREATE ON DATABASE servicehub_lab FROM ch19_extension_installer;SELECT e.extname, e.extversion,       n.nspname AS schema_name,       pg_get_userbyid(e.extowner) AS extension_ownerFROM pg_extension AS eJOIN pg_namespace AS n ON n.oid = e.extnamespaceWHERE e.extname = 'hstore';

The extension object is owned by the caller role. For a trusted extension whose script is allowed to run with bootstrap-superuser privileges, contained objects can have different ownership according to the extension packaging rules. Do not infer contained-object privileges merely from pg_extension.extowner.

6. What files does CREATE EXTENSION conceptually consume?

An SQL-only extension can still be security-sensitive. A native extension adds another boundary: server process code can corrupt memory, crash a backend, or interact with the OS according to its implementation and privileges.

7. IF NOT EXISTS is not verification

CREATE EXTENSION IF NOT EXISTS hstore;-- NOTICE: extension "hstore" already exists, skipping-- The NOTICE does not prove the existing object is in the desired-- schema/version or matches today's package files. Verify:SELECT e.extname, e.extversion, n.nspnameFROM pg_extension AS eJOIN pg_namespace AS n ON n.oid = e.extnamespaceWHERE e.extname = 'hstore';

PostgreSQL explicitly warns that IF NOT EXISTS only avoids an error; it does not prove the existing extension matches what the current statement would install. Treat installed version/schema/dependencies as acceptance evidence.

8. Review update paths before running an update

SELECT name, default_version, installed_versionFROM pg_available_extensionsWHERE name = 'hstore';SELECT *FROM pg_extension_update_paths('hstore')WHERE source = (  SELECT extversion FROM pg_extension WHERE extname = 'hstore')ORDER BY target;

ALTER EXTENSION hstore UPDATE can only apply update scripts that exist in the installed package. PostgreSQL chooses an available path; it does not understand semantic version ordering. Review the path and extension release notes, back up/restore-test, and then apply the update in a change window appropriate to the extension's objects.

9. Dependencies and member objects

SELECT pg_describe_object(d.classid, d.objid, d.objsubid) AS memberFROM pg_depend AS dJOIN pg_extension AS e  ON e.oid = d.refobjidWHERE d.deptype = 'e'  AND e.extname = 'hstore'ORDER BY memberLIMIT 30;

Extension member objects are managed as one package. Changing a member manually can create drift: pg_dump normally dumps CREATE EXTENSION, not the member's modified definition. Extension changes belong in versioned update scripts or a separately owned object outside the extension.

10. Checkpoint

Authoritative references

Extension, FDW, language, and server-code behavior is privilege-, package-, version-, and platform-sensitive. These PostgreSQL 18 primary sources define the mechanisms used here.

• PostgreSQL 18 — CREATE EXTENSION
• PostgreSQL 18 — Packaging Related Objects into an Extension
• PostgreSQL 18 — pg_available_extension_versions
• PostgreSQL 18 — Supplied Modules and Trusted Extensions
• PostgreSQL 18 — ALTER EXTENSION