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?

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

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
            }
        }
    

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

Submit an Answer