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
    • Audit logging
  • 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;

Audit logging

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

LOG:  current_user = "someuser"
ERROR:  COPY TO command is not allowed
HINT:   Contact DBA to request access

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

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.