pgpass entries for multiple SSH tunnels with randomly assigned local ports using the same credentials but different passwords



  • 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



  • 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?




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2