gem-hunter
Language: C
Unlock advanced Postgres features without granting SUPERUSER access
Supautils is an extension that unlocks advanced Postgres features without granting SUPERUSER access.
It's a loadable library that securely allows creating event triggers, publications, extensions to non-superusers. Built for cloud deployments where giving SUPERUSER rights to end users isn’t an option.
Completely managed by configuration — no tables, functions, or security labels are added to your database. This makes upgrades effortless and lets you apply settings cluster-wide solely via postgresql.conf.
Clone this repo and run
make && make installTo make supautils available to the whole cluster, you can add the following to postgresql.conf (use SHOW config_file for finding the location).
shared_preload_libraries = 'supautils'
supautils.privileged_role = 'your_privileged_role'
Or to make it available only on some PostgreSQL roles use session_preload_libraries.
ALTER ROLE role1 SET session_preload_libraries TO 'supautils';
The privileged role is a proxy role for a SUPERUSER, which is configured by supautils.superuser (defaults to the bootstrap user, i.e. the role used to start the Postgres cluster).
When the privileged role creates a superuser-only database object (like publications):
supautils.superuser, allowing the operation and creating the database object.
The privileged role can create publications. Once created they will be owned by the privileged role.
set role privileged_role; select current_setting('is_superuser'); current_setting ----------------- off (1 row) create publication p for all tables; CREATE PUBLICATION drop publication p; DROP PUBLICATION
The privileged role can create FDWs.
set role privileged_role; select current_setting('is_superuser'); current_setting ----------------- off (1 row) create extension postgres_fdw; CREATE EXTENSION create foreign data wrapper new_fdw handler postgres_fdw_handler validator postgres_fdw_validator; CREATE FOREIGN DATA WRAPPER
The privileged role is also able to create event triggers, while adding protection for privilege escalation.
To protect against privilege escalation, the event triggers created by the privileged role:
The skipping behavior can be logged by setting the supautils.log_skipped_evtrigs config to true, this is false by default.
Superuser have the additional restriction that the event trigger function must be owned by a superuser:
set role privileged_role; select current_setting('is_superuser'); current_setting ----------------- off (1 row) create event trigger evtrig on ddl_command_end execute procedure func(); -- func must be owned by the superuser CREATE EVENT TRIGGER
The privileged role won't be able to ALTER or DROP a superuser event trigger.
Important
Limitation: privileged role event triggers won't fire when creating publications, foreign data wrappers or extensions. See #123.
Certain settings like session_replication_role can only be set by superusers. The privileged role can be allowed to change these settings by listing them in:
supautils.privileged_role_allowed_configs="session_replication_role"
Some extensions also have their own superuser settings with a prefix, those can be configured by:
supautils.privileged_role_allowed_configs="ext.setting, other.nested"
You can also choose to allow all the extension settings by using a wildcard:
supautils.privileged_role_allowed_configs="ext.*"
Note
This functionality is adapted from pgextwlist.
supautils allows you to let non-superusers manage extensions that would normally require being a superuser. e.g. the hstore extension creates a base type, which requires being a superuser to perform.
To handle this, you can put the extension in supautils.privileged_extensions:
supautils.privileged_extensions = 'hstore'
Once you do, the extension creation will be delegated to the configured supautils.superuser. That means the hstore extension would be created as if by the superuser.
Note that extension creation would behave normally (i.e. no delegation) if the current role is already a superuser. Delegation to superuser will also be skipped if the extension does not have a control file on disk. This is done to prevent non-superusers from running code as a superuser via extensions managed by pg_tle or other similar virtual-extension-managing extensions.
This also works for updating and dropping privileged extensions.
If you don't want to enable this functionality, simply leave supautils.privileged_extensions empty. Extensions not in supautils.privileged_extensions would behave normally, i.e. created using the current role.
supautils also lets you set custom scripts per extension and globally that gets run before and after extension creation. The order that these scripts are executed is:
To make this work, configure the setting below:
supautils.extension_custom_scripts_path = '/some/path/extension-custom-scripts'
Then put the scripts inside the path:
> tree . ├── after-create.sql ├── before-create.sql └── fuzzystrmatch ├── after-create.sql └── before-create.sql
This is useful for things like creating a dedicated role per extension, granting privileges as needed to that role, etc.
By default, CREATE EXTENSION without a SCHEMA clause installs the extension into the first schema on the search_path (usually public). You can change this default:
supautils.default_extension_install_schema = 'extensions'
Once set, schema-less creates land in the configured schema:
postgres=> create extension hstore; CREATE EXTENSION postgres=> \dx hstore List of installed extensions Name | Version | Schema | Description --------+---------+------------+-------------------------------------- hstore | 1.8 | extensions | data type for storing sets of (key, value) pairs (1 row)
This is a default, not an enforcement — it only applies when nothing else specifies a schema. In order of precedence:
CREATE EXTENSION ... SCHEMA <schema> clause always winsschema pinned in the extension's control file always winssupautils.extensions_parameter_overrides always winssupautils.default_extension_install_schemaThe default also applies to extensions installed indirectly via CASCADE and to pg_tle extensions. The session's search_path is never affected, even if the statement errors.
If the configured schema does not exist, the setting is ignored and creation behaves normally. The setting requires superuser to change.
You can override CREATE EXTENSION parameters like so:
supautils.extensions_parameter_overrides = '{ "pg_cron": { "schema": "pg_catalog" } }'
Currently, only the schema parameter is supported.
These overrides will apply on CREATE EXTENSION, e.g.:
postgres=> create extension pg_cron schema public; CREATE EXTENSION postgres=> \dx pg_cron List of installed extensions Name | Version | Schema | Description ---------+---------+------------+------------------------------ pg_cron | 1.5 | pg_catalog | Job scheduler for PostgreSQL (1 row)
By default, any user who can create an extension can install any of its available versions via CREATE EXTENSION <name> VERSION '<version>' or ALTER EXTENSION <name> UPDATE TO '<version>'. To restrict this, set:
supautils.restrict_extension_versions = error
The possible modes are:
off (default): no restriction.warn: the specified version is ignored with a warning and the extension's default_version (declared in its control file) is used instead.error: the statement is rejected.Only superusers and the configured supautils.superuser are exempt; the restriction applies to everyone else:
-- with supautils.restrict_extension_versions = error postgres=> create extension hstore version '1.4'; ERROR: permission denied: only superusers can specify extension versions. Use CREATE EXTENSION <name> without a VERSION clause. -- with supautils.restrict_extension_versions = warn postgres=> create extension hstore version '1.4'; WARNING: only superusers can specify extension versions, ignoring version "1.4" and installing the default version CREATE EXTENSION
CREATE EXTENSION <name> and ALTER EXTENSION <name> UPDATE (without version clauses) remain allowed in all modes, subject to the existing privilege checks.
In Postgres, only table owners can create RLS policies for a table. This can be limiting if you need to allow certain roles to manage policies without allowing them to perform other DDL (e.g. to prevent them from dropping the table).
With supautils, this can be done like so:
supautils.policy_grants = '{ "my_role": ["public.not_my_table", "public.also_not_my_table"] }'
This allows my_role to manage policies for public.not_my_table and public.also_not_my_table without being an owner of these tables. This includes CREATE POLICY, ALTER POLICY, DROP POLICY and COMMENT ON POLICY.
You can also allow certain roles to drop triggers on a table without being the table owner:
supautils.drop_trigger_grants = '{ "my_role": ["public.not_my_table", "public.also_not_my_table"] }'
Reserved roles are meant to be used by managed services that connect to the database. They're protected from mutations by end users.
Important
The CREATEROLE problem is solved starting from PostgreSQL 16.
Additionally it solves a problem with the CREATEROLE privilege. As it can ALTER, DROP or GRANT other roles without restrictions.
From role attributes docs:
A role with CREATEROLE privilege can alter and drop other roles, too, as well as grant or revoke membership in them. However, to create, alter, drop, or change membership of a superuser role, superuser status is required; CREATEROLE is insufficient for that.
The above can be solved by configuring this extension to protect a set of roles, using the reserved_roles setting.
supautils.reserved_roles = 'connector, storage_admin'
Roles with the CREATEROLE privilege cannot ALTER or DROP the above reserved roles.
By default, reserved roles cannot have their settings changed. However their settings can be modified by the Privileged Role if they're configured like so:
supautils.reserved_roles = 'connector*, storage_admin*'
Certain default postgres roles are dangerous to expose to every database user. From pg default roles:
The pg_read_server_files, pg_write_server_files and pg_execute_server_program roles are intended to allow administrators to have trusted, but non-superuser, roles which are able to access files and run programs on the database server as the user the database runs as. As these roles are able to access any file on the server file system, they bypass all database-level permission checks when accessing files directly and they could be used to gain superuser-level access, therefore great care should be taken when granting these roles to users.
Supautils allows you to restrict doing GRANT pg_read_server_files TO my_role by setting:
supautils.reserved_memberships = 'pg_read_server_files'
This is also useful to limit memberships to the Reserved Roles.
If built against postgres 17 or below the version of supautils can be accessed using the
supautils.version read-only config item. It can be seen using the SHOW command as below.
SHOW supautils.version;
For postgres versions 18 and above this config is marked as deprecated as version can be
accessed through postgres natively via the pg_get_loaded_modules function as below.
SELECT *
FROM pg_get_loaded_modules();
Errors that originated from "permission denied" (SQLSTATE 42501) errors, will produce a HINT that includes the exact privileges missing to clear the error.
Only roles that are configured in supautils.hint_roles (comma-separated list of roles) get enhanced hints.
For example:
set role hint_role; insert into hint_target(id) values (1) on conflict (id) do update set id = excluded.id; ERROR: permission denied for table hint_target HINT: Grant the required privileges to the current role with: GRANT SELECT, INSERT, UPDATE ON public.hint_target TO hint_role;
The hint is only included when there are lacking SELECT, INSERT, UPDATE or DELETE privileges.
Important
Limitation: enhanced hints do not work for views under pg 18. See #182.
Nix with flakes is required.
First, Install Nix.
Edit /etc/nix/nix.conf, add:
experimental-features = nix-command flakes
and restart the daemon, on macOS:
sudo launchctl kickstart -k system/org.nixos.nix-daemon
If you are a repo member, you can skip building for hours and download packages instead by configuring the nxpg binary cache.
Go to https://app.cachix.org, log in with Github and create a personal auth token.
Use auth token:
nix run nixpkgs#cachix authtoken <token>
sudo nix run nixpkgs#cachix use nxpg
Caution
DO NOT use trusted-users in /etc/nix/nix.conf as it grants root without password. Instead, add the binary cache to extra-substituters and extra-trusted-public-keys.
For testing the module locally, execute:
# Open a development shell with all deps and tools present $ nix develop # test on pg 13 $ xpg -v 13 test # test on pg 14 $ xpg -v 14 test # you can also test manually with $ xpg -v 13 psql -U rolecreator
To test with a postgres built with assertions enabled:
$ xpg -v 17 --cassert testxpg test always runs the full test suite. To narrow it down to one test, set
its name in the REGRESS variable via MAKEFLAGS. For example, to run just the
test/sql/permission_hints.sql test:
$ MAKEFLAGS="REGRESS=permission_hints" xpg -v 15 test
Since supautils modifies default postgres behavior with hooks, we need to test exactly what it changes and see if we don't break existing functionality. For this you can use:
xpg -v 15 test-core
Works on pg 15, 16, 17 and 18.
For coverage, execute:
$ xpg -v 17 coverage
For automatic formatting of source and header files use:
$ supautils-style