Pivotal Knowledge Base

Follow

How to Create a User with an Encrypted Password

Environment

  • Pivotal Greenplum 4.3.x
  • Operating System- Red Hat Enterprise Linux 6.x

Purpose

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

Cause

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

Procedure

  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=>

Additional Information

Review the following article for another security method that avoids printing a plain text password to the log file.

 

Comments

Powered by Zendesk