Heroku Postgresql in Node-Red

General
1

Hello! Im new to Node-RED and Databases,
Windows 11
Node v18.16.1
npm v9.5.1
Node-red v3.0.2
i have an app on Heroku with Postgresql add-on and im trying to connect to it via Node-RED to send data to a table, Query, etc., Node-RED is installed on my laptop, running on 127.0.0.1:1880, when trying to connect a node to Postgres i get "Error: Self signed certificate" when i disable ssl i get something like

"error: no pg_hba.conf entry for host "192.168.1.1", (Example IP)
user "XXXXXX",
database "XXXXX", no encryption"

When trying to create that entry i found it dificult through the Heroku CLI to even get to the file, and once i was there, i couldn edit it (Not 100% sure)

Then read that i could make a new user and give it an ip to access, tried it but then realized Heroku Postgres doesnt let you create new users (Not 100% sure, like 95%)

Finally tried to use chat gpt to solve the problem but it gave me some suggestions that werent possible for me like:

 httpServerOptions: {
    https: { rejectUnauthorized: false }
  },

Checked ServerOptions documentation for its options and rejectUnauthorized isnt one of them

Spent days searching what could it be, tried a clean installation, nothing so im completely lost, i would really apreciate it if anyone could give me any tips to solve this problem

2

Welcome to the forum @Azbbo

I am confused, are you trying to connect to a database on a heroku server from node red running on your laptop?

3

Yeah, Node-RED is running on my PC, and the database is on the server

4

Are you able to access the db using different SQL client software?

5

Yes, i have python code that connects to it every 10 min and adds data, pgAdmin and Table Plus

6

What settings are you using in Python? I am confused that in the error you post it shows a local IP address rather than an external one.

7

the ip was an example to not show the public IP, sorry for the confusion, both through python and pgadmin/tableplus i can connect without a problem, make changes, read data and all

When trying to connect with Node-RED i get "Error: self-signed certificate", turning off SSL from the postgres node gets me

"error: no pg_hba.conf entry for host "182.xxx.xxx.xxx", (Example IP)
user "XXXXXX",
database "XXXXX", no encryption"

which is my public ip

Created a ticket to Heroku support to see if they could help me with this error and they provided the following code

const client= new Client({
connectionString: process.env.DATABASE_URL,
ssl:{
rejectUnauthorized: false
}
});

This is for Node.js, so i need to have a similar configuration for Node-RED, so i changed the setting.js to:

const { Client } = require('pg');

const client = new Client({
ssl: {
rejectUnauthorized: false
}
});

module.exports = {

and

functionGlobalContext: {
// os:require('os'),
client
},

didnt worked, im not sure how to add the code Heroku Support gave me to the settings.js for Node-RED to see if it works

8

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.