pgextwlist

pgextwlist : PostgreSQL Extension Whitelisting

Overview

Packages

Source

pig build pkg pgextwlist;		# build rpm

Install

Make sure PGDG and PIGSTY repo available:

pig repo add pgsql -u   # add both repo and update cache

Install this extension with pig:

pig install pgextwlist;		# install via package name, for the active PG version

pig install pgextwlist -v 18;   # install for PG 18
pig install pgextwlist -v 17;   # install for PG 17
pig install pgextwlist -v 16;   # install for PG 16
pig install pgextwlist -v 15;   # install for PG 15
pig install pgextwlist -v 14;   # install for PG 14

Config this extension to shared_preload_libraries:

shared_preload_libraries = 'pgextwlist';

This extension does not need CREATE EXTENSION DDL command

Usage

pgextwlist: PostgreSQL extension whitelisting

pgextwlist implements extension whitelisting: only explicitly allowed extensions can be installed, and whitelisted extensions are installed with superuser privileges even when requested by non-superusers.

Configuration

Add to postgresql.conf:

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

Or per-role:

ALTER ROLE adminuser SET extwlist.extensions = 'pg_stat_statements, postgis';

Behavior

Non-superusers can install whitelisted extensions:

-- Allowed (hstore is whitelisted)
CREATE EXTENSION hstore;

-- Blocked (not whitelisted)
CREATE EXTENSION earthdistance;
-- ERROR: extension "earthdistance" is not whitelisted

Operations CREATE EXTENSION, DROP EXTENSION, ALTER EXTENSION ... UPDATE, and COMMENT ON EXTENSION are run as superuser for whitelisted extensions.

Custom Scripts

Place scripts in ${extwlist.custom_path}/extname/:

Custom scripts support template variables: @extschema@, @current_user@, @database_owner@.