Overview of pg_hba.conf

Andrey Aksenov

Greengage DB (based on Greenplum) uses the standard PostgreSQL host-based authentication file called pg_hba.conf for client access and authentication. On the master host, the pg_hba.conf file is located in the $MASTER_DATA_DIRECTORY directory. Segments also have their own pg_hba.conf files. However, since segments are not intended to accept client connections, you do not need to edit these files.

For more information about pg_hba.conf, see the corresponding topic in the PostgreSQL documentation: The pg_hba.conf file.

The pg_hba.conf file contains records in the specified format. The format depends on whether a local (a Unix domain socket) or remote (TCP/IP) connection is used.

For local connections, the pg_hba.conf format is the following:

# connection-type  database  user  auth-method  [auth-options]
...

The connection-type value should be local in this case.

The example below shows how to update pg_hba.conf to allow the gpadmin user to connect to any database locally without a password or other authentication:

# connection-type  database  user     auth-method
local              all       gpadmin  trust

For remote connections, there are two pg_hba.conf formats:

The address field is used to specify the client host address that this record matches:
```
# connection-type  database  user  address  auth-method  [auth-options]
...
```

The IP-address and IP-mask fields are used instead of address:

# connection-type  database  user  IP-address  IP-mask  auth-method  [auth-options]
...

See connection-type to learn about possible connection types for remote connections.

The example below shows how to allow the gpadmin user to connect to any database from a remote host with the IP address from the 192.168.1.0/24 subnet. The client should also supply an MD5-hashed password for authentication.

# connection-type  database  user     address         auth-method
host               all       gpadmin  192.168.1.0/24  md5

This section describes pg_hba.conf fields.

The connection-type field matches different types of connections. The possible values are:

local — connection attempts that use Unix domain sockets.
host — connection attempts over TCP/IP. To allow TCP/IP connections, ensure your cluster runs with an appropriate value for the listen_addresses server configuration parameter.
hostssl — connection attempts over TCP/IP, but only if the connection uses SSL encryption.
hostnossl — connection attempts over TCP/IP that do not use SSL.

Example

The example below demonstrates how to allow the gpadmin user to connect to any database locally or remotely:

# connection-type  database  user     address         auth-method
local              all       gpadmin                  trust
host               all       gpadmin  192.168.1.0/24  md5

For local connections, gpadmin can connect without a password or any other authentication.
For remote connections, gpadmin should provide an MD5-hashed password and can connect from a host with the IP address from the 192.168.1.0/24 subnet.

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

A database name. You can also specify several database names by separating them with commas.
all — all databases.
sameuser — a user can only connect to a database with the same name as their username. For example, if the user is alice, they can only connect to a database named alice.
samerole — a user can connect to any database if they are a member of a role (group) with the same name as the database. For example, if the database is named sales, only users who are members of the sales role can connect to it.
The name of a file containing database names. In this case, you need to add @ before the file name.

Example 1

The configuration below lets alice connect to the sales and finance databases:

# connection-type  database       user   address          auth-method
host               sales,finance  alice  192.168.10.0/24  md5

Example 2

Suppose database names are stored in the $MASTER_DATA_DIRECTORY/testdbs file:

# testdbs
sales,finance

In this case, you can reference this file in pg_hba.conf as follows:

# pg_hba.conf
# connection-type  database  user   address          auth-method
host               @testdbs  alice  192.168.10.0/24  md5

The user field specifies which database role names this record matches. This field accepts the following values:

A role name. You can also specify several role names by separating them with commas. If the specified role is a group, and you want all group members to be included, precede the role name with +.
all — all roles.
The name of a file containing role names. In this case, you need to add @ before the file name.

Example

The configuration below allows all users in the sales_team group to connect to the sales database:

# connection-type  database       user         address          auth-method
host               sales          +sales_team  192.168.10.0/24  md5

The address field specifies the client host addresses that this record matches. address can accept the following values:

An IP address range in the CIDR notation (IP-address/mask-length).
A host name.
all — any IP address.
samehost — any of the server’s IP addresses.
samenet — any address in any subnet to which the server is directly connected.

You can use the IP-address and IP-mask fields instead of address.

NOTE

The address field applies to host, hostssl, and hostnossl connection types (see connection-type).

Example 1

The configuration below allows alice to connect to the sales database only from the host with the 192.168.10.55 IP address:

# connection-type  database  user   address           auth-method
host               sales     alice  192.168.10.55/32  md5

Example 2

The configuration below allows alice to connect to the sales database from the host with the IP address from the 192.168.10.0/24 subnet:

# connection-type  database  user   address          auth-method
host               sales     alice  192.168.10.0/24  md5

Example 3

The configuration below allows alice to connect to the sales database from the host whose name has the example.com suffix:

# connection-type  database  user   address       auth-method
host               sales     alice  .example.com  md5

The IP-address field specifies the IP address to match. This field and IP-mask can be used as an alternative to the CIDR address notation. In this case, IP-mask specifies the mask instead of the mask length.

NOTE

IP-address and IP-mask apply to host, hostssl, and hostnossl connection types (see connection-type).

Example

The configuration below allows alice to connect to the sales database only from the host with the 192.168.10.55 IP address:

# connection-type  database  user   IP-address     IP-mask          auth-method
host               sales     alice  192.168.10.55  255.255.255.255  md5

The IP-mask field specifies the subnet mask to apply to IP-address.

The auth-method field specifies the authentication method to use when the connection matches this record. The possible values are listed in Authentication methods.

Example

The configuration below requires performing SCRAM-SHA-256 authentication to verify the user’s password:

# connection-type  database  user   address           auth-method
host               sales     alice  192.168.10.55/32  scram-sha-256

The auth-options field specifies options for the given auth-method.

Example

The configuration below allows the operating system user alice to connect to the database as the Greengage DB user alice_db using the ident authentication method:

# pg_hba.conf
# connection-type  database  user      address          auth-method  auth-options
host               sales     alice_db  192.168.10.0/24  ident        map=testmap

The auth-options field specifies that testmap maps the operating system and database users. The actual mapping is defined in the pg_ident.conf file:

# pg_ident.conf
# MAPNAME  SYSTEM-USERNAME  PG-USERNAME
testmap    alice            alice_db

You can specify an authentication method in the auth-method field. This section lists the available authentication methods.

Allow the connection unconditionally, without a password or any other authentication. This authentication method is required for the gpadmin role and for Greengage DB utilities (such as gpinitsystem, gpstop, or gpstart) that need to connect to other hosts without prompting for input or a password.

Reject the connection unconditionally. You can use this method to block a specific host from connecting while allowing the remaining hosts to connect.

Require the client to supply an unencrypted password for authentication. Learn more in Password authentication.

Perform MD5 or SCRAM-SHA-256 authentication to verify the user’s password. Learn more in Password authentication.

Perform SCRAM-SHA-256 authentication to verify the user’s password. Learn more in Password authentication.

Use the client’s operating system user name to perform authentication. Using ident for remote TCP/IP connections requires the client’s host to run an Ident service (for example, oidentd).

Use GSSAPI to authenticate the user. This authentication method is only available for TCP/IP connections. Learn more in the following topics:

Authenticate using an LDAP server. Learn more in LDAP authentication.

Authenticate using a RADIUS server.

Authenticate using SSL client certificates. Learn more in SSL certificate authentication.

Authenticate using the Pluggable authentication modules (PAM) service the operating system provides.

To configure users' access to databases, edit the pg_hba.conf file on the master host as follows:

Switch to the gpadmin user:
```
$ su - gpadmin
```
Open pg_hba.conf in a text editor:
```
$ vi $MASTER_DATA_DIRECTORY/pg_hba.conf
```

Add a line to the file for each type of connection you want to allow, for example:

# connection-type  database  user         address          auth-method
host               all       dba          192.168.1.0/24   md5
host               sales     +sales_team  192.168.10.0/24  scram-sha-256

Save and close the file.
Reload the configuration using gpstop to apply the changes:
```
$ gpstop -u
```

Connect to Greengage DB via psql

Password

Overview of pg_hba.conf

pg_hba.conf format overview

Local connection

Remote connection

pg_hba.conf fields

connection-type

database

user

address

IP-address

IP-mask

auth-method

auth-options

Authentication methods

trust

reject

password

md5

scram-sha-256

ident

gss

ldap

radius

cert

pam

Edit pg_hba.conf