Connecting to PlanetScale securely (Node.js, Prisma)

Hi,

I get an error when I deploy a simple node.js application using Planetscale and Prisma.

This is the error:
Error opening a TLS connection: error:16000069:STORE routines:func(0):unregistered scheme:…/deps/openssl/openssl/crypto/store/store_register.c:237:scheme=file, error:80000002:system library:func(0):reason(2):…/deps/openssl/openssl/providers/implementations/storemgmt/file_store.c:267:calling stat(/etc/ssl/certs), error:16000069:STORE routines:func(0):unregistered scheme:…/deps/openssl/openssl/crypto/store/store_register.c:237:scheme=file, error:80000002:system library:func(0):reason(2):…/deps/openssl/openssl/providers/implementations/storemgmt/file_store.c:267:calling stat(/etc/ssl/certs), error:16000069:STORE routines:func(0):unregistered scheme:…/deps/openssl/openssl/crypto/store/store_register.c:237:scheme=file, error:80000002:system library:func(0):reason(2):…/deps/openssl/openssl/providers/implementations/storemgmt/file_store.c:267:calling stat(/etc/ssl/certs), error:16000069:STORE routines:func(0):unregistered scheme:…/deps/openssl/openssl/crypto/store/store_register.c:237:scheme=file, error:80000002:system library:func(0):reason(2):…/deps/openssl/openssl/providers/implementations/storemgmt/file_store.c:267:calling stat(/etc/ssl/certs), error:0A000086:SSL routines:func(0):certificate verify failed:…/deps/openssl/openssl/ssl/statem/statem_clnt.c:1896: (unable to get local issuer certificate)

From what I could understand by logging in with “flyctl ssh console”, the path to the system CA roots

/etc/ssl/certs/ca-certificates.crt

does not exist and was not created by Fly and therefore Planetscale cannot open a TLS connection.

This is Planetscale’s guide to creating a secure connection:

I don’t know how to fix this error and get Planetscale working with Fly.io.

Can you help me? Thank you.

Hi,

You may not need to concern yourself with where the CA root it. It may simply be a case of adjusting the connection string you use to connect to it. For example with a base of node:18-slim I was able to successfully connect using mysql2 from Node.js with this:

DATABASE_URL='mysql://user:password@region/name?ssl={"rejectUnauthorized":true}'

If that form does not work for you, take a look at this page and scroll down. You’ll see people finding different variations they got working with mysql, mysql2 and sequelize. One of them should work!

If not, hmm. Next would be figuring out if you are using Fly’s provided buildpack or a Dockerfile? If you are not sure, if you don’t have a file called Dockerfile in the folder your node.js app is in, you’ll be using their buildpack.

I ask because the base OS will determine the path to the CA roots. It may not be where you suggest. It’s in a different place depending on what the Linux distribution is (Debian, Alpine etc). I’m not sure what the base of Fly’s buildpack is, whereas with a Dockerfile you are in control of what base image you use (check the first line). If you take a look at this section of the Planetscale docs, it lists the various places it may be:

But like I say, that shouldn’t matter. It’s more likely the connection string’s SSL bit that needs editing.

1 Like

Is there a way to install the system CA root in the right path “/etc/ssl/certs/ca-certificates.crt”? In a way that automatically handles the update of the certificates.

I am not a Docker user.

I am only deploying a node.js app with “fly deploy”.

thank you.

Hi,

Well … my point was that may not be the “right” path for the system CA. Where it should be varies.

In your DATABASE_URL what have you got for the XXX part here? Don’t paste the full string, of course, just that end bit. Since from those linked posts, it’s worth trying different values for that param to make SSL connections work:

DATABASE_URL='mysql://user:password@region/name?ssl=XXX'

Once you’ve tried all those and so eliminated the ?ssl param as your issue, it would then be worth looking for the CA stuff.

I already tried all those solutions but nothing has changed, I see the same error.

I am using the same connection string that Planetscale recommend in its Web UI through the usage of sslaccept=strict being present in the DATABASE_URL. Without any changes to the connection string.

Strange :thinking:

As those comments say, variations of ?ssl or ?sslmode worked for them (and me). The param at the end of the string shown in the Planetscale UI can’t account for all variations of Node/library so that’s why it’s worth changing it. But you say you have and none worked.

So … given I don’t know how the default Fly buildpack works or what Linux distribution it uses, all I can suggest is trying to use the alternative way to make a Fly app which is to use aDockerfile. It is easier than it sounds. Basically you just need a Dockerfile which tells Fly how to build the app’s image, and a sibling .dockerignore file (to tell it what files to, yep, ignore - like any .env file which you don’t want in there).

If you want to give that approach a try, take a look at this sample repo:

You could then adjust accordingly for your app. Shouldn’t be needed as the buildpack should work. But you’ve found it doesn’t, so I’m out of ideas. It would be a case of someone else replying who has got Planetscale working with a buildpack-built app, and saying how :slight_smile:

1 Like

When I run fly launch, it automatically creates the files:

  • fly.toml
  • Dockerfiles
  • .dockerignore

I don’t know if it’s using the buldpack. These files are created automatically.

The linux distribution is this:

Ah, well I assume Fly have changed how their launch process works since I last used it. That’s fine, you already have a Dockerfile and .dockerignore then. Which suggests it’s not using the alternative (the buildpack).

Since you mention Debian bullseye … I wonder if the fix is as simple as changing the quotes around your URL :thinking: Only this person found that was the problem when they too could not connect to Planetscale (after experimenting with CA):

No, nothing changes. I always get the same error.

Is there a way to install the system CA root via the Dockerfile?

Weird!

Yep, you can do (almost) anything in a Dockerfile. Run whatever command you want in there :slight_smile:

For example this is a Debian Bullsye Dockerfile I found after a quick Google. I’m guessing this is what you are after. Check this line out:

If you like, you could copy your current Dockerfile here to see what it’s currently doing. Since I’m still surprised whatever it is Fly provides as default doesn’t already come with whatever base CA is needed. From what you’ve said it sounds like it doesn’t.

Sure, here is my current Dokerfile:

FROM debian:bullseye as builder

ARG NODE_VERSION=18.6.0

RUN apt-get update; apt install -y curl

RUN curl https://get.volta.sh | bash

ENV VOLTA_HOME /root/.volta

ENV PATH /root/.volta/bin:$PATH

RUN volta install node@${NODE_VERSION}

#######################################################################

RUN mkdir /app

WORKDIR /app

# NPM will not install any package listed in "devDependencies" when NODE_ENV is set to "production",

# to install all modules: "npm install --production=false".

# Ref: https://docs.npmjs.com/cli/v9/commands/npm-install#description

ENV NODE_ENV production

COPY . .

RUN npm install

FROM debian:bullseye

LABEL fly_launch_runtime="nodejs"

COPY --from=builder /root/.volta /root/.volta

COPY --from=builder /app /app

WORKDIR /app

ENV NODE_ENV production

ENV PATH /root/.volta/bin:$PATH

CMD [ "npm", "run", "start" ]

I’m not a Docker user. Could you tell me what should I add to this file to install the system CA?

Well it’s only a guess having not done this myself, but since it is a multi layer I’d try scrolling down and adding a line to add ca-certificates: RUN apt install -y ca-certificates. Maybe put that line in the second half between these two lines:

...
FROM debian:bullseye

RUN apt install -y ca-certificates

LABEL fly_launch_runtime="nodejs"
...

If the build fails as a result of adding that line, well my guess was wrong. But whatever error message it shows should show why not e.g use apt-get or no ca-certificates available or already installed etc.

It doesn’t work.

I see the same error.

Isn’t there someone from the Fly support who can fix this?

I didn’t think adding that line would help since like I say, the base image should come with any CA certificate already.

To investigate I actually tried myself just now, creating a brand new Node.js app with mysql2, with a Planetscale database.

I ran it locally. Worked fine, connected etc.

I ran fly launch. Sure enough that created a Dockerfile exactly the same as the one you have (the buildpack approach I previously mentioned must have now moved to legacy, as that used to be how Node.js worked - now they default to using a Dockerfile which is better). So that duplicates what you have.

I then ran fly secrets set DATABASE_URL='mysql://user:password@host/database-name?ssl={"rejectUnauthorized":true}' copied exactly from the Planetscale UI, using their suggested ssl param. That stages that secret, ready for deployment.

I then ran fly deploy and watched the process, and if you look very carefully you can see certs stuff being added as it whizzes past on screen from the builder output. As suspected. The deploy proceeded and worked. I checked the app using the name.fly.dev and sure enough, a sample query to the database worked.

app.get("/run_example_query", (req, res) => {
  connection.query(
    "SELECT * FROM table_name LIMIT 1",
    function (err, rows, fields) {
      if (err) throw err;

      res.send(rows);
    }
  );
});

That fetched the data from my Planetscale database:

Screenshot 2022-12-16 at 16.58.49

All works fine for me. Node.js, Fly, Planetscale.

So … the only remaining variable may be Prisma. Maybe try not using that, check it connects, then add that back in to the mix. If it breaks then, you will know it’s Prisma. If not, no idea!

Unfortunately you don’t officially get support without a paid plan (check the fly.io for options). Free support comes via this forum. You do see people from Fly on here regularly though so I’m sure someone will respond at some point.

1 Like

I tried, as you suggested, not using Prisma and using mysql2 instead.

I created and deployed a node.js app with express.js and mysql2 connected to a Planetscale database. And as in your case it works without problems.

However, I did not see the installation of certificates in the output of the build process. Maybe it’s working because it’s not using a TLS connection?

I do not know. I won’t be using Prisma with Planetscale on Fly.io for now.

1 Like