How-To: CockroachDB AuthN with Kerberos
Tags: cockroachdb, kerberos, authentication, tutorial
Published: 2022-02-04 13:58:29

CockroachDB supports user AuthN using the following methods:

- password
- cert (TLS client cert)
- cert-password (either cert or password)
- trusted (always authenticate)
- reject (always reject)
- gss (Use GSSAPI e.g. Kerberos)

By default every user can authenticate using either the cert or password methods.

In this brief blog, we outline the high level steps required to configure Kerberos AuthN in place of cert/password AuthN.

## 1 - Create Kerberos SPN and Keytab

In most organizations, you might have to find and ask another department to create the SPN and its keytab on your behalf.

Below the command to create the SPN, assuming we've access to the KDC server.
The keytab default location is `/etc/krb5.keytab`.

In our case, we add a Principal for the LB address only: we are permitting connection to the cluster only via the LB, that is, you can't point to a CockroachDB node directly.

This is to avoid endusers hardcoding a connection to a single node.
With this implementation, we ensure endusers go to the load-balancer for connection, which is the intended behavior.

Please note: user `root` bypasses Kerberos AuthN so it can connect from any node as long as the root key+crt are available.

```bash
# create principal named 'cockroach'
kadmin.local addprinc -pw <password> cockroach/<load-balancer-IP>@<YOUR.REALM>
kadmin.local addprinc -pw <password> cockroach/<load-balancer-hostname>@<YOUR.REALM>

# create the principal keytab at /etc/krb5.keytab
kadmin.local ktadd cockroach/<load-balancer-IP>@<YOUR.REALM>
kadmin.local ktadd cockroach/<load-balancer-hostname>@<YOUR.REALM>
```

Each CockroachDB _User_ must have an **equally named** _Principal_.

```bash
# Principal and DB username is 'fabio'
kadmin.local addprinc -pw <password> fabio@<YOUR.REALM>
```

## 2 - Configure keytab on CockroachDB nodes

Download keytab file `etc/krb5.keytab` locally, then upload it to every CockroachDB cluster node.

The keytab file should be located inside the CockroachDB config directory, by default is `/var/lib/cockroach`.

It is customary to rename the file to `cockroach.keytab`.

Also, we assume a Linux user `cockroach` was created for running CockroachDB.

```bash
# assume krb5.keytab is available in the current directory
sudo mv krb5.keytab /var/lib/cockroach/cockroach.keytab

# ensure permissions and ownership are set correctly
sudo chmod 644 /var/lib/cockroach/cockroach.keytab
sudo chown cockroach:cockroach /var/lib/cockroach/cockroach.keytab
```

CockroachDB will look for this file by querying the environment variable `KRB5_KTNAME`.

You make this env var available to CockroachDB via the [`Environment` parameter](https://www.freedesktop.org/software/systemd/man/systemd.exec.html#Environment) in **systemd** unit file.

Here's an example systemd unit file, notice the `Environment` parameter.

```text
[Unit]
Description=Cockroach Database cluster node
Requires=network.target
[Service]
Type=notify
WorkingDirectory=/var/lib/cockroach

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

ExecStart=/usr/local/bin/cockroach start \
    --certs-dir=/var/lib/cockroach/certs \
    --store=/mnt/cockroach \
    --listen-addr=0.0.0.0:26257 \
    --advertise-addr=host1:26257 \
    --cache=.25 \
    --max-sql-memory=.25 \
    --http-addr=0.0.0.0:8080 \
    --join=host1,host2,host3 \
    --locality=region=us-east4,zone=a
TimeoutStopSec=300
LimitNOFILE=65000
Restart=always
RestartSec=10
StandardOutput=syslog
StandardError=syslog
SyslogIdentifier=cockroach
User=cockroach
[Install]
WantedBy=default.target
```

Reload systemd service files, and restart each CockroachDB node

```bash
# repeat for each CockroachDB node
systemctl daemon-reload
systemctl restart cockroachdb
```

## 3 - Configure CockroachDB for Kerberos AuthN

As an admin user, login into CockroachDB and enter these commands at the SQL prompt.

Please note, you need an **enterprise license** for this feature.

```sql
-- enable Kerberos AuthN for all users from all hosts
SET cluster setting server.host_based_authentication.configuration = 'host all all all gss include_realm=0';

-- if not present already, create user 'fabio'
-- the password is only used for logging into the DB Console.
-- The password doesn't have to match the 
-- password you gave for the Kerberos Principal, they
-- are not related.
CREATE USER fabio WITH password 'cockroach';

-- apply required grants to the user
GRANT admin TO fabio;
```

More info on the cluster setting value is available with great details [here](https://dr-knz.net/authentication-in-postgresql-and-cockroachdb.html).

## 4 - Test

Login into the app server or any other server setup so that you can `kinit` (or equivalent in your favorite programming language).

```bash
# get the kerberos ticket
kinit fabio
```

You can use `klist` to verify the ticket is valid.
Now you're ready to connect to the database.

```bash
cockroach sql --url "postgresql://fabio@<load-balancer-hostname>:26257/defaultdb?sslmode=verify-full&sslrootcert=ca.crt&krbsrvname=cockroach"
```

`krbsrvname=cockroach` this is where you tell CockroachDB what is the SPN user you created in step 1.

To confirm, destroy the ticket and re-attempt connection.
It should fail AuthN of the user.

```bash
# destroy all kerberos tickets
kdestroy

# there's no ticket, AuthN will fail
cockroach sql --url "postgresql://fabio@<load-balancer-hostname>:26257/defaultdb?sslmode=verify-full&sslrootcert=ca.crt&krbsrvname=cockroach"
```

## References

- [CockroachDB](https://www.cockroachlabs.com/product/)
- [Security Overview](https://www.cockroachlabs.com/docs/stable/security-overview.html)
- [GSSAPI Kerberos AuthN](https://www.cockroachlabs.com/docs/stable/gssapi_authentication.html)
- [Ansible Collection](https://github.com/fabiog1901/cockroachdb-collection)

Helpful blogs:

- [Artem Ervits blog series on CockroachDB and Kerberos](https://blog.ervits.com/2020/05/three-headed-dog-meet-cockroach.html)
- [Deploy a Kerberized CockroachDB cluster using Ansible](https://dev.to/cockroachlabs/deploy-cockroachdb-on-the-public-cloud-using-ansible-1ek1)