pg_hba.conf Authentication Overview for PolarDB PostgreSQL - PolarDB for PostgreSQL

PolarDB for PostgreSQL Lightweight Edition supports multiple methods for client authentication. These methods allow you to restrict user access to the database and ensure its security.

Client authentication

Client authentication is primarily controlled by the pg_hba.conf configuration file, which is usually stored in the database cluster directory. In pg_hba.conf, hba stands for host-based authentication. A default pg_hba.conf file is created when you install and initialize the data catalog. You can modify this file to adjust the client authentication methods. After you modify the configuration file, you must run the pg_ctl reload command or call the pg_reload_conf() SQL function to reload the configuration file. The following is an example of a pg_hba.conf file:

#TYPE       DATABASE       USER         ADDRESS              METHOD         [AUTH-OPTIONS]
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]
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]

The pg_hba.conf file consists of a set of records. Each record is made up of several fields separated by spaces or tab characters. A record cannot span multiple lines. A field value enclosed in double quotes can contain whitespace. Each record specifies a connection type, a database name, a username, a client IP address range, and the authentication method for connections that match these parameters. During authentication, the first record that matches the connection parameters is used. If authentication for the selected record fails, the process is aborted. The following table describes the parameters.

Parameter	Description
TYPE	Specifies the connection type. Common connection types are as follows: `host`: A TCP/IP connection. Important By default, the server only listens for TCP/IP connections on the local loopback address `localhost`. `local`: A connection that uses a Unix domain socket. This applies to connections on the same machine. `hostssl`: A TCP/IP connection that requires Secure Sockets Layer (SSL) encryption. `hostnossl`: The opposite of `hostssl`. This matches only TCP/IP connections that attempt to connect without using SSL.
DATABASE	Specifies the applicable database. Valid values are: `all`: Matches all databases. Database name: Specifies a target database name. `sameuser`: Allows a user to connect to a database that has the same name as the user. `samerole`: Allows a user to connect to a database that has the same name as their role. A superuser is not considered a member of a role for the purpose of `samerole` unless the superuser is an explicit member of that role. `replication`: This record matches when a physical replication is requested. This is a specific PolarDB database name. You can specify multiple databases by separating them with commas, or specify a file that contains database names by prefixing the file name with an at sign (`@`).
USER	Specifies the users who are allowed to connect. Valid values are: `all`: Matches all users. Specific username: Specifies a username. Role name: Specifies a role.
ADDRESS	Specifies the range of IP addresses that are allowed to access the database. Both IPv4 and IPv6 are supported. You can use one of the following two formats: IP address/mask length. For example, `10.10.0.0/24`. IP address subnet mask. For example, `10.10.0.0 255.255.255.0`. Note For a subnet mask, 255.0.0.0 represents an IPv4 CIDR mask length of 8, and 255.255.255.255 represents a CIDR mask length of 32. These fields apply only to `host`, `hostssl`, and `hostnossl` records. An IP address in IPv4 format matches IPv6 connections that have the corresponding address. For example, `127.0.0.1` matches the IPv6 address `::ffff:127.0.0.1`.
METHOD	Specifies the authentication method to use for the connection. Supported methods include Trust authentication, Password authentication, and Certificate authentication.
[AUTH-OPTIONS]	Optional. Specifies additional options for the authentication method. For example, the username `map` information used for Certificate authentication.

Authentication methods

Authentication method	Parameter value	Description
Trust authentication	`trust`	This method trusts any connection that matches the record and does not require a password.
Password authentication	SCRAM-SHA-256	Requires SCRAM-SHA-256 encrypted authentication to verify the user's password.
	MD5	Requires MD5 encrypted authentication to verify the user's password.
	`password`	Requires the client to provide an unencrypted password for authentication. Because the password is sent over the network in plaintext, use this method only on trusted networks.
Certificate authentication	`cert`	Uses SSL client certificate authentication.

Trust authentication

With Trust authentication, PolarDB trusts any client that connects to the server and allows access with any database username without requiring a password. This method simplifies the connection process but poses a security risk. Use this method only in protected environments, such as those secured by a firewall, an IP address whitelist, or other security mechanisms.

Trust authentication is convenient for a single-user, local environment. The restrictions in the database and user columns of the pg_hba.conf file still apply. These restrictions can limit which databases a user can access or which usernames they can use. To control access on a multi-user machine, you can use the following methods:

Use file system permissions to restrict access to the server's Unix domain socket file. This prevents unauthorized users from accessing the database through local connections.
Set the unix_socket_directories parameter to place the Unix domain socket file in a restricted directory.

Note that file system permissions protect only Unix domain socket connections. They do not restrict local TCP/IP connections. To use file system permissions for local security, you can remove the host ... 127.0.0.1 ... line from pg_hba.conf or change its method to a method other than Trust.

Password authentication

SCRAM-SHA-256

SCRAM-SHA-256 authentication, as specified in RFC 7677, uses a challenge-response mechanism. This mechanism prevents password sniffing on untrusted connections because it avoids sending passwords in plaintext. It also provides greater security by storing passwords on the server as encrypted hashes.

MD5

MD5 uses a custom, less secure challenge-response mechanism. It prevents password sniffing and avoids storing passwords in plaintext on the server. However, it does not protect against an attacker who has already stolen the password hash from the server.

Note

The MD5 method cannot be used with the db_user_namespace attribute.

To simplify the transition from MD5 to the newer SCRAM-SHA-256 method, if you specify MD5 in pg_hba.conf but the password on the server is encrypted with SCRAM-SHA-256, SCRAM-SHA-256 authentication is automatically used.

Certificate authentication

Certificate authentication uses SSL client certificates for identity verification. This method improves data transmission security and applies only to SSL connections.

When you use certificate authentication, the server requires the client to provide a valid and trusted certificate. The server compares the Common Name (CN) attribute of the certificate with the requested database username. If they match, the logon is allowed. You can use username mapping to define a relationship between system usernames and database usernames. The mapped username can be different from the database username.

In a pg_hba.conf record that specifies certificate authentication, the authentication option clientcert is set to 1 by default and cannot be disabled. In addition to the basic clientcert certificate validation, certificate authentication adds a rule to verify that the CN attribute matches the database username.

FAQ for authentication failures

FATAL: no pg_hba.conf entry for host "123.123.123.123", user "andym", database "testdb"
This error indicates that you successfully connected to the server, but no matching authentication record exists in the pg_hba.conf configuration file.
FATAL: password authentication failed for user "andym"
This error indicates that you connected to the server, but the password is incorrect or the authentication method does not match.
FATAL: user "andym" does not exist
This error indicates that the specified database user does not exist.
FATAL: database "testdb" does not exist
This error indicates that the specified database does not exist.