block_copy_command

Overview

Requires shared_preload_libraries = block_copy_command.

Version

Build

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

pig build pkg block_copy_command         # build RPM / DEB packages

Install

You can install block_copy_command 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 block_copy_command;          # Install for current active PG version

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

dnf install -y block_copy_command_18       # PG 18
dnf install -y block_copy_command_17       # PG 17
dnf install -y block_copy_command_16       # PG 16
dnf install -y block_copy_command_15       # PG 15
dnf install -y block_copy_command_14       # PG 14

apt install -y postgresql-18-block-copy-command   # PG 18
apt install -y postgresql-17-block-copy-command   # PG 17
apt install -y postgresql-16-block-copy-command   # PG 16
apt install -y postgresql-15-block-copy-command   # PG 15
apt install -y postgresql-14-block-copy-command   # PG 14

Preload:

shared_preload_libraries = 'block_copy_command';

Create Extension:

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.