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.
CORE
·
Join the DZone community and get the full member experience.
Join For FreePrevious Articles on Schema Migrations 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.
Published at DZone with permission of Artem Ervits. See the original article here.
Opinions expressed by DZone contributors are their own.
Comments