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. 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.
\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.