Question

Postgres managed DB require pg_hba.conf

Posted May 12, 2019 2.5k views
PostgreSQL

I have set up a managed postgres db v11.

I was provided with connection details. I can connect through pqsl client no problem.

However, when I want to connect a docker container to a database, I get the following error. 

ERROR { error: no pg_hba.conf entry for host "host_ip", user "user_name", database "database_name", SSL off 
at Connection.parseE (/app/node_modules/pg/lib/connection.js:553:11)

I believe I have to update the pg_hba.conf file, however, I am unable to grant myself (the login user) a superuser role to even view the file or create a user who is a superuser

ERROR: must be superuser or a member of pg_read_all_settings to examine "hba_file"

Since this is a managed service and I do not have access to the host, is it possible to access this file elsewhere?

Add a comment
whalelephantK
By:
whalelephantK
Add a comment

These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.

2 answers
dceddia May 13, 2019

I just got this error too, while trying to run a migration with Knex.js. I think the crucial problem is the “SSL off” part. If you notice, the connection string that DO provides has “sslmode=require”, so I’m assuming the server just won’t allow non-SSL connections even if you’re in the pg_hba.conf.

In my case, I was able to fix it by adding these lines to my knexfile.js, which force SSL mode on.

Make sure whatever client/library you’re using is respecting the SSL setting, and make sure it’s on.

Reply Report
  • bloodsportjr May 14, 2019

    Thanks for you reply friend. It really helped me to find the solution to my problem!! Thanks a lot!

    Reply Report
  • whalelephantK May 14, 2019

    Thank you so much!

    In my case I had a connection obj so I had to do 2 things; 1 - add ssl:true key value pair and 2 - add ssl=true at the end of the URL

    ENV: {
        client: 'pg',
        debug: true,
        ssl: true,
        connection: 'XXX?sslmode=require&ssl=true',
        migrations: {
            directory: XXX
        },
        seeds: {
            directory: XXX
        }
    }
    Reply Report
oldjoy December 31, 2019

Does anyone have a example for node-postgres to connect to managed postgres DB over SSL? I only can get hold of the ‘ca-certificate’ on cloud control panel but this doc asks for more https://node-postgres.com/features/ssl

Reply Report
Submit an Answer

Related

Looking for something else?

Ask a new question Search for more help