Skip to content

Instantly share code, notes, and snippets.

@dbist

dbist/cockroachdb-flyway-sslmode.md

Created October 24, 2023 19:35
Show Gist options
  • Save dbist/147a84dcd93014419caffb2039528e01 to your computer and use it in GitHub Desktop.
Save dbist/147a84dcd93014419caffb2039528e01 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
cockroachdb-flyway-sslmode.md

Secure schema migrations with Flyway and CockroachDB Dedicated

CockroachDB Dedicated comes with TLS enabled by default. At the minimum, a certificate authority is needed to authenticate. A customer was having issues using Flyway and TLS.

My 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 straight forward. 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 limited to certain capabilities.

You need a flyway directory with two child directories, conf and sql. The conf directory containes 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 for 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 may have TLS issues, you may need to include &sslmode= and 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, i.e.

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

and 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 eliminiates 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. Unappologetically, I agree and advocate to use the more 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 in 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 healthcheck.

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment