[email protected] and SSL/TLS options

[pcwiek]

I was wondering if there's a way to somehow pass:

{
    ssl: {
        rejectUnauthorized: false
    }
}

to the client created by the migration runner, especially from the CLI level?

Version 8 of pg changed the default behavior, so now ssl=true (in a connection string, for example) is an equivalent of ssl: { rejectUnauthorized: true }, which also rejects self-signed certificates, and ssl=false is an equivalent to having SSL turned off all the way.

[pcwiek]

Since I can't get the latest version to work, I have a quick question re: usability @dolezel
How is the flag intended to be used? 😉

1. Pass DATABASE_URL=postgres://...?sslmode=required and play with --reject-unauthorized flag

Then it doesn't matter what I do ( --reject-unauthorized=true, --reject-unauthorized=false, --no-reject-unauthorized), I always get

could not connect to postgres: Error: Client has already been connected. You cannot reuse a client.
    at Client._connect (/var/nodes/project/node_modules/pg/lib/client.js:92:17)
    at Client.connect (/var/nodes/project/node_modules/pg/lib/client.js:297:10)
    at /var/nodes/project/node_modules/node-pg-migrate/dist/db.js:12:18
    at new Promise (<anonymous>)
    at createConnection (/var/nodes/project/node_modules/node-pg-migrate/dist/db.js:10:36)
    at query (/var/nodes/project/node_modules/node-pg-migrate/dist/db.js:21:15)
    at Object.select (/var/nodes/project/node_modules/node-pg-migrate/dist/db.js:52:32)
    at unlock (/var/nodes/project/node_modules/node-pg-migrate/dist/runner.js:40:31)
    at exports.default (/var/nodes/project/node_modules/node-pg-migrate/dist/runner.js:179:19)
    at processTicksAndRejections (internal/process/task_queues.js:97:5)
Client has already been connected. You cannot reuse a client.
error: no pg_hba.conf entry for host "xxx.xx.xx.xx", user "XXX", database "XXX", SSL off
    at Connection.parseE (/var/nodes/project/node_modules/pg/lib/connection.js:600:48)
    at Connection.parseMessage (/var/nodes/project/node_modules/pg/lib/connection.js:399:19)
    at Socket.<anonymous> (/var/nodes/project/node_modules/pg/lib/connection.js:115:22)
    at Socket.emit (events.js:311:20)
    at addChunk (_stream_readable.js:294:12)
    at readableAddChunk (_stream_readable.js:275:11)
    at Socket.Readable.push (_stream_readable.js:209:10)
    at TCP.onStreamRead (internal/stream_base_commons.js:186:23)

2. Pass DATABASE_URL without sslmode, but set PGSSLMODE=require

Now, no matter if I pass --reject-unauthorized=true, --reject-unauthorized=false, --no-reject-unauthorized or if I don't pass the flag at all, I get:

could not connect to postgres: Error: Client has already been connected. You cannot reuse a client.
    at Client._connect (/var/nodes/project/node_modules/pg/lib/client.js:92:17)
    at Client.connect (/var/nodes/project/node_modules/pg/lib/client.js:297:10)
    at /var/nodes/project/node_modules/node-pg-migrate/dist/db.js:12:18
    at new Promise (<anonymous>)
    at createConnection (/var/nodes/project/node_modules/node-pg-migrate/dist/db.js:10:36)
    at query (/var/nodes/project/node_modules/node-pg-migrate/dist/db.js:21:15)
    at Object.select (/var/nodes/project/node_modules/node-pg-migrate/dist/db.js:52:32)
    at unlock (/var/nodes/project/node_modules/node-pg-migrate/dist/runner.js:40:31)
    at exports.default (/var/nodes/project/node_modules/node-pg-migrate/dist/runner.js:179:19)
    at processTicksAndRejections (internal/process/task_queues.js:97:5)
Client has already been connected. You cannot reuse a client.
Error: self signed certificate in certificate chain
    at TLSSocket.onConnectSecure (_tls_wrap.js:1473:34)
    at TLSSocket.emit (events.js:311:20)
    at TLSSocket._finishInit (_tls_wrap.js:916:8)
    at TLSWrap.ssl.onhandshakedone (_tls_wrap.js:686:12)

DATABASE_URL is constructed as:

postgres://{user}:{password}@{address}:5432/{db}

---

3. Extra point: have a DB that does not support SSL connections, either have ?sslmode=disabled or PGSSLMODE=disabled, and then touching --reject-unauthorized flag results in Server does not support SSL connections error. In this case, the flag should probably be ignored altogether if SSL is switched off.

[pcwiek]

For completeness, I'm using the latest version:

(...)
  --single-transaction           Combines all pending migrations into a single
                                 database transaction so that if any migration
                                 fails, all will be rolled back
                                                       [boolean] [default: true]
  --lock                         When false, disables locking mechanism and
                                 checks                [boolean] [default: true]
  --reject-unauthorized          Sets rejectUnauthorized SSL option    [boolean]
(...)

[dolezel]

True to say, I'm not entirely sure how the connecting works below pg library, but I can't replicate the issue. I can combine --no-reject-unauthorized with ?sslmode and PGSSLMODE.
If you pass --no-reject-unauthorized it passes down config with { connectionString: 'xxx', ssl: { rejectUnauthorized: false } }. Setting PGSSLMODE to something other than disable, results in ssl: true (if not already set somehow).
The only problem is if you append ?ssl=true to the connection string, because it overrides ssl config https://github.com/brianc/node-postgres/blob/master/packages/pg/lib/connection-parameters.js#L50. So you should not use ?ssl=true when using --no-reject-unauthorized because it already advises using an SSL connection.

[pcwiek]

Thanks for the note!

I'll tinker with the environment then, maybe something is off there

[pcwiek]

OK, for some reason the connection string flag doesn't work, but PGSSLMODE env var along with --no-reject-unauthorized does!

Thanks again

[stramel]

PGSSLMODE=no-verify worked for me with my Heroku Postgres instance.