Pivotal Knowledge Base

Follow

Avoid priniting sensitive messages ( like password ) to master logs

Problem

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

-- Creating a user

flightdata=# 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","flightdata",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,

Solution

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 design of the parameter.

If you wish to avoid certain operation (like 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","flightdata",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 similar to the one described in the article

Comments

  • Avatar
    Gijo George

    Even after disabling log_statement at session level, master log is showing password.
    gtr_dev=# set log_statement=none;
    SET
    gtr_dev=# alter role test11 with password '99999';
    ALTER ROLE
    gtr_dev=# show log_statement;

    log_statement

    none
    (1 row)

    But master log is printing that information.

    2016-01-05 04:11:29.208341 EST,"gpadmin","gtr_dev",p10049,th-1750187920,"[local]",,2016-01-05 03:58:33 EST,0,con1545295,cmd7,seg-1,,,,,"LOG","00000","duration: 27.196 ms",,,,,,"alter role test11 with password '99999';",0,,"postgres.c",1829,
    [gpadmin@swgrnd02a pg_log]$

  • Avatar
    Faisal Ali

    Hi Gijo,

    Are you sure that the message that you have pointed out is after you have set log_statement=none.

    As far as i can see the log_statement works as described.

    • The logfile that is currently being written
    flightdata=# \! ls -ltr | tail
    -rw------- 1 gpadmin gpadmin   36015 Dec 30 10:02 gpdb-2015-12-30_075353.csv
    -rw------- 1 gpadmin gpadmin   44565 Dec 31 05:48 gpdb-2015-12-31_000000.csv
    -rw------- 1 gpadmin gpadmin   28593 Dec 31 05:52 gpdb-2015-12-31_055213.csv
    -rw------- 1 gpadmin gpadmin  121167 Dec 31 07:39 gpdb-2015-12-31_055217.csv
    -rw------- 1 gpadmin gpadmin   30332 Jan  4 05:55 gpdb-2016-01-04_055531.csv
    -rw------- 1 gpadmin gpadmin   30518 Jan  4 05:55 gpdb-2016-01-04_055533.csv
    -rw------- 1 gpadmin gpadmin   12987 Jan  4 05:55 startup.log
    -r-------- 1 gpadmin gpadmin     105 Jan  4 05:55 gp_era
    -rw------- 1 gpadmin gpadmin   26367 Jan  4 08:32 gpdb-2016-01-04_055539.csv
    -rw------- 1 gpadmin gpadmin    1004 Jan  5 03:31 gpdb-2016-01-05_000000.csv
    
    • Rotating to a new a new logfile.
    flightdata=# select pg_logfile_rotate();
     pg_logfile_rotate 
    -------------------
     t
    (1 row)
    
    • The new logfile is of the name
    flightdata=# \! ls -ltr | tail
    -rw------- 1 gpadmin gpadmin   44565 Dec 31 05:48 gpdb-2015-12-31_000000.csv
    -rw------- 1 gpadmin gpadmin   28593 Dec 31 05:52 gpdb-2015-12-31_055213.csv
    -rw------- 1 gpadmin gpadmin  121167 Dec 31 07:39 gpdb-2015-12-31_055217.csv
    -rw------- 1 gpadmin gpadmin   30332 Jan  4 05:55 gpdb-2016-01-04_055531.csv
    -rw------- 1 gpadmin gpadmin   30518 Jan  4 05:55 gpdb-2016-01-04_055533.csv
    -rw------- 1 gpadmin gpadmin   12987 Jan  4 05:55 startup.log
    -r-------- 1 gpadmin gpadmin     105 Jan  4 05:55 gp_era
    -rw------- 1 gpadmin gpadmin   26367 Jan  4 08:32 gpdb-2016-01-04_055539.csv
    -rw------- 1 gpadmin gpadmin    1260 Jan  5 03:32 gpdb-2016-01-05_000000.csv
    -rw------- 1 gpadmin gpadmin       0 Jan  5 03:32 gpdb-2016-01-05_033248.csv
    
    • Setting up the role and the password autentication
    flightdata=# set log_statement=none;
    SET
    flightdata=# create role testpasswd;
    NOTICE:  resource queue required -- using default resource queue "pg_default"
    CREATE ROLE
    flightdata=# alter role testpasswd with password '9999';
    ALTER ROLE
    
    • The message on the logfile is none.
    flightdata=# \! ls -ltr | tail
    -rw------- 1 gpadmin gpadmin   44565 Dec 31 05:48 gpdb-2015-12-31_000000.csv
    -rw------- 1 gpadmin gpadmin   28593 Dec 31 05:52 gpdb-2015-12-31_055213.csv
    -rw------- 1 gpadmin gpadmin  121167 Dec 31 07:39 gpdb-2015-12-31_055217.csv
    -rw------- 1 gpadmin gpadmin   30332 Jan  4 05:55 gpdb-2016-01-04_055531.csv
    -rw------- 1 gpadmin gpadmin   30518 Jan  4 05:55 gpdb-2016-01-04_055533.csv
    -rw------- 1 gpadmin gpadmin   12987 Jan  4 05:55 startup.log
    -r-------- 1 gpadmin gpadmin     105 Jan  4 05:55 gp_era
    -rw------- 1 gpadmin gpadmin   26367 Jan  4 08:32 gpdb-2016-01-04_055539.csv
    -rw------- 1 gpadmin gpadmin    1260 Jan  5 03:32 gpdb-2016-01-05_000000.csv
    -rw------- 1 gpadmin gpadmin     248 Jan  5 03:33 gpdb-2016-01-05_033248.csv
    flightdata=# 
    flightdata=# \! cat gpdb-2016-01-05_033248.csv
    2016-01-05 03:33:10.524781 CST,"gpadmin","flightdata",p7212,th72943392,"[local]",,2016-01-05 03:32:12 CST,2835,con32,cmd3,seg-1,,dx31,x2835,sx1,"LOG","00000","statement: set log_statement=none;",,,,,,"set log_statement=none;",0,,"postgres.c",1553,
    flightdata=# 
    

    Thanks

  • Avatar
    Gijo George

    Yes this happened after disabling log_statement at session level.

    gtr_dev=# set log_statement=none;
    SET
    gtr_dev=# show log_statement;

    log_statement

    none
    (1 row)

    gtr_dev=# alter role test11 with password 'gijogeorge';
    ALTER ROLE
    gtr_dev=# show log_statement;

    log_statement

    none
    (1 row)
    Details from logs on the above sequence.

    2016-01-05 04:44:23.345534 EST,"gpadmin","gtr_dev",p10049,th-1750187920,"[local]",,2016-01-05 03:58:33 EST,0,con1545295,cmd12,seg-1,,,,,"LOG","00000","duration: 113.223 ms",,,,,,"set log_statement=none;",0,,"postgres.c",1829,
    2016-01-05 04:44:27.621216 EST,"gpadmin","gtr_dev",p10049,th-1750187920,"[local]",,2016-01-05 03:58:33 EST,0,con1545295,cmd13,seg-1,,,,,"LOG","00000","duration: 1.377 ms",,,,,,"show log_statement;",0,,"postgres.c",1829,
    2016-01-05 04:44:51.132537 EST,"gpadmin","gtr_dev",p10049,th-1750187920,"[local]",,2016-01-05 03:58:33 EST,0,con1545295,cmd14,seg-1,,,,,"LOG","00000","duration: 2411.031 ms",,,,,,"alter role test11 with password 'gijogeorge';",0,,"postgres.c",1829,
    2016-01-05 04:44:54.675434 EST,"gpadmin","gtr_dev",p10049,th-1750187920,"[local]",,2016-01-05 03:58:33 EST,0,con1545295,cmd15,seg-1,,,,,"LOG","00000","duration: 1.876 ms",,,,,,"show log_statement;",0,,"postgres.c",1829,

    Greenplum version is 4.3.5.1

  • Avatar
    Faisal Ali

    Hi,

    The message is related to parameter "log_duration" being turned on , please do turn that off so that it doesn't print the time it took to execute the statement , i will be updating the document with the same information.

    Thanks

Powered by Zendesk