GitHub

Password hashing

Andrey Aksenov

A role that can log in to a database using password authentication should have a password. A password is specified using the PASSWORD attribute when creating a new role or changing the existing one. Greengage DB saves MD5 hashes of passwords rather than storing passwords as plain text to ensure that passwords are protected. If necessary, you can change a password hashing algorithm to improve security.

Overview

When you create a new user with a password or change the existing user’s password, Greengage DB saves a password hash in the pg_authid system catalog. The following configuration parameters control password hashing:

  • password_encryption — specifies whether password hashing is turned on. It is not recommended to turn this parameter off since passwords are stored as plain text in this case.

  • password_hash_algorithm — specifies the algorithm used to hash users' passwords.

The supported hashing algorithms are listed in the table below. Note that certain hashing algorithms require using the specified authentication methods. Learn more in the Authentication methods and password hashing section.

Hashing algorithm Description Supported authentication methods

MD5 (default)

Passwords are hashed using the MD5 algorithm

md5, password

SCRAM-SHA-256

Passwords are hashed using the SCRAM-SHA-256 algorithm

scram-sha-256, md5, password

SHA-256

Passwords are hashed using the SHA-256 algorithm

password

Modifying the hashing algorithm in use may impact the authentication of existing users. To ensure users can successfully log in to the database, you need to:

  • Update an authentication method specified for these users in the pg_hba.conf file.

  • Reset users' passwords via the ALTER ROLE command.

Manage password hashing globally

Show the current hashing algorithm

To determine whether password hashing is enabled, use the password_encryption configuration parameter:

$ gpconfig -s password_encryption

The result should look like this:

Values on all segments are consistent
GUC          : password_encryption
Master  value: on
Segment value: on

To see the used hashing algorithm, use the password_hash_algorithm parameter:

$ gpconfig -s password_hash_algorithm

The result should look as follows:

Values on all segments are consistent
GUC          : password_hash_algorithm
Master  value: MD5
Segment value: MD5

Change the hashing algorithm

To change a password hashing algorithm globally, use the password_hash_algorithm configuration parameter:

$ gpconfig -c password_hash_algorithm -v 'SCRAM-SHA-256'

To apply this change, reload the configuration using gpstop:

$ gpstop -u

Then, you can verify that the specified algorithm is in use:

$ gpconfig -s password_hash_algorithm

The result should look as follows:

Values on all segments are consistent
GUC          : password_hash_algorithm
Master  value: SCRAM-SHA-256
Segment value: SCRAM-SHA-256

Manage password hashing in a session

Show the current hashing algorithm

To determine whether password hashing is enabled, use the password_hash_algorithm configuration parameter:

SHOW password_encryption;

The result should look like this:

 password_encryption
---------------------
 on

To see the used hashing algorithm, use the password_hash_algorithm parameter:

SHOW password_hash_algorithm;

The result should look as follows:

 password_hash_algorithm
-------------------------
 MD5

Change the hashing algorithm

To change a password hashing algorithm on a session level, set the password_hash_algorithm configuration parameter as follows:

SET password_hash_algorithm = 'SCRAM-SHA-256';

Then, you can verify that the specified algorithm is in use for this session:

SHOW password_hash_algorithm;

The result should look like this:

 password_hash_algorithm
-------------------------
 SCRAM-SHA-256

Example: Change password hashing in a session

The example below shows how to change a password hashing algorithm in a session from MD5 to SCRAM-SHA-256.

  1. Create two users with passwords:

    CREATE ROLE alice WITH LOGIN PASSWORD '123456';
CREATE ROLE bob WITH LOGIN PASSWORD 'foobar';

  2. To see how passwords are stored in a database, select data from the pg_authid system catalog:

    SELECT rolname, rolpassword FROM pg_authid;

    The md5 prefix in the rolpassword column values indicates that the MD5 hashing algorithm is used:

     rolname |             rolpassword
---------+-------------------------------------
 gpadmin |
 alice   | md506b4475e55db6d5d87d3f690c591b5d9
 bob     | md5e104270d96d95e992cd5a0889fea9a62

  3. Change a hashing algorithm to SCRAM-SHA-256:

    SET password_hash_algorithm = 'SCRAM-SHA-256';

  4. Reset the password for the alice user:

    ALTER ROLE alice WITH PASSWORD '123456';

  5. Recheck the pg_authid system catalog:

    SELECT rolname, rolpassword FROM pg_authid;

    Note that the rolpassword value for alice starts from SCRAM-SHA-256 now:

     rolname |                                                              rolpassword
---------+---------------------------------------------------------------------------------------------------------------------------------------
 gpadmin |
 bob     | md5e104270d96d95e992cd5a0889fea9a62
 alice   | SCRAM-SHA-256$4096:KqJxVxjdp12Ndd1igeD+ig==$8akIprzpNUTIqxN9di0pz7ao7Jp14RiIEwjzkucBzqA=:HWrnwvb1/1ktYq5adswEY9dzMqTGHMMnzAg7VV6ii3U=

  6. To ensure alice can successfully connect to the database, update the pg_hba.conf file in one of the following ways:

    • The md5 authentication method allows using both MD5 and SCRAM-SHA-256 hashing algorithms:

      # connection-type  database  user   address       auth-method
host               postgres  alice  .example.com  md5

    • The scram-sha-256 authentication method can be used with the SCRAM-SHA-256 hashing algorithm only:

      # connection-type  database  user   address       auth-method
host               postgres  alice  .example.com  scram-sha-256
Previous
Restrict user access by time
Next
Logging
Open search page