[Proposal] add portaddr like hostaddr

Hi Hackers!

Attached is v3 of the portaddr series. No design changes; this is the
result of a self-review pass over v2, plus a rebase onto current master
(applies cleanly on 957d4eae52e).

Changes since v2:

* The new TAP test is now actually exercised by CI: portaddr was missing
from PG_TEST_EXTRA in the CI workflow, so 007_portaddr.pl was skipped
on every automated run. Same treatment as load_balance got in
7f5b19817ea.

* pg_isready now prints the port it actually probes when portaddr is
given, the same way it already prefers hostaddr over host in its
output. Previously it probed portaddr but printed port.

* pg_upgrade refuses to run with PGPORTADDR set. check_pghost_envvar()
exists precisely to keep a stray libpq environment variable from
silently redirecting pg_upgrade's connections, and PGPORTADDR is a
redirect hazard of exactly that class (the series already scrubs it in
pg_regress and PostgreSQL::Test::Utils for the same reason).

* pg_regress --use-existing now also drops an inherited PGPORTADDR when
--host alone is overridden, not only --port, matching the series' own
rule that a portaddr is stale when either half of the endpoint changes.

* Added the LIBPQ_HAS_PORTADDR feature macro for PQportaddr(), per the
policy of shipping a feature-test macro with new libpq API (6991e774e).

* psql: the \conninfo "Port Address" row is no longer suppressed when a
socket-path host is overridden into a TCP connection by hostaddr (the
one corner where the row was hidden while portaddr was in effect), and
the \connect success banner now reports the port address when it
differs, mirroring what 6e5f8d489a did for hostaddr.

* Documentation touch-ups: PQport() now spells out the
portaddr-without-port case (returns the default port number); the
pgpass section states the default-port fallback for the port field,
matching the adjacent host-field sentence; a \connect wording fix in
the psql reference page.

* Commit message fixes, including removing the "ssh -L 127.0.0.1:0:..."
example: OpenSSH rejects port 0 on -L (it is only valid for -R), which
is exactly why the tools that need a dynamic local port pick a free
one themselves right before opening the tunnel.

Thank you,
BR,
Diego

Hi,

I do not think portaddr should be added to libpq.

Context
-------

The main use case comes from pgcli:

* pgcli opens an SSH tunnel on a random local port;
* it replaces the original database port with that local port;
* the existing .pgpass entry no longer matches;
* portaddr is proposed so that libpq connects to the local port but
searches .pgpass using the original port.

Why this should not be in libpq
-------------------------------

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.

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.

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.

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.

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

What should happen instead
--------------------------

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.

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

Best regards,
Denis Smirnov

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]https://github.com/pgadmin-org/pgadmin4/issues/6903.

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]/messages/by-id/CANxoLDev2+tUi+XXyyJZjYrJikRY5aG9eo5qf_Nc=rNa0BkzHQ@mail.gmail.com. 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]https://github.com/pgadmin-org/pgadmin4/issues/1958. 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]https://github.com/pgadmin-org/pgadmin4/issues/6903 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]: /messages/by-id/CANxoLDev2+tUi+XXyyJZjYrJikRY5aG9eo5qf_Nc=rNa0BkzHQ@mail.gmail.com
/messages/by-id/CANxoLDev2+tUi+XXyyJZjYrJikRY5aG9eo5qf_Nc=rNa0BkzHQ@mail.gmail.com
[2]: https://github.com/pgadmin-org/pgadmin4/issues/1958
[3]: https://github.com/pgadmin-org/pgadmin4/issues/6903

Thank you,
BR,
Diego

Hi Diego,

I agree that this is a client-side issue and that libpq is a client
library. But that does not mean libpq should model the whole route to
the server.

A route can contain several SSH hops, proxies, and poolers:

libpq -> localhost:39907 -> SSH jump1 -> SSH jump2
-> PgBouncer:6432 -> PostgreSQL:5432

It is not clear why libpq should expose exactly two endpoints. Which
port is the "server port" here: 39907, 6432, or 5432? Only 39907 is
visible to libpq.

I think libpq should keep one connection endpoint, as it does today.
port and PQport() should continue to mean the port to which libpq
actually connects.

SSH forwarding should be configured by SSH itself, for example with
ProxyJump and LocalForward in ~/.ssh/config. If an application creates
a tunnel, it should manage that mapping itself. Pooler routing should
likewise remain in the pooler configuration.

For these reasons, I do not think portaddr belongs in libpq.

Best regards,
Denis Smirnov

On 2026-08-27 00:49, Denis Smirnov wrote:

Hi Denis!

Thank you, and thank you for conceding the client-side point.

It is not clear why libpq should expose exactly two endpoints. Which
port is the "server port" here: 39907, 6432, or 5432? Only 39907 is
visible to libpq.

The same question, asked about the other coordinate, already has an
answer in libpq. In your chain:

libpq -> localhost:39907 -> SSH jump1 -> SSH jump2
-> PgBouncer:6432 -> PostgreSQL:5432

which one is the host? Only localhost is visible to libpq, and yet
"host=db.example.com hostaddr=127.0.0.1" is supported, documented, and
used in production today -- pgAdmin does exactly this for its tunnels.
libpq does not model the route there either: hostaddr is the first hop,
the only thing libpq dials, and host is who the user says is at the end
of it. Everything in between has always been invisible, and nothing in
the patch changes that.

So portaddr adds no knowledge of the route. It completes the address of
that same first hop. The patch does not expose two endpoints: it
exposes one endpoint and one identity, which is what host and hostaddr
already are.

And "which port is the server port" is answered the same way as "which
host": by the user, explicitly, or not at all. If you authenticate
against PgBouncer, the credential is PgBouncer's and port is 6432; if
the tunnel ends at PostgreSQL, it is 5432. libpq never guesses. Your
example is in fact where today's workaround breaks: with PgBouncer on
6432 and PostgreSQL on 5432 on one host, the wildcard entry that a
tunnel forces you to write, host:*:..., collapses both into whichever
line comes first.

port and PQport() should continue to mean the port to which libpq
actually connects.

PQhost() already does not mean the host to which libpq actually
connects: with hostaddr it returns the host verbatim, and PQhostaddr()
returns the address. Keeping port as the exception is the part that
needs justification.

SSH forwarding should be configured by SSH itself, for example with
ProxyJump and LocalForward in ~/.ssh/config.

Agreed for the chain itself. But LocalForward takes an explicit
[bind_address:]port, so that configuration means a fixed local port,
which is exactly the case I already granted Christoph: write it into
.pgpass and you need no patch. (ProxyJump is not something libpq can
use on its own; libpq calls connect() on a socket and never invokes
ssh.) What remains is the case where no port can be written in advance,
because it does not exist yet.

I would like to hear other opinions on the model.

Thank you,
BR,
Diego

Hi Diego,

hostaddr is the first hop, the only thing libpq dials, and host is
who the user says is at the end of it.

That is not the libpq model.

libpq describes an OS socket endpoint and, when required, a server name.
For TCP, hostaddr is the numeric destination address and port is the
destination port. When hostaddr is absent, host is resolved through DNS.
When both are present, host is retained for TLS and GSSAPI.

host is therefore a name used for address resolution and authentication,
not a general identity at the end of an arbitrary route. Its use as a
.pgpass lookup key does not change that model.

The patch does not expose two endpoints: it exposes one endpoint and
one identity.

By two endpoints I meant the two sides of the forwarding mapping created
by pgcli: the local listener and the remote database target.

The socket endpoint used by libpq is already complete:

127.0.0.1:39907

The remote target:

db.example.com:5432

belongs to the SSH forwarding configuration owned by pgcli. portaddr
would combine the address from the local endpoint with the port from the
remote endpoint. It does not complete one endpoint.

What remains is the case where no port can be written in advance,
because it does not exist yet.

The port exists before pgcli calls libpq. The current pgcli code starts
the SSH tunnel, reads local_bind_ports[0], and then passes that port to
PGExecute. libpq is never asked to connect without a known port.

pgcli creates the tunnel and owns both sides of the mapping. Once the
tunnel is started, pgcli knows the selected local port. It therefore has
all the information needed to construct consistent SSH forwarding and
libpq configuration: SSH maps the local endpoint to the remote database,
while libpq and .pgpass use that local endpoint.

How pgcli stores or generates this mapping is a pgcli implementation
detail. It does not require libpq to introduce a second port.

Best regards,
Denis Smirnov

On Thu, Aug 27, 2026 at 11:18 AM Diego <mrstephenamell@gmail.com> wrote:

I would like to hear other opinions on the model.

I think Denis has this right. You're misinterpreting what host and
hostaddr "mean", in a broader sense, and so the suggested split of
port/portaddr is drawing a parallel where none exists.

Maybe there are good features that would help the UX for a local SSH
tunnel, but I don't get the sense that this is one.

--Jacob

Hi Denis / Jacob!

Thank you both for the time you put into this, including reading the
pgcli code.

I think Denis has this right. You're misinterpreting what host and
hostaddr "mean", in a broader sense, and so the suggested split of
port/portaddr is drawing a parallel where none exists.

Point taken. Spelling out what convinced me: when hostaddr is present,
host keeps real name-based duties, TLS server name and certificate
check, GSSAPI principal. The port coordinate has no counterpart for
any of those; the only consumer of a "logical port" would have been the
passfile lookup. That is too little to hang a host/hostaddr-style
split on, so I'm dropping portaddr and withdrawing the CommitFest
entry.

Maybe there are good features that would help the UX for a local SSH
tunnel, but I don't get the sense that this is one.

May I take you up on that? The residual case is a tunnel on a dynamic
local port: no passfile line can be written in advance, and a client
that wants to keep .pgpass working today has to reimplement the
passfile parser on its side. The first version of this proposal [1]/messages/by-id/c0dfc226-9fbd-450b-a470-00f5427077a4@gmail.com
was a narrowly scoped, lookup-only knob (passfileport): it only changes
the port used in the passfile lookup, and claims nothing about
identity, PQport(), or the socket. Is that a shape worth refining, or
do you see this whole thing as belonging client-side, full stop?

Independently of that, I would like to send a small doc patch for the
pgpass section documenting the workaround that exists today (host +
hostaddr, fixed local port written into .pgpass); the same confusion
keeps resurfacing in client issue trackers.

[1]: /messages/by-id/c0dfc226-9fbd-450b-a470-00f5427077a4@gmail.com

Thank you,
BR,
Diego

Hi Diego,

I think a better solution would be to expose the existing
passwordFromFile() code through a new public libpq API, perhaps
PQpassfileLookup().

Then pgcli could look up the password using the original host and port,
and connect to the local tunnel port with that password. This would avoid
both a new connection parameter and a separate .pgpass parser in each
client.

I would be happy to review such a patch. I think this would be a useful
addition to PostgreSQL.

Best regards,
Denis Smirnov