CockroachDB With GSSAPI Deployed via Systemd

Articles Covering Topics on 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
  10. CockroachDB With MIT Kerberos Using a Native Client
  11. CockroachDB With Mixed Kerberos and Certificates Authentication

Motivation

Systemd has become a standard approach for deploying Linux services. We have documentation to deploy CockroachDB via systemd, however, we do not have steps documented to deploy CockroachDB with GSSAPI and systemd. This tutorial attempts to bridge that gap.

Generally, all of the steps should be similar to my previous articles, specifically the very first article in the series. I also had to refer to my article on using the native CockroachDB binary to access a cluster with GSSAPI as we’ve made that possible since I’d written the first article. And yes, I blog to refer to my articles like documentation, by I digress.

The only thing that will change is starting the service via a systemd config. GSSAPI in CockroachDB relies on an environment variable KRB5_KTNAME that is not available to a process started by systemd. In systemd, we have to add the environment variable to the systemd config. We have a docs issue outstanding to document this, thank you Fabio for doing your research!

High-Level Steps

  • Create a KDC and populate with principals
  • Install CockroachDB
  • Generate certs for a secure installation
  • Start CockroachDB via systemd
  • Confirm KRB5_KTNAME environment variable is read in the process
  • Access CockroachDB as a KDC user to verify GSSAPI works

Step by Step Instructions

Populate KDC With a List of Principals

sudo kadmin.local
kadmin.local:  
  addprinc postgres/node.example.com@EXAMPLE.COM
  addprinc postgres/node@EXAMPLE.COM
  addprinc postgres/127.0.0.1@EXAMPLE.COM
  addprinc postgres/localhost@EXAMPLE.COM
  addprinc carl@EXAMPLE.COM

Still, in the kadmin interface, dump the principals to a keytab:

  ktadd -k keytab postgres/127.0.0.1@EXAMPLE.COM
  ktadd -k keytab postgres/node.example.com@EXAMPLE.COM
  ktadd -k keytab postgres/node@EXAMPLE.COM
  ktadd -k keytab postgres/localhost@EXAMPLE.COM

Verify the Keytab is Populated Correctly

[root@node ~]# ktutil
ktutil:  read_kt keytab
ktutil:  list
slot KVNO Principal
---- ---- ---------------------------------------------------------------------
   1    2           postgres/127.0.0.1@EXAMPLE.COM
   2    2           postgres/127.0.0.1@EXAMPLE.COM
   3    2           postgres/127.0.0.1@EXAMPLE.COM
   4    2           postgres/127.0.0.1@EXAMPLE.COM
   5    2           postgres/127.0.0.1@EXAMPLE.COM
   6    2           postgres/127.0.0.1@EXAMPLE.COM
   7    2           postgres/127.0.0.1@EXAMPLE.COM
   8    2           postgres/127.0.0.1@EXAMPLE.COM
   9    2    postgres/node.example.com@EXAMPLE.COM
  10    2    postgres/node.example.com@EXAMPLE.COM
  11    2    postgres/node.example.com@EXAMPLE.COM
  12    2    postgres/node.example.com@EXAMPLE.COM
  13    2    postgres/node.example.com@EXAMPLE.COM
  14    2    postgres/node.example.com@EXAMPLE.COM
  15    2    postgres/node.example.com@EXAMPLE.COM
  16    2    postgres/node.example.com@EXAMPLE.COM
  17    2                postgres/node@EXAMPLE.COM
  18    2                postgres/node@EXAMPLE.COM
  19    2                postgres/node@EXAMPLE.COM
  20    2                postgres/node@EXAMPLE.COM
  21    2                postgres/node@EXAMPLE.COM
  22    2                postgres/node@EXAMPLE.COM
  23    2                postgres/node@EXAMPLE.COM
  24    2                postgres/node@EXAMPLE.COM
  25    2           postgres/localhost@EXAMPLE.COM
  26    2           postgres/localhost@EXAMPLE.COM
  27    2           postgres/localhost@EXAMPLE.COM
  28    2           postgres/localhost@EXAMPLE.COM
  29    2           postgres/localhost@EXAMPLE.COM
  30    2           postgres/localhost@EXAMPLE.COM
  31    2           postgres/localhost@EXAMPLE.COM
  32    2           postgres/localhost@EXAMPLE.COM

Install CockroachDB

COCKROACH_VERSION=v21.1.5
wget -qO- https://binaries.cockroachdb.com/cockroach-$COCKROACH_VERSION.linux-amd64.tgz | tar  xvz
sudo cp -i cockroach-$COCKROACH_VERSION.linux-amd64/cockroach /usr/local/bin/
cockroach version

Create CockroachDB Certs

HOSTNAME="localhost node.example.com node 127.0.0.1"
mkdir certs my-safe-directory
cockroach cert create-ca --certs-dir=certs --ca-key=my-safe-directory/ca.key
cockroach cert create-node $HOSTNAME --certs-dir=certs --ca-key=my-safe-directory/ca.key
openssl x509 -in certs/node.crt -text | grep "Subject Alternative Name" -A 1
cockroach cert create-client root --certs-dir=certs --ca-key=my-safe-directory/ca.key

Start Node(s) Using Systemd

I am working on this tutorial with a single VM and I’m going to defer to using a single node CockroachDB instance with systemd instead of spinning up three processes. Other than using start-single-node and skipping the initialization step, everything should be the same.

mkdir /var/lib/cockroach
useradd cockroach
mkdir /var/lib/cockroach/certs
mv certs/node.* /var/lib/cockroach/certs/
cp certs/ca.crt /var/lib/cockroach/certs/
mv keytab /var/lib/cockroach/
chown -R cockroach.cockroach /var/lib/cockroach

Create Systemd Config

[Unit]
Description=Cockroach Database cluster node
Requires=network.target
[Service]
Type=notify
WorkingDirectory=/var/lib/cockroach
Environment="KRB5_KTNAME=/var/lib/cockroach/keytab"
ExecStart=/usr/local/bin/cockroach start-single-node --certs-dir=certs --advertise-addr=node.example.com:26257 --cache=.25 --max-sql-memory=.25
TimeoutStopSec=60
Restart=always
RestartSec=10
StandardOutput=syslog
StandardError=syslog
SyslogIdentifier=cockroach
User=cockroach
[Install]
WantedBy=default.target

Save as /etc/systemd/system/securecockroachdb.service.

Notice the Environment property, that’s how we override the environment variable from our docs.

Environment="KRB5_KTNAME=/var/lib/cockroach/keytab"

Start CockroachDB via Systemd

systemctl start securecockroachdb

Confirm the KRB5_KTNAME environment variable is set by first getting the PID:

  PID TTY      STAT   TIME COMMAND
 5573 ?        Rsl    0:07 /usr/local/bin/cockroach start-single-node --certs-dir=certs --advertise-addr=node.example.com:26257 --cache=.25 --max-sql-memory=.25 LANG=en_US.UTF-8 PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin NOTIFY_SOCKET=/run/systemd/notify HOME=/home/cockroach LOGNAME=cockroach USER=cockroach SHELL=/bin/bash KRB5_KTNAME=/var/lib/cockroach/keytab
[root@node ~]# ps aux | grep cockroach
cockroa+  5573  7.4  1.4 926204 114908 ?       Ssl  12:35   0:08 /usr/local/bin/cockroac start-single-node --certs-dir=certs --advertise-addr=node.example.com:26257 --cache=.25 --max-sql-memory=.25
root      5598  0.0  0.0  12528   972 pts/0    R+   12:37   0:00 grep --color=auto cockroach

View the In-Process Environment Variable

strings /proc/5573/environ

LANG=en_US.UTF-8
PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin
NOTIFY_SOCKET=/run/systemd/notify
HOME=/home/cockroach
LOGNAME=cockroach
USER=cockroach
SHELL=/bin/bash
KRB5_KTNAME=/var/lib/cockroach/keytab

There are a few more ways to get the same info:

cat /proc/5573/environ | tr '' 'n'
ps e -ww -p 5573

Enable Enterprise License for GSSAPI

cockroach sql --certs-dir=certs --host=node

SET CLUSTER SETTING cluster.organization = 'Acme Company';
SET CLUSTER SETTING enterprise.license="xxxxxxxxxxxx";

Enable GSSAPI

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

Add a SQL user named Carl:

CREATE USER carl;
GRANT ALL ON DATABASE defaultdb TO carl;

Verify GSSAPI Works

kinit as Carl:

Ticket cache: KEYRING:persistent:0:0
Default principal: carl@EXAMPLE.COM

Valid starting       Expires              Service principal
07/09/2021 12:45:31  07/10/2021 12:45:31  krbtgt/EXAMPLE.COM@EXAMPLE.COM

Connect to CockroachDB Using Carl as Username

/usr/local/bin/cockroach sql 
 --certs-dir=certs --host=node --url "postgresql://carl@node:26257/defaultdb?sslmode=verify-full&sslrootcert=certs/ca.crt"

[root@node ~]# /usr/local/bin/cockroach sql 
>  --certs-dir=certs --host=node --url "postgresql://carl@node: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.5 (x86_64-unknown-linux-gnu, built 2021/07/02 03:57:09, go1.15.11) (same version as client)
#
# Enter ? for a brief introduction.
#
carl@node:26257/defaultdb>

Verify GSSAPI Is Still Enabled

Connecting to CockroachDB again using root user cert:

cockroach sql --certs-dir=certs --host=node

root@node:26257/defaultdb> SHOW CLUSTER SETTING server.host_based_authentication.configuration;
  server.host_based_authentication.configuration
--------------------------------------------------
  host all all all gss include_realm=0
(1 row)

That’s it for today!

.

Leave a Comment