Open search page
Hello, I’m DocuDroid!
Open Ask AI page
Submitting feedback
Thank you for rating our AI Search!
We would be grateful if you could share your thoughts so we can improve our AI Search for you and other readers.
GitHub

Password authentication

Andrey Aksenov

This topic describes password authentication methods. Such methods are used to verify a user’s password when connecting to a database.

To use password authentication, a user should have a password. Otherwise, password authentication fails for this user.

Greengage DB saves hashes of passwords rather than storing passwords as plain text to ensure that passwords are protected. You can change the password hashing algorithm to improve security.

Password authentication methods

The available password authentication methods are described below.

password

The password authentication method requires the client to supply an unencrypted password for authentication. This method is vulnerable to password sniffing attacks. Given that a password is sent as plain text, it is recommended to use SSL-secured client connections.

md5

The md5 authentication method performs MD5 or SCRAM-SHA-256 authentication to verify a user’s password. This method allows falling back to a less secure challenge-response mechanism for users with an MD5-hashed password. The fallback mechanism also prevents password sniffing but provides no protection if an attacker manages to steal the password hash from the server.

The md5 authentication method cannot be used when db_user_namespace is enabled.

scram-sha-256

The scram-sha-256 authentication method performs SCRAM-SHA-256 authentication as described in RFC 7677 to verify a user’s password. This is a challenge-response scheme that prevents password sniffing on untrusted connections. SCRAM-SHA-256 is more secure than MD5, but older client libraries might not support it.

Password hashing

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 password_encryption configuration parameter specifies the algorithm used to hash users' passwords. The supported hashing algorithms are listed in the table below.

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

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.

Note that certain hashing algorithms require using the specified authentication methods. Learn more in the Authentication methods and password hashing section.

Authentication methods and password hashing

The availability of the different password authentication methods depends on how a user’s password is hashed on the server. The table below shows how Greengage DB chooses an authentication method depending on the password hashing algorithm used.

Authentication method Password hashing algorithms

password

The password authentication method can be used for any hashing algorithm supported by Greengage DB:

  • md5

  • scram-sha-256

In all cases, a password is sent as plain text

md5

The md5 authentication method can be used with the following hashing algorithms:

  • md5 — in this case, MD5 authentication is performed to verify a user’s password.

  • scram-sha-256 — in this case, SCRAM-SHA-256 authentication is performed to verify a user’s password.

scram-sha-256

The scram-sha-256 authentication method can only be used with the scram-sha-256 hashing algorithm. In this case, SCRAM-SHA-256 authentication is performed to verify a user’s password

Example 1: Enable MD5 authentication

To follow the examples in this topic, connect to the Greengage DB coordinator host as gpadmin and create a new database:

$ createdb sales

Check the password hashing algorithm

To see the used hashing algorithm, check the password_encryption parameter:

$ gpconfig -s password_encryption

The result should look as follows:

Values on all segments are consistent
GUC              : password_encryption
Coordinator value: md5
Segment     value: md5

Create roles

Create a group role:

$ createuser sales_team --no-login

Create the alice user and assign it to the sales_team role:

$ createuser alice --role=sales_team --pwprompt

When prompted, enter a password for the user:

Enter password for new role:
Enter it again:

Similarly, create the bob user:

$ createuser bob --role=sales_team --pwprompt

Check the pg_authid table

To see how passwords are stored in a database, query the pg_authid table:

$ psql -c 'SELECT rolname, rolpassword FROM pg_authid;'

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

          rolname          |             rolpassword
---------------------------+-------------------------------------
 ...                       | ...
 sales_team                |
 alice                     | md506b4475e55db6d5d87d3f690c591b5d9
 bob                       | md5e104270d96d95e992cd5a0889fea9a62

Edit pg_hba.conf

The pg_hba.conf file controls which authentication methods are used for incoming client connections based on the connection type, database, user, and client address. In this procedure, you add a rule that enables MD5 authentication for the sales_team group role.

Open pg_hba.conf for editing:

$ vi $COORDINATOR_DATA_DIRECTORY/pg_hba.conf

Add the following line to the file:

# connection-type  database  user         address          auth-method
host               sales     +sales_team  192.168.10.0/24  md5

auth-method is set to md5 and enables password authentication for users from sales_team.

Save and close the file. Reload the configuration with gpstop to apply the changes:

$ gpstop -u

Connect to the database

This section describes connecting to a database protected by MD5 authentication using psql. The steps are performed on the client host with an IP address from the 192.168.10.0/24 subnet.

Connect to the sales database under the alice role using psql:

$ psql sales -U alice -h 192.168.1.10

Enter the password for the alice user and press Enter:

Password for user alice:

The output should look like this:

psql (18.3, server 12.22)
Type "help" for help.

sales=>

Example 2: Enable SCRAM-SHA-256 authentication for a specific user

Reset the user’s password

To use SCRAM-SHA-256 authentication, reset the user’s password so that it is stored using the SCRAM-SHA-256 hashing algorithm. On the coordinator, open psql:

$ psql

Set the password_encryption configuration parameter at the session level:

SET password_encryption = 'scram-sha-256';

Reset a password:

ALTER ROLE alice PASSWORD '123456';

Check the pg_authid table

To see how the password of the alice user is stored in a database, select data from the pg_authid table:

SELECT rolname, rolpassword FROM pg_authid;

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

          rolname          |             rolpassword
---------------------------+-------------------------------------
 ...                       | ...
 sales_team                |
 bob                       | md5e104270d96d95e992cd5a0889fea9a62
 alice                     | SCRAM-SHA-256$4096:H0lMkdtcV+ofsUm5n8EzEA==$jLvJU+tnQeJ7xeja3lNK4UGpFaoz9rY03XDCUJtWsyM=:aNaaeam1yQGW29o9UwayPROiAC2evsUwHszzGeZ1kz8=

Connect to the database

Connect to the sales database under the alice role using psql:

$ psql sales -U alice -h 192.168.1.10

Enter the password for the alice user and press Enter. In this case, SCRAM-SHA-256 authentication is used because the md5 authentication method in the pg_hba.conf file supports both MD5 and SCRAM-SHA-256.

Example 3: Enable SCRAM-SHA-256 authentication globally

Change the password hashing algorithm

Set the password_encryption parameter to scram-sha-256:

$ gpconfig -c password_encryption -v 'scram-sha-256'

Reload the configuration to apply the changes:

$ gpstop -u

After this change, passwords for all users are stored using the SCRAM-SHA-256 hashing algorithm when new passwords are set, either during user creation or when existing passwords are updated.

Edit pg_hba.conf

Open pg_hba.conf for editing:

$ vi $COORDINATOR_DATA_DIRECTORY/pg_hba.conf

Update the entry for the sales_team role:

# connection-type  database  user         address          auth-method
host               sales     +sales_team  192.168.10.0/24  scram-sha-256

This entry enforces SCRAM-SHA-256 authentication for all users in the sales_team role.

Save and close the file. Reload the configuration with gpstop to apply the changes:

$ gpstop -u

Connect to the database

Attempt to connect to the sales database as the bob user:

$ psql sales -U bob -h 192.168.1.10

Enter the password for bob and press Enter. The connection fails with an error similar to:

psql: error: connection to server at "192.168.1.10", port 5432 failed: FATAL:  password authentication failed for user "bob"

A corresponding message appears in the logs:

FATAL: |28P01|"password authentication failed for user ""bob"""|"User ""bob"" does not have a valid SCRAM verifier.

The bob user still has a password stored using the MD5 hashing algorithm, so authentication fails. To resolve this, reset the user’s password so that it is stored in SCRAM format.

Reset the user’s password

Open psql on the coordinator host:

$ psql

Reset the password for bob:

ALTER ROLE bob PASSWORD 'foobar';

After resetting the password, the bob user can successfully connect to the sales database.

Previous
pg_ident.conf
Next
MIT Kerberos KDC