PROBLEM:

While connecting to a remote database with psql got below error.

-bash-4.2$hostname -i
192.168.2.3

-bash-4.2$ psql –host 192.268.8.0 -p 5444 -d postgres

psql: error: could not connect to server: FATAL: no pg_hba.conf entry for host “192.168.2.3”, user “enterprisedb”, database “postgres”, SSL off

SOLUTION:

In the above scenario, i was trying to connect to a database on remote host 192.268.8.0 from the local ip 192.168.2.3.

So we need to give authentication for the local ip inside pg_hba.conf file of remote server.

login to the remote server 192.268.8.0 and update the pg_hba.conf file:

pg_hba.conf can be found inside PGDATA directory.

 

vi pg_hba.conf




# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            ident
host    all             all             192.168.2.3/32          trust
# IPv6 local connections:

Now reload the config:


-bash-4.2$ psql -d postgres
psql (12.3.4)
Type "help" for help.

postgres=# SELECT pg_reload_conf()
;
 pg_reload_conf
----------------
 t
(1 row)

postgres=# select * from pg_hba_file_rules;
 line_number | type  |   database    | user_name |   address    |                 netmask                 | auth_method | options | error
-------------+-------+---------------+-----------+--------------+-----------------------------------------+-------------+---------+-------
          80 | local | {all}         | {all}     |              |                                         | peer        |         |
          82 | host  | {all}         | {all}     | 127.0.0.1    | 255.255.255.255                         | ident       |         |
          83 | host  | {all}         | {all}     | 192.168.2.3 | 255.255.255.255                         | trust       |         |
          85 | host  | {all}         | {all}     | ::1          | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff | ident       |         |
          88 | local | {replication} | {all}     |              |                                         | peer        |         |
          89 | host  | {replication} | {all}     | 127.0.0.1    | 255.255.255.255                         | ident       |         |
          90 | host  | {replication} | {all}     | ::1          | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff | ident       |         |
(7 rows)

Now test the connection :


-bash-4.2$  psql --host 192.268.8.0 -p 5444 -d postgres
psql (12.3.4)
Type "help" for help.

postgres=# \conninfo
You are connected to database "postgres" as user "enterprisedb" on host "192.268.8.0" at port "5444".
postgres=#