Setting Up a Secure Cockroach DB Cluster on AWS

The official documentation says quite complex things about setting up a Cockroach DB cluster in AWS securely. It means a lot that you have to setup a whole lot of things which are not said there. Many things need to be figured out beforehand. It took me couple of hours to bring up an effective cluster of Cockroach DB.

I hope I can write a CloudFormation script sooner. I will link it here if I do so.

Few things first. You will surely require access to following things. Skip if you are on root account or system admin access.

  1. A DNS provider. Preferably Route53 or anything else.
  2. Access to EC2 INSTANCE provisioning. You will need access of create to security groups and or modify them. Instance login ssh key generation permission.
  3. VPC configurations. Subnet, routing table, NAT gateway, egress only ipv6 gateway. Create modification permissions.
  4. Bastion Host

Step 1 — Creating Proper Subnet Configuration for VPC.

This assumes you have basic understanding of AWS VPC.

Follow up here for a abstract documentation of the process. I will try to cover this in future articles.

You will require following things in short.

  • A private subnet in each availability zone of your region. A private subnet will not have any publicly accessible IP addresses assigned and it usually routed through a NAT Gateway. Either 3 or 5 Subnets would be easily manageable.
  • A NAT Gateway coupled to the route-table of those subnets. (Required for downloading packages.)
  • A Egress-Only IPV6 Gateway coupled to the route table of those subnets.
  • A security group which has permissions open to port 26257 and 8080 within the VPC level. Thus a VPC of 10.0.0.0/16 should be given 10.0.0.0/16 in the address.

Step 2 — Launching Necessary Instances in the EC2

This step assumes that you have experience in launching instances in AWS EC2.

  • Go to Launching instances.
  • Choose Amazon Linux 2. This has NTP preconfigured. Which is necessary for Cockroach DB.
  • You have to use non-burst CPU instances (M class,C class,R class,I class,H class etc)
  • Minimum Storage of 50GB. You can change sizes later in real-time. Choose the maximum you will need for testing.
  • You can launch SPOT instances if you are just testing. You can later archive and convert them into On-Demand / Reserved without a single failure as needed.
  • You will need minimum one instance in each private subnet you have created.

Step 3 — Setting Up Cockroach DB

$ mkdir certs
$ mkdir safe-dir
$ cockroach cert create-ca \
--certs-dir=certs \
--ca-key=safe-dir/ca.key
  • Use the following commands repeatedly for each instance you have on cluster. For each Instance in your private subnets.

WHILE (ADD INSTANCE)

$ cockroach cert create-node \
<private IP address> \
<private HOST name> \
localhost \
127.0.0.1 \
<a DNS compatible name> \
--certs-dir=certs \
--ca-key=safe-dir/ca.key

PS: a DNS compatible name could look like “db-cluster.example.com”. It will be really helpful if you set a name which is completely exclusive for you. Subdomain of your company domain name could give better results. You can internally route a domain name internally using Route 53 as needed without registering for a domain actually.

  • You will require some simple configuration to access the instances in private subnet. You have to add the following configuration to “~/.ssh/config” for each of the instances you are adding.
Host private_db1
HostName 10.0.x.x #Change this to your instance's private IP.
User ec2-user
IdentityFile ~/.ssh/id_aws_key.pem #You should change this to your one.
ProxyCommand ssh <user>@<host> -W %h:%p #replace publicly accessible host details.
  • You can issue the following command now to copy the files from your machine to the instance inside the private subnet.
$ scp certs/ca.crt certs/node.crt certs/node.key private_db1:~/certs
  • You need to repeat the above procedure for each of the instance. Then you have certificates available on all of those instances.
  • NOTE: You have to SSH into each instance and setup permission of certificates to 700 or less, 600 preferred.
$ chmod 700 ~/certs/*
OR
$ chmod 600 ~/certs/*

END WHILE.

  • Execute the following command in local machine. It generates the client certificates for access.
$ cockroach cert create-client \ root \ --certs-dir=certs \ --ca-key=safe-dir/ca.key
  • Copy the certificates to the publicly accessible instance and install cockroach client. SSH into the public instance first before executing the following set.
$ mkdir certs #creates directory before copying the certs. much needed.
$ wget -qO- https://binaries.cockroachdb.com/cockroach-v2.0.5.linux-amd64.tgz | tar  xvz
$ cp -i cockroach-v2.0.5.linux-amd64/cockroach /usr/local/bin

Execute the following on your machine to copy client certificates to the server.

$ scp certs/ca.crt \
certs/client.root.crt \
certs/client.root.key \
<username>@<public ip>:~/certs

Step 4 — Installing CockroachDB on Server

Ensure that you have Root privilege to edit /usr directory. You have to repeat the following for each instance with its specific details.

  1. SSH using the following command.
$ ssh private_db1

2. Following command downloads and extracts the latest binary as of writing.

$ wget -qO- https://binaries.cockroachdb.com/cockroach-v2.0.5.linux-amd64.tgz | tar  xvz

3. Following command copies the files into $PATH. Prepend with “sudo” if you are logged in as normal user.

$ cp -i cockroach-v2.0.5.linux-amd64/cockroach /usr/local/bin

4. Create the Cockroach directory: Storing of Data. You can create this directory inside Instance store if you prefer to.

$ mkdir /var/lib/cockroach

5. Creating a separate user will allow us to separate concerns.

$ useradd cockroach

6. Move the certs directory to the cockroach directory.

$ mv certs /var/lib/cockroach/

7. Change the ownership of Cockroach directory to the user cockroach:

$ chown -R cockroach.cockroach /var/lib/cockroach

8. Download the sample configuration template and save the file in the /etc/systemd/system/directory as securecockroachdb.service:

$ wget -qO- https://raw.githubusercontent.com/cockroachdb/docs/master/_includes/v2.0/prod-deployment/securecockroachdb.service

Alternatively create following configuration yourself into the directory.

[Unit]
Description=Cockroach Database cluster node
Requires=network.target
[Service]
Type=notify
WorkingDirectory=/var/lib/cockroach
ExecStart=/usr/local/bin/cockroach start --certs-dir=certs --advertise-host=<PRIVATE IP NODE 1> --join=<PRIVATE IP of NODE>:26257,<PRIVATE IP NODE 2>:26257,<PRIVATE IP NODE 3>:26257,<ADD REST AS YOU NEED> --cache=.25 --max-sql-memory=.25
TimeoutStopSec=60
Restart=always
RestartSec=10
StandardOutput=syslog
StandardError=syslog
SyslogIdentifier=cockroach
User=cockroach
[Install]
WantedBy=default.target

9. Creating the instance of Cockroach DB. This will run the CockroachDB instance. It will not initialize the service.

$ systemctl start securecockroachdb

You will have to repeat this for each instance in the VPC which you want to run CockroachDB.

You can verify that cockroach DB is running by issuing the following command

$ netstat -tulpn | grep cockroach #lists listeners of 8080 and 26257

10. Login to your public EC2 instance. As You followed you should have your certs inside ~/certs and cockroach binary in /usr/local/bin

Following command will initiate the cluster. Must be executed from the public subnet which can access the private subnet.

$ cockroach init --certs-dir=certs --host=<address of any node>

Step 5 — Testing CockroachDB Cluster.

We will not cover that here. You can find related documentation here.

Step 6 — Configuring Network Load Balancer on AWS for Cockroach DB
  1. Create a normal network load balancer.
  2. Define TCP 26257 to TCP 26257 forwarding rules.
  3. Create a new target group.
  4. Define health check as HTTPS and override port with 8080, and on path /health?ready=1
  5. Create the load balancer.
  6. Create a DNS CNAME record in Route53 pointing to the “A record” of the NLB and done. ( You can use a internal routing)
  7. Voila!. You have a load-balanced cluster of Cockroach DB.

Step 7 — Testing The Load Balancer

  • SSH into your public instance.
  • Execute the following command.
$ cockroach sql --certs-dir=certs --host=<CNAME RECORD CREATED>
  • It should tentatively result in a connection, and should give a console.

I welcome any comments on detailing of any steps I have explained here. This article entirely assumes you have some basic encounter with AWSs products beforehand. This article entirely bases up on the official documentation for commands and enhances it by a quite more explanations.