How to override `checkServerIdentity` for ssl connection?

[levino]

I'm submitting a...

• [x] Question

Current behavior

I can only connect to my google cloud psql instance with rejectUnauthorized: false.

Expected behavior

I can connect to my google cloud psql instance with rejectUnauthorized: true.

Minimal reproduction of the problem with instructions

Create a google cloud managed postgresql instance and switch on ssl/tls with the self signed CA. Then try to run migrations on the database from a remote system (like your local workstation).

What is the motivation / use case for changing the behavior?

Googles certificates are badly configured. They give you an ip to connect to, but the ip of the server is not in the list of altnames in the certificate the server provides. You will get this error message when attempting to connect with rejectUnauthorized: true:

[ERROR] Error: Hostname/IP doesn't match certificate's altnames: "IP: X.X.X.X is not in the cert's list: "

Now one could possibly work around this with implementing a custom checkServerIdentity function for the tls connection by node which should be more secure than just rejectUnauthorized: false. Alas db-migrate expects configuration via a database.json, not javacript so I cannot implement the checkServerIdentity function.

So now to the question: How to read the config for db-migrate from a javacript file?

Environment

yarn list v1.10.1
├─ db-migrate-base@1.5.3
├─ db-migrate-pg@0.4.0
├─ db-migrate-shared@1.2.0
├─ db-migrate-sqlite3@0.3.1
└─ db-migrate@0.11.3
├─ db-migrate-pg@0.4.0
├─ pg-connection-string@0.1.3
├─ pg-pool@2.0.3
├─ pg-types@1.12.1
├─ pg@7.5.0
└─ pgpass@

Additional information:
- Node version: v8.12.0
- Platform:  Linux (Ubuntu 18.04)

[levino]

I use amazons RDS now. All is working fine, no need for client certificates, which is even better, I find.

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[olso]

in your database.json, you can

"dev": {
    "driver": "pg",
    "url": {
      "ENV": "DATABASE_URL"
    },
    "ssl": {
      "rejectUnauthorized": false
    }
  },

[levino]

in your database.json, you can

"dev": {
    "driver": "pg",
    "url": {
      "ENV": "DATABASE_URL"
    },
    "ssl": {
      "rejectUnauthorized": false
    }
  },

Are you serious? This flag should never be false because the check prevents a MITM-attack on the Server-to-Database-Connection!

For everyone else: Do not switch this check off. Not even in a development setting. Just use AWS as suggested above.

[levino]

@olso If you read my OP carefully you can see that I considered and rejected the idea of doing what you suggest right from the beginning.

[olso]

@Levino I know its wrong, but I'm using Heroku... https://help.heroku.com/MDM23G46/why-am-i-getting-an-error-when-i-upgrade-to-pg-8

[levino]

Their guide is just wrong. They are crazy fools. Stop using them if they work on this poor level. This kind of advice should be illegal and punished.

[levino]

Jesus, if I understand it right until recently node-postgres silently disabled cert checking and only enabled it now. I am flubberghasted! They write unbelievable bulls**t like this:

This means your connection attempt may fail if you are using a self-signed cert.

Of course this must fail because a self-signed cert is insecure ffs! Do not use self signed certs, ever!

[levino]

No offense intended @olso but if you are confused here you should read up a bit about encryption and best practices for secure remote connections.

[olso]

It's fine, I get the issue. I'm talking to Heroku support for more info.

Just found out that 😕 means confused, going to change my reaction

[wzrdtales]

self signed is not a bad thing when it is intended to be a managed PKI which is quite a normal thing for databases in enterprises. of course those PKIs are mildly more secure than just a common people self signed cert, but is equally the same thing. I am agreeing that rejectUnauthorized is horribly bad idea except maybe for your local dev environment, but supplying a CA cert of the signing PKI is always the actual option. You @Levino could ask heroku if there is a CA cert that they can supply to you.

[wzrdtales]

for reference, quick google gives this https://devcenter.heroku.com/articles/heroku-postgres-via-mtls, so they offer something in that regards.

[levino]

Of course if you set up your own CA and install the root certificate on the client machine as trustworthy, you can secure your setup even with certificates signed by your own CA (which is something else than "self-signed" where the cert is signed by itself, is it not? Could not find a proper definition of "self-signed"). But still you need to reject any unauthorized connection to the database. And managing your own CA securely is hard and cumbersome. I do not recommend it.

[wzrdtales]

well managing the trust in the case of auth by cert in a database environment wouldn't be very healthy to be done by a public CA. it would be too slow, too cumbersome to validate new certs and in the end also too expensive and insecure. public CAs are the way to go in general for the validity of user facing certificates of all kinds. System services have different requirements and you certainly don't want anyone else to be able to sign your auth certificates for your services other than you. So you need a PKI infrastructure at that point. And you never install those root CAs on the machine, as they're single purpose and are supplied to such systems (as in this case a database) directly as a configuration param.

and no providing a CA yourself is nothing else than "self-signed". In fact self-signed means you generate a CA adhoc to sign the cert with.

managing your own CA is a piece of cake, everyone can do it. Securing it, is a different tier, but also not essentially hard. In the end it is as secure as the password for the root account is if it is really strong. If the user who has access to it gets compromised, the whole system is compromised. This can be circumvented with zero trust topologies, however rarely implemented, not even by big hairy monkey companies.

[levino]

I get your point. But even with all that you still want "unauthorized" connection attempts to be refused.

[mattbbc]

I use amazons RDS now. All is working fine, no need for client certificates, which is even better, I find.

I'm just starting to use db-migrate and I'm interested to know how you connect it to an AWS RDS instance safely - is your database public?

[levino]

Hey @mattbbc that's an oldy here.

First of all I feel a little ashamed of the strong and harsh language I used here. I would like to apologize to all people whose feelings I might have hurt, for example @olso . I am sorry. That was unnecessary. I have changed a bit since then (at least I hope so). I thought about deleting some of the comments but will leave them for transparency reasons.

On your question: Maybe I exposed the database to the internet, I do not remember. I think you can limit the IP range which is allowed to connect to it. But if you use TLS and a strong password, the database is safe. Maybe it could be ddoses, but that is it. Another possibility is a server in the same VPN as the database which exposes an ssh server with cert only authentication (not password). You then can tunnel the connection through this server to your database via ssh. That is what I would recommend today.

[Zambonilli]

This is one of the top results in duckduckgo for connecting to a GCP Cloud SQL postgres w/self signed certs. After spending a day bashing my head against the wall like @levino did, I can say the only solution right now is to not verify the self signed cert at all via setting the rejectUnauthorized to false when using GCP Cloud SQL postgres with a private IP. The node-postgres package that most of the postgres packages are using does not support the verify-ca ssl mode which would assert the CA but not fail on the CN not matching.

https://github.com/brianc/node-postgres/issues/2934#issuecomment-1948967853