DBA Hub

📋Steps in this guide1/2

psql: error: could not connect to server: FATAL: no pg_hba.conf entry for host - DBACLASS DBACLASS

psql: error: could not connect to server: FATAL: no pg_hba.conf entry for host "", user "enterprisedb", database "postgres", solution is to add

postgresql configurationintermediate
by PostgreSQL
13 views
1

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
2

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 Now reload the config: Now test the connection :

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            ident
<span style="color: #ff0000;"><strong>host    all             all             192.168.2.3/32          trust
</strong></span># IPv6 local connections:

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

postgres=# <span style="color: #ff0000;"><strong>SELECT pg_reload_conf()</strong></span>
;
 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)

-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=#

Comments (0)

Please to add comments

No comments yet. Be the first to comment!