CockroachDB With MIT Kerberos Using Native Client

Articles Covering CockroachDB and Kerberos

I find the topic of Kerberos very interesting and my colleagues commonly refer to me for help with this topic complex. 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

Up to the 20.1 release, CockroachDB was missing a native GSSAPI client to interact with Kerberos-enabled clusters. Users were forced to use tools like psql to connect. As of the 20.2 release, our engineering had built GSSAPI support directly into the cockroach binary and this is what I’m going to demonstrate today. The immediate benefit here is that we can keep our surface area small and “in-house”. Users no longer need to install any third-party client to leverage Kerberos with CockroachDB.

For today’s setup, I have a multi-node CockroachDB cluster, a load balancer, Keberos Distribution Center, and a client container running the same software as my CockroachDB containers plus krb5-user package to make Kerberos possible.

Clone the Repo

git clone https://github.com/dbist/cockroach-docker
cd cockroach-docker/cockroach-gssapi-multinode

Start the Application

You should see output similar to this:

Creating roach-cert ... done
Creating kdc        ... done
Creating roach-0    ... done
Creating roach-1    ... done
Creating roach-2    ... done
Creating lb         ... done
Creating client     ... done
Cluster successfully initialized
CREATE ROLE

Time: 0.006s

GRANT

Time: 0.031s

SET CLUSTER SETTING

Time: 0.009s

SET CLUSTER SETTING

Time: 0.012s

SET CLUSTER SETTING

Time: 0.017s

SET CLUSTER SETTING

Time: 0.009s

Check the Status of the Application

   Name                 Command               State                                         Ports
----------------------------------------------------------------------------------------------------------------------------------------
client       /start.sh                        Up      26257/tcp, 8080/tcp
kdc          /start.sh                        Up
lb           /docker-entrypoint.sh hapr ...   Up      0.0.0.0:26257->26257/tcp, 5432/tcp, 0.0.0.0:8080->8080/tcp, 0.0.0.0:8081->8081/tcp
roach-0      /cockroach/cockroach.sh st ...   Up      26257/tcp, 8080/tcp
roach-1      /cockroach/cockroach.sh st ...   Up      26257/tcp, 8080/tcp
roach-2      /cockroach/cockroach.sh st ...   Up      26257/tcp, 8080/tcp
roach-cert   /bin/sh -c tail -f /dev/null     Up

Connect to the Client

As the first step, let’s confirm our Kerberos ticket cache is empty and if not, empty it:

docker exec -it client bash

root@client:/cockroach# kdestroy
root@client:/cockroach# klist
klist: No credentials cache found (filename: /tmp/krb5cc_0)

Now using a valid user in Kerberos testerlet’s connect to the cockroach client:

cockroach sql --certs-dir=/certs --host=lb --user=tester

#
# Welcome to the CockroachDB SQL shell.
# All statements must be terminated by a semicolon.
# To exit, type: q.
#
ERROR: pq: kerberos error: open /tmp/krb5cc_0: no such file or directory
Failed running "sql"

Let’s kinit as a tester and try again:

root@client:/cockroach# kinit tester
Password for tester@EXAMPLE.COM:

#
# Welcome to the CockroachDB SQL shell.
# All statements must be terminated by a semicolon.
# To exit, type: q.
#
# Server version: CockroachDB CCL v20.2.0-beta.3 (x86_64-unknown-linux-gnu, built 2020/09/30 15:28:26, go1.13.14) (same version as client)
# Cluster ID: 5a82619c-de6e-41ec-981d-fee49ccbb817
# Organization: Cockroach Labs - Production Testing
#
# Enter ? for a brief introduction.
#
tester@lb:26257/defaultdb>

Let’s trace the Kerberos connection just for the matter of fact, note, it will prompt for the password:

docker exec -it client bash
KRB5_TRACE=/dev/stdout kinit -V

Using principal: tester@EXAMPLE.COM
[383] 1601565470.993071: Getting initial credentials for tester@EXAMPLE.COM
[383] 1601565470.993228: Sending request (191 bytes) to EXAMPLE.COM
[383] 1601565470.993279: Resolving hostname kdc
[383] 1601565470.994352: Sending initial UDP request to dgram 172.28.1.3:88
[383] 1601565470.995122: Received answer (749 bytes) from dgram 172.28.1.3:88
[383] 1601565470.995167: Response was not from master KDC
[383] 1601565470.995229: Processing preauth types: 19
[383] 1601565470.995266: Selected etype info: etype aes256-cts, salt "EXAMPLE.COMtester", params ""
[383] 1601565470.995273: Produced preauth for next request: (empty)
[383] 1601565470.995277: Getting AS key, salt "EXAMPLE.COMtester", params ""
Password for tester@EXAMPLE.COM:
[383] 1601565574.73615: AS key obtained from gak_fct: aes256-cts/CD86
[383] 1601565574.73704: Decrypted AS reply; session key is: aes256-cts/459F
[383] 1601565574.73739: FAST negotiation: available
[383] 1601565574.73757: Initializing FILE:/tmp/krb5cc_0 with default princ tester@EXAMPLE.COM
[383] 1601565574.73928: Storing tester@EXAMPLE.COM -> krbtgt/EXAMPLE.COM@EXAMPLE.COM in FILE:/tmp/krb5cc_0
[383] 1601565574.74026: Storing config in FILE:/tmp/krb5cc_0 for krbtgt/EXAMPLE.COM@EXAMPLE.COM: fast_avail: yes
[383] 1601565574.74097: Storing tester@EXAMPLE.COM -> krb5_ccache_conf_data/fast_avail/krbtgt/EXAMPLE.COM@EXAMPLE.COM@X-CACHECONF: in FILE:/tmp/krb5cc_0
Authenticated to Kerberos v5

This basically tells us Kerberos authentication with KDC works but it does not confirm authentication to CockroachDB does indeed work. 20.1 introduced Authentication Logs, by enabling this property, we can diagnose the authentication on the CockroachDB level.

Let’s connect as root as this property requires admin privileges, I did not make tester an admin user and root is the only other user I have in the database.

cockroach sql --certs-dir=/certs --host=lb

SET CLUSTER SETTING server.auth_log.sql_connections.enabled = true;
SET CLUSTER SETTING server.auth_log.sql_sessions.enabled = true;

SET CLUSTER SETTING

Time: 0.025s total (exec 97.1% / net 2.2% / other 0.7%)

Now we can navigate to one of the node’s logs directories and inspect the cockroach-auth logfile.

grep tester /cockroach/cockroach-data/logs/cockroach-auth.log

I201001 15:38:25.393199 113334 sql/pgwire/auth.go:335 ⋮ [n1,client=‹172.28.1.7:36718›,hostssl,user=‹tester›] 5 connection matches HBA rule: ‹host all all all gss include_realm=0›
I201001 15:38:25.399242 113334 sql/pgwire/auth.go:335 ⋮ [n1,client=‹172.28.1.7:36718›,hostssl,user=‹tester›] 6 authentication succeeded
I201001 15:39:30.301827 113333 sql/pgwire/conn.go:229 ⋮ [n1,client=‹172.28.1.7:36718›,hostssl,user=‹tester›] 7 session terminated; duration: 1m4.911381401s

If you don’t see the message, try to login to Cockroach using the tester user again.

Connect to the Client Using –url Flag

In case your organizational policy mandates that SPN does not default to postgresCockroachDB supports the krbsrvname argument modeled after the same scenario in Postgresql. Unfortunately, there’s no way to pass the argument without using --url flag.

Assuming you exited out of the container, command to connect using the --url argument will look like so:

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

In a separate window, inspect the roach-0 logs to make sure authentication is still successful:

I201001 15:51:16.036170 139125 sql/pgwire/auth.go:335 ⋮ [n1,client=‹172.28.1.7:41378›,hostssl,user=‹tester›] 10 connection matches HBA rule: ‹host all all all gss include_realm=0›
I201001 15:51:16.042798 139125 sql/pgwire/auth.go:335 ⋮ [n1,client=‹172.28.1.7:41378›,hostssl,user=‹tester›] 11 authentication succeeded

Now let’s modify the command to include the krbsrvname argument:

cockroach sql 
 --certs-dir=/certs --url  "postgresql://tester:nopassword@lb:26257/defaultdb?sslmode=verify-full&sslrootcert=/certs/ca.crt&krbsrvname=customspn"

I did not see anything different in the authentication logs with the addition of krbsrvname.

To confirm the krbsrvname parameter takes effect, let’s pass a dummy value:

cockroach sql 
 --certs-dir=/certs --url  "postgresql://tester:nopassword@lb:26257/defaultdb?sslmode=verify-full&sslrootcert=/certs/ca.crt&krbsrvname=dummy"

Flag --insecure has been deprecated, it will be removed in a subsequent release.
For details, see: https://go.crdb.dev/issue-v/53404/v20.2
#
# Welcome to the CockroachDB SQL shell.
# All statements must be terminated by a semicolon.
# To exit, type: q.
#
ERROR: pq: failed to get Kerberos ticket: "kerberos error (InitSecContext): [Root cause: KDC_Error] KDC_Error: TGS Exchange Error: kerberos error response from KDC when requesting for dummy/lb: KRB Error: (7) KDC_ERR_S_PRINCIPAL_UNKNOWN Server not found in Kerberos database - LOOKING_UP_SERVER"
Failed running "sql"

Back in the authentication log:

I201001 15:54:48.026974 146168 sql/pgwire/auth.go:335 ⋮ [n1,client=‹172.28.1.7:42662›,hostssl,user=‹tester›] 15 connection matches HBA rule: ‹host all all all gss include_realm=0›
I201001 15:54:48.032162 146168 sql/pgwire/auth.go:335 ⋮ [n1,client=‹172.28.1.7:42662›,hostssl,user=‹tester›] 16 authentication failed: client didn't send required auth data
I201001 15:54:48.033291 146167 sql/pgwire/conn.go:229 ⋮ [n1,client=‹172.28.1.7:42662›,hostssl,user=‹tester›] 17 session terminated; duration: 9.444174ms

And this is a short overview of the new authN capabilities in CockroachDB. At this point, we can ditch our trusty psql client and use the native binaries. This reduces the attack surface and lowers the operational costs of your deployment. The general release of 20.2 will be shipping soon!

If you find this tutorial useful, drop me a note or leave me feedback in the comments.

.

Leave a Comment