CockroachDB With Mixed Kerberos and Certificates Authentication

Most of my tutorials originate as customer questions and this one is no different. I had a customer inquire whether an existing cluster with Kerberos authentication can allow non-root users to authenticate using cert instead of using GSS API. Given the layered pg_hba.conf design influenced by Postgres, we should have no trouble accommodating this use case.

I find the topic of Kerberos very interesting and my colleagues commonly refer to me for help with this complex topic. I am by no means an expert at Kerberos, I am however familiar enough with it to be dangerous. That said, I've written multiple articles on the topic which you may find below:

1. CockroachDB With MIT Kerberos
2. CockroachDB With Active Directory
3. CockroachDB With MIT Kerberos and Docker Compose
4. Executing CockroachDB table import via GSSAPI
5. CockroachDB With SQLAlchemy and MIT Kerberos
6. CockroachDB With MIT Kerberos Cert User Authentication
7. CockroachDB with Django and MIT Kerberos
8. CockroachDB With Kerberos and Custom Service Principal Name (SPN)
9. Simplifying CockroachDB Kerberos Architecture With a Load Balancer
10. CockroachDB With MIT Kerberos Using a Native Client

Motivation

As you may already know, in CockroachDB if you enable GSSAPI, you lose the ability to connect to the cluster with cert-based authentication as a non-root user. Specifically, the following language makes it clear:

SET cluster setting server.host_based_authentication.configuration = 'host all all all gss include_realm=0';

Setting the server.host_based_authentication.configuration cluster setting to this particular value makes it mandatory for all non-root users to authenticate using GSSAPI. The root user is always an exception and remains able to authenticate using a valid client cert or a user password.

What is not immediately clear is that HBA configuration can be applied multiple times allowing for granular authentication mechanisms on top of the cluster-specific setting. So in essence, to achieve today's goal, we will have a root user that always connects with a cert, every other user connect with gss , and one additional user roach which will also connect with a cert. The latter happens to be the missing link which we are going to make unequivocally clear.

High-Level Steps

• Start a CockroachDB cluster with GSSAPI enabled
• Create a cert-based user in CockroachDB that does not exist in KDC
• Define a custom hba config for this user
• Verify
• Clean up

Step by Step Instructions

Start a Cluster

I have a ready-made repeatable environment that I continue to refer to in my tutorials that will spin up a three-node CockroachDB cluster with cert-based authentication for inter-node communication, a root user, and its cert for fallback authentication, and a GSS API configuration that will prevent non-root users from connecting to the cluster. You may find it here.

Start the environment using a helper script .up.sh. After a while, you'll have a three-node cluster, a load balancer container, a KDC container, and a client node.

Create a User in CockroachDB That Does Not Exist in KDC

docker compose exec roach-0 \
 /cockroach/cockroach sql \
 --certs-dir=/certs --host=roach-0 \
 --execute="CREATE USER roach WITH PASSWORD 'roach';"

docker compose exec roach-0 \
 /cockroach/cockroach sql \
 --certs-dir=/certs --host=roach-0 \
 --execute="GRANT ADMIN TO roach;"

The second command is there to make sure we can access the DB Console. Note, the DB console does not have support for SPNEGO and won't work with Kerberos authentication today, at least in version(s) 21.1 and below.

You may skip these steps if you're using my environment as I included both of these commands in my helper script .up.sh.

We can also verify that this user is not available in KDC by connecting to the KDC container, accessing the kadmin.local database and querying it for the principal in question.

docker exec -ti kdc sh
/ # kadmin.local
Authenticating as principal root/admin@EXAMPLE.COM with password.
kadmin.local:  list_principals
K/M@EXAMPLE.COM
customspn/lb@EXAMPLE.COM
kadmin/admin@EXAMPLE.COM
kadmin/changepw@EXAMPLE.COM
kadmin/localhost@EXAMPLE.COM
kiprop/localhost@EXAMPLE.COM
krbtgt/EXAMPLE.COM@EXAMPLE.COM
postgres/lb@EXAMPLE.COM
tester@EXAMPLE.COM
kadmin.local:

As a reminder, I am looking for a user roach, which would've been listed as something like roach@EXAMPLE.COM.

I'm going to verify root user still has cert access.

docker exec -ti client cockroach sql --certs-dir=/certs --host=lb --user=root

#
# Welcome to the CockroachDB SQL shell.
# All statements must be terminated by a semicolon.
# To exit, type: \q.
#
# Server version: CockroachDB CCL v21.1.2 (x86_64-unknown-linux-gnu, built 2021/06/07 18:09:50, go1.15.11) (same version as client)
# Cluster ID: 60abe6e4-c017-4306-8d3f-184ff6a0ba97
# Organization: Cockroach Labs - Production Testing
#
# Enter \? for a brief introduction.
#
root@lb:26257/defaultdb>

The roach user is prevented from access because it does not exist in KDC.

docker exec -ti client cockroach sql --certs-dir=/certs --host=lb --user=roach

#
# Welcome to the CockroachDB SQL shell.
# All statements must be terminated by a semicolon.
# To exit, type: \q.
#
ERROR: requested user is roach, but GSSAPI auth is for tester
Failed running "sql"

Let's create client certs for user roach, configure a custom HBA config and verify.

If you chose to use my repo to follow along, I already took care of the client cert for roach. For posterity, this is how you would generate a cert using a native binary.

cockroach cert create-client roach --certs-dir=/tmp/certs/client/ --ca-key=/tmp/safe/ca.key --also-generate-pkcs8-key

What's left now is to configure a custom HBA config.

The configuration for that will look like so:

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

What this says is allow user roach connect using cert authentication across all databases in the cluster. If you want to learn more about HBA config in CockroachDB, take a look at a wonderful in-depth blog on the topic by one of our engineers.

Verify

Let's connect as user roach again and verify

docker exec -ti client cockroach sql --certs-dir=/certs --host=lb --user=roach

#
# Welcome to the CockroachDB SQL shell.
# All statements must be terminated by a semicolon.
# To exit, type: \q.
#
# Server version: CockroachDB CCL v21.1.2 (x86_64-unknown-linux-gnu, built 2021/06/07 18:09:50, go1.15.11) (same version as client)
# Cluster ID: cc3fc31e-ac56-49fc-8f12-6f8b591a87a3
# Organization: Cockroach Labs - Production Testing
#
# Enter \? for a brief introduction.
#
roach@lb:26257/defaultdb>

We are in! Let's confirm GSS is still enabled on the cluster, let's use the same user tester that exists in KDC.

docker exec -it client cockroach sql \                     --certs-dir=/certs --url "postgresql://tester:nopassword@lb:26257/defaultdb?sslmode=verify-full&sslrootcert=/certs/ca.crt"

#
# Welcome to the CockroachDB SQL shell.
# All statements must be terminated by a semicolon.
# To exit, type: \q.
#
ERROR: no server.host_based_authentication.configuration entry for host "172.28.0.6", user "tester"
Failed running "sql"

What did just happen? We inadvertently overwrote the GSS config. Let's fix that.

Login as root again and change the HBA config to the following:

SET cluster setting server.host_based_authentication.configuration = 'host all all all gss include_realm=0
host all roach all cert';

It is not immediately obvious but what needs to happen is to include all of the existing along with new configs separated by a new line. We can verify that with the following command.

SHOW cluster setting server.host_based_authentication.configuration;

  server.host_based_authentication.configuration
--------------------------------------------------
  host all roach all cert
  host all all all gss include_realm=0
(1 row)

Let's connect again as roach cert user:

docker exec -ti client cockroach sql --certs-dir=/certs --host=lb --user=roach
## Welcome to the CockroachDB SQL shell.
# All statements must be terminated by a semicolon.
# To exit, type: \q.
#
# Server version: CockroachDB CCL v21.1.2 (x86_64-unknown-linux-gnu, built 2021/06/07 18:09:50, go1.15.11) (same version as client)
# Cluster ID: cc3fc31e-ac56-49fc-8f12-6f8b591a87a3
# Organization: Cockroach Labs - Production Testing
#
# Enter \? for a brief introduction.
#
roach@lb:26257/defaultdb>

All good, let's connect as tester, our KDC user:

docker exec -it client cockroach sql \
 --certs-dir=/certs --url  "postgresql://tester:nopassword@lb:26257/defaultdb?sslmode=verify-full&sslrootcert=/certs/ca.crt"                     
#
# Welcome to the CockroachDB SQL shell.
# All statements must be terminated by a semicolon.
# To exit, type: \q.
#
# Server version: CockroachDB CCL v21.1.2 (x86_64-unknown-linux-gnu, built 2021/06/07 18:09:50, go1.15.11) (same version as client)
# Cluster ID: cc3fc31e-ac56-49fc-8f12-6f8b591a87a3
# Organization: Cockroach Labs - Production Testing
#
# Enter \? for a brief introduction.
#
tester@lb:26257/defaultdb>

Originally, only one line existed in HBA, now we have two rules separated by a new line. Another thing that is not obvious is that the rule order matters. If you want everyone but roach user to rely on gss, add the rule for roach user first. If you mistakenly reverse the order, the roach user will not have access to the cluster because all users are required to login with gss.

  server.host_based_authentication.configuration
--------------------------------------------------
  host all all all gss include_realm=0
  host all roach all cert
(1 row)

So the proper hierarchy is your custom auth method user(s) and then gss.

  server.host_based_authentication.configuration
--------------------------------------------------
  host all roach all cert
  host all all all gss include_realm=0
(1 row)

The rules are evaluated top to bottom and as soon as the first rule matching the condition is met, the rule evaluation is stopped.

Clean Up

That's all for today and I hope this was valuable. I certainly learned something new, as always, appreciate your feedback.

You can clean up the environment using my helper script below:

./down.sh