1

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:password@A.A.A.A: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.

Abdollah
  • 4,579
  • 3
  • 29
  • 49
gmm
  • 943
  • 1
  • 17
  • 30
  • Have you restarted the postgresql service? – jlandercy Dec 12 '18 at 15:32
  • 1
    yes, several times. Before I did access on server A didnt work either – gmm Dec 12 '18 at 15:36
  • When `tcpdump -i -n port 5432` on the PostgreSQL server side and you attempt to connect is your IP correct? Is there NAT between the two server or Proxies? This is not a Firewall problem because it is PostgreSQL that is answering and dropping the connection. – jlandercy Dec 12 '18 at 15:38
  • If I do the tcpdump on eth0 on the postgres server, and execute the command on the other server, I see B.B.B.B.53282 . I dont know what that last number is. Is that part of the IP? Both servers are digitalocean droplets, and Im just using the IP digitalocean gives – gmm Dec 12 '18 at 15:46
  • This is just the port opened on the client side, not important so the IP is correct. How many cluster running do you have? Are you sure you have edited the right cluster file? Database and user name are correct and existing? – jlandercy Dec 12 '18 at 15:49
  • I dont have a cluster, just the two servers. The one running postgresql and the second one – gmm Dec 12 '18 at 15:50
  • Ok.. so I removed the line, rewrote it, and it works now. My guess now is that because i had copied and pasted the ip, maybe a weird character had been pasted? Thank you for your help anyway – gmm Dec 12 '18 at 15:54
  • Yes some configuration file are very sensitive to extra space at the end or tabulation. I am glad you have solved your problem – jlandercy Dec 12 '18 at 15:55

1 Answers1

2

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.

Abdollah
  • 4,579
  • 3
  • 29
  • 49
  • 2
    You can always use `show hba_file;` to find the pg_hba file the instance is using you are connected to –  Oct 15 '19 at 11:06
  • 1
    I tried it. Interestingly it showed `/var/lib/postgresql/data/pg_hba.conf`, the wrong one! – Abdollah Oct 15 '19 at 11:08
  • 1
    There is only one PostgreSQL on my Docker. – Abdollah Oct 15 '19 at 11:09
  • 1
    I ran `ps -fHC postgres` to see more info about `postgres` service. The parent `postgres` process shows `/usr/lib/postgresql/12/bin/postgres -D /var/lib/postgresql/12/main -c config_file=/etc/postgresql/12/main/postgresql.conf` in the CMD column in the tabular output. – Abdollah Oct 15 '19 at 11:43