Pgloader: Heroku usslmode=require SSL verify error: 18 X509_V_ERR_DEPTH_ZERO_SELF_SIGNED_CERT

[toobulkeh]

• [x] pgloader --version

  pgloader version "3.4.cb9e01f"
  compiled with SBCL 1.4.5
  

• [x] did you test a fresh compile from the source tree?

• [x] did you search for other similar issues?

Yes, the other X509 were all related to the source (MySQL) database, not the Heroku destination.

Another suggested to manually 'add the server cert to ca-bundles' which I'm not sure how to do.

The last I found was using the HEAD, which I compiled (above).

• [x] how can I reproduce the bug?

Setup a local database dbname below.

Setup a free-tier heroku db.

LOAD DATABASE
  FROM mysql://user:[email protected]:3307/dbname
  INTO
postgres://user:[email protected]:port/dbname2?sslmode=require
ALTER schema 'dbname' rename to 'public'
CAST
    type bigint to bigint drop typemod;

• [x] pgloader output you obtain

KABOOM!
FATAL error: Failed to connect to pgsql at "ec2-ip.compute-1.amazonaws.com" (port 5432) as user "user": SSL verify error: 18 X509_V_ERR_DEPTH_ZERO_SELF_SIGNED_CERT
An unhandled error condition has been signalled:
   Failed to connect to pgsql at "ec2-ip.compute-1.amazonaws.com" (port 5432) as user "user": SSL verify error: 18 X509_V_ERR_DEPTH_ZERO_SELF_SIGNED_CERT

What I am doing here?

Failed to connect to pgsql at "ec2-ip.compute-1.amazonaws.com" (port 5432) as user "user": SSL verify error: 18 X509_V_ERR_DEPTH_ZERO_SELF_SIGNED_CERT

• [x] How the data is different from what you expected, if relevant

Earlier this month, Heroku required all ingress Postgres connections to use sslmode=required. I can connect to their infrastructure just fine with heroku pg:psql, but it appears that these are self-signed certificates, which are not valid to pgloader. Ideally, pgloader uses SSL_VERIFY_NONE to skip this error during the SSL handshake.

They've actually had these connections defaulting to secure for quite some time, so something new must've changed (and broken) these certs.

Thanks for your help!

[sheyd]

Also seeing this issue. I've tried compiling from source back a couple of versions as well, with no luck (3.4.1 and 3.3.2 releases). The 3.4.1 version installed on Ubuntu 16.04 from the Postgres APT repo connects to Heroku Postgres instances with no problem.

[toobulkeh]

I ended up copying to a local PG environment, dumping to a DUMP file, then using heroku pg:restore to load into Heroku.

[nick-solly]

My workaround here was to use the pgloader flag: --no-ssl-cert-verification