Connecting to a Postgres database using SSL

[cryptogohan]

This'll be a bit longer and is all about making sure Deno happily connects to Postgres databases using SSL, which I assume, the maintainers of Deno care about. I love Deno, but got burned quite a bit, ~6 or so hours sunk, when you think you're done, trying to deploy your neatly dockerized project, but with several major cloud providers, Deno has trouble with each when trying to connect to Postgres. Now insecure DB connections actually seem quite common in hindsight, Digital Ocean being the exception here, forcing SSL, when most cloud providers have virtual private networks making this not such a big deal, but still, having Deno be able to make SSL Postgres connections I'd consider far from a luxury and almost a necessity for wide adoption. So let's see what I've found and if people are interested let's improve the road a bit for those who will come after shall we 😄 !

AWS

const DB_HOST = "tmp-1.cddfpn93hvvg.us-east-2.rds.amazonaws.com";
const DB_PORT = 5432;

const conn = await Deno.connect({ hostname: DB_HOST, port: DB_PORT });
console.log("> connection established");
const sslRequest = new Uint8Array([0, 0, 0, 8, 4, 210, 22, 47]);

await conn.write(sslRequest);
const sslResponseByteBuffer = new Uint8Array(1);
await conn.read(sslResponseByteBuffer);
const sslResponse = new TextDecoder().decode(sslResponseByteBuffer);
const sslIsOk = sslResponse === "S";
console.log("> server likes SSL:", sslIsOk);

const tlsConn = await Deno.startTls(conn, {
  hostname: DB_HOST,
  certFile: "./global-bundle.pem",
});
await tlsConn.write(new Uint8Array([0, 0, 0, 8, 0, 3, 0, 0]));
const startUpResponseByteBuffer = new Uint8Array(1);
await tlsConn.read(startUpResponseByteBuffer);
console.log(
  "> tls response:",
  String.fromCodePoint(startUpResponseByteBuffer[0]),
);

Works! You download the AWS global-bundle.pem from a random doc page, but with the certFile property on the new startTls this works. The most popular pg driver for Deno will need an update but I'm happy to PR that.

Google Cloud

const GCLOUD_CA = `
-----BEGIN CERTIFICATE-----
MIIDfzCCAmegAwIBAgIBADANBgkqhkiG9w0BAQsFADB3MS0wKwYDVQQuEyQ1MjVm
MWVkYS1hNjJjLTQ0ZTQtOGQyYS0xM2JkNTViZWQ0MGYxIzAhBgNVBAMTGkdvb2ds
ZSBDbG91ZCBTUUwgU2VydmVyIENBMRQwEgYDVQQKEwtHb29nbGUsIEluYzELMAkG
A1UEBhMCVVMwHhcNMjEwNjAzMTM0OTM1WhcNMzEwNjAxMTM1MDM1WjB3MS0wKwYD
VQQuEyQ1MjVmMWVkYS1hNjJjLTQ0ZTQtOGQyYS0xM2JkNTViZWQ0MGYxIzAhBgNV
BAMTGkdvb2dsZSBDbG91ZCBTUUwgU2VydmVyIENBMRQwEgYDVQQKEwtHb29nbGUs
IEluYzELMAkGA1UEBhMCVVMwggEiMA0GCSqGSIb3DQEBAQUAA4IBDwAwggEKAoIB
AQDOHgKfeh+Pj3uQxCU8J/6dCIqjL45gF2ak3C1xr7ajWBa6DqCsdr/Kq6xGYsLU
lrcuOaSOhUiepp2JnwM7u+qReXdJpSHU1EgwEetB5N6eBM/UvHFW70wKo7FQDu1O
b+WIDLVoxfMGgyCC+ETTBKATTAbj9WCcZV162rpG7Rm9Hl8xn2C/2aoHBsqft2fA
jST7XfcZvKOsseADisKKBMHPf+Knx+1Uw7+6lY170TYnuye5cXOquCLttgIkxzP+
VQzdFWa2PtCovZpGz95NMEySiale2xK1Zj2XGyGb7jmTt3JOirc6wxWDD9Vu5lUS
MmKOLJaxGmkZXhXVfUiTk5ofAgMBAAGjFjAUMBIGA1UdEwEB/wQIMAYBAf8CAQAw
DQYJKoZIhvcNAQELBQADggEBAKlNhrQXbSiy4t2O3tBHdmWedZyz0Cd1wJL1sU+R
3pJ522wVpvUgy7fc98PlP6D/x5+ATpCKv631mNwss0y5XugzkM5oESH0OaJ9c/Aq
d9SMWsbwNNaY75q0lLEgfIt+ZnCCfsVzupT0nMD3Bjp7f7qU2DyoHy5mK+X39EuW
u6svZ/4OQ+RBcejRsQNRSeMg457hH/b5/Ge7FzPmHSOKebqW4Jj5piuDHTNLzneg
aNd/iTTMzoF2h2CsxhMyu1aA9P5LVLDWEzTwHJeUszBuyc1DWL1ZNfVsIB8CHQya
eR3EoHsKdjF7mZz8t/rR1uz5y1/iGirkTCBuvp61W0333HU=
-----END CERTIFICATE-----
`;

const DB_HOST = "34.77.40.50";
const DB_PORT = 5432;

const conn = await Deno.connect({ hostname: DB_HOST, port: DB_PORT });
console.log("> connection established");
const sslRequest = new Uint8Array([0, 0, 0, 8, 4, 210, 22, 47]);

await conn.write(sslRequest);
const sslResponseByteBuffer = new Uint8Array(1);
await conn.read(sslResponseByteBuffer);
const sslResponse = new TextDecoder().decode(sslResponseByteBuffer);
const sslIsOk = sslResponse === "S";
console.log("> server likes SSL:", sslIsOk);

const tlsConn = await Deno.startTls(conn, {
  hostname: "cryptogohan:deno-connect-issue",
  certFile: "./server-ca.pem",
});

await tlsConn.write(new Uint8Array([0, 0, 0, 8, 0, 3, 0, 0]));
const startUpResponseByteBuffer = new Uint8Array(1);
await tlsConn.read(startUpResponseByteBuffer);
console.log(
  "> tls response:",
  String.fromCodePoint(startUpResponseByteBuffer[0]),
);

This one doesn't work. I have a working example running with node. The difference is that Google Cloud doesn't attach DNS to their SQL instances, just IPs, to use TLS we need a hostname. On the node side we can pass a servername like shown here, with Deno we have no such option. No option means no hostname for the certificate authority to have authority over and that seems the end of the exercise. Google Cloud uses private connections between most services anyway so I'm not sure this one is even worth fixing, but perhaps a contributor feels different.

Working Node Example

const net = require("net");
const tls = require("tls");

const GCLOUD_CA = `
-----BEGIN CERTIFICATE-----
MIIDfzCCAmegAwIBAgIBADANBgkqhkiG9w0BAQsFADB3MS0wKwYDVQQuEyQ1MjVm
MWVkYS1hNjJjLTQ0ZTQtOGQyYS0xM2JkNTViZWQ0MGYxIzAhBgNVBAMTGkdvb2ds
ZSBDbG91ZCBTUUwgU2VydmVyIENBMRQwEgYDVQQKEwtHb29nbGUsIEluYzELMAkG
A1UEBhMCVVMwHhcNMjEwNjAzMTM0OTM1WhcNMzEwNjAxMTM1MDM1WjB3MS0wKwYD
VQQuEyQ1MjVmMWVkYS1hNjJjLTQ0ZTQtOGQyYS0xM2JkNTViZWQ0MGYxIzAhBgNV
BAMTGkdvb2dsZSBDbG91ZCBTUUwgU2VydmVyIENBMRQwEgYDVQQKEwtHb29nbGUs
IEluYzELMAkGA1UEBhMCVVMwggEiMA0GCSqGSIb3DQEBAQUAA4IBDwAwggEKAoIB
AQDOHgKfeh+Pj3uQxCU8J/6dCIqjL45gF2ak3C1xr7ajWBa6DqCsdr/Kq6xGYsLU
lrcuOaSOhUiepp2JnwM7u+qReXdJpSHU1EgwEetB5N6eBM/UvHFW70wKo7FQDu1O
b+WIDLVoxfMGgyCC+ETTBKATTAbj9WCcZV162rpG7Rm9Hl8xn2C/2aoHBsqft2fA
jST7XfcZvKOsseADisKKBMHPf+Knx+1Uw7+6lY170TYnuye5cXOquCLttgIkxzP+
VQzdFWa2PtCovZpGz95NMEySiale2xK1Zj2XGyGb7jmTt3JOirc6wxWDD9Vu5lUS
MmKOLJaxGmkZXhXVfUiTk5ofAgMBAAGjFjAUMBIGA1UdEwEB/wQIMAYBAf8CAQAw
DQYJKoZIhvcNAQELBQADggEBAKlNhrQXbSiy4t2O3tBHdmWedZyz0Cd1wJL1sU+R
3pJ522wVpvUgy7fc98PlP6D/x5+ATpCKv631mNwss0y5XugzkM5oESH0OaJ9c/Aq
d9SMWsbwNNaY75q0lLEgfIt+ZnCCfsVzupT0nMD3Bjp7f7qU2DyoHy5mK+X39EuW
u6svZ/4OQ+RBcejRsQNRSeMg457hH/b5/Ge7FzPmHSOKebqW4Jj5piuDHTNLzneg
aNd/iTTMzoF2h2CsxhMyu1aA9P5LVLDWEzTwHJeUszBuyc1DWL1ZNfVsIB8CHQya
eR3EoHsKdjF7mZz8t/rR1uz5y1/iGirkTCBuvp61W0333HU=
-----END CERTIFICATE-----
`;

const DB_HOST = "34.77.40.50";
const DB_PORT = 5432;

const socket = new net.Socket();

socket.on("connect", () => console.log("> socket connected"));
socket.on("close", () => console.log("> socket closed"));
socket.on("error", (err) => console.error(err));
socket.on("listening", () => console.log("listening"));
socket.setNoDelay(true);

socket.once("connect", () => {
  const sslRequest = Buffer.allocUnsafe(8);
  sslRequest.writeInt32BE(8, 0);
  sslRequest.writeInt32BE(80877103, 4);
  socket.write(sslRequest);

  socket.once("data", (res) => {
    const sslIsOk = res.toString("utf8") === "S";
    console.log("> server likes SSL:", sslIsOk);
    const tlsSocket = tls.connect({
      socket,
      ca: GCLOUD_CA,
      servername: "cryptogohan:deno-connect-issue",
    });

    tlsSocket.on("error", (error) => {
      console.error(error);
      socket.end();
    });
    tlsSocket.on("secureConnect", () => {
      console.log("> tls securely connected!");
      socket.end();
    });
    tlsSocket.on("session", console.log);
    tlsSocket.on("OCSPResponse", console.log);
  });
});

socket.connect({ port: DB_PORT, host: DB_HOST });

Digital Ocean

It's a similar set-up with a DNS attached and a cert available like AWS but in this case rustls throws an error: InvalidData: invalid certificate: BadDER, I'll go ask over there why this certificate is judged invalid.

const DB_HOST = "tmp-1-do-user-852566-0.b.db.ondigitalocean.com";
const DB_PORT = 25060;

const conn = await Deno.connect({ hostname: DB_HOST, port: DB_PORT });
console.log("> connection established");
const sslRequest = new Uint8Array([0, 0, 0, 8, 4, 210, 22, 47]);

await conn.write(sslRequest);
const sslResponseByteBuffer = new Uint8Array(1);
await conn.read(sslResponseByteBuffer);
const sslResponse = new TextDecoder().decode(sslResponseByteBuffer);
const sslIsOk = sslResponse === "S";
console.log("> server likes SSL:", sslIsOk);

const tlsConn = await Deno.startTls(conn, {
  hostname: DB_HOST,
  certFile: "./ca-certificate.crt",
});
await tlsConn.write(new Uint8Array([0, 0, 0, 8, 0, 3, 0, 0]));
const startUpResponseByteBuffer = new Uint8Array(1);
await tlsConn.read(startUpResponseByteBuffer);
console.log(
  "> tls response:",
  String.fromCodePoint(startUpResponseByteBuffer[0]),
);

Heroku

I know someone had issues with Heroku, looks like they use an AWS DB, so I assume that'll work with cert passing.

Aside

Just use a non-SSL connection if you get stuck.. that's what I would've done had it not been for Digital Ocean disallowing this and the main Postgres driver trying and crashing whilst attempting to upgrade the connection. I'll go open a corresponding issue over there to make sure non-SSL connections work at least, now that I deeply understand the intricacies of establishing SSL connections with Postgres.

[reggi]

I'm using heroku have the same issue.

Posted abut it here: eveningkid/denodb#356
Found this related here: #2301

One change with the above script it's caCerts: ["./global-bundle.pem"], now.

When I run it I get:

> connection established
> server likes SSL: true
Sending fatal alert BadCertificate
error: Uncaught (in promise) InvalidData: invalid peer certificate contents: invalid peer certificate: UnknownIssuer
await tlsConn.write(new Uint8Array([0, 0, 0, 8, 0, 3, 0, 0]));
^
    at async write (deno:ext/net/01_net.js:33:12)