Content-Type: multipart/alternative;
 boundary="------------k2o0ot1R40J5vQaz7SOq0NCc"
Message-ID: <001a6f1d-4adb-42b2-8bf6-44154ed0ab97@gmail.com>
Date: Tue, 23 Jun 2026 12:57:03 -0300
MIME-Version: 1.0
User-Agent: Betterbird (Linux)
Content-Language: en-US
To: pgsql-hackers@lists.postgresql.org
From: Diego <mrstephenamell@gmail.com>
Subject: libpq: decouple the .pgpass lookup port from the connection port
Archived-At: 
 <https://www.postgresql.org/message-id/001a6f1d-4adb-42b2-8bf6-44154ed0ab97%40gmail.com>
Precedence: bulk

This is a multi-part message in MIME format.
--------------k2o0ot1R40J5vQaz7SOq0NCc
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

Hello hackers,

I would like to float an idea before writing a patch, to find out whether
it is wanted and to get the design right.

Problem
-------

libpq looks up a password in .pgpass using the connection's host and port
as part of the key (host:port:database:user:password). When a client
connects through an SSH tunnel, or through a connection pooler that
listens on a different local port, the port that libpq actually connects
to is not the port of the real server. As a result, the .pgpass lookup is
done against the local/tunnel port and fails to match entries written for
the real server port.

Concretely, suppose the real server is db.example.com:5432 and a user
opens an SSH tunnel so that 127.0.0.1:54321 forwards to it. The natural
.pgpass entry is:

     db.example.com:5432:appdb:alice:secret

The client then connects with host=db.example.com (kept for .pgpass and
TLS), hostaddr=127.0.0.1 and port=54321 (the tunnel). libpq looks up

     db.example.com:54321:appdb:alice

which does not match the 5432 entry, so no password is found and the user
is prompted (or the connection fails under -w).

The host side of this exact problem was already solved
-------------------------------------------------------

libpq already decouples the *host* used for the .pgpass lookup from the
real network endpoint: hostaddr gives the address actually connected to,
while host remains the logical name used for the .pgpass lookup and for
TLS verification. This is the pwhost logic in fe-connect.c, which goes
back to the 2018 thread "Bizarre behavior in libpq's searching of
~/.pgpass":

https://www.postgresql.org/message-id/30805.1532749137%40sss.pgh.pa.us

The port has no equivalent. passwordFromFile() is called with
conn->connhost[i].port, i.e. the real connection port, with no way to say
"connect to this port, but look up .pgpass under that port". The host has
host/hostaddr; the port only has port. This proposal is to close that
asymmetry.

Why the port wildcard is not enough
-----------------------------------

One can write the entry with a wildcard port:

     db.example.com:*:appdb:alice:secret

and it does match the tunnel. But the wildcard over-matches: a single
local forwarding port (say 54321, or even a fixed local port reused for
several tunnels at different times) ends up matching every server reached
through that port, so the same password line can be applied to different
servers. That is precisely the kind of "password sent to the wrong
server" situation the 2018 host fix was trying to avoid. The wildcard
trades safety for convenience; it is not a substitute for matching the
real server port.

Proposal
--------

Add a libpq connection parameter that specifies the port to be used for
the .pgpass lookup, independently of the port libpq connects to. The
connection still uses port (and hostaddr); only the password-file lookup
key uses the new value. When the new parameter is not set, behavior is
unchanged: the lookup uses port exactly as today.

I do not have a strong opinion on the name and would rather not bikeshed
it before the idea itself is judged. Candidates that came to mind:

     - pgpassport / passfileport (it only affects the password file)
     - portaddr (mirrors hostaddr: "port stays logical, portaddr is the
       real endpoint"), though that would invert today's meaning of port,
       which is probably too invasive

A dedicated parameter that affects only the .pgpass lookup (the first
option) seems the least surprising and the smallest change. It is also
easy to reason about for security: it is an explicit, opt-in assertion by
the user, exactly like hostaddr/host.

This is not hypothetical. I ran into it myself while adding SSH tunnel
support to pgcli (a widely used Postgres CLI): with the tunnel active, an
explicit-port .pgpass entry never matches, because the lookup happens
against the random local forwarding port. The user is prompted for a
password even though the matching entry is right there, and only a
wildcard port papers over it. Other tools hit the same wall:

     - pgcli: SSH tunnel rewrites the port before the .pgpass lookup
https://github.com/dbcli/pgcli/pull/1546
     - DBeaver: .pgpass looked up by 127.0.0.1 through an SSH tunnel
https://github.com/dbeaver/dbeaver/issues/16499
     - pgAdmin 4: control the SSH tunnel local port for .pgpass matching
https://github.com/pgadmin-org/pgadmin4/issues/6903

Questions for the list
----------------------

   1. Is decoupling the .pgpass-lookup port from the connection port
      something libpq wants, given that host/hostaddr already does the
      equivalent for the host?
   2. Is a dedicated lookup-only parameter the right shape, or would you
      prefer a different model?
   3. Naming preferences?

If there is interest, I am happy to write the patch (code, docs and
tests).

Thanks for reading,
Diego

--------------k2o0ot1R40J5vQaz7SOq0NCc
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<!DOCTYPE html>
<html>
  <head>

    <meta http-equiv="content-type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <p><font face="monospace">Hello hackers,<br>
      </font></p>
    <p><font face="monospace">I would like to float an idea before
        writing a patch, to find out whether<br>
        it is wanted and to get the design right.<br>
        <br>
        Problem<br>
        -------<br>
        <br>
        libpq looks up a password in .pgpass using the connection's host
        and port<br>
        as part of the key (host:port:database:user:password). When a
        client<br>
        connects through an SSH tunnel, or through a connection pooler
        that<br>
        listens on a different local port, the port that libpq actually
        connects<br>
        to is not the port of the real server. As a result, the .pgpass
        lookup is<br>
        done against the local/tunnel port and fails to match entries
        written for<br>
        the real server port.<br>
        <br>
        Concretely, suppose the real server is db.example.com:5432 and a
        user<br>
        opens an SSH tunnel so that 127.0.0.1:54321 forwards to it. The
        natural<br>
        .pgpass entry is:<br>
        <br>
            db.example.com:5432:appdb:alice:secret<br>
        <br>
        The client then connects with host=db.example.com (kept for
        .pgpass and<br>
        TLS), hostaddr=127.0.0.1 and port=54321 (the tunnel). libpq
        looks up<br>
        <br>
            db.example.com:54321:appdb:alice<br>
        <br>
        which does not match the 5432 entry, so no password is found and
        the user<br>
        is prompted (or the connection fails under -w).<br>
        <br>
        The host side of this exact problem was already solved<br>
        -------------------------------------------------------<br>
        <br>
        libpq already decouples the *host* used for the .pgpass lookup
        from the<br>
        real network endpoint: hostaddr gives the address actually
        connected to,<br>
        while host remains the logical name used for the .pgpass lookup
        and for<br>
        TLS verification. This is the pwhost logic in fe-connect.c,
        which goes<br>
        back to the 2018 thread "Bizarre behavior in libpq's searching
        of<br>
        ~/.pgpass":<br>
        <br>
           
        <a class="moz-txt-link-freetext" href="https://www.postgresql.org/message-id/30805.1532749137%40sss.pgh.pa.us">https://www.postgresql.org/message-id/30805.1532749137%40sss.pgh.pa.us</a><br>
        <br>
        The port has no equivalent. passwordFromFile() is called with<br>
        conn-&gt;connhost[i].port, i.e. the real connection port, with
        no way to say<br>
        "connect to this port, but look up .pgpass under that port". The
        host has<br>
        host/hostaddr; the port only has port. This proposal is to close
        that<br>
        asymmetry.<br>
        <br>
        Why the port wildcard is not enough<br>
        -----------------------------------<br>
        <br>
        One can write the entry with a wildcard port:<br>
        <br>
            db.example.com:*:appdb:alice:secret<br>
        <br>
        and it does match the tunnel. But the wildcard over-matches: a
        single<br>
        local forwarding port (say 54321, or even a fixed local port
        reused for<br>
        several tunnels at different times) ends up matching every
        server reached<br>
        through that port, so the same password line can be applied to
        different<br>
        servers. That is precisely the kind of "password sent to the
        wrong<br>
        server" situation the 2018 host fix was trying to avoid. The
        wildcard<br>
        trades safety for convenience; it is not a substitute for
        matching the<br>
        real server port.<br>
        <br>
        Proposal<br>
        --------<br>
        <br>
        Add a libpq connection parameter that specifies the port to be
        used for<br>
        the .pgpass lookup, independently of the port libpq connects to.
        The<br>
        connection still uses port (and hostaddr); only the
        password-file lookup<br>
        key uses the new value. When the new parameter is not set,
        behavior is<br>
        unchanged: the lookup uses port exactly as today.<br>
        <br>
        I do not have a strong opinion on the name and would rather not
        bikeshed<br>
        it before the idea itself is judged. Candidates that came to
        mind:<br>
        <br>
            - pgpassport / passfileport (it only affects the password
        file)<br>
            - portaddr (mirrors hostaddr: "port stays logical, portaddr
        is the<br>
              real endpoint"), though that would invert today's meaning
        of port,<br>
              which is probably too invasive<br>
        <br>
        A dedicated parameter that affects only the .pgpass lookup (the
        first<br>
        option) seems the least surprising and the smallest change. It
        is also<br>
        easy to reason about for security: it is an explicit, opt-in
        assertion by<br>
        the user, exactly like hostaddr/host.<br>
        <br>
        This is not hypothetical. I ran into it myself while adding SSH
        tunnel<br>
        support to pgcli (a widely used Postgres CLI): with the tunnel
        active, an<br>
        explicit-port .pgpass entry never matches, because the lookup
        happens<br>
        against the random local forwarding port. The user is prompted
        for a<br>
        password even though the matching entry is right there, and only
        a<br>
        wildcard port papers over it. Other tools hit the same wall:<br>
        <br>
            - pgcli: SSH tunnel rewrites the port before the .pgpass
        lookup<br>
              <a class="moz-txt-link-freetext" href="https://github.com/dbcli/pgcli/pull/1546">https://github.com/dbcli/pgcli/pull/1546</a><br>
            - DBeaver: .pgpass looked up by 127.0.0.1 through an SSH
        tunnel<br>
              <a class="moz-txt-link-freetext" href="https://github.com/dbeaver/dbeaver/issues/16499">https://github.com/dbeaver/dbeaver/issues/16499</a><br>
            - pgAdmin 4: control the SSH tunnel local port for .pgpass
        matching<br>
              <a class="moz-txt-link-freetext" href="https://github.com/pgadmin-org/pgadmin4/issues/6903">https://github.com/pgadmin-org/pgadmin4/issues/6903</a><br>
        <br>
        Questions for the list<br>
        ----------------------<br>
        <br>
          1. Is decoupling the .pgpass-lookup port from the connection
        port<br>
             something libpq wants, given that host/hostaddr already
        does the<br>
             equivalent for the host?<br>
          2. Is a dedicated lookup-only parameter the right shape, or
        would you<br>
             prefer a different model?<br>
          3. Naming preferences?<br>
        <br>
        If there is interest, I am happy to write the patch (code, docs
        and<br>
        tests).<br>
        <br>
        Thanks for reading,<br>
        Diego<br>
      </font><br>
    </p>
  </body>
</html>

--------------k2o0ot1R40J5vQaz7SOq0NCc--