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.
Migrating feature toggles with Unleash and CockroachDB
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.
- Deploy a CockroachDB Dedicated cluster
- Configure Flyway
- Verify
- Demonstrate the problem
Spinning up a CockroachDB Dedicated cluster is fairly straight forward. Follow this guide.
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
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
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)
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.