Contents

• block_copy_command
  • How it works
  • Requirements
  • Installation
    • Build from source
    • Enable the extension
  • Usage
    • Blocking behaviour
    • GUC: block_copy_command.enabled
    • GUC: block_copy_command.block_to
    • GUC: block_copy_command.block_from
    • GUC: block_copy_command.block_program
    • GUC: block_copy_command.hint
    • GUC: block_copy_command.blocked_roles
    • GUC: block_copy_command.audit_log_enabled
    • Audit log table
  • Testing
    • With Docker (recommended)
    • With pgrx test runner
  • License

block_copy_command

A PostgreSQL extension that blocks COPY commands by installing a ProcessUtility hook.

How it works

When loaded via shared_preload_libraries, the extension registers a hook into PostgreSQL’s utility command processing pipeline. COPY statements are intercepted before execution according to the following priority:

1. If the role is listed in block_copy_command.blocked_roles → always blocked, even superusers
2. If block_copy_command.block_program = on and the statement is COPY TO/FROM PROGRAM → always blocked, even superusers
3. If block_copy_command.enabled = off → allowed (for roles not in the blocklist)
4. If the user is a superuser → allowed (bypass)
5. Otherwise, direction is checked: block_copy_command.block_to and block_copy_command.block_from

ERROR:  COPY TO command is not allowed
HINT:   Contact DBA to request access
ERROR:  COPY FROM command is not allowed
ERROR:  COPY TO PROGRAM command is not allowed

All other SQL commands (DDL, DML, queries) are unaffected and pass through to the standard handler.

Requirements

• PostgreSQL 13–18
• Built with pgrx 0.17.0

Installation

Build from source

cargo install cargo-pgrx --version 0.17.0 --locked
cargo pgrx init --pg17 download   # or point to your system pg_config
cargo pgrx package --features pg17

Install the produced files:

cp target/release/block_copy_command-pg17/usr/lib/postgresql/17/lib/block_copy_command.so \
   $(pg_config --pkglibdir)/
cp target/release/block_copy_command-pg17/usr/share/postgresql/17/extension/block_copy_command* \
   $(pg_config --sharedir)/extension/

Enable the extension

Add to postgresql.conf:

shared_preload_libraries = 'block_copy_command'

Restart PostgreSQL, then in any database where you want the extension registered:

CREATE EXTENSION block_copy_command;

Note: The hook is active for the entire cluster as soon as the library is loaded — CREATE EXTENSION only registers the extension metadata.

Usage

Blocking behaviour

COPY is blocked for non-superusers by default:

-- as a regular user:
COPY my_table TO STDOUT;
-- ERROR:  COPY TO command is not allowed

COPY my_table FROM STDIN;
-- ERROR:  COPY FROM command is not allowed

COPY (SELECT * FROM my_table) TO '/tmp/out.csv';
-- ERROR:  COPY TO command is not allowed

Superusers are bypassed unless explicitly listed in block_copy_command.blocked_roles or unless block_copy_command.block_program = on:

-- as a superuser (not in blocked_roles):
COPY (SELECT 1) TO STDOUT;
-- 1

-- COPY TO PROGRAM is blocked for everyone by default:
COPY (SELECT 1) TO PROGRAM 'cat';
-- ERROR:  COPY TO PROGRAM command is not allowed

GUC: block_copy_command.enabled

Toggles the block for non-superusers at runtime. Only superusers can change this setting.

Value	Effect
on (default)	COPY blocked for non-superusers (subject to block_to/block_from)
off	COPY allowed (roles not in blocked_roles)

Per-role (takes effect on next connection):

ALTER ROLE etl_user SET block_copy_command.enabled = off;

-- revert
ALTER ROLE etl_user RESET block_copy_command.enabled;

Per-database:

ALTER DATABASE mydb SET block_copy_command.enabled = off;

Per-session (superuser only):

SET block_copy_command.enabled = off;
COPY ...;
SET block_copy_command.enabled = on;

GUC: block_copy_command.block_to

Controls whether COPY TO (export) is blocked for non-superusers. Only evaluated when enabled = on. Only superusers can change this setting.

Value	Effect
on (default)	COPY TO blocked for non-superusers
off	COPY TO allowed for non-superusers

Typical ETL pattern — allow import, block export:

-- Allow COPY FROM (import) for etl_user, keep COPY TO blocked
ALTER ROLE etl_user SET block_copy_command.block_from = off;

GUC: block_copy_command.block_from

Controls whether COPY FROM (import) is blocked for non-superusers. Only evaluated when enabled = on. Only superusers can change this setting.

Value	Effect
on (default)	COPY FROM blocked for non-superusers
off	COPY FROM allowed for non-superusers

GUC: block_copy_command.block_program

Blocks COPY TO PROGRAM and COPY FROM PROGRAM for all users, including superusers. This prevents shell command execution via COPY. Only superusers can change this setting.

Value	Effect
on (default)	COPY ... PROGRAM blocked for everyone
off	COPY ... PROGRAM allowed (subject to other rules)

-- Temporarily allow COPY TO PROGRAM for a superuser session:
SET block_copy_command.block_program = off;
COPY (SELECT 1) TO PROGRAM 'cat';
SET block_copy_command.block_program = on;

GUC: block_copy_command.hint

An optional custom hint appended to the error when a COPY command is blocked. Only superusers can change this setting.

Value	Effect
(empty, default)	No hint shown
any string	Shown as HINT: after the error message

SET block_copy_command.hint = 'Contact DBA to request access';

-- regular user now sees:
-- ERROR:  COPY TO command is not allowed
-- HINT:   Contact DBA to request access

Cluster-wide (in postgresql.conf):

block_copy_command.hint = 'Contact DBA to request access'

Per-database:

ALTER DATABASE mydb SET block_copy_command.hint = 'Contact DBA to request access';

GUC: block_copy_command.blocked_roles

A comma-separated list of role names that are always blocked from running COPY, regardless of superuser status or the enabled setting. Only superusers can change this setting.

-- block a specific superuser
SET block_copy_command.blocked_roles = 'alice';
COPY (SELECT 1) TO STDOUT;  -- blocked even if alice is a superuser

-- block multiple roles
SET block_copy_command.blocked_roles = 'alice, bob, etl_user';

Per-role (takes effect on next connection):

ALTER ROLE alice SET block_copy_command.blocked_roles = 'alice';

-- revert
ALTER ROLE alice RESET block_copy_command.blocked_roles;

GUC: block_copy_command.audit_log_enabled

Controls whether intercepted COPY events are written to block_copy_command.audit_log. Only superusers can change this setting.

Value	Effect
on (default)	Every intercepted COPY is recorded in the audit table
off	No rows are written (server log is unaffected)

-- Disable audit writes for a specific role:
ALTER ROLE etl_user SET block_copy_command.audit_log_enabled = off;

Audit log table

Every intercepted COPY command (allowed and blocked) is recorded in block_copy_command.audit_log:

Column	Type	Description
id	bigserial	Auto-incrementing primary key
ts	timestamptz	Wall-clock time of the event (clock_timestamp())
session_user_name	text	Role that authenticated (stable across SET ROLE)
current_user_name	text	Effective role at the time of COPY
query_text	text	Full query string
copy_direction	text	'TO' or 'FROM'
copy_is_program	bool	true for COPY … PROGRAM statements
client_addr	inet	Client IP address (NULL for Unix-socket connections)
application_name	text	application_name GUC of the client session
blocked	bool	true if the command was blocked
block_reason	text	NULL when allowed; one of role_listed, program_blocked, direction_blocked when blocked

Note on blocked events: the audit row is inserted before the ERROR is raised, so it is rolled back when the transaction aborts. For blocked commands the server LOG line is the authoritative record; audit rows are reliable only for allowed commands.

Example queries:

-- All COPY events in the last hour
SELECT ts, current_user_name, copy_direction, blocked, block_reason
FROM block_copy_command.audit_log
WHERE ts > now() - interval '1 hour'
ORDER BY ts DESC;

-- Blocked events only
SELECT *
FROM block_copy_command.audit_log
WHERE blocked
ORDER BY ts DESC;

-- Activity per user
SELECT current_user_name, count(*) AS total,
       count(*) FILTER (WHERE blocked) AS blocked_count
FROM block_copy_command.audit_log
GROUP BY current_user_name
ORDER BY total DESC;

The schema and table are locked down by default; grant SELECT to monitoring roles as needed:

GRANT USAGE ON SCHEMA block_copy_command TO monitoring_role;
GRANT SELECT ON block_copy_command.audit_log TO monitoring_role;

When a COPY command is blocked, the event is also written to the PostgreSQL server log at LOG level:

LOG:  blocked COPY TO program=false user="someuser" reason="direction_blocked"
ERROR:  COPY TO command is not allowed

Testing

With Docker (recommended)

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

This builds the extension inside Docker, starts a PostgreSQL 17 instance with the extension loaded, and runs the integration test suite covering:

• COPY TO and COPY FROM blocked for non-superusers (default)
• Direction-specific errors (COPY TO command is not allowed / COPY FROM command is not allowed)
• block_from=off: allows COPY FROM while keeping COPY TO blocked
• block_to=off: allows COPY TO while keeping COPY FROM blocked
• block_program=on: COPY TO/FROM PROGRAM blocked even for superusers
• Superuser bypass
• GUC block_copy_command.enabled toggle
• GUC block_copy_command.blocked_roles blocks specific roles including superusers
• DDL, DML, and regular queries unaffected
• Audit log: allowed COPY creates a row; correct copy_direction, copy_is_program, blocked, block_reason values
• Audit log: session_user_name and current_user_name both recorded
• audit_log_enabled=off suppresses writes
• Blocked COPY does not persist in audit log (transaction rollback)

With pgrx test runner

cargo pgrx test pg17

Runs the unit tests embedded in the source using pgrx’s own managed PostgreSQL instance.

License

See LICENSE.