Configuring Client Access Authentication

Background

If a host needs to connect to a database remotely, you need to add information about the host in configuration file of the database system and perform client access authentication. The configuration file (pg_hba.conf by default) is stored in the data directory of the database. HBA is short for host-based authentication.

The system supports the following three authentication methods, which all require the pg_hba.conf file.
- Host-based authentication: A server checks the configuration file based on the IP address, username, and target database of the client to determine whether the user can be authenticated.
- Password authentication: A password can be an encrypted password for remote connection or a non-encrypted password for local connection.
- SSL encryption: The OpenSSL is used to provide a secure connection between the server and the client.
In the pg_hba.conf file, each record occupies one row and specifies an authentication rule. An empty row or a row started with a number sign (#) is neglected.
Each authentication rule consists of multiple columns separated by spaces and forward slashes (/), or spaces and tab characters. If a field is enclosed with quotation marks ("), it can contain spaces. One record cannot span different rows.

Procedure

Log in as the OS user omm to the primary node of the database.
Configure the client authentication mode and enable the client to connect to the host as user jack. User omm cannot be used for remote connection.
Assume you are to allow the client whose IP address is 10.10.0.30 to access the current host.
```
gs_guc set -N all -I all -h "host all jack 10.10.0.30/32 sha256"
```
NOTE:
- Before using user jack, connect to the database locally and run the following command in the database to create user jack:
```
postgres=# CREATE USER jack PASSWORD 'Test@123';  
```
- -N all indicates all hosts in openGauss.
- -I all indicates all instances on the host.
- -h specifies statements that need to be added in the pg_hba.conf file.
- all indicates that a client can connect to any database.
- jack indicates the user that accesses the database.
- 10.10.0.30/32 indicates that only the client whose IP address is 10.10.0.30 can connect to the host. The specified IP address must be different from those used in openGauss. 32 indicates that there are 32 bits whose value is 1 in the subnet mask. That is, the subnet mask is 255.255.255.255.
- sha256 indicates that the password of user jack is encrypted using the SHA-256 algorithm.

This command adds a rule to the pg_hba.conf file corresponds to the primary node of the database. The rule is used to authenticate clients that access primary node.

Each record in the pg_hba.conf file can be in one of the following four formats. For parameter description of the four formats, see Configuration File Reference.

local     DATABASE USER METHOD [OPTIONS]
host      DATABASE USER ADDRESS METHOD [OPTIONS]
hostssl   DATABASE USER ADDRESS METHOD [OPTIONS]
hostnossl DATABASE USER ADDRESS METHOD [OPTIONS]

During authentication, the system checks records in the pg_hba.conf file in sequence for connection requests, so the record sequence is vital.

NOTE:
Configure records in the pg_hba.conf file from top to bottom based on communication and format requirements in the descending order of priorities. The IP addresses of the openGauss cluster and added hosts are of the highest priority and should be configured prior to those manually configured by users. If the IP addresses manually configured by users and those of added hosts are in the same network segment, delete the manually configured IP addresses before the scale-out and configure them after the scale-out.

The suggestions on configuring authentication rules are as follows:

Records placed at the front have strict connection parameters but weak authentication methods.
Records placed at the end have weak connection parameters but strict authentication methods.

NOTE:
If a user wants to connect to a specified database, the user must be authenticated by the rules in the pg_hba.conf file and have the CONNECT permission for the database. If you want to restrict a user from connecting to certain databases, you can grant or revoke the user's CONNECT permission, which is easier than setting rules in the pg_hba.conf file.
The trust authentication mode is insecure for a connection between the openGauss and a client outside the cluster. In this case, set the authentication mode to sha256.

Exception Handling

There are many reasons for a user authentication failure. You can view an error message returned from a server to a client to determine the exact cause. Table 1 lists common error messages and solutions to these errors.

Table 1 Error messages

Symptom	Solution
The username or password is incorrect. FATAL: invalid username/password,login denied	Retry the authentication with the correct username and password.
The database to connect does not exist. FATAL: database "TESTDB" does not exist	Retry the authentication with the correct database name.
No matched client record is found. FATAL: no pg_hba.conf entry for host "10.10.0.60", user "ANDYM", database "TESTDB"	This message indicates that the server is connected but denies the connection request, because it does not find a matched record in pg_hba.conf. Contact the database administrator to add user information to the pg_hba.conf file.

Symptom

Solution

The username or password is incorrect.

FATAL: invalid username/password,login denied

Retry the authentication with the correct username and password.

The database to connect does not exist.

FATAL: database "TESTDB" does not exist

Retry the authentication with the correct database name.

No matched client record is found.

FATAL: no pg_hba.conf entry for host "10.10.0.60", user "ANDYM", database "TESTDB"

This message indicates that the server is connected but denies the connection request, because it does not find a matched record in pg_hba.conf. Contact the database administrator to add user information to the pg_hba.conf file.

Example

TYPE  DATABASE        USER            ADDRESS                 METHOD

"local" is for Unix domain socket connections only
#Allow only the user specified by the -U parameter during installation to establish a connection from the local server.
local   all             all                                     trust
IPv4 local connections:
#User  jack  is allowed to connect to any database from the 10.10.0.50 host. The SHA-256 algorithm is used to encrypt the password.
host    all           jack             10.10.0.50/32            sha256
#Any user is allowed to connect to any database from a host on the 10.10.0.0/24 network segment. The SHA-256 algorithm is used to encrypt the password and SSL transmission is used.
hostssl    all             all             10.10.0.0/24            sha256
#Any user is allowed to connect to any database from a host on the 10.10.0.0/24 network segment. The Kerberos authentication is used. In the current version, Kerberos authentication cannot be used to connect to external clients.
host    all             all             10.10.0.0/24            gss         include_realm=1        krb_realm=HADOOP.COM