While connecting to PostgreSQL, users often notice the error “FATAL: no pg_hba.conf entry”.

As a part of our Server Management Service, we help our customers to fix PostgreSQL-related errors regularly.

Today, let us discuss the possible reasons and fixes for the error.

What is “FATAL: no pg_hba.conf entry” error

Connecting to PostgreSQL often triggers the error as shown below:

This error generally triggers due to incomplete entries in the pg_hba.conf file. Generally, this configuration file controls the client authentication and is stored in the database cluster’s data directory.

The general format of the pg_hba.conf file contains a set of records, one per line. A record contains a number of fields separated by spaces and/or tabs. Fields can contain white space, but we need to quote the field value.

Each record specifies a connection type, a client IP address range, a database name, a user name, and the authentication method for connections matching these parameters.

Further, the first record with a matching connection type, client address, requested database, and user name is used to perform authentication. There is no “fall-through” or “backup”: if one record is chosen and the authentication fails, subsequent records are not considered. If no record matches, access is denied.
 

How to fix “FATAL: no pg_hba.conf entry” error

1. Log in to Postgres SQL server with the use of ssh console.

2. Now, move to the data directory with the cd command.

cd /var/lib/pgsql/9.6/data/

3. Then, open pg_hba.conf file in an editor.

vi pg_hba.conf

4. Add an entry of the host IP address from which we try to connect. We can input the entry of the host to which we would like to provide access to:

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
 
# IPv4 local connections:
host    all         all         127.0.0.1/32          md5
host    all  	    all  	xxx.xxx.xxx.xxx       md5
# IPv6 local connections:
host    all         all         ::1/128               md5

5. Then, restart the postgres SQL server.

systemctl restart postgresql-9.6.service

6. Try again in order to connect with the use of pgAdmin tool and we should be able to connect without any errors.

[Need help to fix PostgreSQL error? We are available 24*7]
 

Conclusion

In short, the “no pg_hba.conf entry” can happen due to missing entries in the configuration file. Today, we saw how our Support Engineers fix this error.