I'm trying to query Timescale DB from United Manufacturing Hub #umh-support

I'm trying to query Timescale DB from

Denis

01/15/2025, 6:01 PM

I'm trying to query Timescale DB from Node-RED using a postgresql node from the node-red-contrib-postgresql package. I fail to establish a connection to the database. This is my config (see image) I'm authenticating with user

kafkatopostgresqlv2

with password

changemetoo

This is the error I get:

Copy code

"error: pg_hba.conf rejects connection for host "10.42.0.106", user "kafkatopostgresqlv2", database "umh_v2", SSL off"

And with SSL enabled:

Copy code

Error: self signed certificate

Does anyone see what I'm doing wrong? https://cdn.discordapp.com/attachments/984083100520808478/1329148549358948382/image.png?ex=6789498c&is=6787f80c&hm=bb38503ce45339b7ff7c35885f7dad0a194c9b98c2d56b4e2ab6d7b24fa6e0f0&

Denis

01/15/2025, 6:03 PM

I tried with the host

localhost

as well

Jermuk

01/15/2025, 6:36 PM

Ahh, I remember this. Postgres has like 4 or 5 certificate options. By default, TimescaleDB requires an encrypted connection but only has self signed certificates. There is one nodered plug-in that doesn’t like this combination

Jermuk

01/15/2025, 6:37 PM

I remember we had something on lean for this

Jermuk

01/15/2025, 6:38 PM

https://community-discord.umh.app/t/22717618/i-m-trying-to-connect-the-timescaledb-in-a-nodered-flow-and-

Jermuk

01/15/2025, 6:39 PM

I hope this helps 🙂

DanielH

01/15/2025, 7:02 PM

this is very similar to what i have struggling with today with chirpstack. i had to reissue a new certificate with the private key for it to work. What options do you have for security? can you disable checking of the certificate issuer?

ricardo_maestas

01/15/2025, 11:07 PM

I was able to reproduce this issue @Denis. Jeremy's assertion about it being ssl certificate related is probably spot on. I figured out I can work around it by involving a traefik reverse proxy that i'm also running in my lab.

ricardo_maestas

01/16/2025, 12:10 AM

Actually I take that back... thought I had the reverse proxy working approach working but it was just taking a while to time out. I read through the conversation Jeremy linked for guidance making these changes in the pg_hba section of the helm chart

Copy code

pg_hba:
    - local     all postgres                   peer
    - hostnossl umh_v2  kafkatopostgresqlv2 0.0.0.0/0 md5
    - host      all postgres,standby 0.0.0.0/0 reject
    - host      all postgres,standby ::0/0     reject
    - hostssl   all all              0.0.0.0/0 md5
    - hostssl   all all              ::0/0     md5
    - hostnossl all all              0.0.0.0/0 reject
    - hostnossl all all              ::0/0     reject

The second line contains the fix, and it must be the 2nd item because the rules are enforced top to bottom. https://cdn.discordapp.com/attachments/1329148549593563180/1329241198380056666/Screenshot_2025-01-15_at_17.07.42.png?ex=67899fd5&is=67884e55&hm=205531ab51724347f6bed736a8257e54d8c968884d301dcb63dc1dc425368382&

ricardo_maestas

01/16/2025, 12:13 AM

https://cdn.discordapp.com/attachments/1329148549593563180/1329242123827941396/Screenshot_2025-01-15_at_17.13.26.png?ex=6789a0b2&is=67884f32&hm=95d41ae288a7be82d82e559827ec9fb691626f524d36416a9126043e7f329028&

DanielH

01/16/2025, 8:27 AM

Yes, that is an option, but if you still want to have ssl enabled you need to reissue certificates.

Jermuk

01/16/2025, 9:06 AM

https://discord.com/channels/700613971941785680/1299279592561049600/1329105291446456352

Jermuk

01/16/2025, 9:06 AM

here the link to the conversation with DanielH with a similar issue

krgrsebastian

01/20/2025, 8:21 AM

@Denis I passed this issue by just placing a Function infront of the postgresql node containging the following content: msg.pgConfig = { user: 'kafkatopostgresqlv2', // default process.env.PGUSER || process.env.USER password: 'changemetoo', // or function, default process.env.PGPASSWORD host: 'united-manufacturing-hub.united-manufacturing-hub.svc.cluster.local', // default process.env.PGHOST database: 'umh_v2', // default process.env.PGDATABASE || process.env.USER port: 5432, // default process.env.PGPORT ssl: { rejectUnauthorized: false // SSL is on but verification is not done }, } return msg; Then set in the postgresql node ssl to true and it shoudl work

Denis

01/20/2025, 8:43 AM

@krgrsebastian , worked like a charm. Thank you

2 Views

Previous Next