0

I am trying to connect
to a PostgreSQL database hosted on a remote server
from a PostGIS docker container which is started on my laptop (Ubuntu 18.04).

My very first step was naturally to open an SSH tunnel from my laptop to the remote server. Everything is OK to this point because I can connect to a terminal on the remote server through the SSH tunnel.

Then, based on this answer: https://stackoverflow.com/a/24326540/6630397 I started the PostGIS docker container on my Ubuntu 18.04 machine to connect to the remote PostgreSQL database. (This is necessary for me because I have some version mismatch issue between my native pg_tools and the pg version on the remote server.)

But when trying to connect to the remote database from my local container, the connection times out.

Technical details

Let's assume the PostgreSQL port on the remote server is the default 5432, the SSH tunnel was mapped to my local 5433 as follows:

ssh -f -N <user@remote-server> -L 5433:127.0.0.1:5432

And the PostGIS docker container was started as:

$ docker run --rm -d \
  --add-host host.docker.internal:host-gateway \
  --name postgis \
  -v "/path/to/local/data_folder:/data" \
  -e POSTGRES_DB=mydbname \
  -e POSTGRES_USER=postgres \
  -e POSTGRES_PASSWORD=password \
  postgis/postgis:13-3.1

Then I simply connect to it using:

$ docker exec -it postgis bash

(connecting to this postgis container by specifying -u postgres doesn't solve the issue)

And from inside the container:

root@c1246180b316:/# psql -U postgres -h host.docker.internal -p 5433

But the connection actually times out (where it should ask for the remote db password):

psql: error: could not connect to server: Connection timed out
    Is the server running on host "host.docker.internal" (172.17.0.1) and accepting
    TCP/IP connections on port 5433?

If I run the exact same psql command from outside the docker container, directly on my local machine, replacing host.docker.internal by localhost it works fine!

How can I fix this problem and successfully connect to my remote database from inside my postgis container?

swiss_knight
  • 5,787
  • 8
  • 50
  • 92
  • Have you checked the log files? Does `docker exec -it -u postgres containername psql` work? – Jim Jones Jul 02 '21 at 04:58
  • I'll check if I can access the logs of the remote server but I fear not. Adding the `-u postgres` option when connecting to my local container doesn't solve the issue. – swiss_knight Jul 02 '21 at 07:59

1 Answers1

2

Solution for me was to make sure that the tunnel accepts all incoming connections. For some reason, it seems that docker doesn't count as coming from the machine or something like that.

So, changing the tunnel line to this:

ssh -f -N <user@remote-server> -L 0.0.0.0:5433:127.0.0.1:5432
Akaisteph7
  • 5,034
  • 2
  • 20
  • 43