CockroachDB With Kerberos and Custom Service Principal Name (SPN)

Articles Covering CockroachDB and Kerberos

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

In our GSSAPI docs, we recommend creating a Service Principal Name (SPN) with a name postgres. This is not a hard requirement and just happens to be a convention. This usually trips people up and causes confusion. Today, I'm going to provide a short overview of how to override the postgres in the SPN with a custom name. 

Typically, postgres is a legacy requirement and typically, customers that want to override this convention will do so for compliance reasons, sometimes it's to be consistent with their domain hierarchies. So we typically see naming conventions like cockroachsvc or cockroachspn used instead of postgres.

We will need an instance of CockroachDB, Kerberos, and a GSSAPI compatible client like psql. Luckily, I have access to my trusty docker-compose environment where we can demo this.

1. Copy the cockroach-gssapi directory to your machine.

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

2. Run ./up.sh script to start the environment.

./up.sh
cockroach uses an image, skipping
Building roach-cert
Step 1/15 : FROM cockroachdb/cockroach:v20.1.3 AS generator
 ---> 25bee4f016c4
...
Creating roach-cert ... done
Creating kdc        ... done
Creating cockroach  ... done
Creating psql       ... done
CREATE ROLE

Time: 8.8429ms

GRANT

Time: 7.2032ms

SET CLUSTER SETTING

Time: 12.1494ms

SET CLUSTER SETTING

Time: 9.583ms

SET CLUSTER SETTING

Time: 8.3226ms

SET CLUSTER SETTING

Time: 8.1053ms

3. Check to make sure all containers are up.

docker-compose ps

   Name                 Command               State                Ports
--------------------------------------------------------------------------------------
cockroach    /cockroach/cockroach.sh st ...   Up      0.0.0.0:26257->26257/tcp,
                                                      0.0.0.0:8080->8080/tcp
kdc          /start.sh                        Up
psql         /start.sh                        Up      5432/tcp
roach-cert   /bin/sh -c tail -f /dev/null     Up

4. Connect to the kdc container to add a new SPN.

docker exec -it kdc bin/sh
kadmin.local

We are going to add an SPN called custom with a password of your choosing.

Authenticating as principal root/admin@EXAMPLE.COM with password.
kadmin.local:  add_principal custom/cockroach@EXAMPLE.COM
No policy specified for custom/cockroach@EXAMPLE.COM; defaulting to no policy
Enter password for principal "custom/cockroach@EXAMPLE.COM":
Re-enter password for principal "custom/cockroach@EXAMPLE.COM":
Principal "custom/cockroach@EXAMPLE.COM" created.
exit

5. Generate a keytab entry for the new SPN.

Still in the kdc container, issue the following command to generate a keytab entry for the newly created SPN.

kadmin.local -q "ktadd -k /keytab/crdb.keytab custom/cockroach@EXAMPLE.COM"

Authenticating as principal root/admin@EXAMPLE.COM with password.
Entry for principal custom/cockroach@EXAMPLE.COM with kvno 2, encryption type aes256-cts-hmac-sha1-96 added to keytab WRFILE:/keytab/crdb.keytab.
Entry for principal custom/cockroach@EXAMPLE.COM with kvno 2, encryption type aes128-cts-hmac-sha1-96 added to keytab WRFILE:/keytab/crdb.keytab.

6. Inspect the keytab file.

We will need access to ktutil application to validate our keytab is populated with the new SPN. I have it installed on our client container, which happens to be psql. We're going to connect to that container and execute the next step. ktutil is available by installing krb-user package on an Ubuntu derivative machine.

docker exec -it psql bin/sh
ktutil

Our keytab is mounted into the /keytab volume in our docker-compose file so we can access it and append it.

ktutil:  read_kt /keytab/crdb.keytab
ktutil:  list
slot KVNO Principal
---- ---- ---------------------------------------------------------------------
   1    2           postgres/cockroach@EXAMPLE.COM
   2    2           postgres/cockroach@EXAMPLE.COM
   3    2          customspn/cockroach@EXAMPLE.COM
   4    2          customspn/cockroach@EXAMPLE.COM
   5    2             custom/cockroach@EXAMPLE.COM
   6    2             custom/cockroach@EXAMPLE.COM
ktutil: exit

I have a couple of SPNs already there but the one we care about is named custom/cockroach@EXAMPLE.COM.

7. Finally, we can use the psql client to connect to CockroachDB with our newly created SPN.

By default, we expect SPN to be named postgres. A connection string leveraging GSSAPI will look like so:

psql "postgresql://cockroach:26257/defaultdb?sslmode=require" -U tester

To override the SPN, we have to take advantage of a property called krbsrvname, which is documented in the Postgres documentation. So adding this property will change our connection string to look like so:

psql "postgresql://cockroach:26257/defaultdb?sslmode=require&krbsrvname=custom" -U tester

psql (9.5.22, server 9.5.0)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES128-GCM-SHA256, bits: 128, compression: off)
Type "help" for help.

defaultdb=>

We can also pass the property in the following way:

psql "host=cockroach port=26257 sslmode=require user=tester krbsrvname=custom"

psql (9.5.22, server 9.5.0)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES128-GCM-SHA256, bits: 128, compression: off)
Type "help" for help.

tester=>

And just so that you're convinced we're authenticating with Kerberos and property takes effect, let's try to pass a dummy SPN.

psql "host=cockroach port=26257 sslmode=require user=tester krbsrvname=dummy"

psql: GSSAPI continuation error: Unspecified GSS failure.  Minor code may provide more information
GSSAPI continuation error: Server dummy/cockroach@EXAMPLE.COM not found in Kerberos database

Disclaimer

Using sslmode=require is susceptible to Man In The Middle (MITM) attack and it is highly encouraged to use verify-ca or verify-full.

verify-ca is not susceptible to MITM attack by verifying the server is trustworthy with the certificate authority.

psql "postgresql://cockroach:26257/defaultdb?sslmode=verify-ca&sslrootcert=/certs/ca.crt" -U tester
psql "host=cockroach port=26257 sslmode=verify-ca user=tester krbsrvname=customspn sslrootcert=/certs/ca.crt"

verify-full will validate the server is trustworthy with CA as well as validate the Common Name attribute of the certificate matches the hostname.

psql "postgresql://cockroach:26257/defaultdb?sslmode=verify-full&sslrootcert=/certs/ca.crt" -U tester
psql "host=cockroach port=26257 sslmode=verify-full user=tester krbsrvname=customspn sslrootcert=/certs/ca.crt"

For more information, please read the following doc.

This is all for today and I hope you find this tutorial useful. Feel free to shoot me an email with more demo ideas for CockroachDB and Kerberos.