[PATCH] - Re: libpq: decouple the .pgpass lookup port from the connection port

Hi,

Following up on my proposal from last week. Since the idea didn't draw
objections, I went ahead and wrote the patch (code, docs and a TAP test)
so the discussion can be more concrete. v1 attached.

It compiles cleanly, the new TAP test 008_passfileport passes (10
subtests), and the full authentication suite stays green. I also
verified it end-to-end against a real server reached through an SSH
tunnel: the .pgpass entry matches with passfileport set and fails
without it.

I'd really value your read on whether the
host/hostaddr-style decoupling is the right shape here, and on the
parameter name (still tentative).

I'm not a professional C developer and I need help.
Ofc, I used Cursor and Claude to review the code before this mail. ;P

Thanks,
Diego

On 2026-06-23 12:57, Diego wrote:
>
> 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
>