Authentication configuration in PostgreSQL and CockroachDB

Connection type

The first column is applied to the connection type as follows:

• local matches connections via the unix domain socket.
• host matches any TCP/IP connection.
• hostssl matches TCP/IP connections with TLS encryption.
• hostgssenc matches TCP/IP connections using GSSAPI (e.g. Kerberos) encryption.
• hostnossl matches TCP/IP connections without TLS encryption. It thus matches either unencrypted or GSSAPI-encrypted connections.
• hostnogssenc matches TCP/IP connections without GSSAPI encryption. It thus matches either unencrypted or TLS-encrypted connections.

Client address

For connections coming over the network, the connection is further filtered based on the client address, compared against the address columns in the rule:

• the keyword all means that any client address matches.
• if the rule specifies numeric addresses (either a specific IP address in a single-column format; or a network/mask in single-column CIDR notation; or separate network and mask addresses), then the client address is compared numerically.
• the keyword samehost and samenet match the server’s IP addresses and networks, respectively.
• if the rule specifies a hostname, or domain name suffix starting with ., a complex (and resource-intensive!) algorithm is used using a double-DNS lookup (reverse on the client address, forward on the rule name). See PostgreSQL’s documentation for details and an additional security note on this point.

Both IPv4 and IPv6 numeric addresses are supported.

For example:

host all all 127.0.0.1/8         trust  # matches all IPv4 loopback addresses.
host all all 127.0.0.1 255.0.0.0 trust  # same as above.
host all all ::1/128             trust  # matches the IPv6 loopback address.
host all all samehost            trust  # matches any of the server's IP addresses.
host all all .example.com        trust  # matches clients whose canonical FQDN
                                        # ends with .example.com.
host all all foo.example.com     trust  # matches clients whose canonical
                                             # FQDN is foo.example.com exactly.

Username

PostgreSQL’s (and CockroachDB’s) client protocol requires the client to present a username to the server upfront, before authentication starts. The server then first checks whether that username has the LOGIN attribute (or does not have NOLOGIN). If the user is allowed to log in, the authentication rules are then filtered based on the client username.

• the keyword all matches any username.
• a simple user/group name means that the rule matches when the client presents exactly that name to the server.
• a name preceded by + means that the rule matches when the username presented by the client is either that name, or that of a user that is directly or indirectly member of that group.
• a name preceded by @ specifies a file to read a list of usernames to match.

Multiple usernames can be provided for a single rule, separated by commas.

For example:

host all all             all trust  # matches everyone.
host all marc            all trust  # matches 'marc'.
host all +admin          all trust  # matches 'admin' itself and any user with
                                    # role 'admin'.
host all alice,bob       all trust  # matches either 'alice' or 'bob'.

Additionally, PostgreSQL and CockroachDB support translation of client-side usernames to server-side usernames (PostgreSQL via user name maps, CockroachDB v22.1 via either user names maps, or the command-line flag --cert-principal-map). When this mapping is configured, the names listed in authentication rules are compared after the translation takes place; that is, the authentication rules always use server-side names. (See also Proper use of database user mapping in the Pitfalls section below.)

Database

Every PostgreSQL client also presents the name of the database that it intends to connect to, prior to authentication. The server can thus use this information to filter authentication rules.

• the keyword all matches any database name.
• the keyword sameuser matches when the presented database and user names are the same.
• the keyword samerole matches when the user whose name is presented by the client is direct or indirect member of a role with the same name as the database.
• the keyword replication specifies that the record matches if a physical replication connection is requested.
• a name preceded by @ specifies a file to read a list of database name to match.
• otherwise, a regular name must match the database name presented by the client exactly.

Multiple database names can be provided for a single rule, separated by commas.

For example:

host   mydb      all  all  trust      # matches 'mydb'.
host   sameuser  all  all  trust      # matches each user's own database.
host   sameuser  marc all  trust      # matches user and database 'marc'.

Authentication method

After an authentication rule has been selected, its method is applied to negotiate authentication.

The following methods are supported; the most commonly used methods are listed at the beginning.

Footnotes:

PostgreSQL defaults

The specific defaults for PostgreSQL depend on how the server is installed. Different package distributions use different specific defaults.

However, the following tend to be true across all default installations:

• connections are accepted over a local unix socket, with the auth methods trust (always accept), md5 (password) or peer (same unix username).
• network connections are accepted from the loopback interface (localhost / 127.0.0.1/8 / ::1/128) using the methods md5 (password) or trust (always accept).

For example, Ubuntu 20.04’s default installation uses:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
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

And the FreeBSD 13 default installation uses:

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

With these defaults, the HBA configuration must be explicitly modified to allow SQL clients over non-local network connections.

CockroachDB defaults

As of v22.1, CockroachDB always uses the following defaults:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             root            all                     cert-password
host    all             all             all                     cert-password
local   all             all                                     password

The first rule enables the root user to log in using either a TLS client certificate or a password. This first rule is special: it cannot be overridden even when replacing the default configuration. This ensures that the root user remains able to access a cluster and fix the authentication configuration even when the current configuration unintentionally blocks every other user from logging in.

The second rule enables network authentication using either TLS certificates or passwords. The password method is automatically upgraded to a SCRAM-SHA-256 handshake, as explained in the next section.

The third rule enables authentication over the local unix socket using passwords.

Personal development server

A developer typically runs their code and their database locally. For convenience, it is thus desirable to let certain client apps (running in development mode) access the database without the overhead of authentication.

This can be achieved as follows:

# trust all connections using the unix socket.
local  all all                  trust

Alternatively, if the client app only uses TCP/IP:

# trust all local network connections.
host  all all  127.0.0.1/8      trust
host  all all  ::1/128          trust

(The two can be combined.)

Alternatively, maybe the developer uses their own username for interactive, short tests and use a custom username for their client apps. They want their own interactive tests to access the database without authentication, but keep authentication active for their test code. This can be achieved as follows:

# trust all unix connections via username 'alex'.
local  all alex                   peer # see note below
# for every other user, use a regular authentication.
local  all all                    <as usual>
host   all all   127.0.0.1/8      <as usual>
host   all all   ::1/128          <as usual>

In all these cases, the following invariant holds: the database is not accessible over the network. This is a suitable default for the common case of laptop-based development in insecure network environments (e.g. a coffeeshop).

Simple web server with local database

This is a typical PostgreSQL deployment: a web application and a PostgreSQL database running on the same server (dedicated or VPS).

In this case, the following configuration is adequate:

# accept connections to database 'appdb'
# and user 'appuser' via the unix domain socket.
# The client app is trusted as long as the connection
# is established by a process running under the unix
# account 'appuser'.
local appdb appuser        peer

# accept connections to any database over
# the unix socket by the 'postgres' (typically
# superuser) using passwords. This is suitable
# to administrate the database.
local all   postgres       md5

This configuration prevents processes running under other accounts than appuser from accessing the database. It also prevents the appuser account from accessing other databases than appdb. The client app does not need to provide a password; there is no additional security provided by passwords after the peer user account is verified.

Private network deployment with a public load balancer

This setup is commonly found in Cloud applications, or enterprise architectures when the database is provided as a service run by its own department.

In this setup, there is one or more networked database server, running on a private network not directly accessible by database clients. A load balancer or connection proxy is situated at the network boundary and mediates access by clients.

For additional security, the operator wishes that one compromised database server does not affect other servers running on the private network.

For this setup, the following configuration can be appropriate:

host  all all    <LB address>     <as usual>
host  all all    all              reject

This configuration accepts authentication requests coming via the load balancer only. It rejects requests coming from other servers on the private network, to avoid misuse by compromised peers. It also rejects accesses via the unix domain socket (due to the lack of any local rule—an explicit reject can be achieved via local all all reject), to avoid misuse by compromised services running under other unix accounts on the same server.

Additionally, the configuration can restrict authentication to only accept non-privileged user accounts by further customizing the “username” column. For example:

host  appdb1  +appdb1-group    <LB address>     <as usual>
host  appdb2  +appdb2-group    <LB address>     <as usual>
# ...
host  all     all              all              reject

This configuration ensures that for every application, only users part of that application’s group/role can access that application’s database.

(Note: group-based filtering is not currently supported by CockroachDB but may become supported in the future.)

Deployment with DMZ or secure control plane

This setup is found in more advanced network setups where the network is organized in three layers:

• an unprivileged network, where database clients run;
• a perimeter network, or DMZ, where monitoring or control services run;
• a private network where the database server(s) run.

In this setup, clients from the unprivileged networks have the least level of access and are restricted to particular databases or usernames. However, control or monitoring software running on the DMZ need additional access, for example to monitor database health or access internal metrics (stored inside the database).

A load balancer for the unprivileged clients is assumed to run at the external edge of the DMZ.

In this case, the following type of configuration is appropriate:

# restrict connections for unprivileged clients to
# specific applications via specific usernames.
host  appdb1  +appdb1-group    <LB address>              <as usual>
host  appdb2  +appdb2-group    <LB address>              <as usual>

# authorize monitoring software in the DMZ
# to access databases.
host  all     +monitoring      <DMZ addr> <DMZ netmask>  cert

# reject everything else.
host  all     all              all                       reject

This configuration, as in the previous section, restricts access to each application database by users in that app’s user group and via the external load balancer.

It then lets applications logging in via usernames in the monitoring group as long as they are running in the DMZ and provide a valid client certificate.

Any other connection is rejected.

Multi-tenant database server

This setup is commonly used by web hosting providers, or the administrator of university campuses, to provide each qualified user/customer with “their own” database.

In this context, user accounts are created and deleted centrally by the organization, and frequently. It is thus undesirable to require a manual change to the database’ configuration upon each account update.

In this case, the following configuration is suitable:

host  sameuser  all    <as usual>     <network method>

To prevent an in-database update when the list of valid users is updated, this configuration must use a network method: one of gss / ldap (common in corporate networks), radius (common in universities) or similar. This way, the list of users is not stored in the database [9] and authentication is always delegated.

The client address is then typically filtered, according to the concepts described in the previous section.

The special sameuser keyword for the database column ensures that clients cannot access databases other than their own.

Proper use of database user mapping

One motivation to use delegated or stateless authentication methods (see previous section) is to avoid synchronizing the database’s user list with an externally managed user directory.

However, both PostgreSQL and CockroachDB require the username of a SQL client session to exist inside the database. The database-level account needs to exist because it connected to SQL-level privileges (GRANT / REVOKE).

These two statements appear contradictory. How can one avoid synchronizing the database with a centrally-managed user directory if user accounts need to exist in the database?

The answer lies in the use of user name maps. A group of N different user accounts managed externally and dynamically can be mapped to M (fewer) database-level accounts created just once, with a fixed set of SQL privileges. For example, the database-level name could be a single myapp-user. Then users with names alice, bob and maria, each with different authentication tokens or methods, can all be mapped during authentication to the single database account myapp-user. (This mechanism was already mentioned in the Username section above.)

Rate limiting

The application of authentication rules to an incoming network connection is resource-intensive. This is especially true for password-based or delegated authentication methods. In these cases, it is possible for an attacker to overload a server (DoS) by issuing a large number of simultaneous authentication attempts.

While PostgreSQL supports a crude limit on the maximum number of open client connections, this approach is defective in that an attacker opening that number of connections will cause legitimate clients to be denied access (another form of DoS). CockroachDB, incidentally, does not support this parameter at all.

The proper way to protect a database server against authentication-based DoS attacks is to perform rate-limiting. While PostgreSQL does not support rate limiting out-of-the-box, CockroachDB uses a semaphore internally to limit CPU usage by clients. However, the best practice remains that rate limiting should thus be deployed at a separate level in the network (e.g. in the load balancer).

Password security and replay protection

Any password algorithm should be secured against replay attacks, hence the recommended choice to mandate SCRAM-SHA-256. Note that the ldap and radius methods do not use a challenge-response method to receive the password from the client. They are thus inherently less resistant to spoofing attacks unless they are combined with mandatory TLS certificate validation (via the option clientcert in PostgreSQL).

TLS server vs client cert validation

TLS certificate validation is possible in two directions:

• the client should verify the server’s identity, to ensure it is connected to the right server. This prevents leaking authentication credentials when the server is compromised.

  This is called server certificate validation.

• the server should verify the client’s identity, to ensure that the client is who it claims to be. This prevents misuse of the database by spoofed connections.

  This is called client certificate validation.

Both are useful and thus should be used in combination.

Server cert validation can be omitted if the client has other ways to ascertain the identity of the server. This is commonly the case in corporate networks.

Client cert validation can be omitted if the server has another way to ascertain the identity of the client. This is commonly the case when TLS encryption is combined with password authentication or another method that uses a secure authentication token.

Certificate revocation

The most secure authentication method using shared secrets is to use TLS client certificates. However, this method is only secure if access can be revoked when the shared secret is compromised.

PostgreSQL supports revocation lists (CRLs) via a file called root.crl.

CockroachDB supports OCSP instead of revocation lists. In this protocol, certificates and CAs provide a URL to a server that CockroachDB queries to verify whether the certificate fingerprint is still valid. OCSP validation can be enabled via the cluster setting security.ocsp.mode, to either lax (accept certs if the OCSP server is unavailable) or strict (reject certs if the OCSP server is unavailable).