While connecting to PostgreSQL, users often notice the error "FATAL: no pg_hba.conf entry".
Here at Ibmi Media, as part of our Server Management Services, we regularly help our Customers to resolve related PostgreSQL errors.
What does "FATAL: no pg_hba.conf entry" error mean ?
"FATAL: no pg_hba.conf entry" 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.