TLS connection failed with message: invalid peer certificate contents / PostgresError: no pg_hba.conf entry for host

[reggi]

I just deployed to a deno project to deno deploy and got this message when I tried to run the insert.

I'm using heroku and they require SSL, from this old stackoverflow sequalize post I'm assuming I need to either 1) send the request using SSL 2) the reject the unauthorized call in deno? I'm not sure how to do either.

Here's the full message:

An error occurred during route handling or page rendering. PostgresError: no pg_hba.conf entry for host "███████", user "██████████", database "█████", no encryption
    at assertSuccessfulStartup (https://deno.land/x/postgres@v0.14.2/connection/connection.ts:40:19)
    at Connection.#startup (https://deno.land/x/postgres@v0.14.2/connection/connection.ts:252:13)
    at async Connection.startup (https://deno.land/x/postgres@v0.14.2/connection/connection.ts:311:21)
    at async Client.connect (https://deno.land/x/postgres@v0.14.2/client.ts:131:13)
    at async PostgresConnector._makeConnection (https://deno.land/x/denodb@v1.0.40/lib/connectors/postgres-connector.ts:28:9)
    at async PostgresConnector.query (https://deno.land/x/denodb@v1.0.40/lib/connectors/postgres-connector.ts:42:9)
    at async Database.query (https://deno.land/x/denodb@v1.0.40/lib/database.ts:117:25)
    at async Function._runQuery (https://deno.land/x/denodb@v1.0.40/lib/model.ts:78:25)
    at async Function.create (https://deno.land/x/denodb@v1.0.40/lib/model.ts:212:25)
    at async endpoint (file:///src/calendar/denodb/endpoint.ts:6:5)
2022-08-21 07:51:25
TLS connection failed with message: invalid peer certificate contents: invalid peer certificate: UnknownIssuer
Defaulting to non-encrypted connection

Any advice on how to fix? Does denodb have a useSSL option?

[reggi]

I added ?sslmode=require to the end of the connection uri and I'm getting a new error:

An error occurred during route handling or page rendering. Error: The certificate used to secure the TLS connection is invalid.
    at Connection.#startup (https://deno.land/x/postgres@v0.14.2/connection/connection.ts:238:31)
    at async Connection.startup (https://deno.land/x/postgres@v0.14.2/connection/connection.ts:311:21)
    at async Client.connect (https://deno.land/x/postgres@v0.14.2/client.ts:131:13)
    at async PostgresConnector._makeConnection (https://deno.land/x/denodb@v1.0.40/lib/connectors/postgres-connector.ts:28:9)
    at async PostgresConnector.query (https://deno.land/x/denodb@v1.0.40/lib/connectors/postgres-connector.ts:42:9)
    at async Database.query (https://deno.land/x/denodb@v1.0.40/lib/database.ts:117:25)
    at async Function._runQuery (https://deno.land/x/denodb@v1.0.40/lib/model.ts:78:25)
    at async Function.create (https://deno.land/x/denodb@v1.0.40/lib/model.ts:212:25)
    at async endpoint (file:///src/calendar/denodb/endpoint.ts:6:5)
    at async Object.handler (file:///src/calendar/denodb/fresh/api.ts:4:25)

[heqian]

I encountered the same initial issue with my project:

Dec 5 10:35:00 PM  TLS connection failed with message: Bad resource ID
Dec 5 10:35:00 PM  Defaulting to non-encrypted connection
Dec 5 10:35:00 PM  PostgresError: SSL/TLS required
Dec 5 10:35:00 PM      at assertSuccessfulStartup (https://deno.land/x/postgres@v0.14.2/connection/connection.ts:68:13)
Dec 5 10:35:00 PM      at Connection.#startup (https://deno.land/x/postgres@v0.14.2/connection/connection.ts:350:7)
Dec 5 10:35:00 PM      at async Connection.startup (https://deno.land/x/postgres@v0.14.2/connection/connection.ts:417:11)
Dec 5 10:35:00 PM      at async Connection.query (https://deno.land/x/postgres@v0.14.2/connection/connection.ts:869:7)
Dec 5 10:35:00 PM      at async PostgresConnector.query (https://deno.land/x/denodb@v1.1.0/lib/connectors/postgres-connector.ts:76:22)
Dec 5 10:35:00 PM      at async Database.query (https://deno.land/x/denodb@v1.1.0/lib/database.ts:240:21)
Dec 5 10:35:00 PM      at async Function._runQuery (https://deno.land/x/denodb@v1.1.0/lib/model.ts:228:21)
Dec 5 10:35:00 PM      at async Function.first (https://deno.land/x/denodb@v1.1.0/lib/model.ts:550:21)

and same error after adding ?sslmode=require.

I'm using PostgreSQL 15. Maybe it is a compatibility issue?

[kaldaf]

Any idea how to fix it? I get an error with ?sslmode=require ➡ Error: the certificate used to secure the TLS connection is invalid. and Sending fatal alert BadCertificate

• deno 1.30.3 (release, x86_64-pc-windows-msvc)
• v8 10.9.194.5
• typescript 4.9.4

[pankgeorg]

Coming to this issue from googling Error: the certificate used to secure the TLS connection is invalid. and Sending fatal alert BadCertificate.

IT seems that deno doesn't load certificates correctly, or doesn't have the default debian certificates or something.

In deno-deploy, I solved this by adding an environment variable with the certificate found in /etc/postgresql/15/main/postgresql.conf: ssl_cert_file, which, in my case is cat /etc/ssl/certs/ssl-cert-snakeoil.pem. In deno, do:

const cert = Deno.env.get("CERTIFICATE")

and then to the client

const options = {
  database: "db",
  hostname: "myhost",
  password: Deno.env.get("POSTGRES_PASSWORD"),
  port: 5432,
  user: "deno",
  tls: {
    caCertificates: [
      certificate,
    ],
    enabled: false,
  },
}
const pool = new Pool(options, 5, true);

this should work!

[jeremyjh]

OP is using Heroku, which uses self-signed certificates by default. They do have a feature for CA-signed certificates in preview. https://devcenter.heroku.com/articles/heroku-postgres-enhanced-certificates