Heroku setup error "NodeBB could not connect to your PostgreSQL database"

nodebber

@gotwf What i found out is that adding the following ssl-param to the connection settings allows to run "./nodebb setup" (locally) and to populate the database.

ssl: {
rejectUnauthorized: false
}

After finishing the setup i am pushing the configuration files to heroku like mentioned in the instructions. There at heroku the connection problem occurs again and leads to an app crash.

PitaJ

@nodebber have you double checked the config.json at heroku still has that option?

gotwf

Sorry but I have no experience with Heroku. Clueless there. Sure seems like something may well be amiss with your pg_hba.conf though.

Maybe this will help shed some light:

Chapter 20. Client Authentication

Chapter 20. Client Authentication Table of Contents 20.1. The pg_hba.conf File 20.2. User Name Maps 20.3. Authentication Methods 20.4. Trust Authentication 20.5. …

PostgreSQL Documentation (www.postgresql.org)

Season for whatever version of postgres you/heroku is running.

Postgres has many options for authenticating users wh/act as gatekeepers to the pgsql server itself. In addition to that, users must also meet role and database access restrictions after connecting. You are not connecting. So that is either pg_hba.conf server side or something amiss nodebb app side. I'd try to verify former before tearing my hair out on the latter?

How to troubleshoot this on Heroku, I do not know. Seems you do not have access to common config files, e.g. pg_hba.conf so kind of groping about in the dark.

Sorry I do not have more precisely targeted solution. Just tossin' some stuff out there that may help you look in the right directions. Hopefully.

Update: Sure seems like it is postgres conf side though:

PostgreSQL returned the following error: no pg_hba.conf entry for host "XXX", user "XXX", database "XXX", SSL off

Cuz, presuming no typo in your XXX settings aboove, how/why else could/would postgres return that error if you're not even talking to it?

nodebber

@pitaj Yes, i checked the config.json and the option was still there. I also tried different other params found at stackoverflow and elsewhere but none worked.

I am also not sure if the ssl-param has the right notation and place. In the heroku docs they mention the connection string should contain an additional ssl-param and give an example of the Client class of the pg module (see here). In the NodeBB version (12) i want to use the Pool class of the module is adressed for the postgres connection (as far as i understand this).

To get ./nodebb setup running i added the ssl-param to the connection options in postgres.js (connOptions.ssl = {rejectUnauthorized: false};) but this is not working after pushing it to heroku. So i am asking myself what part of the code where connections are set up i have missed.

nodebber

@gotwf Thanks for your suggestions. The strange thing is that i can access the database when running ./nodebb setup and successfully populate it from my local device but not connect from the heroku app.

Sounds like there are two different ways connections are established when ./nodebb setup and ./nodebb start are called but i only found one file till now where this is handled (postgres.js in nodebb version 12).

I will have a deeper look into the postgres documentation - thanks for the link.

PitaJ

@nodebber just to confirm, it works when you do

local nodebb -> heroku pg

But not when you do

heroku nodebb -> heroku pg

nodebber

@pitaj Yes i can run ./nodebb setup on my local device but when i push to heroku and run ./nodebb start there the app is crashing.

PitaJ

@nodebber what error do you see on startup?

nodebber

@pitaj After running ./nodebb start the app crashes and i find in logs/output.log the message

node:40) UnhandledPromiseRejectionWarning: error: no pg_hba.conf entry for host "34.XXX.XXX.XXX", user "XXX", database "XXX", SSL off
    at Connection.parseE (/app/node_modules/socket.io-adapter-postgres/node_modules/pg/lib/connection.js:555:11)
    at Connection.parseMessage (/app/node_modules/socket.io-adapter-postgres/node_modules/pg/lib/connection.js:380:19)
    at Socket.<anonymous> (/app/node_modules/socket.io-adapter-postgres/node_modules/pg/lib/connection.js:120:22)
    at Socket.emit (events.js:198:13)
    at addChunk (_stream_readable.js:288:12)
    at readableAddChunk (_stream_readable.js:269:11)
    at Socket.Readable.push (_stream_readable.js:224:10)
    at TCP.onStreamRead [as _originalOnread] (internal/stream_base_commons.js:94:17)
    at TCP.onread (/app/node_modules/async-listener/glue.js:188:31)
(node:40) UnhandledPromiseRejectionWarning: Unhandled promise rejection. This error originated either by throwing inside of an async function without a catch block, or by rejecting a promise which was not handled with .catch(). (rejection id: 1)
(node:40) [DEP0018] DeprecationWarning: Unhandled promise rejections are deprecated. In the future, promise rejections that are not handled will terminate the Node.js process with a non-zero exit code.
2021-03-09T22:48:23.264Z [48283/40] - error: uncaughtException: Connection terminated unexpectedly
Error: Connection terminated unexpectedly
    at Connection.con.once (/app/node_modules/socket.io-adapter-postgres/node_modules/pg/lib/client.js:200:9)
    at Object.onceWrapper (events.js:286:20)
    at Connection.emit (events.js:198:13)
    at Socket.<anonymous> (/app/node_modules/socket.io-adapter-postgres/node_modules/pg/lib/connection.js:130:10)
    at Socket.emit (events.js:203:15)
    at endReadableNT (_stream_readable.js:1145:12)
    at /app/node_modules/async-listener/glue.js:188:31
    at process._tickCallback (internal/process/next_tick.js:63:19) {"error":{},"stack":"Error: Connection terminated unexpectedly\n    at Connection.con.once (/app/node_modules/socket.io-adapter-postgres/node_modules/pg/lib/client.js:200:9)\n    at Object.onceWrapper (events.js:286:20)\n    at Connection.emit (events.js:198:13)\n    at Socket.<anonymous> (/app/node_modules/socket.io-adapter-postgres/node_modules/pg/lib/connection.js:130:10)\n    at Socket.emit (events.js:203:15)\n    at endReadableNT (_stream_readable.js:1145:12)\n    at /app/node_modules/async-listener/glue.js:188:31\n    at process._tickCallback (internal/process/next_tick.js:63:19)","exception":true,"date":"Tue Mar 09 2021 22:48:23 GMT+0000 (Coordinated Universal Time)","process":{"pid":40,"uid":52322,"gid":52322,"cwd":"/app","execPath":"/app/.heroku/node/bin/node","version":"v10.16.3","argv":["/app/.heroku/node/bin/node","/app/app.js"],"memoryUsage":{"rss":153505792,"heapTotal":114143232,"heapUsed":80573096,"external":435084}},"os":{"loadavg":[1.85888671875,1.759765625,2.30126953125],"uptime":1139164},"trace":[{"column":9,"file":"/app/node_modules/socket.io-adapter-postgres/node_modules/pg/lib/client.js","function":"Connection.con.once","line":200,"method":"once","native":false},{"column":20,"file":"events.js","function":"Object.onceWrapper","line":286,"method":"onceWrapper","native":false},{"column":13,"file":"events.js","function":"Connection.emit","line":198,"method":"emit","native":false},{"column":10,"file":"/app/node_modules/socket.io-adapter-postgres/node_modules/pg/lib/connection.js","function":null,"line":130,"method":null,"native":false},{"column":15,"file":"events.js","function":"Socket.emit","line":203,"method":"emit","native":false},{"column":12,"file":"_stream_readable.js","function":"endReadableNT","line":1145,"method":null,"native":false},{"column":31,"file":"/app/node_modules/async-listener/glue.js","function":null,"line":188,"method":null,"native":false},{"column":19,"file":"internal/process/next_tick.js","function":"process._tickCallback","line":63,"method":"_tickCallback","native":false}]}.....

`

gotwf

@nodebbr On a related note: I am curious as to the motivation behind your decision to use Postgres rather than MongoDB. Kind of in same boat myself but not able to do testing at present.

nodebber

@gotwf said in Heroku setup error "NodeBB could not connect to your PostgreSQL database":

I am curious as to the motivation behind your decision to use Postgres rather than MongoDB. Kind of in same boat myself but not able to do testing at present.

Just because i had less experience with nosql databases.

nodebber

@nodebber I contacted herokus support regarding my the connection problem and they found a solution. I had to set a new environment variable named "PGSSLMODE" to the value "require" and afterwards to restart my app / dyno.

As it took me some days to find this out i would suggest an update of the heroku install documentation to prevent future suffering.

Is there a official way to make documentation prosals - maybe as github issue?

PitaJ

@nodebber yeah an issue on NodeBB/docs

julian

@nodebber said in Heroku setup error "NodeBB could not connect to your PostgreSQL database":

Is there a official way to make documentation prosals - maybe as github issue?

Indeed! If you want to make a pull request to the repo @PitaJ linked to, then you will be contributing back and ensuring other users will not experience similar troubles

nodebber

The problem resulted from a change on side of heroku that now (early 2021) require ssl for all heroku-postgres addons.

To solve the problem for nodeBB 1.12 i had to do the following:

set a environment variable at heroku - an advice i got from the heroku support team and found (in herokus documentation it is noted as "PGSSLMODE" = "no-verify" which did not worked for me)

"PGSSLMODE" = "require"

add a ssl param to the postgres connection in src/database/postgres.js before creating a new Pool (two appearances in nodeBB 1.12)

+	connOptions.ssl =  {"rejectUnauthorized": false}
	const db = new Pool(connOptions);

add ssl param as json object to the postgres json object in the config.json

"postgres" {
    ....
    "database": "DBNAME",
+   "ssl" { "rejectUnauthorized": false }
}
...

set my local node version to 10.16.3 for populating the database running ./nodebb setup locally like described in nodebb's documentation
set the node version to 10.16 in package.json before pushing to heroku

To solve the problem for nodeBB 1.16 i had to do the following:

set a environment variable at heroku

"PGSSLMODE" = "require"

add a ssl param to the postgres connection in src/database/postgres.js and src/database/postgres/connection.js before creating a new Pool

+	connOptions.ssl =  {"rejectUnauthorized": false}
 	const db = new Pool(connOptions);

add ssl param as json object to the postgres json object in the config.json

"postgres" {
    ....
    "database": "DBNAME",
+   "ssl" { "rejectUnauthorized": false }
}

set my local node version to 14.16.0 for populating the database running ./nodebb setup locally like described in nodebb's documentation
set the node version to 14.16 in package.json before pushing to heroku

nodebber

@julian Thanks for the invitation to contribute. Is there something i should consider regarding the issue / pull request - markup, document structure,...?