Secure Schema Migrations With Flyway and CockroachDB Dedicated

In this article, explore a solution to TLS certificate issues and containerized schema migrations with Flyway and CockroachDB.

Artem Ervits

CORE ·

Oct. 25, 23 · Tutorial

Likes (3)

Comment

Save

2.5K Views

Previous Articles on Schema Migrations and CockroachDB

Migrating Feature Toggles With Unleash and CockroachDB

Motivation

A prospect was having issues with TLS and Flyway schema migrations, and since both topics are near and dear to my heart, I felt obligated to respond.

High-Level Steps

Deploy a CockroachDB Dedicated cluster
Configure Flyway
Verify
Demonstrate the problem

Step-By-Step Instructions

Deploy a CockroachDB Dedicated Cluster

Spinning up a CockroachDB Dedicated cluster is fairly straightforward. Follow this guide.

Configure Flyway

Setting up a Flyway migration depends on your deployment model. I don't use any sophisticated CI/CD pipeline and rely on Docker for my needs. I'm also using the community edition and am limited to certain capabilities.

You need a Flyway directory with two child directories, conf and sql. The conf directory contains the authentication details in a file called flyway.conf. You can find the connection details in the cloud console or by typing the \c metacommand in the SQL client to capture the connection string.

Connection string: postgresql://artem:~~~~~~@artem-serverless-5954.g8z.cockroachlabs.cloud:26257/defaultdb?application_name=ccloud&connect_timeout=15&sslmode=verify-full

You may have noticed that I am using a serverless cluster instead, but it shouldn't be relevant. The steps in this article are applicable to both scenarios.

flyway.url=jdbc:postgresql://artem-serverless-5954.g8z.cockroachlabs.cloud:26257/defaultdb
flyway.user=artem
flyway.password=password
flyway.connectRetries=3

Populate the SQL directory with one or many migration files.

-rw-r--r--  1 artem  staff   39 Oct 20 14:41 V1_0__Create_database.sql
-rw-r--r--  1 artem  staff   96 Oct 20 14:56 V1_3__Seed_table.sql
-rw-r--r--  1 artem  staff   72 Oct 20 15:00 V1_1__Create_type.sql
-rw-r--r--  1 artem  staff  128 Oct 20 15:04 V1_2__Create_table.sql

Verify

You can test that everything works using the info command.

docker run --rm \
    -v $PWD/flyway/sql:/flyway/sql \
    -v $PWD/flyway/conf:/flyway/conf \
    flyway/flyway \
    info

Flyway Community Edition 9.22.3 by Redgate
See release notes here: https://rd.gt/416ObMi

Database: jdbc:postgresql://artem-serverless-5954.g8z.cockroachlabs.cloud:26257/defaultdb (PostgreSQL 13.0)
WARNING: Flyway upgrade recommended: CockroachDB 23.1 is newer than this version of Flyway and support has not been tested. The latest supported version of CockroachDB is 22.1.
Schema history table "public"."flyway_schema_history" does not exist yet
Schema version: << Empty Schema >>

+-----------+---------+-----------------+------+--------------+---------+----------+
| Category  | Version | Description     | Type | Installed On | State   | Undoable |
+-----------+---------+-----------------+------+--------------+---------+----------+
| Versioned | 1.0     | Create database | SQL  |              | Pending | No       |
| Versioned | 1.1     | Create type     | SQL  |              | Pending | No       |
| Versioned | 1.2     | Create table    | SQL  |              | Pending | No       |
| Versioned | 1.3     | Seed table      | SQL  |              | Pending | No       |
+-----------+---------+-----------------+------+--------------+---------+----------+

This seems to work, but if for any reason you have TLS issues, you may need to include &sslmode= (this is primarily the reason for this article).

Here's a message you'll see if you try to circumvent the sslmode with sslmode=disable.

WARNING: Connection error: FATAL: server requires encryption
Retrying in 4 sec...
ERROR: Unable to obtain connection from database (jdbc:postgresql://artem-serverless-5954.g8z.cockroachlabs.cloud:26257/defaultdb?sslmode=disable) for user 'artem': FATAL: server requires encryption

The easiest way to address the problem is to use sslmode=require. For example:

flyway.url=jdbc:postgresql://artem-serverless-5954.g8z.cockroachlabs.cloud:26257/defaultdb?sslmode=require

It's in line with what we originally saw, but the problem with require is that it makes no effort to validate the authenticity of the cluster CA certififacte, leading to man-in-the-middle attack risks. Hence, we always recommend using the sslmode=verify-full, which I will demonstrate shortly.

Before we proceed, I want to highlight how Docker Compose makes this even easier, as it eliminates the need to type out the entire Docker command. It will become obvious as we discuss the verify-full method.

version: '3.9'

services:

  flyway:
    container_name: flyway
    hostname: flyway
    image: flyway/flyway
    entrypoint: ["flyway", "info"]
    volumes:
      - $PWD/flyway/sql:/flyway/sql
      - $PWD/flyway/conf:/flyway/conf

Save the following in a docker-compose.yml file and then you can invoke it using the following command:

docker compose -f docker-compose.yml up

Demonstrate the Problem

In some cases, a more stringent requirement for security is needed where we have to use sslmode=verify-full. Unapologetically, I agree and advocate the use of the increased security option as much as possible.

flyway.url=jdbc:postgresql://artem-serverless-5954.g8z.cockroachlabs.cloud:26257/defaultdb?sslmode=verify-full

flyway  | Retrying in 4 sec...
flyway  | ERROR: Unable to obtain connection from database (jdbc:postgresql://artem-serverless-5954.g8z.cockroachlabs.cloud:26257/defaultdb?sslmode=verify-full) for user 'artem': Could not open SSL root certificate file /root/.postgresql/root.crt.
flyway  | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
flyway  | SQL State  : 08006
flyway  | Error Code : 0
flyway  | Message    : Could not open SSL root certificate file /root/.postgresql/root.crt.
flyway  | 
flyway  | Caused by: org.postgresql.util.PSQLException: Could not open SSL root certificate file /root/.postgresql/root.crt.
flyway  | Caused by: java.io.FileNotFoundException: /root/.postgresql/root.crt (No such file or directory)
flyway exited with code 1

Clearly, we've not included the CA certificate, but the system is smart enough to look for it in the default location. The problem is it's looking at the default location of the container, not the host system.

What needs to be done is attaching the cert volume to the container. All we need to do is include a single additional line in our Compose file and tell flyway.conf to look for the cert in the URL.

version: '3.9'

services:

  flyway:
    container_name: flyway
    hostname: flyway
    image: flyway/flyway
    entrypoint: ["flyway", "info"]
    volumes:
      - ./flyway/sql:/flyway/sql
      - ./flyway/conf:/flyway/conf
      - $PWD/root.crt:/certs/root.crt

flyway.url=jdbc:postgresql://artem-serverless-5954.g8z.cockroachlabs.cloud:26257/defaultdb?sslmode=verify-full&sslrootcert=/certs/root.crt

At this point, the info command succeeds. Let's make the final change to the Compose file to run a migration upon a successful connection and use the info command as container health check.

version: '3.9'

services:

  flyway:
    container_name: flyway
    hostname: flyway
    image: flyway/flyway
    entrypoint: ["flyway", "migrate"]
    volumes:
      - ./flyway/sql:/flyway/sql
      - ./flyway/conf:/flyway/conf
      - $PWD/root.crt:/certs/root.crt
    healthcheck:
      test: ["CMD-SHELL", "flyway", "info"]
      interval: 10s
      timeout: 10s
      retries: 3
      start_period: 10s

We can now run the migration.

flyway  | Flyway Community Edition 9.22.3 by Redgate
flyway  | See release notes here: https://rd.gt/416ObMi
flyway  | 
flyway  | Database: jdbc:postgresql://artem-serverless-5954.g8z.cockroachlabs.cloud:26257/defaultdb (PostgreSQL 13.0)
flyway  | WARNING: Flyway upgrade recommended: CockroachDB 23.1 is newer than this version of Flyway and support has not been tested. The latest supported version of CockroachDB is 22.1.
flyway  | Successfully validated 4 migrations (execution time 00:00.103s)
flyway  | Current version of schema "public": << Empty Schema >>
flyway  | Migrating schema "public" to version "1.0 - Create database" [non-transactional]
flyway  | Migrating schema "public" to version "1.1 - Create type" [non-transactional]
flyway  | Migrating schema "public" to version "1.2 - Create table" [non-transactional]
flyway  | Migrating schema "public" to version "1.3 - Seed table" [non-transactional]
flyway  | Successfully applied 4 migrations to schema "public", now at version v1.3 (execution time 00:00.750s)

Conclusion

This wraps up our look at TLS certificate issues and containerized schema migrations. If you feel this article incorrectly assumes certain things, please leave feedback in the comments.

CockroachDB TLS Flyway (software) Schema security

Published at DZone with permission of Artem Ervits. See the original article here.

Opinions expressed by DZone contributors are their own.

Related

Trending