How to encrypt specific database tables with CockroachDB

A few days ago, we (Artem and Chris) were working with two different customers that had the same requirement: they needed to encrypt certain tables in their databases.

This is not uncommon. Encryption comes with a slight performance penalty, so many companies prefer not to encrypt their entire database. Instead, the best practice is to encrypt only the tables that contain sensitive information, so less-sensitive data can be accessed without the overhead the encryption creates.

But how do you actually set up a database with selective encryption by table? In this post, we’ll walk through how to set up a CockroachDB cluster that can encrypt your sensitive tables while storing other tables in plaintext.

To accomplish this, we’ll be creating multiple stores on our CockroachDB nodes, and then using CockroachDB’s locality attribute flags to specify which data needs to use the encrypted store. It’s a pretty straightforward process, and we’re going to walk through it step by step.

High-Level Steps:

• Create an Encryption Key (AES-128)

• Start a CockroachDB cluster with encrypted and unencrypted stores

• Create and assign personally-identifiable information (PII) tables to encrypted stores and non-PII tables to non-encrypted stores

• Verify that our encryption is set up correctly

Step 1: Create an encryption key

First, let’s assign some variables to manage this setup in a scriptable fashion. We’ll create a $storepath variable which will tell CockroachDB where to keep its data. We’ll also use a $keypath variable which will have the location of our encryption key.

Next we’ll create an AES-128 encryption key. This functionality is built into CockroachDB:

After running the above command, you’ll get a confirmation message that says your key has been created and lists its directory path:

successfully created AES-128 key: /Users/username/Documents/workdir/key/aes-128.key

Step 2: Start a CockroachDB cluster with encrypted and unencrypted stores

Next, let’s create a cluster with both an encrypted store and a plaintext store. The syntax for this is --store=path=${dir}/1e/data,attrs=encrypt and --store=path=${dir}/1o/data,attrs=open respectively. We’ll be building a three node cluster, so we’ll create both encrypted and unencrypted stores for each node.

The attributes at the end of the store creation are used when pinning a table to an encrypted store. Additionally, the encryption key we created in the prior step is referenced in the --enterprise-encryption flag as well.

Now that the cluster is created, let’s initialize it.

Running this command should generate a Cluster successfully initialized message. We’ve now created and initialized a three node CockroachDB cluster with encrypted and plaintext stores.

Now it’s time for the cool part!

Step 3: Create PII and non-PII tables and assign them to the corresponding stores

Let’s create PII and non-PII tables and put each of them in the proper encrypted or unencrypted stores.

First, we’ll create the pii table, and configure a zone constraint that restricts it to using stores with encrypt attribute. We’ll also insert some sample data so that we can confirm our setup later.

Running the above command should return confirmation that we have created a table, configured a zone, and inserted one row into the table.

Now, let’s repeat the process for our non_pii table, except that this time, we’ll set it to use stores with the open attribute.

Once both tables are created, we can look at the ranges of the table to see if they have been moved to the encrypted store:

Confirm the output has the right constraints. If everything has been set up correctly, you’ll see the following in the output from running the command above:

`TABLE defaultdb.public.pii | ALTER TABLE defaultdb.public.pii CONFIGURE ZONE USING | constraints = '[+encrypt]'` `TABLE defaultdb.public.non_pii | ALTER TABLE defaultdb.public.non_pii CONFIGURE ZONE USING | constraints = '[+open]'`

Note that the pii table does have the encrypt constraint and the non_pii table has the open constraint. That’s exactly what we should see, but let’s take the extra step of verifying everything just to be sure!

Step 4: Verify that our encryption is set up correctly

Verifying that the tables are in the right place requires us to query some of the internal metadata in CockroachDB. Specifically, we need to do the following:

• Find the table ranges

• Find the encrypted and unencrypted store IDs

• Confirm that the ranges from the tables are mapped to the correct stores

Previously in this tutorial we’ve been executing SQL commands via bash, but for this, let’s start a session in CockroachDB’s built-in SQL shell to run our commands. Run the following command in the terminal to open CockroachDB’s SQL shell:

Step 4a: Find the table ranges

Recall that data in CockroachDB is broken up into ranges, which are then replicated across multiple nodes. We want to figure out what the range_id is for our pii table, and we can accomplish that using the SHOW RANGES command:

Running that command will generate output that shows the range_id for pii table:

As we can see, we have one range to verify (range_id = 37), and this range has three replicas (one for each node in our cluster).

Find the encrypted and unencrypted store IDs

Next, we’ll need to query an internal table called crdb_internal.kv_store_status to find out more about the stores being used on our cluster and what attributes they have.

The above command will return the following output:

node_id | store_id | attrs ----------+----------+-------------- 1 | 1 | ["encrypt"] 1 | 2 | ["open"] 2 | 3 | ["encrypt"] 2 | 4 | ["open"] 3 | 5 | ["encrypt"] 3 | 6 | ["open"]

We can see that there are six stores in total. Three of them have an [“encrypt”] attribute and three have an [“open”] attribute. Each node has one encrypted store and one unencrypted store.

This is all expected – it’s what we set up during step 2! Now we need to make sure that the data from our tables is being stored correctly, and we can do this by checking how the table ranges are mapped to these stores.

Step 4b: Confirm that the table ranges are mapped to the correct stores

Finally, we’re going to generate a table that shows the range_id for our pii table and what store_ids it corresponds with. Recall from the previous steps that the range ID for that table is 37 and the store IDs for the encrypted stores are 1, 3, and 5, so if everything is working as expected, we’ll see a table that shows range 37 is being stored in those three stores.

We can generate that output and confirm that the ranges from the tables are mapped to the correct stores using the query below.

This query is a bit more complex than the others we’ve used in this post, but it will get us to the answer we need. Essentially, we’re querying some of the CockroachDB metadata to pull range_id, node_id, and store_id for the pii table.

As expected, when we run this query we can see that range 37, which is our table with personally-identifiable information (PII), is assigned to stores 1, 3, and 5, which are the three encrypted stores we created:

range_id | node_id | store_id -----------+---------+----------- 37 | 1 | 1 37 | 2 | 3 37 | 3 | 5 (3 rows)

We can repeat the same process to confirm that the unencrypted table non_pii is assigned to the unencrypted stores:

Find the range ID for the non_pii table:

range_id | replicas -----------+----------- 38 | {2,4,6}

Check which stores the non_pii table is assigned to:

range_id | node_id | store_id -----------+---------+----------- 38 | 1 | 2 38 | 2 | 4 38 | 3 | 6 (3 rows)

Again, we can see that the range with range_id 38 is stored on stores 2, 4 and 6! In other words, we did it – we’ve set up encryption at rest for our pii table but not for our non_pii table.

This gives us the best of both worlds – secure encryption for the tables that need it, and plaintext performance for the tables that don’t!

Step 5 (Optional): Cleanup

If you’ve been following along with this tutorial, you can run the following command to kill the CockroachDB process and remove the directories we created above.

For more information about encryption in CockroachDB, please refer to our documentation, and check out our free courses for help getting your CockroachDB or CockroachDB Dedicated up and running!

Disclaimer: this tutorial is purposefully using an insecure test instance of CockroachDB which would not prevent unauthorized access to the data, regardless of the table encryption. For real world production scenarios, you’ll want to set up a secure cluster. Additionally, be aware that this kind of encryption at rest will not prevent data access for admin users and the default root user.