Using CockroachDB in Secure Mode Locally

Oct 15, 2025 # CockroachDB

I’ve written a few posts about [CockroachDB]/tag/cockroachdb, and in most of them you can run the database in insecure mode. But, there are a few things you can only do in secure mode. One is creating other users, but there are probably others.

To use secure mode, there are a number of steps.

Create the certs

First, you need to create several certificates, actually quite a few.

# create two directories
mkdir cockroach-data cockroach-data/certs cockroach-data/my-safe-directory

# create the certs
cockroach cert create-ca --certs-dir=cockroach-data/certs --ca-key=cockroach-data/my-safe-directory/ca.key
cockroach cert create-node localhost $(hostname) --certs-dir=cockroach-data/certs --ca-key=cockroach-data/my-safe-directory/ca.key

# create cert for the user root
cockroach cert create-client root --certs-dir=cockroach-data/certs --ca-key=cockroach-data/my-safe-directory/ca.key

# optional, create cert user bryan
cockroach cert create-client bryan --certs-dir=cockroach-data/certs --ca-key=cockroach-data/my-safe-directory/ca.key

Certs are in place now.

Start the database and connect as root

This command starts the database in secure mode, using the certs created a moment ago.

cockroach start-single-node --certs-dir=cockroach-data/certs

Then you securely connect as root.

cockroach sql --certs-dir=cockroach-data/certs --user root

Create a user named bryan

In the SQL prompt, create another user.

CREATE USER bryan WITH LOGIN PASSWORD 'admin';

Create a database and grant access to the user bryan

This creates the database, grants bryan access to all tables that will be created in salesdb, and seeds the table.

CREATE database salesdb; 
use salesdb;
ALTER DEFAULT PRIVILEGES GRANT ALL ON TABLES TO bryan;

CREATE TABLE products (id INT PRIMARY KEY, name STRING, price DECIMAL);
INSERT INTO products (id, name, price) VALUES (1, 'Toy Car', 19.99);
INSERT INTO products (id, name, price) VALUES (2, 'Shoes', 29.99);
INSERT INTO products (id, name, price) VALUES (3, 'Hat', 9.99);

For simplicity, I created the user bryan first, and gave that user access to all tables that will created subsequently. If you have tables already in place, you need to grant access to those too.

Connect as bryan

Everything is in place now. Connect as the bryan user -

cockroach sql --certs-dir=cockroach-data/certs --user bryan --database=salesdb

If you create the cert for the user bryan, you won’t have to enter the password. If you didn’t create the cert, you will have to enter the password.

CockroachDB