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

[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,...?