Postgresql remote access no pg_hba.conf entry for host

Question

I'm trying to get remote access to postgresql, installed on a server with IP A.A.A.A. There are two servers which one them has IP A.A.A.A, where postgresql has been installed on; and the other one has IP B.B.B.B.

I've edited /etc/postgresql/10/main/pg_hba.conf and added the following lines

host my_db db_user B.B.B.B/32 md5
host my_db db_user A.A.A.A/32 md5

I've also edited /etc/postgresql/10/main/postgresql.conf and set listen_addresses to '*', like this:

#---------------------------------------------------------------------    
---------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

listen_addresses = '*'          # what IP address(es) to listen on;
                                    # comma-separated list of     addresses;
                                    # defaults to 'localhost'; use '*' for all
                                    # (change requires restart)
port = 5432                             # (change requires restart)
max_connections = 100                   # (change requires restart)

I have also allowed connections through port 5432 in ufw:

5432                       ALLOW       Anywhere                  
5432 (v6)                  ALLOW       Anywhere (v6)             

Now, if I am in server A (where postgres is installed) and do psql "postgresql://db_user:[email protected]:5432/my_db" it connects fine. But if I do it from the server with IP B.B.B.B I get this error:

psql: FATAL:  no pg_hba.conf entry for host "B.B.B.B", user "db_user", database "my_db", SSL on
FATAL:  no pg_hba.conf entry for host "B.B.B.B", user "db_user", database "my_db", SSL off

I really don't understand where the mistake is. Both entries in pg_hba.conf look the same, but one works and the other doesn't.

Answer 1

I had a similar problem with my PostgreSQL instance on Docker. I spent hours to figure out how to solve it. I am sharing my experience hoping to help others that face the same problem. There are two pg_hba.conf files with following paths:

1. /var/lib/postgresql/data/pg_hba.conf
2. /etc/postgresql/12/main/pg_hba.conf

I was modifying the first one and was not aware of the second one. I used a trick to check if I was working on the right file. I commented out all lines of the file and restarted PostgreSQL service. Then I saw that I was still able to connect to the PostgreSQL service from inside of Docker container. Then I looked for another file with a similar name, so I reached the second one. I modified /etc/postgresql/12/main/pg_hba.conf, restarted PostgreSQL by running service postgresql restart, and changes took effect.