pgextwlist

Overview

Version

Build

You can build the RPM packages for pgextwlist using pig build:

pig build pkg pgextwlist         # build RPM packages

Install

You can install pgextwlist directly. First, make sure the PGDG repository is added and enabled:

pig repo add pgdg -u          # Add PGDG repo and update cache

Install the extension using pig or apt/yum/dnf:

• Install
• pig
• dnf
• apt

pig install pgextwlist;          # Install for current active PG version

pig ext install -y pgextwlist -v 18  # PG 18
pig ext install -y pgextwlist -v 17  # PG 17
pig ext install -y pgextwlist -v 16  # PG 16
pig ext install -y pgextwlist -v 15  # PG 15
pig ext install -y pgextwlist -v 14  # PG 14

dnf install -y pgextwlist_18       # PG 18
dnf install -y pgextwlist_17       # PG 17
dnf install -y pgextwlist_16       # PG 16
dnf install -y pgextwlist_15       # PG 15
dnf install -y pgextwlist_14       # PG 14

apt install -y postgresql-18-pgextwlist   # PG 18
apt install -y postgresql-17-pgextwlist   # PG 17
apt install -y postgresql-16-pgextwlist   # PG 16
apt install -y postgresql-15-pgextwlist   # PG 15
apt install -y postgresql-14-pgextwlist   # PG 14

Preload:

shared_preload_libraries = 'pgextwlist';

Usage

Sources:

• pgextwlist v1.20 README
• Changes from v1.19 to v1.20

pgextwlist lets selected non-superusers run extension lifecycle commands for an explicit allowlist. It temporarily executes those commands with bootstrap-superuser authority, so the allowlist and any custom scripts are part of the database’s security boundary.

The module is preloaded and does not itself need CREATE EXTENSION.

Configure the Allowlist

Load the module for each backend:

local_preload_libraries = 'pgextwlist'
extwlist.extensions = 'hstore,cube,pg_stat_statements'

The list may also be assigned per role:

ALTER ROLE extension_admin
  SET extwlist.extensions = 'hstore,pg_stat_statements';

Reconnect after changing local_preload_libraries. A whitelisted user can then run:

CREATE EXTENSION hstore;
ALTER EXTENSION hstore UPDATE;
COMMENT ON EXTENSION hstore IS 'approved utility';
DROP EXTENSION hstore;

An extension not named in extwlist.extensions is rejected.

Restrict Database Ownership

Version 1.20 adds:

extwlist.restrict_to_database_owner = on

When enabled, the caller must also own the current database. This is off by default for compatibility. Enable it when extension administration should not cross database-ownership boundaries.

Custom Lifecycle Scripts

Set extwlist.custom_path to an existing readable directory. Version 1.20 raises an error for a missing or unreadable path rather than silently skipping it.

For extension extname, scripts under extname/ can include:

• before–1.0.sql and after–1.0.sql around creation of a specific version.
• before-create.sql and after-create.sql as creation fallbacks.
• before-update.sql and after-update.sql around an update.
• before-drop.sql and after-drop.sql around removal.

Templates can use @extschema@, @current_user@, and @database_owner@. Only trusted administrators should be able to write this directory because scripts execute with elevated authority.

Configuration Index

• local_preload_libraries: loads pgextwlist into new sessions.
• extwlist.extensions: comma-separated allowlist.
• extwlist.custom_path: base directory for lifecycle scripts.
• extwlist.restrict_to_database_owner: additionally require database ownership.

Security and Compatibility Notes

• Version 1.20 rejects substitution names containing quote, dollar, apostrophe, or backslash characters, addressing the command-injection class tracked as CVE-2023-39417.
• CREATE EXTENSION, DROP EXTENSION, ALTER EXTENSION UPDATE, and COMMENT ON EXTENSION are supported. ALTER EXTENSION ADD and DROP are not supported.
• Objects created through the elevated path are owned according to the extension/bootstrap-superuser behavior, not necessarily by the requesting role.
• Review extension SQL and custom scripts before adding a name. Whitelisting an extension grants the caller the power embodied by its install and update scripts.