PostgreSQL / Authentication and authorization
Configuring PostgreSQL user authentication
One of the first things you'll need to think about when working with a PostgreSQL database is how to connect and interact with the database instance. This requires coordination between the database client — the component you use to interact with the database, and the database server — the actual PostgreSQL instance that stores, organizes, and provides access to your data.
To manage this successfully, you need to know how to configure a PostgreSQL instance to allow the type of access and authentication methods you require. In this guide, we'll cover how to modify your database server's authentication mechanisms to match your environment's requirements.
In a companion guide, we cover how to connect to a PostgreSQL instance using a database client. Consider reading both guides for a more complete picture of how authentication works in PostgreSQL.
Understanding PostgreSQL's authentication file
If you want to modify the rules that dictate how users can authenticate to your PostgreSQL instances, you can do so by modifying your server's configuration.
The specific file you need to modify is called
Note that you will have to restart your PostgreSQL instance for any new authentication configuration to take effect. Different operating systems will handle this in different ways. In most Linux distributions, you can type
sudo systemctl restart postgresql. If you've installed PostgreSQL through
brew, you can try typing
brew services restart postgresql. An alternative that might work on a broader set of systems is
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;'
If you already have a PostgreSQL session open, you can simply type:
Once you have the location of the
pg_hba.conf file, open it in your text editor to view the configuration and make changes:
By default, the file contains the current configuration as well as a number of helpful comments.
pg_hba.conf file format
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 OPTIONSlocal all postgres peerlocal all all peerhost all all 127.0.0.1/32 md5host all all ::1/128 md5local replication all peerhost replication all 127.0.0.1/32 md5host replication all ::1/128 md5
Let's take a look at the different fields mean and how the file's contents are interpreted.
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.
What each field in the
pg_hba.conf file means
Now that you understand a bit about how the file is structured and interpreted, we can begin to talk about what each of the fields means.
The fields we'll cover are:
- Connection type
- Method of authentication
- Options for the authentication method
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
localmatch 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
hostmatch 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.
hostsslconnection 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.
hostnossltype 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:
hostgssencconnection type matches any network connection that uses GSSAPI encryption. This option only makes sense for those who already use GSSAPI ifor security.
hostnogssentype matches every network connection that doesn't use GSSAPI encryption.
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
allis 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.
sameuservalue matches connections where the requested database and username are the same.
sameroledatabase 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
replicationwill 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
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
alltells 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
For all connection types that begin with
hostnossl, as well as
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
alltells PostgreSQL that any client address will satisfy this condition.
samehost: The value
samehostis used to indicate that any networked connections originating from one of the server's own IP addresses should match.
samenetvalue 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
/32subnet for IPv4 addresses or a
/128subnet 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.
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
trustimmediately 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
rejectimmediately rejects the connection. This is mainly used to filter out connections that match unwanted patterns.
scram-sha-256method will check the password provided by the user using
SCRAM-SHA-256authentication. If all of your clients support it, this is currently the most secure option for password authentication.
md5method also checks user passwords. This method is less secure than
scram-sha-256but more widely supported. The current implementation will automatically use
md5is specified if the password is encrypted with SCRAM.
passwordmethod 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.
gssmethod 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.
sspimethod uses the Windows-only Security Support Provider Interface API to authenticate clients.
identmethod 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.
peerauthentication 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.
ldapmethod authenticates by using an LDAP server to validate usernames and passwords.
radiusto use a RADIUS server to check username and password combinations.
certmethod 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.
pamoption will defer authentication to the operating system's PAM service.
bsdmethod 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,
md5 are sufficient to start with additional methods, like
ldap available depending on your infrastructure.
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.
Configuring common authentication policies
We've introduced some of the main authentication options, but how do you use these to implement reasonable policies? In this section, we'll cover how to configure some of the most common authentication policies.
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
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
# TYPE DATABASE USER ADDRESS METHOD OPTIONSlocal all all peer
If you want to limit this so that only the
sue PostgreSQL users can authenticate in this way, limit the scope of the
# TYPE DATABASE USER ADDRESS METHOD OPTIONSlocal 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 OPTIONSlocal 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:
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-USERNAMEmy-map-name sue susan
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
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 OPTIONShost all all 127.0.0.1/32 md5host 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
# TYPE DATABASE USER ADDRESS METHOD OPTIONSlocal 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.
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 OPTIONSlocal replication all peerhost replication all 127.0.0.1/32 md5host 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 OPTIONShost replication all 192.0.2.0/24 md5
This will allow any replication connections coming from machines within that network to authenticate using
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.
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 OPTIONShost 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
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 OPTIONShostssl 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 OPTIONShostssl 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
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
# TYPE DATABASE USER ADDRESS METHOD OPTIONShostssl all all all cert map=my-map-name
Afterwards, you'd edit the
pg_ident.conf file to explicitly map those two users together:
# MAPNAME SYSTEM-USERNAME PG-USERNAMEmy-map-name katherine kate
You can learn how to create and configure client certificates in PostgreSQL's documentation on TLS/SSL client certificates.
In this guide, we covered PostgreSQL authentication from the server side. We demonstrated how to modify a PostgreSQL instance's configuration to change how clients are allowed to authenticate. After discussing the different options available in the authentication file, we covered how to implement some common authentication strategies using what we learned earlier.
Knowing how to configure authentication, combined with an understanding of how to connect with a PostgreSQL client, allow you to grant access to legitimate clients while guarding against unwanted connections. This configuration is an important part of securing your database instances and preventing disruptive login problems that might hinder your operations.