Re: [Proposal] add portaddr like hostaddr

On 2026-08-26 06:55, Denis Smirnov wrote:

Hi Denis!

Thank you for the review.

tl;dr: I think the split is not new: the docs already say that host
identifies the connection in the password file while hostaddr is where
the connection goes. portaddr is the port half of the same split.
Details inline.

> 1. This is a client-side problem.
> The two ports exist only because pgcli created the tunnel. PostgreSQL
> does not know about the local port. A normal libpq connection has one
> host and one port.

The server never sees the local port, but it never sees host, hostaddr,
passfile or sslmode either: connection parameters are all client-side,
and libpq exists so that every client does not rebuild that half.

Also, the two ports do not only exist when the database client opens
the tunnel. With cloud-sql-proxy, AWS SSM port forwarding, or kubectl
port-forward (":5432" means "pick a random local port"), the mapping
belongs to tooling outside the database client, and the libpq client
behind it is often plain psql, which owns no tunnel at all. There is
no tunnel-creating client there to manage the mapping.

> 2. hostaddr is not a good model for this.
> host and hostaddr are a host name and its numeric network address.
> This distinction is needed for DNS, GSSAPI, and TLS.

And for the password file. The current docs, in the hostaddr entry:

If both host and hostaddr are specified, the value for hostaddr
gives the server network address. The value for host is ignored
unless the authentication method requires it, in which case it
will be used as the host name.
...
Also, when both host and hostaddr are specified, host is used to
identify the connection in a password file

That last sentence already states the division in question, for the
other coordinate of the same endpoint: one parameter identifies the
entry, the other is where the connection goes. And it is longstanding
behavior, not an accident. As the commit message of e3f99e03e2e puts
it, before v10 libpq "always searched ~/.pgpass using the host
parameter, and nothing else"; when v10 briefly searched by hostaddr
instead, that was fixed as a bug, because it contradicted the
documentation. The lookup key is part of the split's contract.

In code, when both are given, host is not resolved at all, and its
remaining duties are identity duties: the .pgpass search key, the
verify-full certificate check, SNI, the GSSAPI principal. hostaddr
decides one thing: where the socket dials. So the distinction libpq
actually implements is not "a name and its own address"; it is "which
server I mean" and "where I reach it".

> Ports do not have names and addresses. port and portaddr would be two
> different ports with different purposes:
> * port would identify an entry in .pgpass;
> * portaddr would be the port used for the connection.
> This gives a new and unclear meaning to port.

I agree that port's identity duties are fewer than host's: no TLS or
GSSAPI role, only the password file plus everything that reports the
connection (psql's prompt, \conninfo, PQport). But the password file
identifies the server by the pair hostname:port, and today only half
of that key can stay logical when the route differs. An endpoint has
two coordinates, host and port; hostaddr split the first, portaddr
completes the second, and there is no third one to creep toward: a
tunnel rewrites exactly these two and nothing else.

And port keeps its meaning under the patch: the server's port, what
the prompt and \conninfo already show. It is the tunnel case today
that forces port into a strange meaning: you must overwrite it with an
ephemeral number that identifies nothing, and every display then
reports that artifact.

> 3. The change is much larger than the use case.
> It adds a new libpq parameter, environment variable, public function,
> and special handling in psql, pg_isready, pg_upgrade, and tests.

Of the 585 lines the v3 series adds, 137 are libpq logic; 58% is tests
and documentation (the TAP suite by itself is larger than the core).
That is the usual checklist for a connection option: load_balance_hosts
(7f5b19817ea) added 431 lines of the same shape: parameter, environment
variable, docs, TAP suites, CI.

As for the special handling, all three tools already contain the
hostaddr half of it: pg_isready has printed hostaddr in preference to
host since 2013 (38f43289813), pg_upgrade's check_pghost_envvar
already polices PGHOSTADDR, and psql already shows a Host Address row
and drops a stale hostaddr on \connect. The series fills in the port
column of those existing patterns (44 lines across the three programs
in 0001). For the rest, portaddr follows port's existing list rule
and rides the same surfaces hostaddr already rides (URI, service
file, environment), so the concept cost is the completion of a charge
hostaddr already paid. The public function is in 0002, which, as I
said in the v2 mail, is separable: 0001 stands on its own.

> Also, the DBeaver example does not show a need in libpq because
> DBeaver uses pgJDBC.

You are right, and I withdraw DBeaver as libpq evidence. What it
still shows is where the trap lives. DBeaver reimplemented the
.pgpass model in its own code, hit exactly this failure
(dbeaver/dbeaver#16499), and fixed it with an option that overrides
the pgpass hostname; its tunnel settings also let the user pin the
local port. Use both and the entry that results is half logical,
half invented: a key for an endpoint that exists nowhere. A client
that needs two workarounds to approximate hostaddr's split is not an
argument against giving the split one shared home. (pgJDBC and
Npgsql reimplement the same file too, with no override at all.) The
report that shows the need in libpq is pgAdmin, which drives libpq
through psycopg [3].

> The client that creates the tunnel should also manage the mapping
between:
> * the original database endpoint; and
> * the local tunnel endpoint.
> It should handle the password lookup itself, use a tunnel-specific
> passfile, or manage its local ports in another way.

pgAdmin has been doing exactly that, client-side, since at least 2012,
and it shows how far a client can get. The trap was reported inside
pgAdmin development in 2012 [1]. In 2018 the request to make .pgpass
work through pgAdmin's tunnels was rejected as impossible by the
project lead: ".pgpass files cannot be used with SSH tunnelling
because a random port is used for the tunnel, so libpq cannot match
the port number used to one in the pgpass file" [2]. In 2023 pgAdmin
adopted the host + hostaddr split for its tunnels ("Added 'hostaddr'
and used host string as it is while creating SSHTunnel", pgAdmin
commit 81dcc917), so the host half now stays logical for .pgpass and
TLS -- and it still has to hand libpq the ephemeral bind port in
port=, because nothing preserves the port half. The still-open
request for a pinnable local port [3] exists to serve the passfile
lookup, and its reporter also shows what the wildcard workaround
costs: their production and test servers differ only by password, so
host:*:... entries collide under first-match-wins.

pgcli (my fix, dbcli/pgcli#1546) converged on the same point:
preserve the host half through hostaddr, find nothing that preserves
the port half.

On handling the lookup in the client: I wrote that too, for pgcli,
before proposing this. It is a hundred lines plus tests that must
reproduce passwordFromFile's documented behavior (escaping, wildcards,
first match wins, permissions, the localhost rules). A
tunnel-specific passfile needs the same matching logic first, to know
which lines to copy, plus a second file of live passwords to clean up.
Multiply by every tunnel-opening client. A client that does this
correctly has rebuilt the passfile half of libpq and privatized the
split: look up by the coordinates you mean, dial the ones you were
given.

And managing local ports "in another way" means pinning a fixed local
port. Where that is available I already agreed with Christoph: write
the pinned port into .pgpass and you need no patch. But it does not
reach the tunnels above that the database client does not own, nor a
client like a shared pgAdmin server, which multiplexes tunnels for
many users and servers out of one machine's port space. And even
where it works, the passfile entry now keys an invented number that
describes no server.

> A pgcli implementation detail should not become a new public libpq
> connection concept.

I see it less as a new concept than as the second half of a split
libpq has documented for host since before v10. pgcli is just where
I hit the port half of it; pgAdmin hit it in 2012.

I will add the patch to the commitfest so it does not get lost, and I
would value your review continuing there. More opinions on the model
question are very welcome. I am not attached to the name -- but I am
attached to the shape: a lookup-only knob (what v1 of this proposal
was) leaves port holding the ephemeral number, so the prompt,
\conninfo and PQport keep reporting an artifact; portaddr keeps port
meaning the server's port for every consumer at once.

[1]
https://www.postgresql.org/message-id/CANxoLDev2%2BtUi%2BXXyyJZjYrJikRY5aG9eo5qf_Nc%3DrNa0BkzHQ%40mail.gmail.com
[2] https://github.com/pgadmin-org/pgadmin4/issues/1958
[3] https://github.com/pgadmin-org/pgadmin4/issues/6903

Thank you,
BR,
Diego