CockroachDB Host Based Authentication

CockroachDB is a distributed SQL database designed for speed, scale and resiliency. CockroachDB supports the PostgreSQL wire protocol and the majority of the PostgreSQL syntax.

Host-based authentication is part of the PostgreSQL wire protocol and is configured through the pg_hba.conf file. This allows database administrators to define granular control of how different clients and users are allowed to authenticate. Entries in the pg_hba.conf file consist of connection type, a database name, a user name, a client IP address range (if relevant for the connection type), and the authentication method to be used for connections.

For organizations with stricter access control requirements, host-based authentication can be enabled for example to force all connections to utilize certificate based authentication instead of password authentication. CockroachDB implements the host-based authentication component of the PostgreSQL wire protocol through the cluster setting

server.host_based_authentication.configuration

CockroachDB will automatically create host-based authentication rules in the background if not specified by an administrator in the cluster setting. These rules can be found in the source code for CockroachDB and are as follows -

host  all root all cert-password # CockroachDB mandatory rule

host  all all  all cert-password # built-in CRDB default
local all all      password      # built-in CRDB default

The first rule is always, automatically and transparently included in the host-based authentication configuration to allow root to be able to authenticate in the event that host-based authentication is misconfigured so that all access to the cluster is not blocked.

The positional entries in the configuration lines above correspond to

connection type: host = TCP/IP connection, local = unix-domain socket requests
database: all
user: root, all, or specific user name
address: all or specific IP addresses/ranges
auth method: cert, password, etc

See the PostgreSQL online documentation for full details.

Now let’s take a look at a concrete example of host-based authentication in action on a secure CockroachDB cluster. My goal is to require all connections to the database to use SSL certificates for authentication. I am going to use the DBeaver SQL client tool to illustrate this. By default all users can authenticate via password to a cluster. To change this behavior I add a host-based authentication rule to my cluster setting. From my SQL command line I specify

set cluster setting server.host_based_authentication.configuration='host  all  all  all cert';

To verify the entry use the command

show cluster setting server.host_based_authentication.configuration;

I created the user “cockroach” and assigned that user a password and now when I attempt to verify my connection to CockroachDB through DBeaver using only that user name and password I get the following exception

To perform certificate authentication I must create a client certificate for my “cockroach” user and provide that in the DBeaver configuration dialog screen.

Note that I had to convert my cockroach.client.key file to a .pk8 format using the command

openssl pkcs8 -topk8 -inform PEM -outform DER -in cockroach.client.key -out cockroach.client.key.pk8

Now when I attempt to verify my connection I can successfully connect over SSL

Remember that the PostgreSQL wire protocol host-based authentication provides the ability to control different authentication methods for different users. This is accomplished by providing multiple directive lines to the CockroachDB cluster setting. If for example, I only wanted my “cockroach” user to be forced to use certificate authentication while all others can also use password authentication I can specify the following.

Using show cluster setting I can see that all users except “cockroach” can connect via user name and password or certs but “cockroach” will be required to provide certificates.

One additional note, the permissions in the cluster setting are checked in the order in which they are entered, so enter your rules in order of least permissive to most permissive.

Using the PostgreSQL wire protocol host-based authentication mechanism within CockroachDB provides a method for organizations with elevated security requirements to control authentication to their CockroachDB clusters. As always, before implementing a feature like host-based authentication functionality in production, thoroughly test all the means and methods by which you will allow connections to the database!