reeves•11mo ago

Postgres TLS Connection is invalid

I've followed other questions about deno pgCLient raising an error

Error: The certificate used to secure the TLS connection is invalid.
    at Connection.#startup

Error: The certificate used to secure the TLS connection is invalid.
    at Connection.#startup

I've added the following to the docker-compose:

  windmill_worker:
    environment:
      - ACCEPT_INVALID_CERTS="true"

  windmill_worker:
    environment:
      - ACCEPT_INVALID_CERTS="true"

It's not fixed the issue. Is this the correct env?

18 Replies

reevesOP•11mo ago

I've fixed my issue with:

import { Client } from "https://deno.land/x/postgres@v0.17.0/mod.ts";

type Postgresql = {
  host: string,
  port: number,
  user: string,
  dbname: string,
  sslmode: string,
  password: string,
  root_certificate_pem: string
}

export async function main(
  dbConfig: Postgresql,
  sql: string,
) {
  const clientOptions = {
    hostname: dbConfig.host,
    port: dbConfig.port,
    user: dbConfig.user,
    database: dbConfig.dbname,
    password: dbConfig.password,
    host_type: "tcp",
    tls: {
      enabled: true,
      enforce: true,
      caCertificates: [dbConfig.root_certificate_pem],
    },

  }

  const client = new Client(clientOptions);
  await client.connect();

  const res = await client.queryObject(sql);

  await client.end();

  return res.rows;
}

import { Client } from "https://deno.land/x/postgres@v0.17.0/mod.ts";

type Postgresql = {
  host: string,
  port: number,
  user: string,
  dbname: string,
  sslmode: string,
  password: string,
  root_certificate_pem: string
}

export async function main(
  dbConfig: Postgresql,
  sql: string,
) {
  const clientOptions = {
    hostname: dbConfig.host,
    port: dbConfig.port,
    user: dbConfig.user,
    database: dbConfig.dbname,
    password: dbConfig.password,
    host_type: "tcp",
    tls: {
      enabled: true,
      enforce: true,
      caCertificates: [dbConfig.root_certificate_pem],
    },

  }

  const client = new Client(clientOptions);
  await client.connect();

  const res = await client.queryObject(sql);

  await client.end();

  return res.rows;
}

@rubenf the Postgres resource template could do with a change to variables to make this a bit tidier to reflect the variable name changes in deno postgres.

rubenf•11mo ago

Thanks for sharing the proper config for deno postgres. I'm not sure to fully understand the requested change. We have to have a resource type that is universal and work across the language.

reevesOP•11mo ago

@rubenf yes, true there's probably lots of other Postgres clients that resource needs to support and it can't be changed. I was suggesting changing the resource from:

type Postgresql = {
  host: string,
  port: number,
  user: string,
  dbname: string,
  sslmode: string,
  password: string,
  root_certificate_pem: string
}

type Postgresql = {
  host: string,
  port: number,
  user: string,
  dbname: string,
  sslmode: string,
  password: string,
  root_certificate_pem: string
}

type Postgresql = {
  hostname: string, // update
  port: number,
  user: string,
  database: string, // update
  sslmode: string,
  password: string,
  root_certificate_pem: string
}

type Postgresql = {
  hostname: string, // update
  port: number,
  user: string,
  database: string, // update
  sslmode: string,
  password: string,
  root_certificate_pem: string
}

rubenf•11mo ago

the issue is that the resource type postgres is shared with all clients and languages so we can't change it to fit only 1 postgresql client (in addition to breaking all existing resources)

reevesOP•11mo ago

Yeah agreed. I didn't think of the wider impact. Still getting my head around concepts in windmill. Enjoying it though. Should the Postgres resource work with a PostgreSQL script with AWS RDS? I'm getting the following error:

ExecutionErr: error during execution of the script:
error performing TLS handshake: error:0A000086:SSL routines:tls_post_process_server_certificate:certificate verify failed:../ssl/statem/statem_clnt.c:1889: (self-signed certificate in certificate chain)

ExecutionErr: error during execution of the script:
error performing TLS handshake: error:0A000086:SSL routines:tls_post_process_server_certificate:certificate verify failed:../ssl/statem/statem_clnt.c:1889: (self-signed certificate in certificate chain)

Hence why I wrote a TS script to basically do the same thing - I'm assuming the PostgrSQL script will be faster though.

rubenf•11mo ago

That's the goal yes. If you can send us an easy reproduction we can investigate and either fix it or give instructions on how to achieve it Ideally, the instructions include what to do on RDS or local postgresql to achieve your setup

reevesOP•11mo ago

What's the easiest way to share reproduction with you with my RDS not public? The only non "standard" setup on the RDS is that i'm using a rds-ca-rsa4096-g1 cert as it gives 100 year expiry. RDS defaults to the rds-ca-2019 short dated one.

rubenf•11mo ago

the easiest is just to explain to us like we're 5. Create an rds, take the pem file from the config and attempt to connect iwth what ssl mode ?

reevesOP•11mo ago

Easy. Is here fine?

rubenf•11mo ago

yes

reevesOP•11mo ago

1) Create an RDS instance on AWS linked to an EC2 instance running windmill with docker 2) Keep the RDS config as it's preset except for changing the Certificate Authority to rds-ca-rsa4096-g1 3) On the RDS instance, create a new postgres user for the windmill workspace "testypops" 4) On the RDS instance, create a new database for the the windmill workspace "testypops" 5) Create a new postgres resource in the windmill workspace. 6) Set the sslmode to require 7) Find the correct certificate bundle for your AWS region (https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingWithRDS.SSL.html#UsingWithRDS.SSL.RegionCertificates) and copy the contents of the pem file to the root_certificate_pem field. 8) create a postgres script using the new postgres resource and get the following error:

ExecutionErr: error during execution of the script:
error performing TLS handshake: error:0A000086:SSL routines:tls_post_process_server_certificate:certificate verify failed:../ssl/statem/statem_clnt.c:1889: (self-signed certificate in certificate chain)

ExecutionErr: error during execution of the script:
error performing TLS handshake: error:0A000086:SSL routines:tls_post_process_server_certificate:certificate verify failed:../ssl/statem/statem_clnt.c:1889: (self-signed certificate in certificate chain)

rubenf•11mo ago

thanks, @guillaume if you can take a look later

guillaume•11mo ago

Will take a look and try to repro. Thanks for the detailed steps @reeves

andness•11mo ago

Is there a github issue created for this issue with AWS RDS long certs? We use AWS RDS and we need to change from the expiring cert so it would be nice to know when that is fixed.

guillaume•11mo ago

Sorry I took an initial look and I faced a similar issue. I wasn't able to find a good solution for it just yet, but I'll try to take some time today And no AFAICT there's no GH issue, feel free to open one

rubenf•11mo ago

@guillaume weren't you able to do do it without passing the pem ?

guillaume•11mo ago

Yes yes you can set SSL = require and not upload any cert, that will work but no cert validation will be done

andness•11mo ago

I think a Github Issue would be wise, anyone who is setting up RDS these days will get a warning about the soon-to-expire cert and will probably try to fix that and then run into this. It doesn't expire until August so there's still plenty of time to find a fix