CockroachDB With SQLAlchemy and MIT Kerberos

Today, I'm going to demonstrate how easily we can integrate an SQLAlchemy application running against a kerberized CockroachDB cluster.

Artem Ervits

CORE ·

Jan. 29, 22 · Tutorial

Likes (4)

Comment

Save

5.2K Views

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:

Part 1: CockroachDB with MIT Kerberos

Part 2: CockroachDB With Active Directory

Part 3: CockroachDB With MIT Kerberos and Docker Compose

Part 5: Executing CockroachDB table import via GSSAPI

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.

Clone the repo.

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

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 
  

3. 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 
  

4. 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 
  

5. 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 the CockroachDB tutorial site where I specify Kerberos-specific connection parameters. Specifically, we only care about our users, 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 Kerberos does indeed 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 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 something 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.

Docker (software) CockroachDB Kerberos (protocol)

Published at DZone with permission of Artem Ervits. See the original article here.

Opinions expressed by DZone contributors are their own.

Related

Trending