How can I stop Amazon RDS for PostgreSQL from logging my passwords in clear-text in the log files?
"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?"
"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 descriptionIf 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 ROLETime: 0.003sOutput 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): yYour call!ALTER ROLETime: 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'ResolutionUse PGAudit pluginUse 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 blockSet 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 locallyCreate 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}'md574e183386ccb9039d0537aeb03c03db9USER@postgresdb:pg> CREATE ROLE test_1 WITH PASSWORD 'md574e183386ccb9039d0537aeb03c03db9';CREATE ROLETime: 0.003sOutput 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 commandWhen 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 ROLEpg=> \password test_roleEnter new password:Enter it again:pg=> ALTER ROLE test_role LOGIN;ALTER ROLEpg=>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_encryption2020-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 informationPostgreSQL database log filesWorking with RDS and Aurora PostgreSQL logsPGAudit for RDS PostgreSQLFollow"