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.
[Need help to fix PostgreSQL error? We are available 24*7. ]
Conclusion
This article covers methods to resolve "FATAL: no pg_hba.conf entry" error. Basically, the "no pg_hba.conf entry" can happen due to missing entries in the configuration file.
A quick way to fix this PostgreSQL server error is to do the following steps:
1. Add or edit the following line in your postgresql.conf :
listen_addresses = '*'
2. 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
3. Restart Postgresql after adding this with service postgresql restart or the equivalent command for your setup.
This article covers methods to resolve "FATAL: no pg_hba.conf entry" error. Basically, the "no pg_hba.conf entry" can happen due to missing entries in the configuration file.
A quick way to fix this PostgreSQL server error is to do the following steps:
1. Add or edit the following line in your postgresql.conf :
listen_addresses = '*'
2. 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
3. Restart Postgresql after adding this with service postgresql restart or the equivalent command for your setup.
