Both PostgreSQL and CockroachDB provide a little-known gem when it comes to controlling incoming SQL connections: a flexible, versatile configuration DSL for client authentication. In this blog post, I will explain this configuration language, describe several common and some advanced use cases, reveal a few security pitfalls, all the while highlighting the commonalities and differences between the two databases.

Note that most of the information enclosed here is still missing from CockroachDB’s official documentation at the time of this writing. This knowledge was instead sourced from CockroachDB’s public source code on GitHub, in particular from my own contributions to the project and my study of PostgreSQL. This text will be updated with more links to CockroachDB’s own documentation when it gets extended in this direction.

Disclaimer: I have been contracted by Cockroach Labs to contribute a large part of CockroachDB’s rule-based authentication infrastructure for the upcoming release of v20.1. Even though this work forced me to learn about how PostgreSQL handles authentication (so as to teach CockroachDB how to imitate it), I may not have learned all the nuances of PostgreSQL’s options. Also, as usual on this blog, opinions expressed here are my own.

Overview

PostgreSQL’s and CockroachDB’s authentication configuration, also called “HBA [1] configuration”, determines how the database server accepts SQL clients and how they should identify themselves to the server.

In PostgreSQL, the current configuration is stored in a file named pg_hba.conf in the data directory. In CockroachDB, it is stored in a cluster setting variable named server.host_based_authentication.configuration.

An example configuration looks like this:

# Allow any user on the local system to connect to any database with
# any database user name using Unix-domain sockets (the default for local
# connections).
#
# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             all                                     trust

# In the absence of preceding "host" lines, these two lines will
# reject all connections from 192.168.54.1 but accept
# password-authenticated clients from everywhere else on the internet.
# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             192.168.54.1/32         reject
host    all             all             all                     password

Each line constitutes an authentication rule. It works like this:

  • For every incoming client connection, the server evaluates the rules one by one:
    • For each rule, if the connection matches the type, database, user and address column, then the rule is selected and the process stops.
    • If the connection does not match, the next rule is evaluated instead (iteration).
  • If there was no matching rule at the end of evaluation, the connection is denied.
  • If there was a matching rule, its method is applied to authenticate the client. If the method succeeds, the client can log in. If it does not, the connection is denied; no other rules are then considered.

Note

The authentication configuration applies for user accounts that have the LOGIN attribute (or, conversely, are not marked as NOLOGIN). Users marked as NOLOGIN are always denied authentication regardless of authentication rules.

[1]HBA” stands for “Host-Based Authentication”, even though the configuration language is not exactly host-based.

Configuration syntax

Each rule can have one of the following formats:

TYPE         DB  USER  ADDRESS     MASK     METHOD       OPTIONS
------------ --- ----- ----------- -------- ------------ --------------

local        db  user                       auth-method  [auth-options]

host         db  user  address              auth-method  [auth-options]
host         db  user  IP-address  IP-mask  auth-method  [auth-options]

hostnossl    db  user  address              auth-method  [auth-options]
hostnossl    db  user  IP-address  IP-mask  auth-method  [auth-options]

hostssl      db  user  address              auth-method  [auth-options]
hostssl      db  user  IP-address  IP-mask  auth-method  [auth-options]

hostgssenc   db  user  IP-address  IP-mask  auth-method  [auth-options]
hostnogssenc db  user  IP-address  IP-mask  auth-method  [auth-options]

The first five fields are used to select a matching rule upon an incoming client connection. Once a rule is selected, the method and options are used to authenticate the client.

On each line, a # character can start a comment; comments extend to the end of the line and are further ignored.

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.

Note

PostgreSQL supports all of these formats; CockroachDB currently (as of v20.1) only supports the first three (local and host types).

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.

Note

PostgreSQL supports all four types of address validation. CockroachDB (as of version v20.1) only supports numeric address validation and the all keyword.

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 v20.1 via 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.)

Note

PostgreSQL supports all four types of username validation. CockroachDB (as of version v20.1) only supports the first two: all and specific names.

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'.

Note

The database column is where a major conceptual difference exists between PostgreSQL and CockroachDB. In PostgreSQL, a client is bound to a specific database throughout its SQL session. In CockroachDB, a client can switch between databases (via SET database or USE). Therefore, database-based filtering in CockroachDB would be meaningless and is thus not supported.

At the time of this writing, the database column is thus unused in CockroachDB, and the server only accepts the keyword all in that position. This may change in the future, as outlined in section Multi-tenant database server below.

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.

Method Description Category [2] PostgreSQL CockroachDB
password Direct password authentication ⚠️ [3]. (documentation link) Password stored and checked by server.
cert TLS client certificate. (documentation link) Stateless and no 3rd party. Reasonably secure. 🔒
cert-password Either a simple password or a TLS client certificate. Hybrid.
md5 Either SCRAM-SHA-256 or MD5 password validation ⚠️ [4]. (documentation link) Password stored and checked by server.
scram-sha-256 Use SCRAM-SHA-256 password validation. (documentation link) Password stored and checked by server. Reasonably secure. 🔒
trust Allow the connection unconditionally. (documentation link) Stateless and no 3rd party.
reject Always reject the connection. Stateless and no 3rd party.
gss Use GSSAPI (e.g. Kerberos) authentication. This is only valid for TCP/IP connections; either in combination with TLS or GSSAPI encryption or over unencrypted connections. (documentation link) Authn delegated to network service. Reasonably secure. 🔒 [5]
peer Ask the OS for the peer username of the connection and accept the connection if it matches the presented username. Only valid for local connections. (documentation link) Authn delegated to local OS. Reasonably secure. 🔒
ident For local connection, synonymous to peer. For network connections, use the standard RFC 1413 Identification protocol to request the peer username and accept the connection if it matches the presented username ⚠️ [6]. (documentation link) Authn delegated to OS or network service.
sspi Use SSPI authentication (Windows-only). This will attempt to use Kerberos and fall back to NTLM if it fails. When using Kerberosm it is equivalent to gss. (documentation link) Authn delegated to OS or network service. Reasonably secure. 🔒
ldap Use LDAP authentication. This also uses passwords. the server first checks whether the presented username is present in the directory, then attempts to connect to the directory using the provided username/password pair ⚠️ [7]. (documentation link) Authn delegated to network service.
radius Use RADIUS authentication. The server sends an authentication request to a RADIUS server using the username/password pair ⚠️ [8] provided by the client. (documentation link) Authn delegated to network service.
pam Use PAM authentication. The PAM rule-based authentication configuration on the host system is used to validate the client connection. (documentation link) Authn delegated to local OS. Reasonably secure. 🔒
bsd Use BSD-style authentication. This is an OpenBSD technology conceptually similar to PAM, and uses a rule-based configuration in the system’s login.conf. (documentation link) Authn delegated to local OS. Reasonably secure. 🔒

Note

PostgreSQL supports the most diverse set of authentication methods, with a combination of various state-of-the-art secure methods for network clients (e.g. SCRAM-SHA-256, GSSAPI, TLS client certificates).

In comparison, CockroachDB’s only state-of-the-art secure methods are, as of v20.1, just cert and gss. Password authentication should be avoided for network connections from the public Internet until/unless CockroachDB learns at least about SCRAM-SHA-256.

For more details, see Selecting an authentication method below.

Note

In addition to the above, PostgreSQL supports adding TLS client certificate validation to any of the other authentication methods, when the authentication takes place over TLS. This is achieved by passing the method-independent option clientcert in the last column. See PostgreSQL’s documentation for details.

This feature is not yet supported by CockroachDB.

Footnotes:

[2]The general category can help guide the selection of a “good” authentication method. For more details, see Selecting an authentication method later below.
[3]The password method expects the client to provide the password as-is to the server. This is generally considered safe over local connections (either over a unix socket or network loopback), even when the connection is unencrypted, because of the limited vulnerability to MITM attacks in that case. With non-local network connections, this method is somewhat weak. It is generally unsafe if the connection is not encrypted via TLS or GSSAPI. It is vulnerable to replay attacks even when the connection is encrypted.
[4]Pure MD5-based password validation is obsolete. It is also vulnerable to replay attacks. New deployments should always use SCRAM-SHA-256 or better.
[5]CockroachDB currently (as of v20.1) only supports gss authentication over unencrypted or TLS-encrypted connections.
[6]Note that RFC 1413 peer authentication is usually considered obsolete and insecure. New deployments should avoid it.
[7]I was not able to determine whether the password is forwarded to the LDAP server as-is, or whether password encryption/hashing is possible. It seems to me that this method could be insecure unless combined with mandatory TLS certificate validation.
[8]I was not able to determine whether the password is forwarded to the RADIUS server as-is, or whether password encryption/hashing is possible. It seems to me that this method could be insecure unless combined with mandatory TLS certificate validation.

Default configuration rules

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 19.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 12 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 v20.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 direct passwords. This default is relatively secure because, as of v20.1, passwords can only be used over TLS-encrypted network connections.

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

Use cases

The configuration language is flexible and enables a myriad of deployment scenarios; site administrators can mix and match options to their infrastructure needs and obtain the security level they seek, without explicit support by the database vendor.

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).

Note

The peer method ensures that the client program connecting to the database is run by user alex. This is important to restrict access in multi-user systems if there are other users logged in on the development server.

Warning

CockroachDB does not support the peer authentication method. An alternative is trust; however trust makes it possible for other users logged in on the same server to spoof the user’s identity and access the database.

Warning

CockroachDB does not currently allow the authentication configuration to prevent network access by the root user. To achieve a safe setup in this case, the server should be started with --listen-addr=localhost or equivalent to only listen on the loopback interface.

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 application, 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.

Note

Certificate-based authentication is usually preferrable in this case: it can be made more resistant to system compromises in the DMZ by ensuring that the client certificates are only stored in RAM. Alternatively, the monitoring software can use a secure authentication token from an external server via gss. Generally, password-based or ident-based authentication is not suitable for DMZ monitoring/control agents because they are more vulnerable to compromises of other services running on the same server.

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.

Note

CockroachDB does not yet support sameuser. (More generally, CockroachDB is not yet suitable for multi-tenant deployments due to the lack of physical separation between user accounts and databases.) If/when CockroachDB is extended to support multi-tenant deployments, it is likely to repurpose the database column in the authentication configuration to filter tenants.

[9]This is only true when user maps are configured; see also Proper use of database user mapping below.

Selecting an authentication method

As explained in Use cases above, the general structure of the authentication rules depends on the deployment topology and decides which clients can connect from where and with which usernames.

However, once a topology is chosen, the question remains of which authentication method to use to verify the client’s identity.

As outlined in section Authentication method, there are three general categories of methods:

  • Password check by the database server itself (e.g. password, md5, scram-sha-256). This requires the database to know both about the user and its password.

    Pros: methods from this category make authentication conveniently self-contained and mininize the number of “moving pieces” to care about by a site administrator. It is thus easier to set up initially.

    Cons: dynamic environments where user accounts are routinely added or deactivated/deleted require synchronization with the database server. If user account updates are frequent, or if there are many database servers, this administration becomes cumbersome. Additionally, faults in the synchronization processes can become outright security vulnerabilities. All-in-all, db-level password checks provide a false sense of security and should generally be avoided for production deployments.

  • Delegated authentication (e.g. gss, peer, ident, sspi, ldap, radius, pam, bsd). This forwards the responsibility for authentication to a separate service. This can use either passwords or secure app-specific tokens (e.g. Kerberos tickets).

    Pros: methods in this category remove the burden of synchronizing the database configuration with a centralized user database and authentication system. It generally transfers the responsibility for authentication away from the database server. (However, see Security pitfalls below.)

    Cons: these methods are generally more complex to set up. A misconfiguration can block access to the database even by site administrators and can be difficult to troubleshoot.

  • Stateless authentication (e.g. cert or PostgreSQL’s method-independent option clientcert). This uses TLS public/private key cryptography to authenticate clients.

    Pros: the database does not need either a local password store nor an external authentication server to verify the identity of clients. This can be easier to deploy in Cloud infrastructures and yield slightly faster connections.

    Cons: the site administrator is responsible for issuing client certificates and safeguarding the private keys of the shared CA. Client access can only be revoked using certificate revocation lists which are cumbersome to administrate.

Which methods are best?

  • Within an organizational network, delegated authentication should be preferred with secure tokens, such as that offered via gss or sspi. It facilitates sharing the cost of user administration between multiple services, and enables the principle of least privilege by issuing narrow-scoped tokens to each individual user or application.

  • For accesses across privileged networks of separate organizations, or across unprivileged networks, either delegated authentication with a secure password algorithm (see Security pitfalls below) or stateless authentication should be preferred.

    Delegated authentication should be used when the validity (or expiry) of user accesses is dynamic and can be updated at short notice. It is also adequate when users cannot be reliably trusted to safeguard their access tokens, so that access frequently needs to be blocked on a short notice.

    Stateless authentication (with self-contained tokens such as TLS certificates) can be used when the validity of user accesses is more static and the clients are known to protect their authentication tokens reasonably well.

The following table provides an approximate decision tree:

Topology User administration Shared secrets Recommended authn method
Organizational network Centralized (best) No (best) gss (best), sspi, GSSAPI via pam.
Yes cert with org-level CA (best); ldap, radius, LDAP/RADIUS via pam.
Per database server No (Not available in either PostgreSQL nor CockroachDB yet.)
Yes cert with db-level CA (best); scram-sha-256, pam or bsd with secure methods at OS level. Use password or md5 over encrypted connections only (beware of replay attacks).
Cross-organizational networks or public Internet Centralized (best) No (best) gss or GSSAPI via pam, with Kerberos ticket issuance over VPN.
Yes cert with public CA and centrally managed revocation list (best); radius, or RADIUS via pam.
Per database server No (Not available in either PostgreSQL nor CockroachDB yet.)
Yes cert with public CA and per-db revocation lists (best); scram-sha-256 if passwords cannot be avoided. Avoid md5 and password at all costs.

Security pitfalls

The following sections highlight common yet serious security pitfalls. As of this writing, PostgreSQL offers more comprehensive solutions to each of them than CockroachDB.

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.)

User map method PostgreSQL CockroachDB
With gss, option include_realm (obsolete, also limited N-M mapping power)
Uniform map in pg_ident.conf (recommended) ❌ (as of v20.1)
With cert, command-line flag --cert-principal-map (CockroachDB-specific) ✔ (as of v20.1)

As shown in this table, user maps are supported for every authentication method in PostgreSQL via pg_ident.conf, and are only supported for the gss and cert methods in CockroachDB.

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. As of this writing, neither PostgreSQL nor CockroachDB currently support rate-limiting out-of-the-box; 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).

Note that CockroachDB does not yet support SCRAM-SHA-256; at this time, any password-based method in CockroachDB is inherently vulnerable to spoofing attacks.

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.

Selected configuration Resulting security
Client-side configuration Server-side configuration Encryption + tamper protection Server cert validation? Client cert validation?
sslmode=disable Method other than cert No No No
sslmode=require Method other than cert Yes No CA only
sslmode=verify-ca Method other than cert Yes CA only CA only
sslmode=verify-full Method other than cert Yes Yes CA only
sslmode=require + sslcert= / sslkey= Method cert selected Yes No Yes
sslmode=verify-ca + sslcert= / sslkey= Method cert selected Yes CA only Yes
sslmode=verify-full + sslcert= / sslkey= Method cert selected Yes Yes Yes

Note

If the server selects method cert but the client fails to present a valid TLS client cert (e.g. sslcert= / sslkey= are omitted), server-side authentication fails.

Revocation lists

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.

Unfortunately, CockroachDB does not support certificate revocation lists as of this writing. To revoke one certificate requires replacing the entire CA and thus rotating every other client certificates as well. This is extremely cumbersome/costly and nearly negates the benefits of TLS-based authentication.

Summary

The authentication handshake between a PostgreSQL-compatible client and a PostgreSQL or CockroachDB server works uniformly as follows:

  1. the client connects using either a unix domain socket or a TCP/IP connection.
  2. the client first presents its desired username and database name to the server.
  3. the server translates the client-side provided username to a server-side username using the configured name map, if any. (See sections Username and Proper use of database user mapping.)
  4. the server then checks if the user has the NOLOGIN attribute; if it has, the connection is immediately denied.
  5. the server selects an authentication method by finding the first configuration rule that matches the connection type, presented database and username, and the client address. If no rule matches, the connection is denied. (See section Configuration syntax.)
  6. the selected Authentication method is applied. If it fails, the connection is denied and no further rule is evaluated. If it succeeds, the SQL session is established.

PostgreSQL’s default configuration rules balance convenience with security: it uses weak authentication methods, but restricts access to clients running on the same server. CockroachDB’s defaults provide access via the network but mandate TLS client certificates or encrypted passwords. (See Default configuration rules.)

For more advanced deployments, both CockroachDB and PostgreSQL support state-of-the-art authentication methods for shared secrets (TLS certificate validation via cert) and for externally issued authentication tokens (GSSAPI token validation via gss). See Selecting an authentication method for details.

Thanks to the flexibility of their common authentication configuration language, site administrators can deploy both databases in a diverse variety of topologies and security requirements without requiring ad-hoc vendor-supplied features for each configuration.

However, for both PostgreSQL and CockroachDB, database-level authentication is not sufficient for good security hygiene (see Security pitfalls): all secure deployments should also care about rate limiting and set up processes to both safeguard and revoke secrets and certificates.

Ackowledgements

My work on CockroachDB was funded by Cockroach Labs. I am grateful to Matt Jibson for awakening my curiosity about GSSAPI and Kerberos, and to Cockroach Labs’ resident security expert Aaron for teaching me about the nuances of various attack vectors and authentication requirements.

Like this post? Share on: TwitterHacker NewsRedditLinkedInEmail


Raphael ‘kena’ Poss Avatar Raphael ‘kena’ Poss is a computer scientist and software engineer specialized in compiler construction, computer architecture, operating systems and databases.
Comments

So what do you think? Did I miss something? Is any part unclear? Leave your comments below.


Keep Reading


Reading Time

~24 min read

Published

Category

CockroachDB

Tags

Stay in Touch