AWS Postgres with n8n: unable to get local issuer certificate

Hello,

We are trying to connect our N8N with our managed AWS Postgres. It works perfectly without SSL enabled, but when we enable the SSL, we get the following error: " unable to get local issuer certificate". We are using the RDS CA and set this option “DB_POSTGRESDB_SSL_REJECT_UNAUTHORIZED” to false as recommended in [1]

In our Dockerfile, we are passing the following environmental variables as follows:

ENV DB_POSTGRESDB_SSL_CA=/home/node/rds-ca-2019-root.pem
ENV DB_POSTGRESDB_SSL_REJECT_UNAUTHORIZED=false

ADD https://s3.amazonaws.com/rds-downloads/rds-ca-2019-root.pem /home/node/

[1] Databases | Docs

Best,
Laurent

It might sound silly but… does n8n have access to the path with your root pem in it?

“-rw----r-- 1 root root 1.4K Sep 4 2019 rds-ca-2019-root.pem”
→ n8n has the right to read this file.

I have no experience in running Postgres over SSL, but shouldn’t you use the DB_POSTGRESDB_SSL_CERT key instead of DB_POSTGRESDB_SSL_CA?

Another important point is that your n8n instance needs to be able to connect to the Certificate Authority (which I believe requires internet access). That is also worth checking in your networking settings.

Let me know if you make progress. I hope this helps.

1 Like

Hi krynble,

Unfortunately, this does not work :frowning:

From what I understand after reading this Medium article [1], the “ssl.ca” entry should point to the AWS RDS root certificate, meaning “ssl.ca” in the n8n code [1]. And this variable is initialized from the environmental variable “DB_POSTGRESDB_SSL_CA”

[1] n8n/Db.ts at f29950ee819c339f65fe8aa5096c4fc7347f580e · n8n-io/n8n · GitHub

[2] How to establish a secure connection (SSL) from a Node.js API to an AWS RDS | by Fabricio Pautasso | Nexton | Medium

Best,
Laurent

Hi @jan,

Afais DB_POSTGRESDB_SSL_CA accepts a certificate and not a certificate pathname as highlighted in the documentation [1] ? Could we please update the code as follows:

Before [2]

ssl = {
   ca: sslCa || undefined,
   cert: sslCert || undefined,
   key: sslKey || undefined,
   rejectUnauthorized: sslRejectUnauthorized,
};

After

ssl = {
   ca: fs.readFileSync(sslCa).toString() || undefined,
   cert: sslCert || undefined,
   key: sslKey || undefined,
   rejectUnauthorized: sslRejectUnauthorized,
};

[1] Databases | Docs
[2] n8n/Db.ts at f29950ee819c339f65fe8aa5096c4fc7347f580e · n8n-io/n8n · GitHub

Best,
Laurent

Did you try to append _FILE so DB_POSTGRESDB_SSL_CA_FILE?

It should then read the value from the file with the given path.

Hi jan,

I am not seeing this variable being defined in the code [1]

[1] n8n/index.ts at master · n8n-io/n8n · GitHub

Best,
Laurent

Because it is not. Would not be great to have to define every variable twice just for that. It simply checks on runtime for each of them if it exists or not:

@laurent did you try? Does it now work as expected?

Hello Jan,

We found the issue. In the Postgres node (via UI), we cannot specify the CA file when enabling the SSL mode. By modifying the code as follows, we made it work.

async execute(this: IExecuteFunctions): Promise<INodeExecutionData[][]> L255 (Postgres.node.ts)

if (credentials.allowUnauthorizedCerts === true) { // L272
  config.ssl = {
    rejectUnauthorized: false,
    ca: fs.readFileSync("/home/node/rds-ca-2019-root.pem", 'utf8')
   };
} else {
   config.ssl = {ca: fs.readFileSync("/home/node/rds-ca-2019-root.pem", 'utf8')}
   config.sslmode = (credentials.ssl as string) || 'disable';
}

Best,
Laurent

1 Like

Hi Jan,

Let us know if you need some help to code this functionality.

Best,
Laurent

Hey @laurent

So just to clarify, are you trying to use SSL to a Postgres instance that is being used as n8n’s backend or are you trying to set up SSL for a node that connects to a Postgres instance on AWS, with no relation to n8n’s backend database?

In the first case, the suggestion from Jan solves the issue, but for the second case, then it is really not possible at the moment. In this case, instead of reading from a fixed file I would suggest pasting in the contents of the files as part of the credentials. This would work better.

Hi krynble,

“”"
you trying to set up SSL for a node that connects to a Postgres instance on AWS, with no relation to n8n’s backend database
“”"
→ we are trying to address this issue.

“”"
In this case, instead of reading from a fixed file I would suggest pasting in the contents of the files as part of the credentials. This would work better.
“”"
→ both ways would work for us :wink:

Best,
Laurent