0

Let's say I have 4 SSH tunnels to different remote servers, each having different PostgreSQL databases up and running. Each tunnel is mapped to a specific port on my local machine (localhost) as follows:

ssh -f -N -L 1234:127.0.0.1:5432 userA@serverA
ssh -f -N -L 1235:127.0.0.1:5432 userB@serverB
ssh -f -N -L 1236:127.0.0.1:5432 userC@serverC
ssh -f -N -L 1237:127.0.0.1:5432 userD@serverD

Now, in my PostgreSQL .pgpass file I can refer for example to my databases with:

# hostname:port:database:username:password
127.0.0.1:1234:database_a:pg_user_a:postgres_password_a
127.0.0.1:1235:database_b:pg_user_b:postgres_password_b
127.0.0.1:1236:database_c:pg_user_c:postgres_password_c
127.0.0.1:1237:database_d:pg_user_d:postgres_password_d

And this is fine.

But now, within the pgAdmin4 docker image (https://hub.docker.com/r/dpage/pgadmin4/) the port mapping on the docker 'localhost' cannot be chosen by the user; it's randomly getting assigned a value. Therefore, and because I cannot guess in advance all the ports that will be used for the tunneling, I must change the .pgpass file using wildcards in this way:

127.0.0.1:*:database_a:pg_user_a:postgres_password_a
127.0.0.1:*:database_b:pg_user_b:postgres_password_b
127.0.0.1:*:database_c:pg_user_c:postgres_password_c
127.0.0.1:*:database_d:pg_user_d:postgres_password_d

This is always fine. I guess. (unless I know which IP range is used by pgAdmin4 internally and if I can set up this exact same range in the pgpass file but I don't think this is possible.)

Now, what if I'd like to connect to the default postgres maintenance database with the default postgres user on each?

127.0.0.1:*:postgres:postgres:postgres_password_a
127.0.0.1:*:postgres:postgres:postgres_password_b
127.0.0.1:*:postgres:postgres:postgres_password_c
127.0.0.1:*:postgres:postgres:postgres_password_d

How could I let pgAdmin4 knows which password to use with which server as all first four fields are now exactly the same?

Setting the same password on all servers, reducing the pgpass file to this single line:

127.0.0.1:*:postgres:postgres:unique_postgres_password

is definitely not an option as I cannot change those {databasenames:usernames:passwords} myself.

The local ports being randomly assigned, I guess I'll have to play with the local IP but I'm not successful for the moment as it seems to be automatically set to 127.0.0.1.

This latter case is the most extreme one, but one can easily imagine the same issue with two different databases; a pre-production and a production one, having both the same {database:username} but different passwords.

Doc:
https://www.postgresql.org/docs/14/libpq-pgpass.html

1
  • Thete is no solution. Choose a smarter tool to open those ssh tunnels. Or do without them, thst would be simpler. Feb 16, 2022 at 16:29

1 Answer 1

0

pgAdmin4 offers to set up tunnels as a convenience, not a burden. You are free to set up the tunnels yourself, and point pgAdmin4 to use those existing ones with their predictable (chosen by you) port numbers.

Also, pgAdmin4 has its own built in password manager. How are you getting it to use .pgpass in the first place?

2
  • A pgpass file can be use per the doc: "Use the Password File field to specify the location of a password file (.pgpass). A .pgpass file allows a user to login without providing a password when they connect. For more information, see Section 33.15 of the Postgres documentation." pgadmin.org/docs/pgadmin4/6.5/server_dialog.html
    – s.k
    Feb 17, 2022 at 22:30
  • @s.k I think that feature must have been broken/missing back when I first started using pgAdmin4. Anyway, I think your options then are to create the tunnels yourself, or to use pgAdmin4's built in password manager (which should know what server it is talking to despite the randomly determined port) rather than .pgpass
    – jjanes
    Feb 17, 2022 at 22:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.