Postgres managed DB require pg_hba.conf

May 12, 2019 763 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?

1 Answer

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.

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

  • 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
            }
        }
    
Have another answer? Share your knowledge.