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.
Because of this, you need to understand how to connect as a client by providing the required information to authenticate. You also 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 both of these components. We'll start by covering how to connect to a PostgreSQL database using the native
psql command line client — one of the most common and useful ways of interacting with a database instance. Afterwards, we'll cover how to configure a PostgreSQL server's authentication mechanisms to match your environment's requirements. By combining these two components, you should be able to establish policies that allow your clients to authenticate securely.
psql client, the native command line client for PostgreSQL, can connect to database instances to offer an interactive session or to send commands to the server. It is especially useful when implementing your initial settings and getting the basic configuration in place, prior to interacting with the database through application libraries. In addition,
psql is great for interactive exploration or ad-hoc queries while developing the access patterns your programs will use.
The way that you connect depends on the configuration of the PostgreSQL server and the options available for you to authenticate to an account. In this section, we'll go over some of the basic connection options. For clarity's sake, we'll differentiate between local and remote connections:
- local connection: a connection where the client and the PostgreSQL instance are located on the same server
- remote connection: where the client is connecting to a network-accessible PostgreSQL instance running on a different computer
Let's start with connecting to a database from the same computer.
Without any arguments, the
psql command attempts to connect to a Unix socket file to access a local database. It uses your operating system username as the PostgreSQL username and database name that you are trying to connect to.
By default, modern versions of PostgreSQL are configured for something called peer authentication. Peer authentication authenticates users automatically if a valid PostgreSQL user exists that matches the user's operating system username.
By default, the super user, or administrative account, for PostgreSQL is called
postgres. Upon installation, a user called
postgres is also created on the operating system. So, to log into PostgreSQL as the
postgres user, you need to connect as the
postgres operating system user. There are a number of ways to do this.
If you typically switch accounts with the
su program, can log in to the operating system's
postgres user using
su. You will either have to have permission to do so without a password (usually only
root has this permission) or you will have to set a password for the
postgres account. Afterwards, calling
psql will log you into an interactive PostgreSQL session:
su - postgres psql
If you use
sudo to change permissions, you can also run as the
postgres system user.
To open a shell session for the
postgres user and then log into the database, you can type:
sudo -i -u postgres psql
If you don't need to perform any shell commands as the
postgres user, you can also just run the
psql command directly as the
postgres user. This will log you in to a PostgreSQL session immediately instead of taking you to a shell first:
sudo -i -u postgres psql
Any of these methods should allow you to log into the
postgres PostgreSQL user account.
For security reasons and because of the reliance on a local socket file, peer authentication cannot be used for remote connections. Instead, users will need to log in using another method.
The available authentication methods vary based on the PostgreSQL instance's configuration. Most commonly, though, you will be able to authenticate by providing the following pieces of information:
- hostname: The network host name or the IP address of the PostgreSQL server. The
-hoption is used to specify the hostname.
- network port: The network port that the PostgreSQL server is running on. By default, this is port 5432. This can be omitted if the default is used. To specify a different port, you can use the
- PostgreSQL username: The database username you wish to connect as. If not specified, your operating system username will be used. The
-Uoption is used to override the default and define the username to connect with.
- PostgreSQL database: The PostgreSQL database name that you want to access. If not specified, your operating system username will be used as the database name. To specify a different database, use the
If the account you are connecting to requires a password,
psql will prompt you for it when you connect.
So the basic format for connecting to a remote database typically looks something like this:
psql -h <hostname> -p <port> -U <username> -d <database>
The remote server will indicate that it requires a password for most accounts, at which point
psql will prompt you for the password. If you authenticate successfully, a new interactive PostgreSQL session will be started.
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
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:
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:
By default, the file contains the current configuration as well as a number of helpful comments.
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.
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.
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.
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.
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 OPTIONS local 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 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:
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
sue operating system will be able to authenticate to the
susan PostgreSQL user with
peer authentication as if they matched.
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 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.
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 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.
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 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
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 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
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.
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
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 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:
# 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.
In this guide, we covered PostgreSQL authentication from both the client side and the server side. We demonstrated how to use the
psql command line client to connect to both local and remote database instances using a variety of methods. Afterwards, we shifted focus to explore the server-side configuration that affects how clients are allowed to authenticate.
Knowledge of these two constituent parts allows you to configure your PostgreSQL instances to allow access to legitimate clients while guarding against unwanted connections. Likewise, understanding how PostgreSQL conceptualizes authentication will make it more straightforward to connect to databases as a client.