CockroachDB With Kerberos and Custom Service Principal Name (SPN)
This is another installment of CockroachDB and Kerberos coverage. Today, I'm going to demonstrate a way to override a 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:
- CockroachDB With MIT Kerberos
- CockroachDB With Active Directory
- CockroachDB With MIT Kerberos and Docker Compose
- Executing CockroachDB table import via GSSAPI
- CockroachDB With SQLAlchemy and MIT Kerberos
- CockroachDB With MIT Kerberos Cert User Authentication
- 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.
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
cockroachspn used instead of
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.
- Copy the
cockroach-gssapidirectory to your machine.
git clone https://github.com/dbist/cockroach-docker cd cockroach-docker/cockroach-gssapi/
./up.shscript 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
- Check to make sure all containers are up.
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
- Connect to the
kdccontainer 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
- 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.
- 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
- Finally, we can use the
psqlclient 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
sslmode=require is susceptible to Man In The Middle (MITM) attack and it is highly encouraged to use
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.