Home PostgreSQL pg_hba.conf in postgresql; FATAL: no pg_hba.conf entry for host ““, user “XXXXX”,...

pg_hba.conf in postgresql; FATAL: no pg_hba.conf entry for host ““, user “XXXXX”, database “XXXX”, SSL off ; psql: could not connect to server: Connection refused

0
128

We often face with following connection refused errors while accessing postgresql from different clients (pg_admin, jdbc connection, psql ).

1. FATAL: no pg_hba.conf entry for host "<IP>", user "XXXXX", database "XXXX", SSL off

2. org.postgresql.util.PSQLException: Connection refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections.

Let me first discuss about what pg_hba.conf file in postgresql.

pg_hba.conf

It stands for host based authentication for any client access. This file gets created automatically during initialization of the database(via initdb) and stored within “data” directory. 

You can find following default entry in pg_hba.conf file in any new postgresql instance.

In the above image you can clearly see there are four sections.

  1. First section says that “local” which means it is for Unix domain socket connections only. Hence, this part we do not modify or do not add anything in this.
  2. Second section says, its for IPv4 connections meaning it controls TCP/IP connections from any client. Here only we add all our individual client IP or group of IP with proper masking. Host records match either SSL or non-SSL connection attempts.
  3. These section is used for IPv6 connections. same as point 2.
  4. Forth section is completely for replication . Here you can allow IPs for which you want data to be replicated .

We are clear that we will use mostly section 2 and section 4 for our activities. Now if you see column-wise you will find following columns.

# TYPE      DATABASE        USER            ADDRESS                 METHOD
  1. TYPE: we will always use “host” as the value.
  2. DATABASE: This is the column where you can restrict each client for access only a particular database or all the databases. Give the database name say “postgres” or “<abcd>”  for a particular database or you can give  value “all” for all databases. For multiple database but not all databases , you can use multiple entry for the same host or you can control the access through privileges in database.
  3. USER: This the login id by which client will connect to the permitted database.
  4. ADDRESS: In this column we give the particular IP address along with its subnet mask or multiple IPs in the same VLAN with proper masking field.  Following are the three sample entries about how to change the details. In the first entry we give access to IP “192.168.64.105” and login is allowed only for login id “marjina” to the database dvdrental. In the second entry it allows to all IPs in that network which starts with “192.168”. In the thirst entry it allows for all logins and all databases from 64 VLAN for network “192.168”. Make sure to change the masking (32,16,24) respectively for each IPs.
host    dvdrental     marjina    192.168.64.105/32 md5

host dvdrental marjina 192.168.0.0/16 md5

host all all 192.168.64.0/24 trust

5. METHOD: Specifies the authentication method to use when a connection matches this record. We generally use following three method which are shown below. There are many other options which you can find here.

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.  This is not recommended until and unless required.

reject: Reject the connection unconditionally. This is useful for "filtering out" certain hosts from a group, for example a reject line could block a specific host from connecting, while a later line allows the remaining hosts in a specific network to connect. This options can be used to block particular IPs.

md5: Require the client to supply an MD5-encrypted password for authentication. This is the preferred method which will authenticate before access to the database.

Resolution to connection refused error

The connection refused error can come because of 2 reasons.

  1. You postgresql instance is not running. In that case postmaster process is not able to serve TCP/IP incoming connections. Hence, you have to restart the postgresql instance to access the database.
  2. The client which wants to connect to the database does not have access or entry in the pg_hba.conf file. In this case make an entry of the pg_hba.conf file or the required client and reload the psql services to apply the change in running instance.
  3. Make sure that you do not have an entry for a particular client with both “reject” and “md5 or trust” method. In that case it will give preference to reject method and won’t allow to access the database even after restart of the instance. This can happen when you have put a reject entry for a particular VLAN and then you are trying to give access to a particular client IP in that VLAN. This won’t allow access until and unless you remove “reject” entry and reload the service.

Reload of postgresql service.

You can reload the service using following command without impacting the current production run and apply the pg_hba.conf file changes.

/usr/local/bin/pg_ctl -D /usr/local/var/postgres reload