Check out my series of articles on CockroachDB and Kerberos below:
- Part 1: CockroachDB with MIT Kerberos
- Part 2: CockroachDB with Active Directory
- Part 3: CockroachDB with MIT Kerberos and Docker Compose
- Part 4: CockroachDB with MIT Kerberos and custom SPN
- Part 5: Executing CockroachDB table import via GSSAPI
- Part 6: Three-headed dog meet cockroach, part 6: CockroachDB, MIT Kerberos, HAProxy and Docker Compose
- Part 7: CockroachDB with MIT Kerberos and Django
- Part 8: CockroachDB with MIT Kerberos and SQLAlchemy
roach-0
- CockroachDB noderoach-1
- CockroachDB noderoach-2
- CockroachDB nodelb
- HAProxy acting as load balancerroach-cert
- Holds certificates as volume mountskdc
- MIT Kerberos realmweb
- sqlaclhemy server
If you are using Google Chrome as your browser, you may want to navigate here
chrome://flags/#allow-insecure-localhost
and set this flag toEnabled
.
The SQLAlchemy application with CockroachDB is based on the CockroachDB SQLAlchemy tutorial. Feel free to read the article [8](#Part 8) above.
- 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
- 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
- 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
- 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
Both root
user using cert and sqlalchemy
user using GSSAPI are leveraging hostssl
connection, even though we specified host all all ...
in our hba_conf config. To see that, take a look at enabling authentication logs and then inspect the logs.
I200817 19:06:12.898466 2878 sql/pgwire/conn.go:226 [n1,client=172.28.1.7:56844,hostssl,user=sqlalchemy] 14 session terminated; duration: 240.3243ms
The hostssl
attempts to authenticate only when connection is made with SSL encryption.
t","message":"Negative balance","path":"/transfer"}
2020-08-18 19:35:09.640 INFO 1 --- [ main] io.roach.data.jdbc.JdbcApplication : Worker finished - 7 remaining
2020-08-18 19:35:09.641 INFO 1 --- [ main] io.roach.data.jdbc.JdbcApplication : Worker finished - 6 remaining
2020-08-18 19:35:09.750 WARN 1 --- [pool-1-thread-3] io.roach.data.jdbc.JdbcApplication : {"timestamp":"2020-08-18T19:35:09.750+0000","status":400,"error":"Bad Request","message":"Negative balance","path":"/transfer"}
2020-08-18 19:35:09.751 INFO 1 --- [ main] io.roach.data.jdbc.JdbcApplication : Worker finished - 5 remaining
2020-08-18 19:35:09.751 INFO 1 --- [ main] io.roach.data.jdbc.JdbcApplication : Worker finished - 4 remaining
2020-08-18 19:35:09.751 INFO 1 --- [ main] io.roach.data.jdbc.JdbcApplication : Worker finished - 3 remaining
2020-08-18 19:35:09.751 INFO 1 --- [ main] io.roach.data.jdbc.JdbcApplication : Worker finished - 2 remaining
2020-08-18 19:35:09.751 INFO 1 --- [ main] io.roach.data.jdbc.JdbcApplication : Worker finished - 1 remaining
2020-08-18 19:35:09.828 WARN 1 --- [pool-1-thread-8] io.roach.data.jdbc.JdbcApplication : {"timestamp":"2020-08-18T19:35:09.827+0000","status":400,"error":"Bad Request","message":"Negative balance","path":"/transfer"}
2020-08-18 19:35:10.015 WARN 1 --- [pool-1-thread-8] io.roach.data.jdbc.JdbcApplication : {"timestamp":"2020-08-18T19:35:10.015+0000","status":400,"error":"Bad Request","message":"Negative balance","path":"/transfer"}
2020-08-18 19:35:10.016 INFO 1 --- [ main] io.roach.data.jdbc.JdbcApplication : Worker finished - 0 remaining
2020-08-18 19:35:10.016 INFO 1 --- [ main] io.roach.data.jdbc.JdbcApplication : All client workers finished but server keeps running. Have a nice day!