Configuring PostgreSQL user authentication

Finding the pg_hba.conf file

To find the pg_hba.conf file on your server, you can look in the PostgreSQL configuration directory. The specific location will depend on the operating system and PostgreSQL version you are using.

If you don't know where the authentication configuration file is, but you do have access to the database, you can query PostgreSQL for the file location, as Craig Ringer demonstrates in this post.

If you are on the command line, you can type the following, which queries for the location of the pg_hba.conf file and tells PostgreSQL to print only the file location without formatting:

psql -t -P format=unaligned -c 'SHOW hba_file;'

/etc/postgresql/10/main/pg_hba.conf

If you already have a PostgreSQL session open, you can simply type:

SHOW hba_file;

              hba_file
-------------------------------------
 /etc/postgresql/10/main/pg_hba.conf
(1 row)

Once you have the location of the pg_hba.conf file, open it in your text editor to view the configuration and make changes:

vim /etc/postgresql/10/main/pg_hba.conf

By default, the file contains the current configuration as well as a number of helpful comments.

Understanding the pg_hba.conf file format

The pg_hba.conf file uses a table-like structure implemented in plain text. With blank lines and comments removed, a basic file might look something like this:

# TYPE    DATABASE    USER        ADDRESS         METHOD  OPTIONS
local     all         postgres                    peer
local     all         all                         peer
host      all         all         127.0.0.1/32    md5
host      all         all         ::1/128         md5
local     replication all                         peer
host      replication all         127.0.0.1/32    md5
host      replication all         ::1/128         md5

Let's take a look at the different fields mean and how the file's contents are interpreted.

How the pg_hba.conf file is structured and interpreted

Each line in the pg_hba.conf file describes a way for clients to authenticate to the system. The majority of each line describes match conditions used to compare with incoming connection requests. The final components specify an authentication method allowed and any options needed for authentication.

When PostgreSQL evaluates connection requests against the authentication rules, it does so in sequence, from top to bottom. If the configuration in a line matches the characteristics of the connection request, PostgreSQL will use the authentication information specified on the line to decide whether to authenticate the client.

If the client successfully authenticates, a connection will be established. If authentication is unsuccessful, the connection will be refused. PostgreSQL does not continue processing to see if other rules match the request. Because of this, the order of your rules is significant.

Within each line, fields are separated by white space — either spaces or tabs. Although it is usually visually helpful to format these fields into columns, PostgreSQL does not require this.

Connection type

The first field in each record specifies the type of connection request to match. Only connections that use the specified connection will match each rule.

The connection type must be one of the following:

• local: Records with local match connections made over a local Unix domain socket file instead of over a network. Local connections are preferred when possible for security and performance reasons.
• host: Lines that begin with host match any connection request made over the network. This is a general catch-all for network connections. More granular matching is available with the following types.
• hostssl: The hostssl connection type matches any connections that are made over the network with TLS/SSL encryption. This is usually the best connection type to use when allowing external connections.
• hostnossl: The hostnossl type matches any network connection that is not secured by TLS/SSL.

Starting with PostgreSQL 12, support has been added for GSSAPI connections as well, introducing these additional options:

• hostgssenc: The hostgssenc connection type matches any network connection that uses GSSAPI encryption. This option only makes sense for those who already use GSSAPI ifor security.
• hostnogssenc: The hostnogssen type matches every network connection that doesn't use GSSAPI encryption.

Database

The next field specifies the database that the request is attempting to access. The database specified in the connection request must satisfy the value found in this column for the line to match.

The values can be any of the following:

• all: A database value of all is a catch all value that matches any database requested. This is useful if you don't want the current match rule to evaluate the database value.
• sameuser: The sameuser value matches connections where the requested database and username are the same.
• samerole: The samerole database value will match a connection if the user specified is a member of a role with the same name as the requested database.
• replication: Using a value of replication will match any incoming connection used for database replication. Connections used for replication to not provide a database target, so this matches replication requests instead.
• [specific database name]: You can also provide one or more specific database names to match. These will only match connections if they request one of the listed databases. You can separate multiple database names with a comma or specify a file to read names from by preceding the filename with an @ symbol.

User

The next field is used to match against the user provided by the connection request. The connection's user value must satisfy the rule's user field to match the rule.

The user field can take these options:

• all: A value of all tells PostgreSQL that any value in the connection's user parameter satisfies this rule's user requirements.
• [specific user or group name]: The only other option for the user field is to provide a specific user, a list of users, or a group. Multiple users can be specified by separating the values with a comma. If a name begins with a + symbol, it is interpreted as a group name rather than a user name. In this case, the rule will match if the requested user is a member of the group the rule specifies. Again, you can tell PostgreSQL to read values from a file instead of providing them inline by instead providing a filename preceded by the @ symbol.

Address

For all connection types that begin with host (host, hostssl, and hostnossl, as well as hostgssenc and hostnogssenc in PostgreSQL 12 and later), an address field comes next. For local connections, this field is skipped.

The address field specifies the client machine's addresses or patterns to match against the connection's address. This means that the connection is evaluated according to where it is originating. The connection's origin must satisfy the rule's address value for the rule to match.

The address field can be filled out with any of these:

• all: An address value of all tells PostgreSQL that any client address will satisfy this condition.
• samehost: The value samehost is used to indicate that any networked connections originating from one of the server's own IP addresses should match.
• samenet: The samenet value indicates that any IP address from the server's network subnets will match.
• [CIDR IP address range]: You can also supply an IP address range using CIDR notation. This can specify a single IP address (using a /32 subnet for IPv4 addresses or a /128 subnet for IPv6 addresses) or a range of addresses by providing a more expansive CIDR mask. IP address ranges will only match client connections made from within the specified range using the IP protocol specified.
• [host name]: A host name can also be specified directly. In this case, the client's host name will be evaluated using a forward and reverse DNS query to ensure it resolves as expected. If the specified hostname starts with a dot, any host that resolves correctly on that domain will satisfy the requirements.

Authentication method

If a connection satisfies all of the previous match criteria, the given authentication method is then applied. This is the next field in each line.

The authentication method is the way that PostgreSQL decides whether to accept connections that match the rule. It can be set to any of the following choices:

• trust: A value of trust immediately accepts the connection without further requirements. This assumes that other external authentication methods are in place. It is not recommended in most cases.
• reject: A value of reject immediately rejects the connection. This is mainly used to filter out connections that match unwanted patterns.
• scram-sha-256: The scram-sha-256 method will check the password provided by the user using SCRAM-SHA-256 authentication. If all of your clients support it, this is currently the most secure option for password authentication.
• md5: The md5 method also checks user passwords. This method is less secure than scram-sha-256 but more widely supported. The current implementation will automatically use scram-sha-256 even if md5 is specified if the password is encrypted with SCRAM.
• password: The password method is the least secure password authentication method. It sends passwords in plain text and should not be used unless the connection uses TLS/SSL to encrypt the entire connection.
• gss: The gss method uses GSSAPI for authentication. This can be used for authentication regardless of whether GSSAPI encryption is used for the connection. This allows authenticating through Kerberos and similar software.
• sspi: The sspi method uses the Windows-only Security Support Provider Interface API to authenticate clients.
• ident: The ident method checks with a client's ident server for the user initiating the connection. Since this relies on the client's machine, it should only be used for trusted networks where the client machines are tightly controlled.
• peer: The peer authentication method is used for local connections. It asks the local operating system for the client's system username. It checks if the name matches the requested database name.
• ldap: The ldap method authenticates by using an LDAP server to validate usernames and passwords.
• radius: Select radius to use a RADIUS server to check username and password combinations.
• cert: The cert method authenticates clients using TLS/SSL client certificates. This is only available for TLS/SSL connections. The client certificate must be a valid, trusted certificate to be accepted.
• pam: The pam option will defer authentication to the operating system's PAM service.
• bsd: The bsd method uses the BSD Authentication service to validate usernames and passwords. This method is only available on OpenBSD hosts.

Some of the methods above are only applicable to certain types of connections or with additional pieces of infrastructure in place. For most deployments, reject, peer, and scram-sha-256 or md5 are sufficient to start with additional methods, like ldap available depending on your infrastructure.

Authentication options

After the authentication method, a final, optional column may be present to provide additional options for the authentication method. The use of this column is largely dependent on the type of authentication method selected.

For authentication methods that reference external servers, these options often specify the host and connection information so that PostgreSQL can successfully query the authentication service. Another option that is common to quite a few authentication methods is a map parameter that allows you to translate between system and PostgreSQL database usernames.

Each authentication method has its own set of valid options. Be sure to check the applicable options on the page for each method in the PostgreSQL documentation.

Allow local users to connect to matching databases

It is common to configure PostgreSQL to allow users on the same machine machine to authenticate to the same PostgreSQL username. For example, using peer authentication, an operating system user named john would be able to log in automatically without a password if PostgreSQL also has a username named john.

This will work for any local connections made using the PostgreSQL socket file. If you specify any network address, even if it is the 127.0.0.1 local loopback device, the connection will not use the socket and will not match the peer authentication line. Connections to localhost, however, will use the socket file and will match these lines.

To allow all PostgreSQL users to authenticate from a matching operating system user, add a line that matches the local connection type, allows all databases and usernames, and uses peer authentication:

# TYPE    DATABASE    USER        ADDRESS         METHOD  OPTIONS
local     all         all                         peer

If you want to limit this so that only the john and sue PostgreSQL users can authenticate in this way, limit the scope of the USER column:

# TYPE    DATABASE    USER        ADDRESS         METHOD  OPTIONS
local     all         john,sue                    peer

If you need allow an operating system user named sue to authenticate to a database user named susan, you can specify a map option at the end of the line. Choose a map name to identify this mapping:

# TYPE    DATABASE    USER        ADDRESS         METHOD  OPTIONS
local     all         john,sue                    peer    map=my-map-name

Then, you can map your users by opening the pg_ident.conf file in the same directory:

vim pg_ident.conf

Create the map you need by adding a line in this file specifying your chosen map name, the operating system username, and the PostgreSQL username, separated by spaces:

# MAPNAME       SYSTEM-USERNAME         PG-USERNAME
my-map-name     sue                     susan

Now, the sue operating system will be able to authenticate to the susan PostgreSQL user with peer authentication as if they matched.

Allow network connections from the same machine using passwords

To authenticate network connections from the PostgreSQL server's machine (non-socket connections) using passwords, you need to match a host connection type instead of local. You can then limit the acceptable addresses to the local loopback devices and allow users to authenticate using md5 or scram-sha-256.

For instance, if a user on the machine that PostgreSQL is hosted on tries to connect by specifying 127.0.0.1 as the host, PostgreSQL can perform password authentication.

To set this up, we need to use the host connection type. Next, we need to specify the range of acceptable addresses. Since this rule should only match local connections, we'll specify the local loopback device. We will have to add two separate lines to match the IPv4 and IPv6 loopback devices.

Afterwards, you can specify the password scheme you want to use for authentication. The scram-sha-256 method is more secure, but the md5 method is more widely supported.

The finished authentication lines will look something like this:

# TYPE    DATABASE    USER        ADDRESS         METHOD  OPTIONS
host      all         all         127.0.0.1/32    md5
host      all         all         ::1/128         md5

You can limit the databases or users that are allowed to authenticate using this method by changing the appropriate columns from all to a comma-separated list of specific entities.

Allow automatic maintenance

An assortment of automated maintenance tasks are performed on a regular basis. To ensure that these operations can authenticate and run as expected, you need to ensure that a administrative account is capable of authenticating non-interactively.

By default, the postgres account is configured for this role using peer authentication. This line is very likely already present in your pg_hba.conf file:

# TYPE    DATABASE    USER        ADDRESS         METHOD  OPTIONS
local     all         postgres                    peer

Ensure that this or a similar line is present in your file, especially if you are changing a lot of other authentication methods.

Allow connections used for replication

Replication is a special processes that copies data from one database to another, usually on a frequent basis. Unlike other types of connections, replication connections do not specify a specific database they want to connect to.

The replication keyword in the database column is used to match these replication connections. Any user with the replication privilege is able to establish a replication connection.

To allow for all local replication connections, in a way that mirrors our previous values for regular connections (peer for connections over the Unix socket and md5 for connections over the local network), we can add the following lines:

# TYPE    DATABASE    USER        ADDRESS         METHOD  OPTIONS
local     replication all                         peer
host      replication all         127.0.0.1/32    md5
host      replication all         ::1/128         md5

To allow replication from additional locations, you can add additional addresses. For example, to allow replication from any machines on the local 192.0.2.0/24 network, you can add a line that looks like this:

# TYPE    DATABASE    USER        ADDRESS         METHOD  OPTIONS
host      replication all         192.0.2.0/24    md5

This will allow any replication connections coming from machines within that network to authenticate using md5-encrypted passwords.

Allow connections from local network using passwords

Above, we demonstrated how to configure password authentication for local replication connections. This can be generalized to allow password authentication for any local network connections.

To allow md5 password authentication for any connections coming from the local 192.0.2.0/24 network, you can add a line like this:

# TYPE    DATABASE    USER        ADDRESS         METHOD  OPTIONS
host      all         all         192.0.2.0/24    md5

This will allow all hosts within the 192.0.2.0/24 network to authenticate to PostgreSQL over the network using md5-encrypted passwords.

Allow remote connections using SSL and passwords

To allow connections from outside of a trusted network, you should always tunnel the connection through secure encryption, like TLS/SSL. If you need to allow these connections, you should match against the hostssl connection type.

For example, to allow password authentication from anywhere that can connect to the database server, but only if TLS/SSL is used, you can add a line like this to your authentication file:

# TYPE    DATABASE    USER        ADDRESS         METHOD  OPTIONS
hostssl   all         all         all             md5

This will allow any external connections using TLS/SSL to authenticate using md5-encrypted passwords. You can easily limit the access by specifying more restrictive addresses.

If you use the hostssl connection type, you will have to configure SSL for your PostgreSQL instance. You will have to generate or otherwise obtain an SSL certificate, an SSL key, and an SSL root certificate and then modify the postgresql.conf configuration file, as specified in the PostgreSQL documentation on configuring SSL.

Allow remote connections using SSL and SSL client certificates

If you are already forcing SSL for external connections, you may want to consider using SSL client certificates for authentication instead of passwords. This will allow clients to present their client SSL certificate. The server checks that it is valid and signed by a trusted certificate authority. If so, it will allow authentication according to the rules provided.

To set up SSL client authentication, we can use a similar line to the one we used before:

# TYPE    DATABASE    USER        ADDRESS         METHOD  OPTIONS
hostssl   all         all         all             cert

With this configuration, the server will not prompt users for passwords, but will instead require a valid SSL certificate. The certificate's common name (CN) field must match the database user that is being requested, or else be configured with a map file.

For instance, for a certificate with a CN of katherine to authenticate to a PostgreSQL user named kate, you'd need to specify a map file in the pg_hba.conf file:

# TYPE    DATABASE    USER        ADDRESS         METHOD  OPTIONS
hostssl   all         all         all             cert    map=my-map-name

Afterwards, you'd edit the pg_ident.conf file to explicitly map those two users together:

vim pg_ident.conf

# MAPNAME       SYSTEM-USERNAME         PG-USERNAME
my-map-name     katherine               kate

You can learn how to create and configure client certificates in PostgreSQL's documentation on TLS/SSL client certificates.