How to Create a User with an Encrypted Password

Products

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

Issue/Introduction

This article discusses how to create an encrypted password and how to assign that encrypted password to a user during the CREATE ROLE statement. 

 

Environment

Cause

Creating a user from the PSQL prompt may result in logging the password in plain text to the pg_log file.

 

Resolution

1. Create an encrypted password using the following bash command:

   echo -n ${USERPASSWORD}${USERNAME} | md5sum

2. Copy the checksum that displays after running the command in step 1.
    
3. Enter a PSQL prompt as the admin user.
    
4. Run CREATE ROLE test WITH PASSWORD 'md5<output_from_step_2>'

It is very important to add the option -n to the command echo, otherwise, this won't work.

Refer below for a working example of the procedure

In this example, we use password pivotal for user test.

1. Generate the md5 checksum:

   [gpadmin@mdw2 boc_4361_-1]$ echo -n pivotaltest | md5sum
   562cbe7b006b198b75ca1858da667e6b  -
   [gpadmin@mdw2 boc_4361_-1]$ psql
   psql (8.2.15)
   Type "help" for help.

2. Apply password to database user (Don't forget the leading md5 before the checksum):

   gpadmin=# alter role test password 'md5562cbe7b006b198b75ca1858da667e6b';
   ALTER ROLE

3. PSQL Example Prompt:

   [gpadmin@mdw2 boc_4361_-1]$ psql -U test -h 127.0.0.1 gpadmin
   Password for user test:
   psql (8.2.15)
   Type "help" for help.
   gpadmin=>

4. Test user Login:[gpadmin@mdw2 boc_4361_-1]$ psql -U test -h 127.0.0.1 gpadmin Password for user test: psql (8.2.15) Type "help" for help. gpadmin=>

   [gpadmin@mdw2 boc_4361_-1]$ psql -U test -h 127.0.0.1 gpadmin
   Password for user test:
   psql (8.2.15)
   Type "help" for help.
   
   gpadmin=>

 

Additional Information

Review the article Avoid Printing Sensitive Messages like Password to Master Logs for another security method that avoids printing a plain text password to the log file.