Contents

• pg_command_fw
  • Building
  • Running tests
    • Unit tests (pgrx managed instance)
    • Integration tests (Docker)
  • Installation
  • Command categories
  • GUC reference
    • Master switch
    • Per-category flags
    • Cross-category
  • Audit log
  • Examples

pg_command_fw

A PostgreSQL extension that intercepts and optionally blocks DDL, utility commands, and dangerous built-in functions. Two hooks are used: ProcessUtility for DDL/utility statements, and the post-parse analyze hook for function calls such as pg_read_file. Each command category is independently controlled by a GUC flag.

Supported PostgreSQL versions: 15–18.

Building

The extension is built with pgrx.

Prerequisites: Rust toolchain, clang, PostgreSQL server dev headers.

cargo install cargo-pgrx --version 0.17.0 --locked
cargo pgrx init          # downloads and configures a managed PostgreSQL instance

Build for a specific PostgreSQL version (15–18):

cargo build --features pg17

Produce an installable package (.so + extension files):

cargo pgrx package --features pg17

Running tests

Unit tests (pgrx managed instance)

Spins up a temporary PostgreSQL process, runs all #[pg_test] functions, then shuts it down:

cargo pgrx test --features pg17

To run against a different PostgreSQL version replace pg17 with pg15–pg18.

Integration tests (Docker)

Builds the extension inside Docker and runs the full integration suite in tests/docker/test.sh against a real PostgreSQL 17 instance:

docker compose up --build --abort-on-container-exit --exit-code-from test

This is the same command run by CI on every push.

Installation

Add to postgresql.conf:

shared_preload_libraries = 'pg_command_fw'

Then create the extension in the target database:

CREATE EXTENSION pg_command_fw;

Command categories

Category	GUC	Default	Who is blocked
TRUNCATE	pg_command_fw.block_truncate	on	Non-superusers
DROP TABLE	pg_command_fw.block_drop_table	off	Non-superusers (opt-in)
ALTER SYSTEM	pg_command_fw.block_alter_system	on	Everyone including superusers
LOAD	pg_command_fw.block_load	on	Everyone including superusers
COPY … PROGRAM	pg_command_fw.block_copy_program	on	Everyone including superusers
Plain COPY	pg_command_fw.block_copy	off	Non-superusers (opt-in)
pg_read_file / pg_read_binary_file / pg_stat_file	pg_command_fw.block_read_file	on	Everyone including superusers

Superusers are always exempt from non-superuser checks unless they appear in pg_command_fw.blocked_roles.

GUC reference

Master switch

pg_command_fw.enabled (bool, default on) Set to off to disable all firewall checks without unloading the extension.

Per-category flags

pg_command_fw.block_truncate (bool, default on) Block TRUNCATE for non-superusers.

pg_command_fw.block_drop_table (bool, default off) Block DROP TABLE for non-superusers. When production_schemas is set, only drops targeting those schemas are blocked; otherwise all DROP TABLE is blocked.

pg_command_fw.production_schemas (string, default empty) Comma-separated list of schemas for DROP TABLE checks. Only schema-qualified table names are matched; unqualified names are not resolved via search_path.

pg_command_fw.block_alter_system (bool, default on) Block ALTER SYSTEM for all roles including superusers.

pg_command_fw.block_load (bool, default on) Block LOAD (dynamic library loading) for all roles including superusers.

pg_command_fw.block_copy_program (bool, default on) Block COPY … TO/FROM PROGRAM for all roles including superusers. Prevents shell command execution via COPY.

pg_command_fw.block_copy (bool, default off) Block plain COPY (to/from file or stdout) for non-superusers. Superusers are exempt unless listed in blocked_roles.

pg_command_fw.block_read_file (bool, default on) Block calls to pg_read_file(), pg_read_binary_file(), and pg_stat_file() for all roles including superusers. These functions allow reading arbitrary server-side files and represent the same data-exfiltration risk as COPY TO FILE. Enforced via the post-parse analyze hook, so calls are caught before planning regardless of how they are nested in the query.

Cross-category

pg_command_fw.blocked_roles (string, default empty) Comma-separated list of roles that are always blocked from any firewall-governed command, regardless of superuser status or per-category flags.

pg_command_fw.hint (string, default empty) Custom hint message appended to the error when a command is blocked (e.g. 'Contact your DBA to request access').

pg_command_fw.audit_log_enabled (bool, default on) Write every intercepted command to command_fw.audit_log via SPI. Blocked events are best-effort: the INSERT is rolled back when the transaction aborts, so the server log is authoritative for blocked events.

Audit log

Every intercepted command (allowed or blocked) is recorded in command_fw.audit_log:

Column	Type	Description
id	bigint	Auto-increment primary key
ts	timestamptz	Event timestamp
session_user_name	text	Session-level user
current_user_name	text	Current (possibly SET ROLE) user
query_text	text	Original query string
command_type	text	e.g. TRUNCATE, DROP_TABLE, ALTER_SYSTEM, LOAD, COPY_PROGRAM, COPY, READ_FILE, STAT_FILE
target_schema	text	Schema that triggered the block (DROP TABLE with production_schemas)
target_object	text	Object name (LOAD: library path)
client_addr	inet	Client IP address
application_name	text	application_name setting
blocked	bool	Whether the command was blocked
block_reason	text	Internal reason code

Examples

Block TRUNCATE and DROP TABLE in production schemas for all non-superusers:

ALTER SYSTEM SET pg_command_fw.block_truncate = on;
ALTER SYSTEM SET pg_command_fw.block_drop_table = on;
ALTER SYSTEM SET pg_command_fw.production_schemas = 'public, payments';
ALTER SYSTEM SET pg_command_fw.hint = 'File a ticket at https://internal/infra';
SELECT pg_reload_conf();

Prevent a specific role from running any governed command even if it is a superuser:

ALTER SYSTEM SET pg_command_fw.blocked_roles = 'app_deploy';
SELECT pg_reload_conf();

Temporarily disable the firewall for a maintenance session:

SET pg_command_fw.enabled = off;
TRUNCATE big_table;
SET pg_command_fw.enabled = on;