Password authentication

ADPG implements several methods of user authentication by a password. The difference between these methods lies in different ways of storing and transmitting passwords from users to ADPG. At the same time, passwords stored in ADPG databases and passwords of operating system users are separated from each other. Passwords of database users in ADPG are stored in the hashed form in the PostgreSQL /pg_authid system table.

To manage passwords, you can use the SQL commands CREATE ROLE and ALTER ROLE, for example:

CREATE ROLE foo WITH LOGIN PASSWORD 'secret'

Also, you can use this command:

$ psql \password
NOTE
If the password isn’t set for the user, then the value NULL is stored instead, and this user won’t be able to authenticate using the password.

The availability of various password authentication methods depends on how user password hashing (encryption) was configured in ADPG. This setting is determined by the password_encryption configuration parameter in the postgresql.conf file.

If the password was first encrypted by using the scram-sha-256 method, then this password can be used for authentication methods scram-sha-256 and password (but in the latter case, the password will be transmitted in plain text, which is unsafe).

If the authentication method md5 is installed, the automatic transition to the use of the scram-sha-256 method will be carried out. If the password was encrypted using by the md5 method, then this password can only be used for the 'md5` and password authentication methods.

To view the password hashes stored in the database, you need to access the system table pg_authid using the command:

select rolname, rolpassword from pg_authid;

Then the output of this command will look something like this:

postgres=# select rolname, rolpassword from pg_authid;
rolname          |                                                              rolpassword

---------------------------+----------------------------------------------------------------------------------------------------------------
-----------------------
 postgres                  |
 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         |
 admin                     | SCRAM-SHA-256$4096:acY++jeKJnklz23Uoct1qA==$sh3YjghKLKWhy9BvREptSZSz9yJh6g41rUCvChd5pbk=:IiVRexo7lL1KYFD1o58xZa
7z/rnY3ZRegYo49p+q0tY=
(13 rows)

In the example shown preceding, the password of the user admin is hashed using the scram-sha-256 method.

Password authentication methods

To specify one of the available password authentication methods, it’s necessary to define the authentication configuration for a certain type of connection to ADPG databases in the pg_hba.conf file.

Example

If the string is of the following type:

# host          DATABASE  USER  ADDRESS  METHOD  [OPTIONS]

Replace with:

host              demo    admin   all  scram-sha-256

This will mean that for all connections established over TCP/IP, the admin user gets access to the demo database via login and password. In this case, the user password will be encrypted.

The available password authentication methods are listed below.

The scram-sha-256 method. Using this method, authentication is performed using the SCRAM-SHA-256 mechanism as described in RFC 7677. This method implements the call-response scheme that prevents sending passwords through untrusted connections. The scram-sha-256 method supports storing passwords as a cryptographic hash.

The md5 method. This method implements a less secure version of the call-response scheme due to this method not implementing protection against theft of password hashes. Also, the md5 method is less protected from direct hacking attacks.

When choosing the md5 authentication method, if the user’s password was encrypted using the scram-sha-256 method, forced authentication using the most secure method scram-sha-256 will occur.

CAUTION
The md5 method is incompatible with the db_user_namespace functionality.

The password method. When choosing this authentication method, the password is transmitted in non-encrypted text. This is unsafe and can lead to a password being discredited if the network traffic is intercepted. It’s recommended to use this authentication method exclusively for test purposes.

The security of this authentication method can be improved if an SSL connection is established between the client (user) and ADPG.

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