HOW-TO

PASSWORD ENCRYPTION

ISSUE : How what do we have to do to prevent postgres from putting a password in clear text in the audit file for a command like below alter role testrole password ‘testpassword’

It seems you might have enabled the log_statements to track the SQL statements which are getting fired on the database, unfortunately for all the values of log_statement except ‘none’ which is the default value, a password will also be logged along with the other SQL queries.

In order to avoid this, either DBA /user can use session-level value for log_statement while Creating the user /

Altering the user to change the password i.e, SET log_statement ='<value>’

Work-around 1 : If you want to skip entire User create / Alter user statement

[enterprisedb@slave bin]$ ./psql -d edb -p 5444 -U enterprisedb

psql.bin (9.5.0.5)

Type “help” for help.

edb=# set log_statement=’none’;

SET

edb=#

edb=# create user u2 with password ‘u2’;

CREATE ROLE

edb=#

Which will skip the logging of particular statements fired from the session.

Coming to the edb_audit_statement, this can’t be set at the session level and it requires reload of the server

i.,e You need to modify the pg_audit_statement value in postgresql.conf file and then reload the server .

NOTE: Reloading the server will not affect any existing connections.

To sum up the steps described till now :

1. Either modify / maintain a postgresql.conf file in such a way the value of edb_audit_statement is set to ‘none’

2. Reload the database server using the pg_ctl -D <data_directory_path> reload or using the OS level service

script.

3. Login to the database server and set the log_statement =’none’

if you have made log_statement to ‘none’ by maintaining separate postgresql.conf file along with edb_audit_statement

parameter you can skip this step.

4. Create / Alter user commands. # which will be not logged in pg_logs.

Work-around 2 : Creating encrypted password using OS level .

Other alternative method is to encrypt the password before creating /altering the user :

i.,e From OS level create the encrypted password Where U =user and P is for password

[root@localhost ~]# U=test1; P=password; echo -n md5; echo -n $P$U | md5sum | cut -d’ ‘ -f1

md565cb1da342495ea6bb0418a6e5718c38

edb=# alter user test1 with password ‘md565cb1da342495ea6bb0418a6e5718c38’ ;

ALTER ROLE

It will be the same encrypted password , which will be generated by the database server even if

you create the password in plain text format.

Work-around 3 : The Recommended way is to set the password using the below option:

edb=# create user gk1_test1 with \password

Enter new password:

Enter it again:

edb-# ;

CREATE ROLE

edb=# alter user gk1_test1 with \password

Enter new password:

Enter it again:

edb-# ;

ALTER ROLE

Logs :

pg_log files : It logs the command like below as enterprisedb user will generate the encrypted password using os level encryption how ever it also logs the

statement which has generated the password

time= 2017-01-16 21:34:04 IST , processs_id = 29915 : [83-1] , database = edb , username= enterprisedb , application = psql.bin , [local]LOG: 00000: statement: ALTER USER enterprisedb PASSWORD ‘md5156d6ddd36e5927a2ebdf14fb7ea031c’

time= 2017-01-16 21:34:05 IST , processs_id = 29915 : [87-1] , database = edb , username= enterprisedb , application = psql.bin , [local]LOG: 00000: statement: alter user gk1_test1 with;

edb_audit logs :

2017-01-16 21:34:04.586 IST,”enterprisedb”,”edb”,29915,”[local]”,587cea82.74db,21,”idle”,2017-01-16 21:15:06 IST,2/44,0,AUDIT,00000,”statement: ALTER USER enterprisedb PASSWORD ‘md5156d6ddd36e5927a2ebdf14fb7ea031c'”,,,,,,,,”exec_simple_query, postgres.c:1145″,”psql.bin”,””

2017-01-16 21:34:05.674 IST,”enterprisedb”,”edb”,29915,”[local]”,587cea82.74db,22,”idle”,2017-01-16 21:15:06 IST,2/45,0,AUDIT,00000,”statement: alter user gk1_test1 with

;”,,,,,,,,”exec_simple_query, postgres.c:1145″,”psql.bin”,””

PAM Authentication With PostgreSQL

About the author

bensonyerima

Hi, I'm Benson Yerima, a database administrator with an obsession for all things tech. This blog is dedicated for helping people learn about database technology.

View all posts