Overview of PG_HBA configuration

Eugenia Kuzina

In ADPG, the PG_HBA field is used to configure the client authentication that is controlled by the pg_hba.conf file in PostgreSQL. This field contains authentication records in the same format as pg_hba.conf.

CAUTION
We do not recommend editing the pg_hba.conf file directly. When the ADPG service executes the Reconfigure & Restart action, pg_hba.conf is rewritten with service settings and settings specified in the PG_HBA field from the Primary configuration tab of the ADPG service.

PG_HBA format

PG_HBA is supposed to contain a set of records with authentication rules, one per line. A record consists of fields separated by spaces or tabs. A field can contain white spaces if the field value is double-quoted. Blank lines and any text after the # comment character are ignored. To continue a record on the next line, end the line with a backslash (\).

The PG_HBA records are processed from top to bottom. ADPG determines whether a requested connection (based on a connection type, database name, user, and IP address) matches each record. If so, authentication is performed using the method specified in the record. If the result is successful, the connection is allowed, otherwise it is prohibited. Subsequent records are not considered. So, entries should go from the particular to the general.

A record can have multiple formats and contain various fields:

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]

include             file
include_if_exists   file
include_dir         directory

For the database and user fields, you can specify a separate file by preceding the file name with @. This file may contain database names or users, respectively. Files included by the @ constructs are read as lists of names, which can be separated by either whitespaces or commas. The nested @ constructs are allowed. To add a comment to a file, use the # symbol. Unless the file name following @ is an absolute path, it is taken to be relative to the directory containing the referencing file. When using @ in the PG_HBA field, the path is set relative to the directory specified in the data_directory configuration parameter. The default value is pg_data1/adpg16/.

The record fields are described in detail in the following sections.

Authentication record fields

This section describes the authentication record fields. The number of fields may vary depending on the record type.

Connection type

Except for records containing include, the first field of a record determines a connection type. The possible values are listed in the table below.

Name Description

local

Matches connections using Unix-domain sockets. Without a record of this type, Unix-domain socket connections are not allowed

host

Manages connections made over TCP/IP. The host entries correspond to different connection types. This record matches an SSL or non-SSL connection as well as GSSAPI encrypted or non-GSSAPI encrypted connection

hostssl

Matches connections via TCP/IP, when the connection uses the SSL encryption. The SSL support is enabled in ADPG during its build. To use hostssl, SSL must be enabled by setting the ssl configuration parameter as described in Enable SSL encryption. Otherwise, the hostssl entry is ignored, except to log a warning that it cannot match any connection

hostnossl

Matches connections made over TCP/IP that do not use SSL

hostgssenc

Matches TCP/IP connections with the GSSAPI encryption

hostnogssenc

Matches connections made over TCP/IP that do not use the GSSAPI encryption

database

The database field specifies which database the record matches. This field accepts the following values:

  • The name of a specific database or a regular expression defining database names. You can specify multiple regular expressions and/or database names separated by commas (,). If a name starts with a slash (/), the value is treated as a regular expression. See Regular expression details. For example, the value /^db\d{2,4}$ allows connections to any databases with a name starting with db and ending with a number containing 2 to 4 digits. Such names can be db1234 or db98.

  • all — all databases.

  • sameuser — the record matches if the requested database has the same name as the requested user. For example, if the user is postgres, they can only connect to the postgres database.

  • samerole — specifies that a user must be a member of the role with the same name as a database. samegroup is an obsolete name for the samerole option that is allowed. If a database name is customers, only users who are members of the customers role can connect to it. Superusers are considered to match samerole only when they explicitly belong to such a role.

  • replication — specifies that the record matches if a physical replication connection is requested. However, it does not match with logical replication connections.

  • A separate file containing database names and/or regular expressions. It can be specified by preceding the file name with @.

For example, the configuration below allows user1 to connect to the book and author databases:

# type      database        user      address       auth-method
  host      book,author     user1     0.0.0.0/0     md5

user

The field specifies which database user names (roles) this record matches. This field allows the following values:

  • A name of a specific role (user name) or a regular expression starting with a slash (/) that determines a role name. You can add multiple roles and/or regular expressions separated by commas. For example, the regular expression /^.*support$ allows connections for users whose names end with support.

  • A group name preceded by +. There is no distinction between users and groups in ADPG/PostgreSQL. + means that roles match the rule if they are direct or indirect members of the specified role. A name without + matches only the current role. A superuser is only considered to be a member of a role if they are explicitly a member of the role.

  • all — all roles (users).

  • A separate file containing user names and/or regular expressions. It can be specified by preceding the file name with @.

The configuration below allows all members of the support role to connect to the access_stat database:

# type      database        user        address       auth-method
  host      access_stat     +support    0.0.0.0/0     md5

address

This field specifies the client host addresses that this record matches. This field can contain either a host name, an IP address range, or one of the special keywords. The following values are allowed:

  • An IP address range specified using CIDR notation.

  • all — any IP address.

  • samehost — any of the server’s IP addresses.

  • samenet — any address in a subnet that the server is directly connected to.

  • A host name. Any value that is not an IP address range or a special keyword is treated as a host name. A host name that is used in this field should be the one that address-to-name resolution of the client’s IP address returns, otherwise the value will not be matched. Some hostname databases allow associating an IP address with multiple host names, but the operating system will only return one host name when resolving an IP address. A host name specification that starts with a dot (.) matches a suffix of the actual host name. So .example.com would match home.example.com but not only example.com.

The examples below allow any user on the local system to connect to any database with any database user name.

Use local loopback TCP/IP connections:

# type      database        user     address             auth-method
  host      all             all      127.0.0.1/32        trust

Over IPv6:

# type      database        user     address        auth-method
  host      all             all      ::1/128        trust

Use a host name (it typically covers both IPv4 and IPv6):

# type      database        user       address          auth-method
  host      all             all        localhost        trust

Allow any user from any host with IP address 192.168.54.x to connect:

# type      database        user       address               auth-method
  host      all             all        192.168.54.0/24       trust

IP-address and IP-mask

These two fields can be used as an alternative to the IP-address/mask-length CIDR notation. Instead of specifying the mask length, the actual mask is set in a separate column. For example, 255.0.0.0 represents an IPv4 CIDR mask length of 8, and 255.255.255.255 determines an IPv4 CIDR mask length of 32.

These fields cannot be applied to the records with the local connection type.

The configuration below allows any user on the local system to connect to any database with any database user name:

# type     database    user    IP-address      IP-mask             auth-method
  host     all         all     127.0.0.1       255.255.255.255     trust

auth-method

The auth-method field specifies the authentication method to use when a connection matches this record. All the options are lower case and treated case sensitively, so even acronyms like ldap must be specified as lower case. For more information on authentication methods, see Authentication methods.

The table below lists supported values for authentication method.

Value Description

trust

Allows the connection unconditionally. This method allows anyone who can connect to the ADPG/PostgreSQL database server, to log in as any PostgreSQL user without any authentication. See Trust authentication

reject

Rejects the connection unconditionally. This is useful for filtering certain hosts from a group. For example, you can block a specific host from connecting, while a later record allows the remaining hosts in a specific network to connect

scram-sha-256

Performs the SCRAM-SHA-256 authentication to verify the user password. See Password authentication

md5

Performs the SCRAM-SHA-256 or MD5 authentication to verify a user’s password. See Password authentication

password

Requires the client to supply an unencrypted password for authentication. Since the password is sent in clear text over the network, this should not be used on untrusted networks. See Password authentication

gss

Uses GSSAPI to authenticate the user. This is only available for TCP/IP connections. It can be used in combination with GSSAPI encryption. See GSSAPI authentication

sspi

Uses SSPI to authenticate the user. This is only available for Windows clients. See SSPI Authentication

ident

Obtains a user name of the client operating system by contacting an ident server on the client. The system checks if it matches the requested database user name. The ident authentication can only be used on TCP/IP connections. When it is specified for local connections, the peer authentication will be used instead. See Ident authentication

peer

Obtains the client’s operating system user name and check if it matches the requested database user name. This is only available for local connections. See Peer authentication

ldap

Authenticates using an LDAP server. See The Kerberos authentication

radius

Authenticates using a RADIUS server. See RADIUS Authentication

cert

Authenticates using SSL client certificates. See Enable SSL encryption

pam

Authenticates using the Pluggable Authentication Modules (PAM) service provided by the operating system. See PAM Authentication

bsd

Authenticates using the BSD Authentication service provided by the operating system. See BSD Authentication

The trust method does not perform authentication. It only makes sense to use trust for local connections. The reject method unconditionally denies access. You can use it to prohibit any connections of a specific type or from certain addresses. For example, you can prohibit unencrypted connections.

The scram-sha-256, md5, ldap, radius, and pam methods prompt the user for a password and check it against a password stored in the DBMS or in an external service.

The peer, cert, gss, and sspi methods provide external authentication. As a result of successful authentication, ADPG/PostgreSQL receives the name specified during connection (internal DBMS name), the name identified by the external system (external name). Therefore, all the listed methods allow you to specify an additional parameter map in auth-options. The map option defines the rules for matching internal and external names.

The following configuration allows all users to connect to all databases using the SSL encryption and SSL client certificates:

# type     database    user    address      auth-method
  hostssl     all      all     0.0.0.0/0    cert

auth-options

The auth-options field specifies options for the given auth-method. Options should be added in the following format: <name>=<value>.

In addition to the method-specific options, there is a method-independent authentication option clientcert, which can be specified in any hostssl record. It can take the following values:

  • verify-ca — requires the client to provide a valid (trusted) SSL certificate;

  • verify-full — requires that the client must provide a valid (trusted) SSL certificate and CN (Common Name) in the certificate must match the user name or an applicable mapping.

This option works similarly to the cert authentication method but allows the verification of client certificates to be associated with any authentication method that supports hostssl records.

In any record that includes client certificate authentication (a record with the cert authentication method or the clientcert parameter), the optional clientname parameter specifies which credential element in the client certificate should be matched during verification. This parameter can have one of two values:

  • CN — the user name is matched against the Common Name (CN) field. This is the default value.

  • DN — the user name is matched against the Distinguished Name (DN) field.

The map option is specified for methods that use name mapping. It defines the rules for matching internal and external names. To set the map option, you need to add the rules to the pg_ident.conf file first. The ident_file configuration parameter determines its location. The default path is pg_data1/adpg16/pg_ident.conf. For example:

# pg_ident.conf
# MAPNAME       SYSTEM-USERNAME     PG-USERNAME
  map_admin     admin               admin_db

Then you need to assign the mapping name to the map parameter. The configuration below allows the operating system user admin to connect as the database user admin_db using the ident authentication method only from 192.168.12.10:

# type     database    user         address            auth-method   auth-options
  host     all         admin_db     192.168.12.10/32   ident         map=map_admin

Include external files

The records listed in the table below allow you to include the content of external files in the PG_HBA field.

Name Description

include

The record will be replaced with the contents of the specified file

include_if_exists

The record will be replaced with the content of the given file if the file exists. Otherwise, a message is logged to indicate that the file has been skipped

include_dir

The record will be replaced by the contents of all the files found in the directory if they do not start with . and end with .conf processed in alphabetical order

Example:

include file1

Display pg_hba.conf records

You can use the pg_hba_file_rules view to display a summary of the pg_hba.conf contents. A row appears in this view for each non-empty, non-comment line in pg_hba.conf with an annotation indicating whether the rule could be applied successfully.

Note that this view reports the current contents of the file, not the content that was last loaded by the server. A row reflecting an incorrect entry will have values only for the line_number and error fields. In ADPG, the pg_hba.conf content is validated during the Reconfigure & Restart action. If the validation fails, the action will be terminated with an error and the old pg_hba.conf content will be used.

SELECT * FROM pg_hba_file_rules;

A possible result:

 rule_number |          file_name           | line_number | type  |   database    |     user_name     |   address    |     netmask     |  auth_method  |  options   | error
-------------+------------------------------+-------------+-------+---------------+-------------------+--------------+-----------------+---------------+------------+-------
           1 | /pg_data1/adpg16/pg_hba.conf |           3 | local | {all}         | {all}             |              |                 | peer          |            |
           2 | /pg_data1/adpg16/pg_hba.conf |           4 | local | {replication} | {adpg_replicator} |              |                 | scram-sha-256 |            |
           3 | /pg_data1/adpg16/pg_hba.conf |           5 | host  | {replication} | {adpg_replicator} | 10.92.40.105 | 255.255.255.255 | scram-sha-256 |            |
           4 | /pg_data1/adpg16/pg_hba.conf |           6 | host  | {all}         | {adpg_replicator} | 10.92.40.105 | 255.255.255.255 | scram-sha-256 |            |
           5 | /pg_data1/adpg16/pg_hba.conf |           7 | host  | {replication} | {adpg_rewind}     | 10.92.40.105 | 255.255.255.255 | scram-sha-256 |            |
           6 | /pg_data1/adpg16/pg_hba.conf |           8 | host  | {all}         | {adpg_rewind}     | 10.92.40.105 | 255.255.255.255 | scram-sha-256 |            |
           7 | /pg_data1/adpg16/pg_hba.conf |           9 | host  | {replication} | {adpg_replicator} | 10.92.40.214 | 255.255.255.255 | scram-sha-256 |            |
           8 | /pg_data1/adpg16/pg_hba.conf |          10 | host  | {all}         | {adpg_replicator} | 10.92.40.214 | 255.255.255.255 | scram-sha-256 |            |
           9 | /pg_data1/adpg16/pg_hba.conf |          11 | host  | {replication} | {adpg_rewind}     | 10.92.40.214 | 255.255.255.255 | scram-sha-256 |            |
          10 | /pg_data1/adpg16/pg_hba.conf |          12 | host  | {all}         | {adpg_rewind}     | 10.92.40.214 | 255.255.255.255 | scram-sha-256 |            |
          11 | /pg_data1/adpg16/pg_hba.conf |          13 | host  | {all}         | {postgres}        | 0.0.0.0      | 0.0.0.0         | ident         | {map=test} |
          12 | /pg_data1/adpg16/file1       |           1 | host  | {all}         | {all}             | 0.0.0.0      | 0.0.0.0         | trust         |            |

PG_HBA field

To edit the PG_HBA field, in ADCM UI, open the Clusters page, select an ADPG cluster, and switch to the Services tab. Select the ADPG service. Click the PG_HBA field in the Configuration tree on the Primary configuration tab.

Edit the PG_HBA field
Edit the PG_HBA field

Enter a record in the pop-up window and click Apply.

Add a record
Add a record

To save changes, click Save.

Save changes
Save changes

Execute the Reconfigure & Restart action to apply changes.

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