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.
| Name | Description |
|---|---|
scram-sha-256 |
Performs the |
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 |
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. Refer to the following link for the detailed field description: Overview of PG_HBA configuration.
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 Save on the Configuration tab and execute the Reconfigure & Restart action to apply changes. The following lines are added to pg_data1/adpg16/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, expand the ADPG configurations section, and set a new value for password_encryption in the postgresql.conf field (see Configuration parameters). 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.
\passwordAfter 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.