SSL connect to PostgreSQL

Questions
23

Sorry, I just tried it and it does not work:

2021-10-05T13:40:28.603Z | error    | There was an error initializing DB: "getaddrinfo ENOTFOUND host.redacted?sslmode=require" {"file":"start.js"}
24

Thanks for trying, I hope the solution comes quickly for you… Best,

1 Like
25

I had expected to see a lot more, Was that from the log file?

26

Yes, it’s the same in the console and the log file

27

I am just signing up for an Azure account now to see if I can get it working.

1 Like
28

Alright so…

I have set…

      - DB_TYPE=postgresdb
      - DB_POSTGRESDB_HOST=REMOVED.postgres.database.azure.com
      - DB_POSTGRESDB_PORT=5432
      - DB_POSTGRESDB_DATABASE=REMOVED
      - DB_POSTGRESDB_USER=REMOVED
      - DB_POSTGRESDB_PASSWORD=REMOVED
      - DB_POSTGRESDB_SSL_CA=/files/DigiCertGlobalRootCA.crt.pem

The certificate I have used is the one from here: Encrypted connectivity using TLS/SSL in Azure Database for PostgreSQL - Flexible Server | Microsoft Docs

In my n8n.log I have

{"level":"error","message":"There was an error initializing DB: \"self signed certificate in certificate chain\"","metadata":{"file":"start.js","timestamp":"2021-10-05T15:13:45.239Z"}}

I will keep working away and see if I can work it out.

29

ok @alakran I have it working.

So in my docker image I have /files/ mapped to a folder on my machine

    volumes:
      - /home/jonathan/docker/n8n-psql-test/.n8n:/home/node/.n8n
      - /home/jonathan/docker/n8n-psql-test/files:/files

Then in /home/jonathan/docker/n8n-psql-test/files I have the crt.pem that the Azure docs tells me to download. I then have the following env options for Postgres

      - DB_TYPE=postgresdb
      - DB_POSTGRESDB_HOST=REMOVED.postgres.database.azure.com
      - DB_POSTGRESDB_PORT=5432
      - DB_POSTGRESDB_DATABASE=postgres
      - DB_POSTGRESDB_USER=REMOVED
      - DB_POSTGRESDB_PASSWORD=REMOVED
      - DB_POSTGRESDB_SSL_CA=/files/DigiCertGlobalRootCA.crt.pem
      - DB_POSTGRESDB_SSL_REJECT_UNAUTHORIZED=false

The bottom line is getting around the self signed cert error I was getting and got everything working, I have used the flexi server option of Postgres rather than the full server option but in theory it should be the same deal.

1 Like
30

Bingo! Thanks a lot, this combination worked! :smiley:

1 Like
32

I know this thread is old, but for anyone just coming across, I advise you: please don’t do this. Setting DB_POSTGRESDB_SSL_REJECT_UNAUTHORIZED=false will result in the certificate NOT being verified and the provided CA NOT being used. That’s why it worked for @Jon and @alakran. This can be confirmed by simply removing the DB_POSTGRESDB_SSL_CA variable altogether: it will work just as well.
The correct way to handle this is to set DB_POSTGRESDB_SSL_REJECT_UNAUTHORIZED to true (or not specify it, since true is the default) and set DB_POSTGRESDB_SSL_CA_FILE instead of DB_POSTGRESDB_SSL_CA:

      - DB_POSTGRESDB_SSL_CA_FILE=/files/DigiCertGlobalRootCA.crt.pem
      - DB_POSTGRESDB_SSL_REJECT_UNAUTHORIZED=true
1 Like
33

I had the same issue with my Postgres setup. Just setting DB_POSTGRESDB_SSL_CA wasn’t enough—in my case, I had to explicitly set sslmode=require in the connection string or config. Once I added that, the SSL handshake worked and the error went away. Hope this helps someone stuck on the same step!

34

Thanks for this! I was running n8n on GCP Cloud Run and using Neon Postgres as the database and was running into SSL errors. I just had to set the DB_POSTGRESDB_SSL_CA_FILE environment variable, instead of the DB_POSTGRESDB_SSL_CA env var.

DB_POSTGRESDB_SSL_CA_FILE = /etc/ssl/certs/ca-certificates.crt
1 Like