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

Question

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(). — Craig Ringer, Dec 1 '14 at 12:26

Harry · Accepted Answer · 2018-12-17 15:12:24Z

252

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.

edited Dec 17 '18 at 15:12

Harry

1034 bronze badges

answered Dec 1 '14 at 13:49

Jérôme Radix

2,6361 gold badge8 silver badges2 bronze badges

6

Note that for this to work, a password must have been set for the user: sudo -u <username> psql postgres , then \password at the resultant sql prompt will prompt for setting one. – ijoseph May 30 '18 at 21:54
3

Don't forget to restart postgresql after adding this: service postgresql restart – ammills01 Oct 24 '18 at 22:50
1

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 – Nate Wanner Feb 28 '19 at 13:49
1

You also need to add a line with an address of ::0/0, otherwise connections from IPv6 addresses will still be rejected. – Herohtar Oct 18 '19 at 21:31
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 – Mehdi LAMRANI Oct 19 '19 at 19:19

| show 2 more comments

Community · Accepted Answer · 2020-06-15 09:05:18Z

26

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

edited Jun 15 at 9:05

Community♦

1

answered Nov 27 '15 at 21:18

Jose Nobile

3933 silver badges7 bronze badges

only worked when I used ::0/0 – dangel Dec 14 '19 at 4:11

add a comment |

joanolo · Accepted Answer · 2017-06-05 06:39:33Z

13

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.

edited Jun 5 '17 at 6:39

joanolo

11.3k6 gold badges27 silver badges58 bronze badges

answered Jun 5 '17 at 6:23

Roberto Lopez

1311 silver badge2 bronze badges

5

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. – joanolo Jun 5 '17 at 6:44

add a comment |

willianpts · Accepted Answer · 2017-12-05 23:02:58Z

6

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

answered Dec 5 '17 at 23:02

willianpts

611 silver badge2 bronze badges

add a comment |

Jürgen Steinblock · Accepted Answer · 2019-06-06 14:45:26Z

5

Add the following line in the bottom of pg_hba.conf:

hostnossl all all 0.0.0.0/0 md5
Add/modify the line in postgresql.conf:

listen_addresses = '*'
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

edited Jun 6 '19 at 14:45

Jürgen Steinblock

1033 bronze badges

answered Nov 6 '18 at 2:04

nguyên

1511 silver badge3 bronze badges

1

Don’t use trust for remote connections – eckes Nov 15 '18 at 19:57

add a comment |

Finesse · Accepted Answer · 2018-03-24 02:02:54Z

3

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.

answered Mar 24 '18 at 2:02

Finesse

1513 bronze badges

add a comment |

Ravistm · Accepted Answer · 2019-02-14 13:48:42Z

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

Thanks man, it worked for me, I just need to replace all the METHOD with trust for using no password — Muhammad, Jun 15 at 4:47

Eugene Kulabuhov · Accepted Answer · 2019-05-18 23:45:12Z

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

Feriman · Accepted Answer · 2019-08-26 14:24:48Z

2

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

answered Aug 26 '19 at 14:24

Feriman

1413 bronze badges

add a comment |

WesternGun · Accepted Answer · 2018-10-09 10:41:13Z

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

peterh - Reinstate Monica · Accepted Answer · 2018-09-02 08:24:15Z

It works for me only adding the below configuration:

# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
host    all             all             0.0.0.0/0               md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
host    all             all             0.0.0.0/0               md5

jvaldezb · Accepted Answer · 2019-08-23 15:10:09Z

-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 = '*'

answered Aug 23 '19 at 15:10

jvaldezb

1

add a comment |

ypercubeᵀᴹ · Accepted Answer · 2017-12-12 16:00:08Z

Just write the following sequence in the configuration file:

const 
  pg                = require('pg'),
config          = require('./db-config'),
    dbOption    = {
      host    : ,
      port    : ,
      user    : ,
      password  :,
      database  : 
    },

  pool          = new pg.Pool(dbOption)
  pg.defaults.ssl = true;

This is all. The file db-config contains the specification data of the host, user, password and database. With this small change it works perfectly both locally and in a pass service (Heroku). Fully tested.

What (and where) is this db-config? Is it a XAMPP config file? — ypercubeᵀᴹ, Dec 12 '17 at 16:14

Stack Exchange Network

current community

your communities

more stack exchange communities

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

13 Answers 13

add at final

Your Answer

Not the answer you're looking for? Browse other questions tagged postgresql php pg-hba.conf or ask your own question.

Linked

Hot Network Questions

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

13 Answers 13

add at final

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged postgresql php pg-hba.conf or ask your own question.

Linked

Related

Hot Network Questions