I want to run a CREATE USER or an ALTER USER statement using Amazon Relational Database Service (Amazon RDS) for PostgreSQL. When I do this, PostgreSQL logs my password in clear-text in the log files. How can I stop PostgreSQL from showing my password 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.
- Turn on pgaudit for your Amazon RDS PostgreSQL instance.
- 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