Hi, I’m trying to connect to a postgres database which has SSL required. I don’t know how to configure it, because I tried configuring DB_POSTGRESDB_SSL_CA with the certificate but it keeps telling me the following error:
There was an error initializing DB: “SSL connection is required. Please specify SSL options and retry.”
Using another languages/platforms I only need to put sslmode=require, but I don’t know how to do it here.
Hey @alakran,
Welcome to the community 
Did you configure the DB_POSTGRESDB_SSL_CERT env as well? The CA env is for the passing the Certificate Authority and the CERT env is for passing the certificate.
You can find more information here: Environment Variables | Docs
Hey @alakran,
Just to check are you connecting to Postgres for n8ns internal data or are you trying to connect to Postgres using the Node in a workflow?
@jon I’m trying to connect n8n to its internal data
Perfect, So it will probably be the key you have found and the one that @harshil1712 has mentioned, It is a bit odd that there is no bool option for using SSL/TLS but looking at the code it will automatically use it if any of the SSL options are set.
@harshil1712 thanks!
I wasn’t using that env variable, but I don’t know how to use it in my case:
I’m using a postgres database on Azure and I don’t have any cert other than the CA’s certificate:
https://www.digicert.com/CACerts/BaltimoreCyberTrustRoot.crt.pem
If I put that cert on both env vars DB_POSTGRESDB_SSL_CA and DB_POSTGRESDB_SSL_CERT, it gives me the following error:
There was an error initializing DB: “error:0909006C:PEM routines:get_name:no start line”
@alakran Do you not have a certificate configured on your Postgres instance?
@Jon I don’t think so. I don’t see it on the Azure config, my other applications are using sslmode=require to connect and the Azure tutorials only use the CA cert
Maybe this question is relevant
@alakran that is strange, I have taken a look at the Azure documentation and they seem to only provide the CA cert for the CA they use so no cert file itself which is annoying.
In theory just having one of the options set should be enough to get it working, Annoyingly I don’t have an Azure instance to play with so we may need to wait for @harshil1712.
Hi Jon,
At the end of the connection URL, put ?sslmode=require
https://mypostgres.azure.com:5432?sslmode=require
This is the simplist fix and shouldn’t require the CA.
Most URL options that work for the JDBC connection string work on the URL line as part of the query.
Also, be sure to sub the port for 5432 if yours is different.
Hope this helps,
That is good to know, Does it work for n8n as well with the initial database creation?
It should yes, that has been my experience with Jira Server. I haven’t spun a private n8n instance yet, but will soon and I would expect that’s part of it.
There are some other options besides require too if you need deeper security.
Example of other sslmode variables.
Just a random blog with a good list of the options.
In my experience, most cloud DB’s need at least “require”.
Ah ok, So there is a bit of a difference between Jira and n8n. n8n doesn’t use JDBC for the connection it uses a javascript library called TypeORM. Looking at the Db.ts file it enables the SSL option if any of the options are set.
if (sslCa !== '' || sslCert !== '' || sslKey !== '' || !sslRejectUnauthorized) {
ssl = {
ca: sslCa || undefined,
cert: sslCert || undefined,
key: sslKey || undefined,
rejectUnauthorized: sslRejectUnauthorized,
};
}
TypeORM passes this back to Node-Postgres which should then be handling it.
I am wondering if maybe the environment variable is not being picked up properly or has not been set correctly.
@alakran would you be able to share how you are launching n8n.
Thanks Jon, I see now… I thought we were talking the Postgres Node, now I see I overlooked the post talking internal connection.
The URL trick may still work in my experience, regardless of it not being JDBC… I have used this with other tools like Navicat with success. (It would be ironic if that was a JS/Java app, I don’t know…)
But I get it might not even apply here…
I’m launching n8n in a docker container. I’m using this variables:
- DB_TYPE=postgresdb
- DB_POSTGRESDB_HOST=REDACTED
- DB_POSTGRESDB_PORT=5432
- DB_POSTGRESDB_DATABASE=n8n
- DB_POSTGRESDB_USER=postgres
- DB_POSTGRESDB_PASSWORD=REDACTED
- DB_POSTGRESDB_SSL_CA=/n8n/BaltimoreCyberTrustRoot.crt.pem
- DB_POSTGRESDB_SSL_CERT=/n8n/BaltimoreCyberTrustRoot.crt.pem
In my entrypoint I just do some stuff and start n8n at the end with n8n command.
My base image is python:3.7 and I install n8n in it like this:
RUN curl -sL https://deb.nodesource.com/setup_14.x | bash -
RUN apt-get update && apt install -y nodejs build-essential
RUN npm install node-gyp n8n -g --unsafe-perm
If I use sqlite it works fine
I wonder if maybe it can’t read the CA file, Can you remove the SSL_CERT option and add in
N8N_LOG_LEVEL=debug
N8N_LOG_OUTPUT=console,file
Then remake the image and check <n8nFolderPath>/logs/n8n.log to see if it has anything else in there (or remove anything important and paste it).
Just wondering if you tried adding a query string to the host variable as I suggested?
DB_POSTGRESDB_HOST=my.host.com?sslmode=require
I’ll have to be doing this myself soon, so it would be good to know if this doesn’t work.
Best,
Done!
I think the file should be read because it has full permissions and it’s shown when I list the directory before starting n8n.
This is the log shown before the container dies:
2021-10-05T13:30:23.804Z | info | Initializing n8n process {"file":"start.js"}
2021-10-05T13:30:23.810Z | info |
n8n now checks for new versions and security updates. You can turn this off using the environment variable N8N_VERSION_NOTIFICATIONS_ENABLED to "false"
For more information, please refer to https://docs.n8n.io/getting-started/installation/advanced/configuration.html
{"file":"start.js"}
UserSettings were generated and saved to: /root/.n8n/config
2021-10-05T13:30:24.938Z | debug | No codex available for: ElasticSecurity.node.js {"file":"LoadNodesAndCredentials.js","function":"addCodex"}
2021-10-05T13:30:28.340Z | debug | No codex available for: Misp.node.js {"file":"LoadNodesAndCredentials.js","function":"addCodex"}
2021-10-05T13:30:29.131Z | debug | No codex available for: Netlify.node.js {"file":"LoadNodesAndCredentials.js","function":"addCodex"}
2021-10-05T13:30:29.135Z | debug | No codex available for: NetlifyTrigger.node.js {"file":"LoadNodesAndCredentials.js","function":"addCodex"}
2021-10-05T13:30:29.185Z | debug | No codex available for: N8nTrainingCustomerDatastore.node.js {"file":"LoadNodesAndCredentials.js","function":"addCodex"}
2021-10-05T13:30:29.189Z | debug | No codex available for: N8nTrainingCustomerMessenger.node.js {"file":"LoadNodesAndCredentials.js","function":"addCodex"}
2021-10-05T13:30:32.905Z | error | There was an error initializing DB: "Connection terminated unexpectedly" {"file":"start.js"}
2021-10-05T13:30:32.907Z | info |
Stopping n8n... {"file":"start.js","function":"stopProcess"}