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

4 minute read

I want to use Amazon Relational Database Service (Amazon RDS) for PostgreSQL to run a CREATE USER or an ALTER USER statement. When I do this, PostgreSQL logs my password in clear text in the log files.

Short description

You can set the log_statement parameter to ddl or all, and then run one of the following commands:

CREATE ROLE/USER ... WITH PASSWORD ... ;
ALTER ROLE/USER ... WITH PASSWORD ... ;,

When you do, PostgreSQL creates an entry in the PostgreSQL logs. PostgreSQL logs the password in cleartext and can cause a potential security risk.

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 cleartext:

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 cleartext:

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.004s

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.

Turn on pgaudit for your Amazon RDS for PostgreSQL instance.
In your custom parameter group, set the pgaudit.log parameter to specify the statement class that you want to log. This parameter takes 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.

Add ROLE to pgaudit.log to log CREATE/ALTER ROLE. 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

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

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 you create or alter 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 you use PostgreSQL, you can use the \password function built in to 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.

To use the \password function, 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;