Is it safe to put a credential in CREATE SERVER, USER MAPPING or SUBSCRIPTION?

Foreign server, user mapping or subscription created with an embedded credential

Critical: a working credential committed to the repository; rotate it, git history keeps it.

What happens

CREATE USER MAPPING … OPTIONS (password '…'), CREATE SERVER … OPTIONS (secret_key '…') and CREATE SUBSCRIPTION … CONNECTION 'host=… password=…' are the three places Postgres itself asks for a remote credential, and a migration is the wrong file to answer in. The value lands in git history, pull-request diffs and CI output, and pg_user_mappings / pg_subscription keep it readable to superusers afterwards. Silent when the option holds a placeholder, is empty, or points at a passfile / certificate instead.

Why it is dangerous on a populated table

Table size does not change this one: a committed credential is live from the first row, and git history keeps it after the fix.

Fires on

CREATE USER MAPPING FOR app_user SERVER analytics OPTIONS (user 'reporter', password 's3cret');

The safe pattern

Keep the object in the migration and the credential out of it: create the mapping or subscription connection from the deploy job with the value read from the secret store, or authenticate without a password, a client certificate (sslcert/sslkey) or a passfile on the server (passfile= in the conninfo, password_required 'false' on postgres_fdw mappings).

CREATE SERVER analytics FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'analytics.internal', dbname 'reports');
CREATE USER MAPPING FOR app_user SERVER analytics OPTIONS (user 'reporter', password_required 'false');
-- and a certificate or passfile on the server, provisioned by the deploy, not committed.

Fixtures

The rule ships with these files and the test suite runs them on every change: the first set must fire, the second must stay silent.

Fires (4)

alter subscription uri

ALTER SUBSCRIPTION orders_sub CONNECTION 'postgres://replicator:repl-pass-2026@primary.internal/app';

foreign server secret key

CREATE SERVER exports FOREIGN DATA WRAPPER s3_fdw OPTIONS (region 'eu-west-1', access_key 'reports-writer', secret_key 'wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY');

subscription conninfo

CREATE SUBSCRIPTION orders_sub CONNECTION 'host=primary.internal dbname=app user=replicator password=repl-pass-2026' PUBLICATION orders_pub;

user mapping password

CREATE USER MAPPING FOR app_user SERVER analytics OPTIONS (user 'reporter', password 's3cret-pass');

Stays silent (4)

placeholder password

CREATE USER MAPPING FOR app_user SERVER analytics OPTIONS (user 'reporter', password '{{ANALYTICS_PASSWORD}}');

server host only

CREATE SERVER analytics FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'analytics.internal', dbname 'reports', port '5432');

subscription passfile

CREATE SUBSCRIPTION orders_sub CONNECTION 'host=primary.internal dbname=app user=replicator passfile=/var/lib/postgresql/.pgpass sslmode=verify-full' PUBLICATION orders_pub;

user mapping without password

CREATE USER MAPPING FOR app_user SERVER analytics OPTIONS (user 'reporter', password_required 'false');

How to check locally

Catch this before it ships

This rule runs locally in the free CLI, or with the full corpus through the hosted service. No install, nothing leaves your machine:

npx bolvrk check migration.sql

Browse all 135 rules

← BC001 BC003 →