How can I stop Amazon RDS for PostgreSQL from logging my passwords in clear-text in the log files?

Short description

If you set the log_statement parameter to ddl or all, and run a CREATE ROLE/USER ... WITH PASSWORD ... ; or ALTER ROLE/USER ... WITH PASSWORD ... ;, command, then PostgreSQL creates an entry in the PostgreSQL logs. PostgreSQL logs the password in clear-text, which can cause a potential security risk.

Currently, PostgreSQL doesn't identify sensitive information. This is expected behavior and is according to the design of PostgreSQL engine.

This example runs a CREATE ROLE statement with password, and then displays the password in the logs in clear-text:

USER@postgresdb:pg> CREATE ROLE test_role WITH PASSWORD 'test123';
CREATE ROLE
Time: 0.003s

Output in the logs:

2020-04-15 14:57:29 UTC:x.x.x.x(42918):USER@pg:[13790]:LOG: statement: CREATE ROLE test_role WITH PASSWORD 'test123'

This example runs an ALTER ROLE statement with password, and then displays the password in the logs in clear-text:

USER@postgresdb:pg> ALTER ROLE test_role WITH PASSWORD 'test';
You're about to run a destructive command.
Do you want to proceed? (y/n): y
Your call!
ALTER ROLE
Time: 0.004sOutput in the logs:

Output in the logs:

2020-04-15 14:59:45 UTC:x.x.x.x(42918):USER@pg:[13790]:STATEMENT: ALTER ROLE test_role SET PASSWORD 'test'

Resolution

Use PGAudit plugin

Use the pgaudit extension to redact your password from the PostgreSQL logs.

1. Turn on pgaudit for your Amazon RDS PostgreSQL instance.
2. In your customer parameter group, set the pgaudit.log parameter to specify which statement class you want to log. This parameter can take multiple values, like DDL, role, write, and read.

Note: The main difference between pgaudit.log='DDL' and log_statement='DDL' is that pgaudit, DDL doesn't record any CREATE/ALTER ROLE query in Postgres logs.

You can log CREATE/ALTER ROLE by adding ROLE to pgaudit.log. This redacts your password.

Output in logs:

2020-07-09 13:33:50 UTC:x.x.x.x(58670):grysa@pg:[26513]:LOG:  
AUDIT: SESSION,3,1,ROLE,CREATE ROLE,,,CREATE ROLE test_role WITH LOGIN 
PASSWORD <REDACTED>,<not logged>

Set log_statement to none at the session level inside a transaction block

Set the log_statement parameter to none at the session level inside a transaction block to stop PostgreSQL from recording the operation entirely.

Note: When both pgaudit.log and log_statement are set to DDL, then log_statement must be set to none at the session level.

Example:

BEGIN;
SET LOCAL log_statement = 'none';
ALTER ROLE ... WITH PASSWORD ...;
COMMIT;

Create password hash locally

Create your password hash locally, and then use the hash when creating or altering the role or user password.

Note: The hash you create and use to run your statement is still visible in the logs. You can decrypt this, but it's not logged in clear-text.

Example:

[ec2-user@ip-x.x.x.x ~]$ username='test_1'; dbpass='test123'; echo -n "${dbpass}${username}" | md5sum | awk '{print "md5" $1}'
md574e183386ccb9039d0537aeb03c03db9

USER@postgresdb:pg> CREATE ROLE test_1 WITH PASSWORD 'md574e183386ccb9039d0537aeb03c03db9';
CREATE ROLE
Time: 0.003s

Output in the logs:

2020-04-15 15:12:08 UTC:x.x.x.x(42918):grysa@pg:[13790]:LOG: statement: CREATE ROLE test_1 WITH PASSWORD 
'md574e183386ccb9039d0537aeb03c03db9'

Use the \password command

When using PostgreSQL, you can use the \password function built into the PostgreSQL client. This prompts you for a new password when you create or alter an existing role password. The PostgreSQL logs only the hash for the password.

Note: This solution also shows the password hash in the Postgres logs but doesn't log it in clear-text.

Run the following command:

pg=> CREATE ROLE test_role NOLOGIN;
CREATE ROLE
pg=> \password test_role
Enter new password:
Enter it again:
pg=> ALTER ROLE test_role LOGIN;
ALTER ROLE
pg=>

Output in logs:

2020-04-09 12:29:29 UTC:x.x.x.x(39876):grysa@pg:[1879]:LOG: statement: CREATE ROLE test_role NOLOGIN;
2020-04-09 12:30:02 UTC:x.x.x.x(39876):grysa@pg:[1879]:LOG: statement: show password_encryption
2020-04-09 12:30:02 UTC:x.x.x.x(39876):grysa@pg:[1879]:LOG: statement: ALTER USER test_role PASSWORD 'md5175cad7c36a640b1fcfa0144056923f5'
2020-04-09 12:30:30 UTC:x.x.x.x(39876):grysa@pg:[1879]:LOG: statement: ALTER ROLE test_role LOGIN;

Related information

PostgreSQL database log files

Working with RDS and Aurora PostgreSQL logs

PGAudit for RDS PostgreSQL