PostgreSQL management is a never-ending rabbit hole and in my case I just want to create a user, a database and set up some ownership.. without resorting to SQL or complex configuration frameworks.

Docker

Using Docker Desktop and an SSH tunnel I am able to run pgAdmin locally to manage my remote databases. The critical part is, the database does not need to be exposed to the Internet, this is where the SSH tunnel comes in.

This assumes your PostgreSQL is configured to access connections on localhost and that you have an account set up.

It’s time to start a local instance of pgAdmin using Docker.

> docker pull dpage/pgadmin4
Using default tag: latest
latest: Pulling from dpage/pgadmin4
Digest: sha256:2ab69d31e8a4cc03df29c1916431cc535016a64aabceebc91bc6762c6c53ecf5
Status: Image is up to date for dpage/pgadmin4:latest
docker.io/dpage/pgadmin4:latest

All we have left to do is start the container , this involves quite a few flags:

  • --name "pgadmin": a sensible container name, makes starting and stopping easier.
  • -e "[email protected]": Admin username
  • -e "PGADMIN_DEFAULT_PASSWORD=boop": Admin password
  • -p 5050:5050: Map the container’s port 5050 to your desktop port 5050. This port hosts the web interface.
  • -p 5432:5432: Map the container’s port 5432 to your desktop port 5432. This allows the container to connect to the PostgreSQL port which we opened a forwarding to using SSH.

All together the command is:

docker run --name "pgadmin" -e "[email protected]" -e "PGADMIN_DEFAULT_PASSWORD=boop" -e "PGADMIN_LISTEN_PORT=5050" -p 5050:5050 -p 5432:5432 -d dpage/pgadmin4

Now you can access pgAdmin in your web browser at http://localhost:5050

pgAdmin

Log in with the credentials set earlier, once in just right-click Servers and select Create -> Server....

pgAdmin - Create Server

pgAdmin - Create Server

  • Set a Name
  • Go to the Connection tab
  • Set Host name/address to localhost
  • Set the username and password to your PostgreSQL account
  • Go to the SSH Tunnel tab
  • Set Use SSH tunneling to On
  • Set Tunnel host to your remote server address
  • Set Username and Password to your Linux user on the remote server
  • Save

You should be able to access your PostgreSQL server via the left Servers column!