Cannot connect to Postgres, self-signed certificate

PostgreSQL
1

Hello, I’m trying to deploy a next.js app on Vercel but I’m running into an issue. Since vercel isn’t keeping files such as ca.pem, I can’t keep the connection string that I was using locally. (…?sslmode=require&sslrootcert=ca.pem)

So I’m trying to simply keep ?sslmode=require which shouldn’t need ca.pem but then I get this error “ERROR: Error: cannot connect to Postgres. Details: self-signed certificate in certificate chain” which seem to indicate that I can’t connect to Aiven without providing ca.pem. Is there no way to connect to the db on a free plan without ca.pem ?

2

Are you able to provide the self-signed certificate and specify it as the sslrootcert?

3

I’m not sure I understand. I have the file “ca.pem” that i downloaded from Aiven in the root of my directory on github (even if it seem unsafe) but during deployment on vercel, next build doesn’t keep it.

I tried scripts in my package.json (I had pasted ca.pem as an env variable "PG_SSL_CERT) like

"postinstall": "echo \"$PG_SSL_CERT\" > ./ca.pem",

or using “prebuild”: but nothing seem to work

4

It should not be unsafe as the ca.pem file contains the public certificate that is also passed by the server in the handshake. The point is to confirm that the server is the right one. The private key that must be used to sign output as that identity is held on the server side so an attacker cannot use ca.pem to impersonate it.

My suggestion was basically what you proposed - find some way to include the file. I have no personal experience with the frameworks or services you are talking of but looking at their help it seems like an await fs.readFile (or a plain fs.readFileSync) of the ca.pem might trigger its inclusion in the app if the default tracing doesn’t do it.

2 Likes
5

Thanks for the help, I managed to have ca.pem be included in my deployment. Now, once my app is deployed sslrootcert doesn’t work since vercel has weird filepath even if I added an env variable for deployment finishing like this : &sslrootcert=/vercel/path0/ca.pem. I can’t resolve this so I’m trying to not use sslrootcert anymore but `

pool: {
connectionString: process.env.DATABASE_URI || ‘’,
ssl: {
rejectUnauthorized: true,
ca: fs.readFileSync(‘./ca.pem’).toString(),
},

having added ssl parameter should work the same as sslrootcert but once again I get self-signed certificate in certificate chain. Any idea why ? I tried to deploy to netlify too but same error. I also tried to watch some official aiven video like this one : https://www.youtube.com/watch?v=GPfRr5ObpyE&t=704s but it doesn’t include anything about certificate.

Edit : According to this example from aiven github, it should work : aiven-examples/postgresql/nodejs/example.js at main · aiven/aiven-examples · GitHub

Edit 2 : Now sslrootcert mysteriously work as intended without real change except redeploy but I get this error now : cannot connect to Postgres. Details: remaining connection slots are reserved for roles with the SUPERUSER attribute. I don’t think I can get around this on a free plan

1 Like
6

That last one sounds like it is exceeding the capacity of the server (perhaps less likely if you manually closed connections as soon as possible) and may need some kind of pool manager such as pgbouncer to restrict the flow (or yes, an expense to increase capacity).