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

Question

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?

score 439 · Accepted Answer · edited Jul 26 '23 at 17:27

439

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

edited Jul 26 '23 at 17:27

Daniel

103
2

answered Dec 01 '14 at 13:49

Jérôme Radix

4,521
1
12
4

14

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
5

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
6

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
2

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. – uchuugaka Dec 05 '19 at 00:55
I had to add a firewall rule before the above worked, run the following in an elevated powershell window. New-NetFirewallRule -DisplayName "WSL" -Direction Inbound -InterfaceAlias "vEthernet (WSL)" -Action Allow – baldy Mar 18 '21 at 08:59
That's no so smart - now any IP can connect - you might want to limit it to your internal network addresses. More precisely, to the narrowest range of your internal network addresses that need access. – Quandary Oct 17 '21 at 19:11
You can also reload pg_hba.conf with SELECT pg_reload_conf() – Rafael Membrives Nov 23 '22 at 09:31
2

@NateWanner Your example should read 172.0.0.0/8, otherwise it's no different than 0.0.0.0/0! – not2savvy May 15 '23 at 11:29
@not2savvy Thanks! Good catch. – Nate Wanner May 15 '23 at 16:03
Also make sure you've entered the right password. Otherwise you'll get the same error message even if your network configuration is right. – Megacier Jan 07 '24 at 20:54
Or enable SSL in your framework. Example if using NestJS add TypeOrmModule.forRoot({ ssl: { rejectUnauthorized: false, }, }), – Mahmoud Zalt Mar 25 '24 at 22:14

score 46 · Answer 2 · edited Dec 29 '21 at 11:51

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

edited Dec 29 '21 at 11:51

John K. N.

17,649
12
51
110

answered Nov 27 '15 at 21:18

Jose Nobile

593
4
7

2

only worked when I used ::0/0 – dangelsaurus Dec 14 '19 at 04:11
1

My path was /etc/postgresql/12/main/pg_hba.conf on Ubuntu 20. – Vael Victus Nov 30 '21 at 22:37
1

For me it worked with ::0/0 only. – Superluminal Aug 12 '22 at 10:31
::0/0 works for connections not coming from localhost. ::1/128 is just for localhost It is better to connect remotely using SSH or something secure, not directly to the database, at least if you want a secured system, as defined by PCI. – Jose Nobile Aug 13 '22 at 16:40
You can use pg_ctl reload to reload the config without downtime. At least you can in the Red Hat version of Postgres that I was using. It may differ across different implementations. – Jimbali Nov 07 '22 at 16:48

score 30 · Answer 3 · edited Jun 05 '17 at 06:39

30

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 05 '17 at 06:39

joanolo

13,397
7
36
65

answered Jun 05 '17 at 06:23

Roberto Lopez

401
4
2

8

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 05 '17 at 06:44

score 19 · Answer 4 · answered May 18 '19 at 23:45

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

score 16 · Answer 5 · answered Dec 05 '17 at 23:02

16

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 05 '17 at 23:02

willianpts

161
1
3

score 13 · Answer 6 · edited Jun 06 '19 at 14:45

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

Ravistm · Answer 7 · 2019-02-14T13:48:42.960

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

score 9 · Answer 8 · answered Oct 21 '20 at 13:01

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.

answered Oct 21 '20 at 13:01

CrazyPyro

241
4
7

2

I had same issue. When connecting with Npgsql, if database expects SSL connection and you omit it from connection string, then you get a bizarre pg_hfa.conf error. – JamesHoux Dec 08 '20 at 19:55
3

I had the same issue with Azure's new "flexible server" while trying to connect with psql. Adding --set=sslmode=require to the command fixed it. – Tristan Jan 10 '23 at 20:47
Same with me. Was connecting AWS DMS to a hosted Postgres (on Aptible). Setting sslmode=required eliminated the pg_hba.conf error. – aqm Aug 25 '23 at 16:18

score 6 · Answer 9 · answered Mar 24 '18 at 02:02

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.

answered Mar 24 '18 at 02:02

Finesse

211
3
3

score 4 · Answer 10 · answered Oct 09 '18 at 10:41

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

score 4 · Answer 11 · answered Aug 26 '19 at 14:24

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

score -1 · Answer 12 · answered Aug 23 '19 at 15:10

-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

11

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

12 Answers12

Edit pga_hba.conf File

Append To pga_hba.conf File

Restart Service

Linked