Skip to content

Instantly share code, notes, and snippets.

@dbist

dbist/cockroachdb-kerberos-sqlalchemy.md

Last active June 26, 2023 08:21
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save dbist/ea71cd39b8cd5b4250bad10622269e9a to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save dbist/ea71cd39b8cd5b4250bad10622269e9a to your computer and use it in GitHub Desktop.
Download ZIP
Raw
cockroachdb-kerberos-sqlalchemy.md

CockroachDB with SQLAlchemy and MIT Kerberos

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:

Today, I'm going to demonstrate how to leverage CockroachDB with MIT Kerberos and SqlAlchemy. We have a lot of customers using us for their Python database needs and you can view some of the options on our docs site. For today's setup, I have a multi-node CockroachDB cluster, a Django container called web, a load balancer container and a Kerberos kdc container. You can find the code for this example in my repo.

  1. Clone the repo
git clone https://github.com/dbist/cockroach-docker
cd cockroach-docker/cockroach-gssapi-sqlalchemy
  1. Start the application
./up.sh
Creating network "cockroach-gssapi-sqlalchemy_default" with the default driver
Creating network "cockroach-gssapi-sqlalchemy_roachnet" with the default driver
Creating volume "cockroach-gssapi-sqlalchemy_certs-roach-0" with default driver
Creating volume "cockroach-gssapi-sqlalchemy_certs-roach-1" with default driver
Creating volume "cockroach-gssapi-sqlalchemy_certs-roach-2" with default driver
Creating volume "cockroach-gssapi-sqlalchemy_keytab" with default driver
Creating volume "cockroach-gssapi-sqlalchemy_certs-client" with default driver
Creating roach-cert ... done
Creating kdc        ... done
Creating roach-0    ... done
Creating roach-1    ... done
Creating roach-2    ... done
Creating lb         ... done
Creating web        ... done
CREATE ROLE

Time: 8.6299ms

CREATE DATABASE

Time: 15.1892ms

GRANT

Time: 6.4917ms

SET CLUSTER SETTING

Time: 12.3533ms

SET CLUSTER SETTING

Time: 11.2168ms

SET CLUSTER SETTING

Time: 15.9956ms

SET CLUSTER SETTING

Time: 12.6019ms
  1. Check the logs
docker logs web
2020-08-17 14:29:32,949 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2020-08-17 14:29:32,949 INFO sqlalchemy.engine.base.Engine SAVEPOINT cockroach_restart
2020-08-17 14:29:32,950 INFO sqlalchemy.engine.base.Engine {}
2020-08-17 14:29:32,952 INFO sqlalchemy.engine.base.Engine SELECT accounts.id AS accounts_id, accounts.balance AS accounts_balance
FROM accounts
WHERE accounts.id = %(id_1)s
2020-08-17 14:29:32,952 INFO sqlalchemy.engine.base.Engine {'id_1': 95435663}
2020-08-17 14:29:32,955 INFO sqlalchemy.engine.base.Engine UPDATE accounts SET balance=%(balance)s WHERE accounts.id = %(accounts_id)s
2020-08-17 14:29:32,956 INFO sqlalchemy.engine.base.Engine {'balance': 484504, 'accounts_id': 95435663}
2020-08-17 14:29:32,958 INFO sqlalchemy.engine.base.Engine UPDATE accounts SET balance=(accounts.balance + %(balance_1)s) WHERE accounts.id = %(id_1)s
2020-08-17 14:29:32,959 INFO sqlalchemy.engine.base.Engine {'balance_1': 484503, 'id_1': 756738049}
2020-08-17 14:29:32,961 INFO sqlalchemy.engine.base.Engine RELEASE SAVEPOINT cockroach_restart
2020-08-17 14:29:32,961 INFO sqlalchemy.engine.base.Engine {}
2020-08-17 14:29:32,966 INFO sqlalchemy.engine.base.Engine COMMIT
  1. Check the status of the application
docker-compose ps
   Name                 Command               State                                         Ports
----------------------------------------------------------------------------------------------------------------------------------------
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
web          ./sqlalchemy/start.sh            Up      0.0.0.0:8000->8000/tcp
  1. Connect to CockroachDB and check whether accounts are populated
docker exec -it roach-0 sh
cockroach sql --certs-dir=/certs --host=lb
#
# Welcome to the CockroachDB SQL shell.
# All statements must be terminated by a semicolon.
# To exit, type: \q.
#
# Server version: CockroachDB CCL v20.1.4 (x86_64-unknown-linux-gnu, built 2020/07/29 22:56:36, go1.13.9) (same version as client)
# Cluster ID: 333acd7f-ec6e-4e47-9b92-4130c8aad13b
# Organization: Cockroach Labs - Production Testing
#
# Enter \? for a brief introduction.
#
root@roach-0:26257/defaultdb> select * from bank.accounts;
     id     | balance
------------+----------
   28585249 |  269455
   76361884 |  638333
...
  997258425 |  181144
(100 rows)

Time: 2.1635ms

root@roach-0:26257/defaultdb> \q

Let's walk through the finer details of this application.

This is a similar setup to my previous tutorials aside from a few changes. I have a new folder called sqlalchemy in my project tree

.
├── README.md
├── docker-compose.yml
├── down.sh
├── haproxy
│   ├── Dockerfile
│   └── haproxy.cfg
├── kdc
│   ├── Dockerfile
│   ├── krb5.conf
│   └── start.sh
├── prune.sh
├── roach-cert
│   ├── Dockerfile
│   └── README.md
├── sqlalchemy
│   ├── Dockerfile
│   ├── requirements.txt
│   ├── sqlalchemy-basic-sample.py
│   └── start.sh
├── up.sh
└── writeup.md

4 directories, 17 files

Our SQLAlchemy container is a standard python 3 image where I install krb5-user package. The Dockerfile is below.

Dockerfile

FROM python:3
ENV PYTHONUNBUFFERED 1

RUN apt-get update && \
  DEBIAN_FRONTEND=noninteractive apt-get install --yes --no-install-recommends \
  krb5-user \
  && rm -rf /var/lib/apt/lists/*

RUN mkdir /code
WORKDIR /code
COPY requirements.txt /code/
RUN python -m pip install --upgrade pip && \
  pip install -r requirements.txt
COPY . /code/
ENTRYPOINT ["./sqlalchemy/start.sh"]

I have a requirements file which we will look at next that I inject into the container and install with pip. I then copy the rest of the application code into the container and start the container with a shell script.

requirements.txt

psycopg2==2.8.*
sqlalchemy==1.3.*
sqlalchemy-cockroachdb==1.3.*

start.sh

#!/bin/sh

set -e

echo psql | kinit sqlalchemy@EXAMPLE.COM

env

sleep 10

python ./sqlalchemy/sqlalchemy-basic-sample.py

tail -f /dev/null

In the shell script, I kinit as sqlalchemy user, which happens to be an authorized user in our KDC. I then run a python script which is a slightly modified version of the code you can find CockroachDB tutorial site where I specify Kerberos-specific connection parameters. Specifically, we only care about our user, which we added to KDC and optionally a custom SPN unless you prefer the default postgres.

sqlalchemy-basic-sample.py

if secure_cluster:
    connect_args = {
        'sslmode': 'verify-full',
        'sslrootcert': '/certs/ca.crt',
        'user': 'sqlalchemy',
        'krbsrvname': 'customspn'
    }
else:
    connect_args = {'sslmode': 'disable'}

The only thing I changed in my KDC Dockerfile was the name of the principal

kadmin.local -q "addprinc -pw psql sqlalchemy@EXAMPLE.COM

I also kept most of the service definitions in my compose file to match the Django example from last week.

docker-compose.yaml

  web:
    container_name: web
    hostname: web
    build: sqlalchemy/.
    extra_hosts:
      - "lb:172.28.1.7"
    depends_on:
      - roach-cert
      - lb
      - kdc
      - roach-0
      - roach-1
      - roach-2
    volumes:
      - .:/code
      - ./kdc/krb5.conf:/etc/krb5.conf
      - certs-client:/certs
      - ./sqlalchemy/start.sh:/start.sh
      - keytab:/keytab
    ports:
      - "8000:8000"
    networks:
      roachnet:
        ipv4_address: 172.28.1.8

I removed the command argument as I control the deployment via the web Dockerfile. I inject the same krb5.conf into the web container as well.

We now have a fully kerborized SQLAlchemy deployment. Lastly, just to be absolutely certain that Keberos does indee work, let's modify the connection properties with a dummy SPN.

if secure_cluster:
    connect_args = {
        'sslmode': 'verify-full',
        'sslrootcert': '/certs/ca.crt',
        'user': 'sqlalchemy',
        'krbsrvname': 'dummy'
    }

If we run the application again, the web container will fail to start and we can inspect the logs where we're going to be greeted with a similar stack trace to this

    connection = pool._invoke_creator(self)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/strategies.py", line 114, in connect
    return dialect.connect(*cargs, **cparams)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 493, in connect
    return self.dbapi.connect(*cargs, **cparams)
  File "/usr/local/lib/python3.8/site-packages/psycopg2/__init__.py", line 127, in connect
    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) GSSAPI continuation error: Unspecified GSS failure.  Minor code may provide more information
GSSAPI continuation error: Server dummy/lb@EXAMPLE.COM not found in Kerberos database

Let's remove the krbsrvname entirely to make sure our application works and indeed works with the default postgres SPN.

if secure_cluster:
    connect_args = {
        'sslmode': 'verify-full',
        'sslrootcert': '/certs/ca.crt',
        'user': 'sqlalchemy',
    }

We can restart the web container with docker-compose start web and inspect the logs. After a few seconds, we should see the following

FROM accounts
WHERE accounts.id = %(id_1)s
2020-08-17 17:34:01,232 INFO sqlalchemy.engine.base.Engine {'id_1': 642966854}
2020-08-17 17:34:01,235 INFO sqlalchemy.engine.base.Engine UPDATE accounts SET balance=%(balance)s WHERE accounts.id = %(accounts_id)s
2020-08-17 17:34:01,235 INFO sqlalchemy.engine.base.Engine {'balance': 439621, 'accounts_id': 642966854}
2020-08-17 17:34:01,238 INFO sqlalchemy.engine.base.Engine UPDATE accounts SET balance=(accounts.balance + %(balance_1)s) WHERE accounts.id = %(id_1)s
2020-08-17 17:34:01,238 INFO sqlalchemy.engine.base.Engine {'balance_1': 439620, 'id_1': 492716732}
2020-08-17 17:34:01,240 INFO sqlalchemy.engine.base.Engine RELEASE SAVEPOINT cockroach_restart
2020-08-17 17:34:01,240 INFO sqlalchemy.engine.base.Engine {}
2020-08-17 17:34:01,245 INFO sqlalchemy.engine.base.Engine COMMIT

If we login to the web container and run klist, we should see the current ticket cache

# klist
Ticket cache: FILE:/tmp/krb5cc_0
Default principal: sqlalchemy@EXAMPLE.COM

Valid starting     Expires            Service principal
08/17/20 17:33:50  08/18/20 17:33:50  krbtgt/EXAMPLE.COM@EXAMPLE.COM
	renew until 08/17/20 17:33:50
08/17/20 17:34:00  08/18/20 17:33:50  postgres/lb@
	renew until 08/17/20 17:33:50
08/17/20 17:34:00  08/18/20 17:33:50  postgres/lb@EXAMPLE.COM
	renew until 08/17/20 17:33:50

so indeed the postgres SPN is working.

Let's also try to change the user name to somethig that is not known to the KDC.

if secure_cluster:
    connect_args = {
        'sslmode': 'verify-full',
        'sslrootcert': '/certs/ca.crt',
        'user': 'unknown',
    }

Restart the container with docker-compose restart web. After a few seconds, you should see the following message in the logs.

    connection = pool._invoke_creator(self)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/strategies.py", line 114, in connect
    return dialect.connect(*cargs, **cparams)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 493, in connect
    return self.dbapi.connect(*cargs, **cparams)
  File "/usr/local/lib/python3.8/site-packages/psycopg2/__init__.py", line 127, in connect
    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) ERROR:  password authentication failed for user unknown

(Background on this error at: http://sqlalche.me/e/13/e3q8)

Hope you're as convinced as I am. If you find this tutorial useful, drop me a note or leave me feedback in the comments.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment