Password authentication

ADPG supports several password authentication methods that differ in how a password is stored on the server and transmitted over the connection. The table below contains supported methods.

Password authentication methods
Name Description

scram-sha-256

Performs the scram-sha-256 authentication according to RFC 7677. This is a challenge-response scheme that prevents password sniffing on untrusted connections. Passwords are stored on the server in a cryptographically hashed form. scram-sha-256 is the most secure method, but older client libraries do not support it

md5

Uses a custom secure challenge-response mechanism. It prevents password sniffing and stores passwords on the server in a hashed form but provides no protection if the password hash is stolen from the server. The md5 hash algorithm is not secure against collision attacks.

The md5 method does not support the db_user_namespace feature

password

Sends the password in the clear text format. This method is vulnerable to password sniffing attacks. You can use this method if the connection is protected by SSL encryption

Specify the authentication settings

The following options control the password authentication settings:

  • The pg_hba.conf file defines client authentication settings.

  • The password_encryption parameter specifies the algorithm used to encrypt user passwords.

The pg_hba.conf file

The pg_hba.conf file contains a set of records, one per line. A record consists of fields separated by spaces or tabs. Each record defines an authentication rule for a connection: it specifies a connection type, a client IP address range, a database name, a user name, and the authentication method. The first record, that matches the current connection, is used to perform authentication. If the authentication fails, subsequent records are not considered. If no record matches, access is denied.

A record can have one of the following formats:

local         database  user  auth-method [auth-options]
host          database  user  address     auth-method  [auth-options]
hostssl       database  user  address     auth-method  [auth-options]
hostnossl     database  user  address     auth-method  [auth-options]
hostgssenc    database  user  address     auth-method  [auth-options]
hostnogssenc  database  user  address     auth-method  [auth-options]
host          database  user  IP-address  IP-mask      auth-method  [auth-options]
hostssl       database  user  IP-address  IP-mask      auth-method  [auth-options]
hostnossl     database  user  IP-address  IP-mask      auth-method  [auth-options]
hostgssenc    database  user  IP-address  IP-mask      auth-method  [auth-options]
hostnogssenc  database  user  IP-address  IP-mask      auth-method  [auth-options]

Refer to the following link for the detailed field description: The pg_hba.conf file.

A record field can contain a white space if its value is double-quoted. To use a keyword in a database, user, or address field, enclose it in quotes. Blank lines and text after the # comment character are ignored. To continue a record on the next line, end the line with a backslash. You can also use backslashes within quoted text or comments.

Use the ADCM UI to edit pg_hba.conf. To do this, open the Clusters → ADPG cluster → Services → ADPG → Primary configuration tab and add lines to the PG_HBA field. For example, the following record allows a connection established over TCP/IP to all databases, by all users, from all IPv4 addresses with the scram-sha-256 authentication method:

host  all  all 0.0.0.0/0 scram-sha-256

Click the Save button on the Configuration tab and execute the Reconfigure & Reload action to apply changes. The following lines are added to pg_data1\adpg14\pg_hba.conf:

# BEGIN Customs from ADCM
host  all  all 0.0.0.0/0 scram-sha-256
# END Customs from ADCM

The password_encryption parameter

If you set a password in the CREATE ROLE or ALTER ROLE SQL command, the password_encryption parameter determines the password encryption algorithm. Possible values are scram-sha-256 (the default value) and md5.

If md5 is specified as an authentication method in pg_hba.conf but the user’s password on the server is encrypted with the scram-sha-256 algorithm, the scram-sha-256 authentication is used as the most secure method.

You can change the password_encryption parameter via ADCM UI. To do this, open the Clusters → ADPG cluster → Services → ADPG → Primary configuration tab, enable the Show advanced toggle button to display postgresql.conf custom section, and set a new value for password_encryption in this section. For example, the following line sets the md5 algorithm for the password encryption:

password_encryption=md5

You can also execute the following SQL statement to change the password_encryption parameter for the current session:

SET password_encryption = 'scram-sha-256';

Set and change a user password

If you do not set a password for a user, the user’s password is NULL and the password authentication fails for this user.

Specify the PASSWORD parameter of the CREATE ROLE SQL command to set the password for a new user role.

CREATE ROLE user1 WITH LOGIN PASSWORD 'password1';

To change the password for an existing role, execute the ALTER ROLE command.

ALTER ROLE user1 WITH PASSWORD 'password2';

The \password psql meta-command also changes the password of the current role.

\password

After the \password command is executed, the command prompt asks you to enter a new password twice:

Enter new password for user "postgres":
Enter it again:

If the entered passwords match, the current password is changed.

View passwords

The password hash for each database user is stored in the pg_authid catalog. You can run the following SQL command to view roles and passwords:

SELECT rolname, rolpassword FROM pg_authid;

The command output:

          rolname          |          rolpassword
---------------------------+----------------------------------------
 pg_database_owner         |
 pg_read_all_data          |
 pg_write_all_data         |
 pg_monitor                |
 pg_read_all_settings      |
 pg_read_all_stats         |
 pg_stat_scan_tables       |
 pg_read_server_files      |
 pg_write_server_files     |
 pg_execute_server_program |
 pg_signal_backend         |
 postgres                  | SCRAM-SHA-256$4096:2yHlw+fjx7PWll0dScESNA==$SETiIdi
xLSGgtE/EEk/5kfRBsIpu+09XuCDPWIFmAuE=:Fyy6OPtQmm1+Ms8ryqww47NT1YhLJ6/zik4iskkm3M
o=
 user_md5                  | md5ea4a7de3c817a0c8cc2670dab9aecc47
(13 rows)

An encrypted password starts with the method name. In this example, the postgres role password is encrypted using the scram-sha-256 method and the user_md5 role password is encrypted with the md5 algorithm.

Found a mistake? Seleсt text and press Ctrl+Enter to report it