384

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

I found this answer that 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 persists. I restarted my XAMPP server several times but nothing changes.

What do I need to change in pg_hba.conf?

mustaccio
  • 28,207
  • 24
  • 60
  • 76
Jin
  • 3,953
  • 3
  • 14
  • 6

12 Answers12

454

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 scram-sha-256

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

Edit: The authentication method was updated from md5 to scram-sha-256 because "the MD5 hash algorithm is nowadays no longer considered secure against determined attacks." Please note that scram-sha-256 "is the most secure of the currently provided methods, but it is not supported by older client libraries." Source: official documentation

Daniel
  • 103
  • 2
Jérôme Radix
  • 4,671
  • 1
  • 13
  • 4
46

This solution works for IPv4 / IPv6:

Edit pga_hba.conf File

Open up the pga_hba.conf file in your favourite editor:

[root@localhost ~]#  nano /var/lib/pgsql/data/pg_hba.conf

Append To pga_hba.conf File

Append the following lines to the end of the pga_hba.conf file:

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

Quit and save the editor of your preference.

Restart Service

Restart the postgresql service with the following command:

[root@localhost ~]# /etc/init.d/postgresql restart
John K. N.
  • 18,854
  • 14
  • 56
  • 117
Jose Nobile
  • 593
  • 4
  • 7
31

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.

joanolo
  • 13,657
  • 8
  • 39
  • 67
Roberto Lopez
  • 411
  • 4
  • 2
21

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
Eugene Kulabuhov
  • 311
  • 2
  • 4
17

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
willianpts
  • 171
  • 1
  • 3
14

  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

Jürgen Steinblock
  • 103
  • 3
nguyên
  • 241
  • 2
  • 4
9

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

Ravistm
  • 199
  • 1
  • 5
9

In my case I ran into this where I didn't have access to edit any conf files on the server (.NET connecting to a managed db on DigitalOcean) so the other answers weren't an option.

The host provided me a postgresql:// connection URL which had a ?sslmode= option on the end. I got the exact same error until I added "SSL Mode=Prefer;Trust Server Certificate=true;" to my translated .NET connectionString.

That may not be the optimal solution for me or for you, but I wanted to point out it's possible that this is an issue with the connection string rather than the server config.

CrazyPyro
  • 241
  • 4
  • 7
6

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.

Finesse
  • 221
  • 3
  • 3
4

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
WesternGun
  • 141
  • 4
4

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
Feriman
  • 171
  • 5
-1

According to the link https://docs.bitnami.com/aws/apps/noalyss/administration/configure-pgadmin/.

For PgAdmin 4 on Windows. I added these lines below

pg_hba.config:

host all all all md5

and modify postgresql.config:

listen_addresses = '*'
jvaldezb
  • 11