Avoid Printing Sensitive Messages like Password to Master Logs

Products

• VMware Tanzu Greenplum
• Greenplum
• VMware Tanzu Data Suite
• VMware Tanzu Data Suite

Issue/Introduction

How to prevent the master log from printing sensitive information like password when users are creating or altered. for eg.s

-- Creating a user

db01=# create user aaa with password 'a1';
NOTICE:  resource queue required -- using default resource queue "pg_default"
CREATE ROLE

-- The master log prints the messages along with the password for the user

2015-01-08 01:40:25.484248 PST,"gpadmin","db01",p12150,th-370254432,"[local]",,2015-01-08 01:39:16 PST,1152,con10,cmd5,seg-1,,dx10,x1152,sx1,"LOG","00000","statement: create user aaa with password 'a1';",,,,,,"create user aaa with password 'a1';",0,,"postgres.c",1543,

Environment

Resolution

Technically if you set log_statement=all, all the things you do on the database will be logged onto the master log and that is with the design of the parameter.

If you wish to avoid certain operation (like a password) not to be logged you can use

-- Alteration of the log_statement parameter

set log_statement=none; 

at the session level and run the command, this will make sure no commands are logged in the database logs by that session and turn on the parameter to enable the logging back.

Note- If log_duration is turned on, then the parameter will try to print the duration it took to execute the statement, so make sure you turn that off as well via "set log_duration=off" to avoid the message being printed when the password is being changed.

-- Hide using dynamic variables like for eg.s

Create the user with dynamic password:

[gpadmin@mdw pg_log]$ psql -c " create user aa with password ':pass' " -v pass=aa
NOTICE: resource queue required -- using default resource queue "pg_default"
CREATE ROLE

The message logged in the logs:

2015-01-08 01:56:36.513629 PST,"gpadmin","db01",p13296,th-370254432,"[local]",,2015-01-08 01:56:36 PST,1166,con12,cmd1,seg-1,,dx22,x1166,sx1,"LOG","00000","statement: create user aa with password ':pass'",,,,,," create user aa with password ':pass' ",0,,"postgres.c",1543,

-- Or, you can supply encrypted password as described in article How to Create a User with an Encrypted Password

 

Additional Information