cockroachdb

CockroachDB uses the PostgreSQL wire protocol.

Setting up a testing environment (and test)

Download and unpack the monolithic cockroach command.

First run the server in insecure mode to do setup:

• cockroach start --insecure
• cockroach sql --insecure

  create user rktclient;
  create database test;
  grant all on database test to rktclient;
  

• In Racket:

  (require db)
  (postgresql-connect #:port 26257 #:database "test" #:user "rktclient")
  

Set up certificates:

mkdir certs priv
cockroach cert create-ca --certs-dir=certs --ca-key=priv/ca.key
cockroach cert create-node --certs-dir=certs --ca-key=priv/ca.key localhost
cockroach cert create-client --certs-dir=certs --ca-key=priv/ca.key rktclient

Run the server in secure mode:

• cockroach start --listen-addr=localhost --certs-dir=certs

Test Racket connection with client certificate:

(require db openssl)
(define ctx (ssl-make-client-context 'auto))
(ssl-load-private-key! ctx "certs/client.rktclient.key" #f)
(ssl-load-certificate-chain! ctx "certs/client.rktclient.crt")
(postgresql-connect #:port 26257 #:ssl 'yes #:ssl-context ctx #:database "test" #:user "rktclient")
;; ok, but didn't verify server cert!
(ssl-set-verify! ctx #t)
(ssl-set-verify-hostname! ctx #t)
;; (ssl-load-verify-source! ctx "certs/node.crt") ;; WRONG!
(ssl-load-verify-source! ctx "certs/ca.crt")
(postgresql-connect #:port 26257 #:ssl 'yes #:ssl-context ctx #:database "test" #:user "rktclient")

---

References:

• https://www.cockroachlabs.com/docs/stable/create-security-certificates.html