Connect to Cockroachdb from Phoenix app

Phoenix
1

Are there instructions for connecting to an external database like Cockroach db from a Phoenix application? I’m getting tripped up on how to set up the certs and ssl connection.

2

Hi @choiway

Connecting to CockroachDB is the same as connecting to a Postgres database with a custom CA certificate.

I would recommend using username/password authentication for your app rather than certificate based authentication.

How we handle the CA certificate is we base64 encode the certificate then store it in an Environment variable using fly secrets. Then in our application code we read the environment variable and base64 decode the certificate, then just pass it along with the other standard config.

Here’s an example config for our app built with Node.js, it should be similar to the postgres config available in Phoenix:

{
        database: 'mydb',
        host: 'my-crdb.fly.dev',
        port: 26257,
        ssl: { ca: BASE64DECODED_DB_CA_CRT, rejectUnauthorized: false },
        user: 'myusername',
        password: 'mypassword',
        application_name: 'My App'
}
2 Likes
3

This wasn’t as straightforward as I thought so I thought I would follow up on my own question. There were two big gotchas that got me: 1) Managing the cert in the Docker Container and 2) configuration settings in the Phoenix application. I should also note that this is specific to

Dockerfile

Rather than pipe the ssl certificate into fly secret, I chose to save the cert into the Docker container during the build process. I didn’t realize that that standard Phoenix Docker build process actually creates a build container that feeds into the release container.

I used curl to download the cert so had to install curl in the Runner Image and saved it to the /app/ directory. This made the cert available to the release version of the Phoenix app from /app/.postgresql/root.crt.

 FROM ${RUNNER_IMAGE}

 RUN apt-get update -y && apt-get install -y libstdc++6 openssl libncurses5 locales curl \
    && apt-get clean && rm -f /var/lib/apt/lists/*_*

RUN curl --create-dirs -o /app/.postgresql/root.crt -O https://cockroachlabs.cloud/clusters/OMITTED-UUID/cert

runtime.exs

Here’s the config that ended up working. This stackoverflow post helped a ton.

    config :myapp, MyApp.Repo,
      ssl: true,
      pool_size: String.to_integer(System.get_env("POOL_SIZE") || "10"),
      socket_options: [],
      username: "admin",
      password: "XXXXXXXXXXXXXXXX",
      database: "defaultdb",
      hostname: "free-tier4.aws-us-west-2.cockroachlabs.cloud",
      port: "26257",
      ssl: true,
      ssl_opts: [
        cacertfile: "/app/.postgresql/root.crt"
      ],
      parameters: [options: "--cluster%3Dserverless-cockroach-2215"],
      migration_lock: false

A couple things to point out:

  1. You have to remove maybe_Ipv6 from socket_options or you’ll encounter an unreachable domain error.
  2. Migration lock needs to be set to off.
  3. If you’re using mix phx.gen.auth make you look at the stack overflow post about changing citext to string for the user email address in the migration.

release.exs

You have make sure ssl is started when running migrations in release. To do that I added the ensure_started function and added it to the beginning of the migrate and rollback functions.

  defmodule MyApp.Release do
    @moduledoc """
    Used for executing DB release tasks when run in production without Mix
    installed.
    """
    @app :myapp

    def migrate do
      ensure_started()
      load_app()

      for repo <- repos() do
        {:ok, _, _} = Ecto.Migrator.with_repo(repo, &Ecto.Migrator.run(&1, :up, all: true))
      end
    end

    def rollback(repo, version) do
      ensure_started()
      load_app()
      {:ok, _, _} = Ecto.Migrator.with_repo(repo, &Ecto.Migrator.run(&1, :down, to: version))
    end

    defp repos do
      Application.fetch_env!(@app, :ecto_repos)
    end

    defp load_app do
      Application.load(@app)
    end

    defp ensure_started do
      Application.ensure_all_started(:ssl)
    end
  end
2 Likes
4

@choiway Did you have to do anything around ca cert file permissions? I’m following your setup but when it tries to connect it complains about bad permissions on /app/.postgresql/root.crt. Thank you!

5

It’s been a while but I don’t recall altering the permissions of the crt file for the container.