What is the cause and fix for the error FATAL: no pg_hba.conf entry for host “host”, user “user”, database “db”, SSL off in postgres

by

The error message FATAL: no pg_hba.conf entry for host "host", user "user", database "db", SSL off indicates that the client connection from the specified host to the PostgreSQL server for the given user and database is not allowed based on the current settings in the pg_hba.conf file. The pg_hba.conf file controls which hosts are allowed to connect, which authentication methods are used, and which databases they can access.

Cause

The primary cause of this error is that there is no entry in the pg_hba.conf file that matches the connection attempt with the given parameters (host, user, database, and SSL usage). This could be because:

  1. The IP address of the client is not included in any of the pg_hba.conf entries.
  2. The user trying to connect does not have an entry with the correct privileges.
  3. The database specified does not match any of the accessible databases for the user in the pg_hba.conf entries.
  4. The connection is attempting to use SSL, which is not configured or required by the pg_hba.conf entry, or vice versa.

Fix

To resolve this error, you need to edit the pg_hba.conf file and add or modify an entry that matches the connection attempt. Here’s how to do it:

  1. Locate the pg_hba.conf File: Find the pg_hba.conf file in your PostgreSQL data directory. The location of this file can vary depending on your operating system and PostgreSQL installation.
  2. Edit the pg_hba.conf File: Open the pg_hba.conf file with a text editor (you’ll need appropriate permissions to edit this file).
  3. Add or Modify an Entry: Add a new entry or modify an existing one to allow the connection. Here’s an example of an entry that allows a user to connect from a specific host to a specific database without SSL:
   host    dbname    username    host_address/32    md5

In this line:

  • host specifies the connection type.
  • dbname is the database name the user is allowed to connect to.
  • username is the name of the user.
  • host_address/32 is the IP address of the client machine (use 0.0.0.0/0 for any host or a subnet mask for a range of addresses).
  • md5 is the authentication method (you can also use password, trust, peer, ident, etc.). If you want to allow SSL connections, you can use hostssl instead of host.
  1. Reload the Configuration: After saving changes to the pg_hba.conf file, you need to reload the PostgreSQL server configuration to apply the changes. You can do this by running the following SQL command in the psql interface:
   SELECT pg_reload_conf();

Alternatively, you can use the command-line instruction:

   pg_ctl reload
  1. Test the Connection: Attempt to connect to the database again to ensure the changes have fixed the issue.

It’s important to be cautious when editing the pg_hba.conf file, as incorrect settings can affect the security and accessibility of your PostgreSQL server. Always back up the current pg_hba.conf file before making changes.

Leave a Comment