Redacting passwords in audit logs v16

You can use the GUC parameter edb_filter_log.redact_password_commands under the postgresql.conf file to redact stored passwords in the log file.

The syntax is:

{CREATE|ALTER} {USER|ROLE|GROUP} identifier 
   { [WITH] [ENCRYPTED] PASSWORD 'nonempty_string_literal' 
    | IDENTIFIED BY { 'nonempty_string_literal' 
    | bareword } } 
    [ REPLACE { 'nonempty_string_literal' | bareword } ]

To enable password redaction, you must first enable the parameter by modifying the postgresql.conf file. Add the following value to those specified in the shared_preload_libraries parameter:

$libdir/edb_filter_log

Then, set edb_filter_log.redact_password_commands to true:

edb_filter_log.redact_password_commands = true

After modifying the postgresql.conf file, you must restart the server for the changes to take effect.

Examples

When the following statement is logged by log_statement, the server redacts the password to x. For example, this command is added to the log file:

CREATE USER carol with login PASSWORD '1safepwd';

It appears as:

2021-05-17 05:01:46.841 IST,"enterprisedb","edb",18415,"[local]",60a230f0.47ef,1,"idle",2021-05-17 05:01:36 IST,3/3,0,AUDIT,00000,"statement: CREATE USER carol with login PASSWORD 'x';",,,,,,,,,"psql","client backend","CREATE ROLE","","create"

When the following statement is logged by log_statement, the server identifies the new password, replace, and redact the password to x. For example, this command is added to the log file:

ALTER USER carol IDENTIFIED BY 'new_pass' REPLACE '1safepwd'; 

It appears as:

2021-05-19 04:41:45.718 IST,"enterprisedb","edb",19354,"[local]",60a23a72.4b9a,1,"idle",2021-05-19 04:41:23 IST,3/3,0,AUDIT,00000,"statement: ALTER USER carol IDENTIFIED BY 'x' REPLACE 'x';",,,,,,,,,"psql","client backend","ALTER ROLE","","alter"

The statement that includes a password is logged. The server redacts the password text to x. When the statement is logged as context for some other message, the server omits the statement from the context.