App cannot connect to a managed postgresql cluster on digitalocean

[conorsch]

When connecting to a localhost postgres database via the DATABASE_URL env var, the app is able to connect and query just fine. Same is true of a bespoke public postgres TLS-connected endpoint, using fullchain certs. If I try to use a DATABASE_URL pointing at a managed postgres setup, however, the app throws the error SELF_SIGNED_CERT_IN_CHAIN:

Error occurred while requesting Blocks Error: self-signed certificate in certificate chain
    at /app/node_modules/.pnpm/[email protected][email protected]/node_modules/pg-pool/index.js:45:11
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async l (/app/apps/web/.next/server/app/api/blocks/route.js:4:1302)
    at async c (/app/apps/web/.next/server/app/api/blocks/route.js:4:147)
    at async /app/node_modules/.pnpm/[email protected][email protected][email protected][email protected]/node_modules/next/dist/compiled/next-server/app-route.runtime.prod
.js:6:36258
    at async eR.execute (/app/node_modules/.pnpm/[email protected][email protected][email protected][email protected]/node_modules/next/dist/compiled/next-server/app-route.
runtime.prod.js:6:26874)
    at async eR.handle (/app/node_modules/.pnpm/[email protected][email protected][email protected][email protected]/node_modules/next/dist/compiled/next-server/app-route.r
untime.prod.js:6:37512)
    at async doRender (/app/node_modules/.pnpm/[email protected][email protected][email protected][email protected]/node_modules/next/dist/server/base-server.js:1377:42)
    at async cacheEntry.responseCache.get.routeKind (/app/node_modules/.pnpm/[email protected][email protected][email protected][email protected]/node_modules/next/dist/ser
ver/base-server.js:1599:28)
    at async NextNodeServer.renderToResponseWithComponentsImpl (/app/node_modules/.pnpm/[email protected][email protected][email protected][email protected]/node_modules/ne
xt/dist/server/base-server.js:1507:28) {
  code: 'SELF_SIGNED_CERT_IN_CHAIN'

Note that psql works just fine with this URL, so the connection problem is specific to the application code. After doing some reading, it appears there are workarounds like passing in a custom CA (which we can indeed fetch from the DO admin panel). Doing so will require updating the db connection logic in the app code:

cuiloa/apps/web/src/lib/db.ts

Lines 4 to 6 in 1268001

	const pool = new Pool({
	connectionString: process.env.DATABASE_URL,
	});

but seems reasonable.

Questions to answer:

1. Why are digitalocean managed db URLs considered deficient by the node-postgres code?
2. How can we use a digitalocean managed db URL as backend for the app?

[conorsch]

Resolved by #194. Please reopen if any problems discovered with db connections.