With command line, I will login to the PostgreSQL host through SSH, and call sudo su - postgres, then call psql as the PostgreSQL superuser to connect through the UNIX socket at /run/postgresql/.s.PGSQL.5432.
In this process, no password is needed as long as you are running as the Linux user postgres, and it is a common best practice to let postgres have no password and no SSH login. If you are root, this way does not work, because Linux user root is usually not configured to access the UNIX socket.
I want to use GUI, e.g., PgAdmin 4, and its connection has a tab of “SSH Tunnel”, but I am not sure whether I can add sudo su - postgres and connect through the UNIX socket.
Another workaround is to add a password to the superuser postgres, then connect through SSH tunnel to the port 5432 on the localhost. But, the additional step of setting password is not very elegant.
We have seen a similar question PgAdmin 4 – How to connect to database using unix socket via ssh tunnel?. However, I want to access the UNIX socket as postgres while this user does not have SSH. So, I have a SUDO Linux login and hope to access the UNIX socket as postgres, like doing sudo su - postgres in CLI. For this specific part, I could not find the answer in the referred thread above.
