Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Unable to connect to PostgreSQL with SSL

Hello,   I am encountering a serious issue today and I think you could help me solve it.   I already set up a machine using Windows Server 2019 with Power BI Report Server on it. We have another...
Issue
v-luwang-msft's avatar
v-luwang-msft
Community Support
4 years ago

Hi Anonymous ，

This error  main casuse by configuring the wrong pg_hba.conf file. You needed to configure the file in test-qa-db because that was where the database resided.

 connect locally with psql and run this

SHOW hba_file;

Then from outside edit that file. Perhaps you have two versions of PostgreSQL or you built your own. Or, you're restarting the wrong PostgreSQL. Perhaps make sure you're restarting the right one by connecting, again locally over psql, and running

SELECT pg_reload_conf();

Make sure you're editing the pg_hba.conf on the SERVER you're trying to connect to and not the CLIENT. 

And a tip need to notise:

The pg_hba.conf file is read on start-up and when the main server process receives a SIGHUP signal. If you edit the file on an active system, you will need to signal the postmaster (using pg_ctl reload or kill -HUP) to make it re-read the file.

 

Similar question refer:

https://dba.stackexchange.com/questions/161104/pgadmin-postgresql-no-pg-hba-conf-entry-for-host 

https://dba.stackexchange.com/questions/77099/no-pg-hba-conf-entry-for-host-10-1-1-91-user-user-database-testing-ssl-o 

 

 

Did I answer your question? Mark my post as a solution!


Best Regards

Lucien

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hello and thank you Lucien,

     

    I don't really understand what you think is wrong with my configuration file. Could you be more explicit?

     

    The connection works fine when using Power BI Desktop on another machine. When I use Power BI Report Server on this same machine, it does not work anymore.

     

    Here is my configuration : 

     

    # TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     scram-sha-256
# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
#host    all             all             163.84.146.147/32       scram-sha-256

hostssl  all  			all  			0.0.0.0/0  				scram-sha-256 
#host    all             all             0.0.0.0/0               scram-sha-256
# IPv6 local connections:
host    all             all             ::1/128                 scram-sha-256
#hostssl  all  			 all  			 ::1/128  				scram-sha-256 clientcert=verify-full
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     scram-sha-256
#host    replication     all             127.0.0.1/32            scram-sha-256
#host    replication     all             ::1/128                 scram-sha-256

     

    The only difference between Desktop and Report Server seems to be that Report Server does not support SSL connections to PostgreSQL. ("ssl off" when SSL is required in log message)

     

    Thank you.