connect to PostgreSQL server: FATAL: no pg_hba.conf entry for host

  • Hello I am trying to run a website sent to me but after doing so this error appeared

    connect to PostgreSQL server: FATAL: no pg_hba.conf entry for host "4X.XXX.XX.XXX", user "userXXX", database "dbXXX", SSL off in C:\xampp\htdocs\xmastool\index.php on line 37
    

    after Googling it it says that i just need to add an entry in the pg_hba.conf file for that particular user. this is my pg_hba.conf file.

    # TYPE  DATABASE        USER            ADDRESS                 METHOD
    # IPv4 local connections:
    local dbXXX userXXX md5
    host    dbXXX  userXXX  XX.XXX.XXX.XXX           md5
    host    all             all             127.0.0.1/32            md5
    # IPv6 local connections:
    host    all             all             ::1/128                 md5
    # Allow replication connections from localhost, by a user with the
    # replication privilege.
    #host    replication     postgres        127.0.0.1/32            md5
    #host    replication     postgres        ::1/128                 md5
    

    but after doing so, the error still persist. I restarted my XAMPP server several times but no changes appears. Thanks in advance

    Did you restart PostgreSQL its self though, not just the web server? Try `SELECT pg_reload_conf()`.

  • Add or edit the following line in your postgresql.conf :

    listen_addresses = '*'
    

    Add the following line as the first line of pg_hba.conf. It allows access to all databases for all users with an encrypted password:

    # TYPE DATABASE USER CIDR-ADDRESS  METHOD
    host  all  all 0.0.0.0/0 md5
    

    Restart Postgresql after adding this with service postgresql restart or the equivalent command for your setup.

    Note that for this to work, a password must have been set for the user: `sudo -u psql postgres` , then `\password` at the resultant sql prompt will prompt for setting one.

    Don't forget to restart postgresql after adding this: service postgresql restart

    If you want a little extra security limiting the connections to private domains (such as within an office, and not the rest of the Internet), you can change the first number to 10, 172 or 192 to match your network: for example 172.0.0.0/0 instead of 0.0.0.0/0

    You also need to add a line with an address of `::0/0`, otherwise connections from IPv6 addresses will still be rejected.

    Note that this it WORST PRACTICE in terms of security. Try to make things work precisely and not just avoid issues. so add you server IP instead of 0.0.0.0/0 or at least narrow the IP Class

    It is in secure and should be more isolated, but it gets one up and running.

    I would add one other best practice when editing .conf files for any application, be sure to first make a backup copy ending with .bak or similar. Then in the edited file sandwich your edits in comment lines to note begin and end of your changes as well as a comment line for the purpose and who to contact. This will save you much trouble later. Be sure to make notes of what was changed and in what files, where, and put those notes in a consistent place that is related and locatable.

  • This solution works for IPv4 / IPv6

    nano /var/lib/pgsql/data/pg_hba.conf

    add at final

    host all all      ::1/128      md5
    host all postgres 127.0.0.1/32 md5
    

    and then restart postgresql service

    /etc/init.d/postgresql restart
    

    only worked when I used `::0/0`

  • The way I solved this was:

    Added the line as below in pg_hba.conf:

    hostnossl    all          all            0.0.0.0/0  trust        
    

    and this was modified in postgresql.conf, as shown:

    listen_addresses = '*'  
    

    I had this instance running on a Centos 7.3 and Postgres 9.5 in a VM in Azure, given this was a POC (proof of concept) you won't want to connect without SSL in your actual prod environment.

    To connect to the instance I was using pgAdmin 4 on macOS Sierra.

    Not only SSL... on anything that is not a proof of concept and is network acessible, you would not *trust* connections, you would require some *authentication*.

  • Fresh Postgres 9.5 install, Ubuntu.

    The key was the local connection type, since psql uses domain socket connection.

    pg_hba.conf

    # TYPE DATABASE USER CIDR-ADDRESS  METHOD
    local all all md5
    host  all  all 0.0.0.0/0 md5
    
    1. Add the following line in the bottom of pg_hba.conf:

      hostnossl all all 0.0.0.0/0 md5

    2. Add/modify the line in postgresql.conf:

      listen_addresses = '*'

    3. MAKE SURE THAT the user that is connecting has a password: (Example connect user named postgres)

      a. Run the following psql command with the postgres user account:

      sudo -u postgres psql postgres

      b. Set the password:

      # \password postgres

    enter image description here

    Don’t use trust for remote connections

  • I had the same error when I tried to connect to a local database using an SSH tunnel. I solved it by changing the host name from localhost to 127.0.0.1.

  • Instructions for Debian users.

    Login as posgres user:

    $ sudo su - postgres
    

    Get the location of pg_hba.conf by quering the database:

    $ psql -c "SHOW hba_file;"
    
                  hba_file               
    -------------------------------------
    /etc/postgresql/11/main/pg_hba.conf
    (1 row)
    

    Open pg_hba.conf:

    nano /etc/postgresql/11/main/pg_hba.conf
    

    Add configuration where it says "Put your actual configuration here":

    # TYPE DATABASE USER CIDR-ADDRESS  METHOD
    host  all  all 0.0.0.0/0 md5
    

    Logout to your user:

    $ exit
    logout
    

    Restart your postgres server for changes to take effect:

    $ sudo systemctl restart postgresql
    
  • Find the correct configuration file:

    su - postgres -c "psql -t -P format=unaligned -c 'show hba_file';"
    

    Add the following at the end of file:

    local all all peer
    

    Then restart your PostgreSQL application:

    /bin/systemctl restart postgresql*.service
    
  • This below worked for me: (pg_hba.conf)

    # TYPE  DATABASE        USER            ADDRESS                 METHOD
    # "local" is for Unix domain socket connections only     
    local   all             all                                     md5
    # IPv4 local connections:
    host    all             all             127.0.0.1/32            trust
    host    all             all             0.0.0.0/0               trust
    # IPv6 local connections:
    host    all             all             ::1/128                 trust
    host    all             all             0.0.0.0/0               trust
    

    trust

    Allow the connection unconditionally. This method allows anyone that can connect to the PostgreSQL database server to login as any PostgreSQL user they wish, without the need for a password or any other authentication.

    md5

    Require the client to supply a double-MD5-hashed password for authentication.

    refer for more here

  • In my case, I had to add the exact line as suggested by the error information. Cannot bypass it by adding "all" users with all IPs as rule. Now it is like:

    PosgreSQL 10.5 on CentOS 7.

    # IPv4 local connections:
    host    all             all             127.0.0.1/32                    md5
    host    <db_name>       postgres        <my_client_machine_ip>/32       md5
    

License under CC-BY-SA with attribution


Content dated before 6/26/2020 9:53 AM