Monday, April 27, 2020

Secure CockroachDB with Custom Common Name

CockroachDB out of the box comes with ability to generate certificates with cockroach cert command. This command will provision certs for client and nodes. One common gap we get from our customers is the explicit reliance on CN=node and CN=root. In our latest development release, we're introducing ability to map root and node principals to custom CNs. The process bypasses cockroach cert command in favor of openssl utility. It is very well documented and I recorded a live walk-through of the entire process. I am including my openssl configuration files for convenience:


# OpenSSL CA configuration file
[ ca ]
default_ca = CA_default

[ CA_default ]
default_days = 365
database = index.txt
serial = serial.txt
default_md = sha256
copy_extensions = copy
unique_subject = no

# Used to create the CA certificate.
[ req ]
distinguished_name = distinguished_name
x509_extensions = extensions

[ distinguished_name ]
organizationName = Example Inc
commonName = Example Inc CA

[ extensions ]
keyUsage = critical,digitalSignature,nonRepudiation,keyEncipherment,keyCertSign
basicConstraints = critical,CA:true,pathlen:1

# Common policy for nodes and users.
[ signing_policy ]
organizationName = supplied
commonName = optional

# Used to sign node certificates.
[ signing_node_req ]
keyUsage = critical,digitalSignature,keyEncipherment
extendedKeyUsage = serverAuth,clientAuth

# Used to sign client certificates.
[ signing_client_req ]
keyUsage = critical,digitalSignature,keyEncipherment
extendedKeyUsage = clientAuth


# OpenSSL node configuration file
[ req ]
distinguished_name = distinguished_name
req_extensions = extensions

[ distinguished_name ]
organizationName = Example Inc

[ extensions ]
subjectAltName = critical,DNS:localhost,,IP:

client.cnf for root

[ req ]
distinguished_name = distinguished_name
req_extensions = extensions

[ distinguished_name ]
organizationName = Example Inc 
commonName = john

[ extensions ]
subjectAltName =,,DNS:root

client.cnf for additional users

[ req ]
distinguished_name = distinguished_name
req_extensions = extensions

[ distinguished_name ]
organizationName = Example Inc 
commonName =

[ extensions ]
subjectAltName =,,DNS:user2

What is insecure may never break: CockroachDB insecure cluster take over

I came across an interesting scenario last week. A customer had asked whether it is possible to secure a previously insecure cluster. The short answer is yes. Now, Cockroach Labs does not recommend running an insecure cluster in production. There are only a few additional steps necessary to secure an instance, so why do it? Convenience, you say. It can hurt you down the line but fret not, this article will demonstrate how to fix this. We are going to follow the standard insecure cluster start up procedure. Once complete, we're going to flip to the documentation for a secure cluster to turn each node on with security enabled. Here's a handy video of the procedure in action: I also included the step by step instructions below:

Friday, April 24, 2020

New tricks in CockroachDB: Sql statement redirection from an external file

CockroachDB is the SQL database for building global, scalable cloud services that survive disasters. It is designed to be a Postgresql compatible database with distributed roots. The Postgresql compatibility is being built from scratch in Go. That said, product is undergoing fast pace of innovation and development. One convenience method of passing sql statements from an external file to CLI has been missing. It is not available in the current GA release and I opened a GitHub issue to implement it. In lieu of available convenience option, this post will discuss other methods of achieving the same.


I have a table called population with the following schema:

CREATE TABLE population ( state CHAR(2) NULL, city STRING NULL, population INT8 NULL, FAMILY "primary" (state, city, population, rowid) )

The associated CSV file has the following structure:

TX,Fort Worth,9000748
IN,South Bend,2590270
KS,Shawnee Mission,9444064

The associated IMPORT command:

IMPORT TABLE population ( state char(2), city TEXT, population INT8 ) CSV DATA ('');

The associated IMPORT INTO command:

IMPORT INTO population (state, city, population) CSV DATA ('');

The big difference between IMPORT and IMPORT INTO is the former will create the table for you and the latter expects the table to exist. That said, you can repeatedly issue IMPORT INTO command.

CockroachDB has ability to import CSV from cloud storage, http(s) endpoint, local filesystem, etc. You can read more about IMPORT command, here and the associated IMPORT INTO command, here.

Method 1a: Unix file redirection

Suppose I want to pass an import statement from a file. With Unix file redirection the following can be done with command below:

cockroach sql -e="$(<import_into_statement.sql)" --url 'postgresql://'

-e is functionally equivalent to --execute. This is pretty convoluted and can be further simplified by the next approach.

Method 1b: Unix file redirection

cockroach sql < import_into_statement.sql --url 'postgresql://'

Method 2: Using psql

Given CockroachDB is Postgresql wire compatible, you can use psql utility to achieve the same. I'm using Mac OSX and psql can be easily installed with:

brew install libpq
brew link --force libpq

Once installed, import from an external file can be easily done with built-in -f and --file= flags:

psql -f import_into_statement.sql 'postgresql://root@localhost:26257/defaultdb?sslcert=certs%2Fclient.root.crt&sslkey=certs%2Fclient.root.key&sslmode=verify-full&sslrootcert=certs%2Fca.crt'

Additional file redirection tricks are illustrated in our docs.