block_copy_command

block_copy_command : Block COPY commands via a configurable ProcessUtility hook

Overview

Packages

Source

pig build pkg block_copy_command;		# build rpm/deb

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 block_copy_command;		# install via package name, for the active PG version

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

Config this extension to shared_preload_libraries:

shared_preload_libraries = 'block_copy_command';

Create this extension with:

CREATE EXTENSION block_copy_command;

Usage

• Source: README

block_copy_command installs a ProcessUtility hook that intercepts COPY statements. The hook is cluster-wide once the library is loaded, while CREATE EXTENSION only registers metadata in a database.

Enable It

shared_preload_libraries = 'block_copy_command'

CREATE EXTENSION block_copy_command;

Blocking Rules

By default, non-superusers cannot run COPY TO or COPY FROM:

COPY my_table TO STDOUT;
COPY my_table FROM STDIN;
COPY (SELECT * FROM my_table) TO '/tmp/out.csv';

Priority is documented as:

• block_copy_command.blocked_roles: always blocked, even superusers.
• block_copy_command.block_program = on: blocks COPY ... PROGRAM for everyone.
• block_copy_command.enabled = off: allows COPY for roles not in blocked_roles.
• Superusers otherwise bypass direction blocking.
• block_copy_command.block_to and block_copy_command.block_from control export/import blocking for non-superusers.

Main Settings

• block_copy_command.enabled: master switch for non-superuser blocking.
• block_copy_command.block_to: block COPY TO.
• block_copy_command.block_from: block COPY FROM.
• block_copy_command.block_program: block COPY TO/FROM PROGRAM for all users.
• block_copy_command.hint: append a custom HINT to blocked-command errors.
• block_copy_command.blocked_roles: comma-separated always-blocked roles.
• block_copy_command.audit_log_enabled: write intercepted events to the audit table.

Audit And Caveats

Allowed and blocked attempts are intercepted, and the extension defines block_copy_command.audit_log plus server-log entries for blocked events. The README notes one important caveat: blocked audit rows are inserted before the error is raised, so they are rolled back with the transaction. In practice, PostgreSQL server logs are the authoritative record for blocked COPY attempts.