Log security: what statement logging exposes

Observability puts query text on disk and in views. Query text contains whatever the application put in it.

What the log receives

$ ALTER SYSTEM SET log_statement = 'all'; SELECT pg_reload_conf();
CREATE ROLE demo_user LOGIN PASSWORD 'S3cr3t-P4ssw0rd!';
SELECT 'card '||'4111111111111111' AS pan WHERE 1=0;
ALTER ROLE demo_user PASSWORD 'An0ther-Secret!';

LOG:  statement: CREATE ROLE demo_user LOGIN PASSWORD 'S3cr3t-P4ssw0rd!';
LOG:  statement: SELECT 'card '||'4111111111111111' AS pan WHERE 1=0;
LOG:  statement: ALTER ROLE demo_user PASSWORD 'An0ther-Secret!';

Both passwords in cleartext. The card number too — in a statement that returned no rows, because WHERE 1=0 matched nothing. The statement is logged whether or not it matched anything.

There is no redaction. log_statement logs the text as submitted.

This applies equally to log_min_duration_statement, auto_explain, and anything else that records statement text: if the value is in the SQL, it is in the log.

What the statistics view receives

This one is less well known and worse.

$ SELECT pg_stat_statements_reset();
CREATE ROLE pss_demo LOGIN PASSWORD 'Another-S3cret!';
SELECT left(query,90) AS stored_query FROM pg_stat_statements WHERE query ILIKE '%pss_demo%';

                     stored_query
-------------------------------------------------------
CREATE ROLE pss_demo LOGIN PASSWORD 'Another-S3cret!'

$ SELECT * FROM pgbench_accounts WHERE aid = 42;
SELECT * FROM pgbench_accounts WHERE aid = 99;
SELECT calls, query FROM pg_stat_statements WHERE query LIKE '%pgbench_accounts WHERE aid%';

 calls |                     query
-------+-----------------------------------------------
   2 | SELECT * FROM pgbench_accounts WHERE aid = $1

Setting a password without writing it anywhere

psql> \password demo_user
Enter new password:
Enter it again:

\password prompts, hashes the password client-side into a SCRAM verifier, and sends ALTER ROLE ... PASSWORD 'SCRAM-SHA-256$...'. The cleartext never reaches the server, so it cannot reach the log or pg_stat_statements.

The same applies to any tool that pre-computes the verifier. What matters is that the cleartext is never in a statement.

What else ends up in query text

Passwords are the obvious case. The rest are more common:

• Personal data in predicates. WHERE email = '...', WHERE national_id = '...'.
• Card numbers, tokens, API keys passed as literals.
• Whole rows in INSERT ... VALUES (...) — every column, logged.
• Search terms, which can be sensitive in their own right.

Parameterised queries help enormously: WHERE email = $1 logs the parameter separately, and log_parameter_max_length controls whether parameters are logged at all.

log_parameter_max_length = 0        # do not log bind parameters
log_parameter_max_length_on_error = 0

Setting these to 0 keeps the shape of the query in the log while omitting the values. It makes debugging harder and is frequently the right trade for a system holding regulated data.

What to take from this

• log_statement logs the text as submitted. Measured: two passwords and a card number in cleartext.
• A statement is logged whether or not it matched any rows.
• pg_stat_statements does not normalise utility statements. Measured: a CREATE ROLE password stored verbatim.
• pg_monitor therefore grants password visibility. Treat it as privileged.
• Use \password, which hashes client-side.
• log_parameter_max_length = 0 keeps query shape without values.
• The real exposure is the log platform downstream, with different access and longer retention.

Cross-course references

• Secrets, PKI & Certificate Management — Part XVIII (Incidents and recovery) covers treating a credential that appeared in a log as compromised, which is the response statement logging can force.
• Observability for Production Sysadmins — Part LXXXII (Secrets and sensitive telemetry) covers the same problem for the logging pipeline itself, where the exposure is duplicated to every consumer.
• Linux for Production Sysadmins — Part XXXI (Audit and security logging) covers who can read the log files on the host.

Quiz