supautils

Overview

Hook library only; no CREATE EXTENSION objects.

Version

Build

You can build the RPM / DEB packages for supautils using pig build:

pig build pkg supautils         # build RPM / DEB packages

Install

You can install supautils directly. First, make sure the PGDG and PIGSTY repositories are added and enabled:

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

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

pig install supautils;          # Install for current active PG version

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

dnf install -y supautils_18       # PG 18
dnf install -y supautils_17       # PG 17
dnf install -y supautils_16       # PG 16
dnf install -y supautils_15       # PG 15
dnf install -y supautils_14       # PG 14

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

Preload:

shared_preload_libraries = 'supautils';

Usage

Sources:

• v3.4.4 README
• v3.4.4 release
• Version restriction implementation

supautils is a loadable library that unlocks selected superuser-only PostgreSQL features for non-superusers through configuration. Upstream emphasizes that it adds no tables, functions, or security labels to the database.

Load it

Cluster-wide:

shared_preload_libraries = 'supautils'
supautils.privileged_role = 'your_privileged_role'

Per role:

ALTER ROLE role1 SET session_preload_libraries TO 'supautils';

Privileged role capabilities

The README documents a privileged proxy role that can create publications, foreign data wrappers, event triggers, and privileged extensions without granting SUPERUSER.

SET ROLE privileged_role;
CREATE PUBLICATION p FOR ALL TABLES;
DROP PUBLICATION p;

For event triggers, the README says privileged-role triggers run for non-superusers, skip superusers, and also skip reserved roles. It also documents one limitation: those triggers do not fire while creating publications, foreign data wrappers, or extensions.

Important configuration knobs

• supautils.superuser
• supautils.privileged_role
• supautils.privileged_role_allowed_configs
• supautils.privileged_extensions
• supautils.extension_custom_scripts_path
• supautils.constrained_extensions
• supautils.extensions_parameter_overrides
• supautils.policy_grants
• supautils.drop_trigger_grants
• supautils.reserved_roles
• supautils.reserved_memberships
• supautils.hint_roles
• supautils.log_skipped_evtrigs

Useful examples

Allow a non-superuser to create specific privileged extensions:

supautils.privileged_extensions = 'hstore'

Allow a role to manage RLS policies on tables it does not own:

supautils.policy_grants = '{ "my_role": ["public.not_my_table"] }'

Force an extension into a specific schema on CREATE EXTENSION:

supautils.extensions_parameter_overrides = '{ "pg_cron": { "schema": "pg_catalog" } }'

Protect managed-service roles from CREATEROLE users:

supautils.reserved_roles = 'connector, storage_admin'
supautils.reserved_memberships = 'pg_read_server_files'

Version Selection and Operational Boundaries

supautils.restrict_extension_versions controls explicit version clauses for non-superusers: off allows them, warn ignores them and selects the control-file default with a warning, and error rejects them. This applies to both extension creation and upgrades; superusers and the configured proxy superuser are exempt. Omitting an explicit version remains allowed subject to normal privilege checks.

Cluster preload requires a restart; role-specific session preload applies to new connections. Do not run CREATE EXTENSION for supautils itself. Source release 3.4.4 is a library update and has no SQL extension-update step. It avoids ACCESS EXCLUSIVE locks during allowlisted-table policy checks and restores the caller’s role on every exit from an elevated region.

Review allowed extensions and custom scripts as trusted code because their operations run with delegated superuser privileges. Enhanced privilege hints do not work for views on PostgreSQL 18 according to the tagged README. Test role transitions, event-trigger ownership and reserved-role protections before broadening grants.