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.
This post was originally published in April 2020, targeting CockroachDB v20.1. It was edited in February 2022 to reflect changes in CockroachDB v22.1. A summary of changes is available in this page.
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 v20.1, then the authentication code in subsequent erleases. 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.
PostgreSQL’s and CockroachDB’s authentication configuration, also called “HBA  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
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.
The authentication configuration applies for user accounts
that have the
(or, conversely, are not marked as
NOLOGIN). Users marked
NOLOGIN are always denied authentication regardless
of authentication rules.
|||“HBA” stands for “Host-Based Authentication”, even though the configuration language is not exactly host-based.|
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.
The first column is applied to the connection type as follows:
localmatches connections via the unix domain socket.
hostmatches any TCP/IP connection.
hostsslmatches TCP/IP connections with TLS encryption.
hostgssencmatches TCP/IP connections using GSSAPI (e.g. Kerberos) encryption.
hostnosslmatches TCP/IP connections without TLS encryption. It thus matches either unencrypted or GSSAPI-encrypted connections.
hostnogssencmatches TCP/IP connections without GSSAPI encryption. It thus matches either unencrypted or TLS-encrypted connections.
PostgreSQL supports all of these formats; CockroachDB currently (as of
v22.1) only supports the first three (
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
allmeans 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
samenetmatch the server’s IP addresses and networks, respectively.
- if the rule specifies a hostname, or domain name suffix starting
., 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.
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.
PostgreSQL supports all four types of address
validation. CockroachDB (as of version v22.1) only supports numeric
address validation and the
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
NOLOGIN). If the user is allowed to log in, the
authentication rules are then filtered based on the client username.
- the keyword
allmatches 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.
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.)
PostgreSQL supports all four types of username
validation. CockroachDB (as of version v22.1) only supports the
all and specific names.
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
allmatches any database name.
- the keyword
sameusermatches when the presented database and user names are the same.
- the keyword
samerolematches 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
replicationspecifies 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.
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'.
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
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.
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.
||Direct password authentication ⚠️ . (documentation link)||Password stored and checked by server.||✔||✔|
||TLS client certificate. (documentation link)||Stateless and no 3rd party. Reasonably secure. 🔒||✔||✔|
||Either a simple password, a SCRAM-SHA-256 handshake or a TLS client certificate.||Hybrid.||❌||✔|
||Either SCRAM-SHA-256 or MD5 password validation ⚠️ . (documentation link)||Password stored and checked by server.||✔||❌|
||Use SCRAM-SHA-256 password validation. (documentation link)||Password stored and checked by server. Reasonably secure. 🔒||✔||✔|
||Either SCRAM-SHA-256 password validation or a TLS client certificate.||Hybrid.||❌||✔|
||Allow the connection unconditionally. (documentation link)||Stateless and no 3rd party.||✔||✔|
||Always reject the connection.||Stateless and no 3rd party.||✔||✔|
||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. 🔒||✔||✔ |
||Ask the OS for the peer username of the connection and accept
the connection if it matches the presented username. Only
||Authn delegated to local OS. Reasonably secure. 🔒||✔||❌|
||Authn delegated to OS or network service.||✔||❌|
||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
||Authn delegated to OS or network service. Reasonably secure. 🔒||✔||❌|
||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 ⚠️ . (documentation link)||Authn delegated to network service.||✔||❌|
||Use RADIUS authentication. The server sends an authentication request to a RADIUS server using the username/password pair ⚠️  provided by the client. (documentation link)||Authn delegated to network service.||✔||❌|
||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. 🔒||✔||❌|
||Use BSD-style authentication. This is an OpenBSD technology
conceptually similar to PAM, and uses a rule-based
configuration in the system’s
||Authn delegated to local OS. Reasonably secure. 🔒||✔||❌|
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
clientcert in the last column. See PostgreSQL’s
This feature is not yet supported by CockroachDB.
|||The general category can help guide the selection of a “good” authentication method. For more details, see Selecting an authentication method later below.|
|||Pure MD5-based password validation is obsolete. It is also vulnerable to replay attacks. New deployments should always use SCRAM-SHA-256 or better.|
|||CockroachDB currently (as of v22.1) only supports |
|||Note that RFC 1413 peer authentication is usually considered obsolete and insecure. New deployments should avoid it.|
|||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.|
|||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
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
peer(same unix username).
- network connections are accepted from the loopback interface
::1/128) using the methods
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.
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
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.
In both PostgreSQL and CockroachDB, the method
password forces the
server to request a cleartext of a password from the client (although
the password is not really transmitted in-clear over the network, if
the connection uses TLS).
In PostgreSQL, the method
md5 forces the server to request either
a hash (if the stored credential actually uses the
or a SCRAM-SHA-256 handshake. In either case, the method is
guaranteed to not request a cleartext password from the client.
scram-sha-256, supported by both PostgreSQL and
CockroachDB (as of v22.1), further restricts the authentication to use
only a SCRAM handshake. This requires the stored credential to use the
SCRAM-SHA-256 encoding beforehand.
cert-password method is special. If a SQL
client does not present a TLS client certificate, then the server will
request a password as follows:
if the current stored credential uses the SCRAM-SHA-256 encoding, the server will request a secure SCRAM handshake.
if the current stored credential uses a bcrypt hash, the server will request a cleartext password and compute/compare its hash server-side.
After authentication succeeds, the server also reencodes the password using the SCRAM-SHA-256 algorithm, so that subsequent authentication events for the same principal can use a SCRAM handshake instead.
This automatic upgrade of the password algorithm was designed to
evolve pre-v22.1 clusters into using SCRAM-SHA-256 without additional
operator input. It can be manually disabled via the cluster setting
cert-scram-sha-256 method accepts either a
TLS client certificate, or a SCRAM-SHA-256 handshake.
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).
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.
CockroachDB does not support the
method. An alternative is
it possible for other users logged in on the same server
to spoof the user’s identity and access the database.
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
appuser from accessing the database. It also prevents the
appuser account from accessing other databases than
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
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.
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
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
ldap (common in corporate networks),
radius (common in
universities) or similar. This way, the list of users is not
stored in the database  and authentication is always delegated.
The client address is then typically filtered, according to the concepts described in the previous section.
sameuser keyword for the database column ensures that
clients cannot access databases other than their own.
CockroachDB does not yet support
As of v21.1, CockroachDB contains native support for multi-tenant deployments. This mechanism is more fundamental than the HBA mechanism: each tenant has its own authentication configuration, invisible from every other tenant.
Cockroach Labs does not recommend using HBA rules to create the illusion of multi-tenancy on top of a single logical CockroachDB cluster. Instead, users should consider using the CockroachCloud “serverless” offering instead.
|||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.
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.
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.
certor 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
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)||
|Per database server||No||(Not available in either PostgreSQL nor CockroachDB yet.)|
|Cross-organizational networks or public Internet||Centralized (best)||No (best)||
|Per database server||No||(Not available in either PostgreSQL nor CockroachDB yet.)|
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
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
maria, each with different authentication tokens or
methods, can all be mapped during authentication to the single
myapp-user. (This mechanism was already mentioned
in the Username section above.)
|User map method||PostgreSQL||CockroachDB|
|Uniform map in
||✔||✔ (as of v21.2)|
||❌||✔ (as of v20.1)|
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
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.
|Selected configuration||Resulting security|
|Client-side configuration||Server-side configuration||Encryption + tamper protection||Server cert validation?||Client cert validation?|
||Method other than
||Method other than
||Method other than
||Yes||CA only||CA only|
||Method other than
If the server selects method
cert but the client fails
to present a valid TLS client cert (e.g.
sslkey= are omitted), server-side authentication fails.
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
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
(reject certs if the OCSP server is unavailable).
The authentication handshake between a PostgreSQL-compatible client and a PostgreSQL or CockroachDB server works uniformly as follows:
- the client connects using either a unix domain socket or a TCP/IP connection.
- the client first presents its desired username and database name to the server.
- 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.)
- the server then checks if the user has the
NOLOGINattribute; if it has, the connection is immediately denied.
- 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.)
- 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
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.
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.
- Data flows and security architecture in CockroachDB.
- PostgresSQL manual Chapter 20, Client Authentication: The pg_hba.conf file.
- CockroachDB issue #47403: Align client cert validation with pg.
- CockroachDB issue #47405: Restrict the mandatory root rule to localhost conns.
- RFC 7677: SCRAM-SHA-256 and SCRAM-SHA-256-PLUS Simple Authentication and Security Layer (SASL) Mechanisms
- CockroachDB’s implementation of SCRAM-SHA-256 (RFC)
So what do you think? Did I miss something? Is any part unclear? Leave your comments below.