Configuring PostgreSQL user authentication
Introduction
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 pg_hba.conf.
PostgreSQL reads
pg_hba.confwhen it starts and whenever it reloads its configuration, so you don't need to restart the server for changes to take effect. A reload is enough, and existing connections aren't interrupted. From a superuser session, you can runSELECT pg_reload_conf();. From the command line,pg_ctl reloadworks on most systems (pass your data directory with-Dif thePGDATAenvironment variable isn't set), and in most Linux distributions you can typesudo systemctl reload postgresql. The new rules apply to connections made after the reload.If the file contains an error, PostgreSQL logs it and keeps using the previous rules, even though
pg_reload_conf()still returnst. To check the file for errors, query thepg_hba_file_rulesview:SELECT line_number, error FROM pg_hba_file_rules WHERE error IS NOT NULL;
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:
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.
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:
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.
Let Prisma Postgres handle the operations
Prisma Postgres provisions a fully managed Postgres database with automated backups and connection pooling — so you can focus on your app, not the infrastructure.
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
- Database
- Username
- Address
- Method of authentication
- Options for the authentication method
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 withlocalmatch 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 withhostmatch any connection request made over the network (TCP/IP), whether or not it uses TLS/SSL encryption. This is a general catch-all for network connections. More granular matching is available with the following types.hostssl: Thehostsslconnection type matches only 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: Thehostnossltype 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: Thehostgssencconnection type matches any network connection that uses GSSAPI encryption. This option only makes sense for those who already use GSSAPI for security.hostnogssenc: Thehostnogssenctype matches every network connection that doesn't use GSSAPI encryption.
The connection type is the only part of a line that controls whether the connection must be encrypted. The authentication method only decides how the client proves who it is. For example, scram-sha-256 never sends the password itself over the network, but it doesn't encrypt the connection: on a host line, a client that connects without TLS/SSL authenticates successfully, and its queries and their results then travel unencrypted. To require encryption, use hostssl lines, and make sure that no host line elsewhere in the file also matches the same connections. Clients should connect with sslmode=verify-full, which encrypts the connection and also checks that the server's certificate is valid and matches the host name. With sslmode=require, the connection is encrypted, but the client doesn't verify which server it's talking to. The section on allowing remote connections using SSL and passwords shows an example.
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 ofallis 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: Thesameuservalue matches connections where the requested database and username are the same.samerole: Thesameroledatabase value will match a connection if the user specified is a member of a role with the same name as the requested database.replication: Matches physical replication connections, which do not select a database. Logical replication connects to a named database and is matched by that database's rules instead. This keyword does not match logical replication connections; a rule withallin the database column does.- [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 ofalltells 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 ofalltells PostgreSQL that any client address will satisfy this condition.samehost: The valuesamehostis used to indicate that any networked connections originating from one of the server's own IP addresses should match.samenet: Thesamenetvalue 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.
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 oftrustimmediately 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 ofrejectimmediately rejects the connection. This is mainly used to filter out connections that match unwanted patterns.scram-sha-256: Thescram-sha-256method will check the password provided by the user usingSCRAM-SHA-256authentication. It's a challenge-response scheme, so the password itself is never sent to the server, and the server only stores a salted hash of it. It's the most secure of PostgreSQL's password methods, and the one to use when clients authenticate with a password. It needs a client library that supports SCRAM, which libpq has since PostgreSQL 10.md5: Themd5method is the legacy password method. It uses a weaker challenge-response mechanism based on MD5 password hashes, and support for MD5-hashed passwords is deprecated: PostgreSQL 18 warns whenever one is set, and a future release will remove them. For compatibility, anmd5line automatically uses SCRAM authentication for users whose stored password is a SCRAM hash, so anmd5line doesn't necessarily mean that MD5 is used. Keepmd5lines only while you migrate an existing installation, as described in migrating from MD5 to SCRAM.password: Thepasswordmethod 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: Thegssmethod 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: Thesspimethod uses the Windows-only Security Support Provider Interface API to authenticate clients.ident: Theidentmethod 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: Thepeerauthentication 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 username.ldap: Theldapmethod authenticates by using an LDAP server to validate usernames and passwords.radius: Selectradiusto use a RADIUS server to check username and password combinations.cert: Thecertmethod 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: Thepamoption will defer authentication to the operating system's PAM service.bsd: Thebsdmethod 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 are sufficient to start with. Additional methods, like ldap, are 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.
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 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 or the name localhost, the connection will not use the socket and will not match the peer authentication line. To use the socket with a client like psql, leave out the host (for example, psql -d mydb rather than psql -h localhost -d mydb).
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:
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:
If you need to 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. Since the USER column is compared with the database user being requested, list susan there rather than sue:
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. Once a line uses a map, connections that match the line are only accepted if the map allows them, so john needs an entry too:
Now, the sue operating system user will be able to authenticate to the susan PostgreSQL user with peer authentication as if they matched, and john can still log in as john.
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 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, specify scram-sha-256 as the authentication method.
The finished authentication lines will look something like this, allowing the app_user role to connect to the app_db database:
You can change which databases or users are allowed to authenticate using this method by changing the appropriate columns to a comma-separated list of specific entities, or to all.
To test the rule, connect over TCP/IP by naming the host, for example with psql "host=127.0.0.1 dbname=app_db user=app_user", and enter the role's password when prompted. If no line in the file matches a connection's role and database, the connection is rejected before any password is checked:
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:
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
Physical replication copies the database cluster's changes and does not select a database. The examples in this section are for physical replication. Logical replication selects a database and requires an HBA rule for that actual database, together with the relevant role and table privileges. See logical replication security and the HBA matching rules.
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 scram-sha-256 for connections over the loopback network), we can add the following lines:
To allow replication from additional locations, you can add additional addresses. For example, to allow a dedicated replicator role to replicate from machines on the local 192.0.2.0/24 network, you can add a line that looks like this:
This will allow replication connections for the replicator role coming from machines within that network to authenticate using SCRAM 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 password authentication for the app_user role and the app_db database from machines on the local 192.0.2.0/24 network, you can add a line like this:
This will allow hosts within the 192.0.2.0/24 network to authenticate to the app_db database as app_user over the network using SCRAM passwords. Because the line uses the host connection type, it accepts connections with or without TLS/SSL. SCRAM prevents the password from being sniffed, but without TLS/SSL the rest of the session isn't encrypted. If the network isn't fully trusted, use hostssl instead, as shown in the next section.
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 the app_user role to connect to the app_db database with a password from your application servers on the 198.51.100.0/24 network, but only if TLS/SSL is used, you can add a line like this to your authentication file:
This will allow connections from that network that use TLS/SSL to authenticate using SCRAM passwords. Unencrypted connections from the same addresses don't match the line, and are rejected with an error ending in no encryption unless another line matches them. Only use all in the ADDRESS column if clients really need to connect from anywhere.
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.
On the client side, connect with sslmode=verify-full and point the client at the certificate of the authority that signed the server's certificate, so that the client checks it's talking to the right server:
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:
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:
Afterwards, you'd edit the pg_ident.conf file to explicitly map those two users together:
You can learn how to create and configure client certificates in PostgreSQL's documentation on TLS/SSL client certificates.
Migrating from MD5 to SCRAM
Servers that were set up before PostgreSQL 14, when md5 was the default for the password_encryption setting, often still have md5 lines in pg_hba.conf and roles with MD5-hashed passwords. A role whose stored password is still an MD5 hash can't log in through a scram-sha-256 line (the client gets password authentication failed, and the server log adds a detail like User "legacy_user" does not have a valid SCRAM secret.), so the order of the following steps matters:
Step 1. Make sure all of your clients support SCRAM. libpq has supported it since PostgreSQL 10, and so do current versions of other drivers.
Step 2. Set password_encryption = 'scram-sha-256' in postgresql.conf (it's the default since PostgreSQL 14, but an older configuration file may still set it to md5), reload the configuration, and check the setting:
From now on, passwords are stored as SCRAM hashes when they're set. If the setting is still md5, PostgreSQL 18 warns each time a password is set:
Step 3. As a superuser, find the roles that still have MD5-hashed passwords:
Step 4. Set the password of each of those roles again. An MD5 hash can't be converted to SCRAM, so the password has to be entered again, but it can be the same password. psql's \password command prompts for the password and hashes it with SCRAM before sending it to the server:
Because an md5 line also accepts SCRAM-hashed passwords, clients can keep connecting while you do this. Repeat the query from the previous step until it returns no rows.
Step 5. Change the md5 lines in pg_hba.conf to scram-sha-256, for example:
Then reload the configuration with SELECT pg_reload_conf(); and check the pg_hba_file_rules view for errors.
Conclusion
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.
Create a managed Postgres database
Hosted Postgres with automated backups, connection pooling, and Query Insights — ready in seconds.
