Strongloop Heroku Postgres setup SSL error

Question

I am new to the world of Node.js, and have setup an app running on Heroku(free) using StrongLoop. I setup the heroku postgresql addon (free tier), and tried to add the datasource to StrongLoop's arc composer UI. This UI updates the server/datasources.json. When I try connecting to my datasource I get this error:

no pg_hba.conf entry for host "X.X.X.X", user "myUser", database "mydb", SSL off

I understand that the problem must be with setting up SSL on postgres. The closest StrongLoop documentation doesn't quite discuss this: https://strongloop.com/strongblog/postgresql-node-js-apis-loopback-connector/ ... Because I'm using StrongLoop rather than just straight Node.js, Heroku's documentation also left me lacking https://devcenter.heroku.com/articles/heroku-postgresql#connecting-in-node-js. I'm confused as to what I need to do exactly from here.

I have fairly simplistic newsfeed type JSON data that I manipulate with handlebars. So if it's an issue with being on the free tier, I'm open to other free suggestions with my setup. I appreciate your help.

Edit, datasources.json:

{"db":{"name":"db","connector":"memory"},
"mydb":{
"host":"myhost",
"port":####,
"url":"myamazonawsurl:####/mydbname",
"database":"mydbname",
"password":"mypw",
"name":"mydatasourcename",
"ssl":true,
"user":"myuser",
"connector":"postgresql"}}

More error details:

error: no pg_hba.conf entry for host "X.X.X.X", user "myuser", database "mydb", SSL off at 
Connection.parseE (c:\myroot\node_modules\loopback-connector-postgresql\node_modules\pg\lib\connection.js:539:11) at 
Connection.parseMessage (c:\myroot\node_modules\loopback-connector-postgresql\node_modules\pg\lib\connection.js:366:17) at 
Socket.<anonymous> (c:\myroot\node_modules\loopback-connector-postgresql\node_modules\pg\lib\connection.js:105:22) at 
Socket.emit (events.js:107:17) at readableAddChunk (_stream_readable.js:163:16) at 
Socket.Readable.push (_stream_readable.js:126:10)

score 5 · Accepted Answer · answered Jun 05 '16 at 17:52

5

It should be "?ssl=true" not "?sslmode=require"

"devpostgresql": {
    "url": "postgres://user:password@ec2-54-228-226-93.eu-west-1.compute.amazonaws.com:5432/dbname?ssl=true",
    "name": "devpostgresql",
    "connector": "postgresql"
  }

answered Jun 05 '16 at 17:52

Madi Anas

66
2
5

elithrar · Answer 2 · 2015-07-15T11:02:49.767

3

Based on the article you've linked, you'll need to modify your datasources.json configuration to suit your Heroku environment.

Get your details from heroku pg:credentials DATABASE_URL which will spit out the below (without the place-holders I've used, of course!):

postgres://user:password@ec2-host.compute-1.amazonaws.com:5432/your-db-name

Paste that into datasources.json:

  "accountDB": {
     "connector": "postgresql",
     "url": "postgres://user:password@ec2-host.compute-1.amazonaws.com:5432/your-db-name?sslmode=require"
  }

The PostgreSQL docs for Loopback provide some further details - http://docs.strongloop.com/display/public/LB/PostgreSQL+connector - but the above should get you started.

edited Jul 15 '15 at 11:02

answered Jul 15 '15 at 06:10

elithrar

23,364
10
85
104

I tried that with no luck: {"db":{"name":"db","connector":"memory"},"mydb":{"host":"myhost","port":####,"url":"myamazonawsurl:####/mydbname","database":"mydbname","password":"mypw","name":"mydatasourcename","ssl":true,"user":"myuser","connector":"postgresql"}} – woodlumhoodlum Jul 15 '15 at 06:58
Can you update your question with what you put in datasources.json? Comments don't format JSON well. If you can also post the error you get it'll help. Based on what you pasted I don't think you've entered it correctly. – elithrar Jul 15 '15 at 06:59
yeah, sorry about that. Added them. StrongLoop's UI automatically places the connection strings in their fields in the datasources.json. I manually added the "ssl": true to the file. – woodlumhoodlum Jul 15 '15 at 07:10
1

Please note that `url` property shadows other configuration attributes. You should make them part of the url as query params, for example: "url": "postgres://user:password@ec2-host.compute-1.amazonaws.com:5432/your-db-name?ssl=true", – Raymond Feng Jul 15 '15 at 07:10
right @RaymondFeng I agree it's much cleaner, but beyond adding the ssl key there is still something on heroku side that must need configuring. – woodlumhoodlum Jul 15 '15 at 07:17
@woodlumhoodlum See the update. Specify SSL as part of the connection string. The error log you posted shows that SSL was still set to off. – elithrar Jul 15 '15 at 11:05
identical error when I added "?sslmode=require" to the connection string – woodlumhoodlum Jul 16 '15 at 03:08

score 2 · Answer 3 · answered Feb 14 '16 at 04:32

2

Require SSL for Postgres connections by setting the following environment variable in command line:

$ export PGSSLMODE=require

answered Feb 14 '16 at 04:32

Lieblingsfarbe

21
2

score 1 · Answer 4 · answered Aug 16 '18 at 17:05

1

I ran into similar problem myself and I found this solution to fix it for me. Similar to @Lieblingsfarbe's answer, but this is in Heroku CLI.

Go into Heroku CLI -> your app directory -> issue this command:

heroku config:set PGSSLMODE=require

Reference: Found it here after a few hours of trying and researching (https://stackoverflow.com/a/27732431/7430591)

answered Aug 16 '18 at 17:05

GameBoy

111
7

This worked!! Thank you so much!! Heroku, if you are on here, please put this in the docs for upgrading DB plans... – ohjeeez Apr 24 '19 at 15:29

score 0 · Answer 5 · edited Oct 01 '18 at 08:20

0

I tried doing all above options, but in my case I was using wrong driver for postgresql.

After checking my postgresql version, which is 10.4, I had to use the gradle configuration below:

compile group: 'org.postgresql', name: 'postgresql', version: '42.2.5'

edited Oct 01 '18 at 08:20

sɐunıɔןɐqɐp

3,332
15
36
40

answered Oct 01 '18 at 07:13

nimesh makwana

121
1
4

Strongloop Heroku Postgres setup SSL error

5 Answers5