0

This is extraordinary tricky but I just discovered that, when you first open an SSH tunnel manually in bash (and somehow, I hope that I'm wrong somewhere...);

user@localhost:~$ ssh -N -f -L 2345:localhost:5432 remote-user@remote.server.ip

corresponding to:
schema of the setup

then, when it comes to setting up the value of the PORT in the web based pgadmin4 GUI from dpage/pgadmin4 https://hub.docker.com/r/dpage/pgadmin4/ this port value has to match the port of the localhost (here 2345) which then matches the PostgreSQL port on the remote's side of the SSH tunnel. And this is fine, I mean, it was always what I was doing, even before using the pgadmin4 docker image:

localhost port side of the SSH tunnel

But when you setup your remote server in the servers.json file using the port 2345 of the localhost's side of the tunnel in the "Port": entry of your server's definition (which you expect to be the same than the one in the GUI):

    "1": {
      "Name": "remote-postgres-12",
      "Group": "Remote-servers",
      "Host": "localhost",
      "Port": 5432,  // <-- this is NOT the localhost side of the tunnel but the remote server's side port !
      "MaintenanceDB": "postgres",
      "Username": "postgres",
      "SSLMode": "prefer",
      "UseSSHTunnel": 1,
      "TunnelHost": "remote.server.ip",
      "TunnelPort": "22",
      "TunnelUsername": "remote-user",
      "TunnelAuthentication": 0
    }

it doesn't work after you bring the docker image up and try to connect using the web GUI. And the docker logs will tell you this:

Could not establish connection from local ('127.0.0.1', 35729) to remote ('localhost', 2345) side of the tunnel: open new channel ssh error: ChannelException(2, 'Connect failed')

and indeed, the port 2345 is NOT the remote side of the tunnel as said in this error message, it's the local side ! Please, also notice that the port 35729 seems to be randomly generated by the application. But I suspect this to be from 'inside' the container, not from the localhost machine.

So, in case you use the definition of your remote server from the servers.json file using the docker image, the value of the "Port": has to be the port of the remote PostgreSQL database, namely 5432 if you let the default value. In that case, the web GUI can actually connect to the remote database without trouble. But if you log into the docker container and try using the same 5432 port to connect to the remote's database it doesn't work (this is expected from my point of view):

/usr/local/pgsql-12 $ ./psql -U postgres -d postgres -p 5432 -h localhost
Password for user postgres:
psql: error: FATAL:  password authentication failed for user "postgres"
FATAL:  password authentication failed for user "postgres"

because it obviously wants to connect to my local machine's database, which doesn't have the same postgres password.

So, this behaviour is counter-intuitive and the logic is the opposite of the one you face when setting up the tunnel manually before and parameterizing the connection from the web GUI.

On the other hand, if you setup the 2345 port in the servers.json file (as you would have done in the web GUI), you will get trouble connecting in the web GUI once the docker container is up:

unable to connect to remote

and when I connect inside the docker container and try to connect to my remote's DB using the tunnel it says:

/usr/local/pgsql-12 $ ./psql -U postgres -d postgres -p 2345 -h localhost
psql: error: could not connect to server: Connection refused
    Is the server running on host "localhost" (127.0.0.1) and accepting
    TCP/IP connections on port 2345?

as if the SSH tunnel was actually simply not set up.

Does someone has an explanation about that?

Or is there something I wrongly understood, maybe due to the added layer of complexity because of the docker image? Because I have something which is working, but without the natural settings... I'm confused.

This may be of some importance: I've setup this network mode in my docker-compose.file:

network_mode: host
4
  • That's a lot of words but I don't see where the SSH tunnel is set up: in the "local machine" or inside the container? – mustaccio Mar 10 at 22:52
  • The tunneling is defined in the servers.json file, from all the variables beginning with 'Tunnel', I guess. This servers.json file is embedded inside the container (from a bind mount volume actually) so you can consider it being inside the container. From what I understood of this docker image, this file is used when spinning up the container to set up the servers, so the first time you login to the web GUI, the servers defined in that file are already there in the left menu, with the parameters defined in this servers.json file. – s.k Mar 10 at 22:56
  • I suggest you learn how docker networks operate, because right now you're you seem to be confusing the docker host and the container. In any case, this has nothing to do with database administration, so I'm going to vote to close this question as off-topic. – mustaccio Mar 11 at 0:23
  • I thought the host network mode was for making all hosts and IPs from the host machine available to the container in a transparent manner. Hmm, I'm OK for closing if you think it has nothing to do here, but then could you provide me some hints on where this question would most be suited? The general SO? Server Fault? I sometime miss a specialized docker-SE... – s.k Mar 11 at 7:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.