[Proposal] add portaddr like hostaddr

Hi Hackers!

tl;dr: I want to add portaddr, like hostaddr, when the ssh tunnels uses
dynamic ports is a mess.
       From my last attempt, [1], I undertand it sounds better in my
mind that the first try ;p

Proposal
--------

Add a libpq connection parameter that says where the server is actually
reached, so that port can go on identifying it -- exactly what hostaddr
does for host.  The .pgpass lookup then stays on the logical (host, port)
pair, and the password file code does not change at all.

I do not have a strong opinion on the name: portaddr is the one that
mirrors hostaddr, but I am happy to take suggestions.

It is also easy to reason about for security: like hostaddr, it is an
explicit, opt-in assertion by the user, and nothing changes unless it is
given.

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:

- pgAdmin 4: control the SSH tunnel local port for .pgpass matching
https://github.com/pgadmin-org/pgadmin4/issues/6903

- DBeaver: .pgpass looked up by 127.0.0.1 through an SSH tunnel
https://github.com/dbeaver/dbeaver/issues/16499

- pgcli: SSH tunnel rewrites the port before the .pgpass lookup (myself)
https://github.com/dbcli/pgcli/pull/1546

Recap, since the approach changed
---------------------------------

The first version of this proposal added a parameter that affected only
the .pgpass lookup.  Christoph pointed out [2] that for a tunnel you open
by hand, with a fixed local port, you can simply write that port into
.pgpass.  That is correct, and I withdrew that part of the motivation.

What remains is the case where the local port is not known when the
password file is written, because the tunnel is not opened by hand.
Clients that open it for you bind the local end to port 0 and let the
kernel choose.  pgcli, the case I ran into, does exactly that: it hands
sshtunnel a local bind address with no port in it,

    "local_bind_address": ("127.0.0.1",),

and only afterwards asks which port it got,

    port = self.ssh_tunnel.local_bind_ports[0]

which it then substitutes into the connection string.  The forwarding
port is different on every run, and those two lines are precisely what
breaks the .pgpass lookup.  pgAdmin 4 and DBeaver open their own tunnels
the same way, which is what the two reports above are about, and outside
SSH the same shape shows up in kubectl port-forward, where ":5432" means
"listen on a random local port".

There is no port you can write into .pgpass in advance.

(Plain ssh(1) cannot even express this: OpenSSH rejects port 0 on -L and
accepts it only for -R, so the wrappers pick a free local port
themselves and pass it to -L.  Same outcome for .pgpass.)

The model
---------

libpq already allows the host that identifies a server to differ from the
address it is reached at:

    host identifies the server, hostaddr is where we connect,
    and the password file is searched by host.

v2 does the same for ports, instead of adding a password-file-only knob:

    port identifies the server, portaddr is where we connect,
    and the password file is searched by port.

The tunnel case is then spelled

    host=db.example.com hostaddr=127.0.0.1 port=5432 portaddr=39907

and the password file entry is the one a direct connection already uses:

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

What I like about this shape is that the password file code does not
change at all.  passwordFromFile() already receives connhost[i].port, so
keeping portaddr in a separate field leaves the lookup logical by
construction.  That is the same reasoning as e3f99e03e2e, which settled
that the .pgpass host key is host and not hostaddr.

The patches
-----------

Both patches are attached.  They apply cleanly on master as of
957d4eae52e.

0001  libpq: add portaddr, the port equivalent of hostaddr
      The parameter, the PGPORTADDR environment variable, documentation,
      and a TAP test.  It also contains a psql \connect fix, see below.

0002  libpq: add PQportaddr(), and show the port address in psql \conninfo
      The accessor mirroring PQhostaddr(), and the psql display. This one
      is separable: if the list does not want it, 0001 stands on its own.

The new test, src/interfaces/libpq/t/007_portaddr.pl, needs the server to
listen on TCP, so it is skipped unless PG_TEST_EXTRA lists portaddr:

    make check-world PG_TEST_EXTRA=portaddr

The environment variable works for meson builds as well.  Without it the
test plans skip_all and nothing else in the suite is affected.

Decisions I made, and where I would like guidance
-------------------------------------------------

* portaddr applies to TCP only, and is ignored for Unix-domain socket
  connections, whose socket file name is built from port. hostaddr does
  not apply to those either.

* The list semantics follow port rather than hostaddr: a single portaddr
  applies to every host, otherwise the element count must match the host
  count, and an empty item means "use the corresponding port". hostaddr
  has to match exactly because it determines the number of hosts;
  portaddr does not.

* port is still parsed and range-checked even when portaddr overrides it.
  hostaddr does not validate host, but any string is a plausible host
  name, whereas a port has a syntax; silently accepting a bad value that
  then becomes the password file search key seemed worse than erroring
  out.

* PQport() keeps returning port, mirroring PQhost(), which returns host
  and not hostaddr.  Its documentation promised "the port actually
  connected to", so I reworded it, and 0002 adds PQportaddr() for the
  actual port.

* Connection failure messages report the port actually attempted, since
  "connection to server at ..., port N failed" is about where we tried to
  go.

* psql's \connect drops an inherited hostaddr when the host argument
  changes.  portaddr needs the same treatment, and it has to key on both:
  a portaddr describes where to reach one particular server, so 0001 drops
  it when either the host or the port argument changes.  Without that,
  "\c - - otherhost" would keep tunnelling to the old portaddr on a
  different machine.  This is in 0001 because leaving it out is a bug, not
  a missing polish item.

* PGPORTADDR is added to the environment variables cleared by pg_regress
  and PostgreSQL::Test::Utils, next to PGHOSTADDR, which is scrubbed there
  for exactly the same reason: it would silently redirect test connections
  away from the temporary cluster.

* The new TAP test has to make the server listen on TCP, since portaddr
  does not apply to Unix-domain sockets.  Following ssl, kerberos, ldap and
  load_balance, it runs only when PG_TEST_EXTRA lists portaddr, and
  regress.sgml documents the new value.  I am not attached to the name, or
  to having a keyword of its own rather than folding it into an existing
  one.

The POC #1 first flight
-----------------------

daf(at)t:postgres$ test=/home/daf/scripts/postgres/portaddr-demo
daf(at)t:postgres$ BIN_CON="$test/con-el-patch/usr/local/bin"
daf(at)t:postgres$ LIB_CON="$test/con-el-patch/usr/local/lib/x86_64-linux-gnu"
daf(at)t:postgres$ PSQL_CON="$test/con-el-patch/usr/local/bin/psql"
daf(at)t:postgres$ LIB_SIN="$test/sin-patch/usr/local/lib/x86_64-linux-gnu"
daf(at)t:postgres$ PSQL_SIN="$test/sin-patch/usr/local/bin/psql"
daf(at)t:postgres$ unset PGPASSWORD PGPORTADDR
daf(at)t:postgres$ export LD_LIBRARY_PATH="$LIB_CON"
daf(at)t:postgres$ export PGDATA=/tmp/pgd-test-portaddr
daf(at)t:postgres$ export PGPASSFILE=/tmp/pgpass-test-portaddr
daf(at)t:postgres$
daf(at)t:postgres$ # init
daf(at)t:postgres$
daf(at)t:postgres$ "$BIN_CON/initdb" -D "$PGDATA" -U postgres -A trust
--no-sync
The files belonging to this database system will be owned by user "daf".
This user must also own the server process.

The database cluster will be initialized with locale "C.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are enabled.

creating directory /tmp/pgd-test-portaddr ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default "max_connections" ... 100
selecting default "shared_buffers" ... 128MB
selecting default time zone ... America/Argentina/Buenos_Aires
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok

Sync to disk skipped.
The data directory might become corrupt if the operating system crashes.

Success. You can now start the database server using:

/home/daf/scripts/postgres/portaddr-demo/con-el-patch/usr/local/bin/pg_ctl
-D /tmp/pgd-test-portaddr -l logfile start

daf(at)t:postgres$ printf 'local all all trust\nhost all all 127.0.0.1/32
scram-sha-256\n' > "$PGDATA/pg_hba.conf"
daf(at)t:postgres$ cat "$PGDATA/pg_hba.conf"
local all all trust
host all all 127.0.0.1/32 scram-sha-256
daf(at)t:postgres$ "$BIN_CON/pg_ctl" -D "$PGDATA" -o "-p 5441 -c
listen_addresses=127.0.0.1" -w start
waiting for server to start....2026-08-13 14:23:31.316 -03 [1018117]
LOG:  starting PostgreSQL 20devel on x86_64-linux, compiled by
gcc-12.2.0, 64-bit
2026-08-13 14:23:31.316 -03 [1018117] LOG:  listening on IPv4 address
"127.0.0.1", port 5441
2026-08-13 14:23:31.319 -03 [1018117] LOG:  listening on Unix socket
"/tmp/.s.PGSQL.5441"
2026-08-13 14:23:31.325 -03 [1018122] LOG:  database system was shut
down at 2026-08-13 14:22:49 -03
2026-08-13 14:23:31.330 -03 [1018117] LOG:  database system is ready to
accept connections
 done
server started
daf(at)t:postgres$ "$BIN_CON/psql" -X -p 5441 -U postgres -h /tmp -d
postgres -c "create role tuser login password 'sekret';"
CREATE ROLE
daf(at)t:postgres$
daf(at)t:postgres$ echo "127.0.0.1:5440:postgres:tuser:sekret" > "$PGPASSFILE"
daf(at)t:postgres$ chmod 600 "$PGPASSFILE"
daf(at)t:postgres$

daf(at)t:postgres$
daf(at)t:postgres$ cat "$PGPASSFILE"
127.0.0.1:5440:postgres:tuser:sekret
daf(at)t:postgres$
daf(at)t:postgres$ # 1) Original without patch, FAIL -> bug: .pgpass not match
LD_LIBRARY_PATH="$LIB_SIN" "$PSQL_SIN" -X -w "host=127.0.0.1 port=5441
user=tuser dbname=postgres" -tAc "select 'AUTENTICO-OK'"
psql: error: connection to server at "127.0.0.1", port 5441 failed:
fe_sendauth: no password supplied
daf(at)t:postgres$
daf(at)t:postgres$ # 2) without patch + portaddr -> FAIL
LD_LIBRARY_PATH="$LIB_SIN" "$PSQL_SIN" -X -w "host=127.0.0.1 port=5440
portaddr=5441 user=tuser dbname=postgres" -tAc "select 'AUTENTICO-OK'"
psql: error: invalid connection option "portaddr"
daf(at)t:postgres$
daf(at)t:postgres$ # 3) WITH patch + portaddr -> AUTENTICA (same line as
#2, different libpq)
LD_LIBRARY_PATH="$LIB_CON" "$PSQL_CON" -X -w "host=127.0.0.1 port=5440
portaddr=5441 user=tuser dbname=postgres" -tAc "select 'AUTENTICO-OK'"
AUTENTICO-OK
daf(at)t:postgres$
daf(at)t:postgres$ # 4) WITH patch, no portaddr -> FAIL, default not changed
LD_LIBRARY_PATH="$LIB_CON" "$PSQL_CON" -X -w "host=127.0.0.1 port=5441
user=tuser dbname=postgres" -tAc "select 'AUTENTICO-OK'"
psql: error: connection to server at "127.0.0.1", port 5441 failed:
fe_sendauth: no password supplied
daf(at)t:postgres$
daf(at)t:postgres$ # 5) WITH patch, env var instead of the parameter ->
AUTENTICA
PGPORTADDR=5441 LD_LIBRARY_PATH="$LIB_CON" "$PSQL_CON" -X -w
"host=127.0.0.1 port=5440 user=tuser dbname=postgres" -tAc "select
'AUTENTICO-OK'"
AUTENTICO-OK
daf(at)t:postgres$ # 6) WITH patch, what the client sees -> Server Port
5440 / Port Address 5441
LD_LIBRARY_PATH="$LIB_CON" "$PSQL_CON" -X -w "host=127.0.0.1 port=5440
portaddr=5441 user=tuser dbname=postgres" -c "\conninfo"
      Connection Information
      Parameter       |   Value
----------------------+-----------
 Database             | postgres
 Client User          | tuser
 Host                 | 127.0.0.1
 Server Port          | 5440
 Port Address         | 5441
 Options              |
 Protocol Version     | 3.2
 Password Used        | true
 GSSAPI Authenticated | false
 Backend PID          | 1018919
 SSL Connection       | false
 Superuser            | off
 Hot Standby          | off
(13 rows)

daf(at)t:postgres$
daf(at)t:postgres$ 2026-08-13 14:28:31.428 -03 [1018120] LOG: checkpoint
starting: time
2026-08-13 14:28:36.176 -03 [1018120] LOG:  checkpoint complete: time:
wrote 47 buffers (0.3%), wrote 3 SLRU buffers; 0 WAL file(s) added, 0
removed, 0 recycled; write=4.713 s, sync=0.024 s, total=4.748 s; sync
files=15, longest=0.018 s, average=0.002 s; distance=345 kB,
estimate=345 kB; lsn=0/017D4910, redo lsn=0/017D4878

"$BIN_CON/pg_ctl" -D "$PGDATA" -m immediate stop
waiting for server to shut down....2026-08-13 14:29:17.551 -03 [1018117]
LOG:  received immediate shutdown request
2026-08-13 14:29:17.563 -03 [1018117] LOG:  database system is shut down
 done
server stopped

The POC #2 withreal case
------------------------

daf(at)t:postgres$ # ---- setup
daf(at)t:postgres$ test=/home/daf/scripts/postgres/portaddr-demo
daf(at)t:postgres$ LIB_CON="$test/con-el-patch/usr/local/lib/x86_64-linux-gnu"
daf(at)t:postgres$ PSQL_CON="$test/con-el-patch/usr/local/bin/psql"
daf(at)t:postgres$ LIB_SIN="$test/sin-patch/usr/local/lib/x86_64-linux-gnu"
daf(at)t:postgres$ PSQL_SIN="$test/sin-patch/usr/local/bin/psql"
daf(at)t:postgres$ SSH_HOST=beta
daf(at)t:postgres$ REAL_HOST=beta.xxxx.yyyy.zzz
daf(at)t:postgres$ REAL_PORT=55432
daf(at)t:postgres$ LOCAL_PORT=5533
daf(at)t:postgres$ SSH_SOCK=/tmp/portaddr-beta-ssh.sock
daf(at)t:postgres$
daf(at)t:postgres$ unset PGPASSWORD PGPORTADDR
daf(at)t:postgres$ export PGPASSFILE=/tmp/pgpass-demo-portaddr-beta
daf(at)t:postgres$
daf(at)t:postgres$ ssh -M -S "$SSH_SOCK" -f -N "$SSH_HOST"
daf(at)t:postgres$ ssh -S "$SSH_SOCK" -O forward -L
$LOCAL_PORT:localhost:$REAL_PORT "$SSH_HOST"
daf(at)t:postgres$ ss -ltn | grep $LOCAL_PORT
LISTEN 0      128        127.0.0.1:5533       0.0.0.0:*
daf(at)t:postgres$
daf(at)t:postgres$ cat "$PGPASSFILE"
127.0.0.1:55432:*:daf:sekret
daf(at)t:postgres$ chmod 600 "$PGPASSFILE"
daf(at)t:postgres$
daf(at)t:postgres$
daf(at)t:postgres$ # 1) Original without patch, FAIL -> bug: .pgpass no
matchea el puerto del tunel
LD_LIBRARY_PATH="$LIB_SIN" "$PSQL_SIN" -X -w "host=127.0.0.1
port=$LOCAL_PORT user=daf dbname=daf" -tAc "select 'AUTENTICO-OK'"
psql: error: connection to server at "127.0.0.1", port 5533 failed:
fe_sendauth: no password supplied
daf(at)t:postgres$
daf(at)t:postgres$ # 2) without patch + portaddr -> FAIL
LD_LIBRARY_PATH="$LIB_SIN" "$PSQL_SIN" -X -w "host=127.0.0.1
port=$REAL_PORT portaddr=$LOCAL_PORT user=daf dbname=daf" -tAc "select
'AUTENTICO-OK'"
psql: error: invalid connection option "portaddr"
daf(at)t:postgres$
daf(at)t:postgres$ # 3) WITH patch + portaddr -> AUTENTICA (same like #2,
other libpq)
LD_LIBRARY_PATH="$LIB_CON" "$PSQL_CON" -X -w "host=127.0.0.1
port=$REAL_PORT portaddr=$LOCAL_PORT user=daf dbname=daf" -tAc "select
'AUTENTICO-OK'"
AUTENTICO-OK
daf(at)t:postgres$ # 4) WITH patch, no portaddr -> FAIL, same default
LD_LIBRARY_PATH="$LIB_CON" "$PSQL_CON" -X -w "host=127.0.0.1
port=$LOCAL_PORT user=daf dbname=daf" -tAc "select 'AUTENTICO-OK'"
psql: error: connection to server at "127.0.0.1", port 5533 failed:
fe_sendauth: no password supplied
daf(at)t:postgres$
daf(at)t:postgres$ # 5) WITH patch, env var en vez del parametro -> AUTENTICA
PGPORTADDR=$LOCAL_PORT LD_LIBRARY_PATH="$LIB_CON" "$PSQL_CON" -X -w
"host=127.0.0.1 port=$REAL_PORT user=daf dbname=daf" -tAc "select
'AUTENTICO-OK'"
AUTENTICO-OK
daf(at)t:postgres$
daf(at)t:postgres$ # 6) WITH patch, lo que ve el cliente -> Server Port
55432 / Port Address 5533 / Password Used true
LD_LIBRARY_PATH="$LIB_CON" "$PSQL_CON" -X -w "host=127.0.0.1
port=$REAL_PORT portaddr=$LOCAL_PORT user=daf dbname=daf" -c "\conninfo"
      Connection Information
      Parameter       |   Value
----------------------+-----------
 Database             | daf
 Client User          | daf
 Host                 | 127.0.0.1
 Server Port          | 55432
 Port Address         | 5533
 Options              |
 Protocol Version     | 3.0
 Password Used        | true
 GSSAPI Authenticated | false
 Backend PID          | 290199
 SSL Connection       | false
 Superuser            | on
 Hot Standby          | off
(13 rows)

daf(at)t:postgres$
daf(at)t:postgres$
# ---------------------------------------------------------------
# 7) THE MAIN CASE: the full symmetry, using your REAL ~/.pgpass untouched
#
#   host     = beta.xxxx.yyyy.zzz   hostaddr = 127.0.0.1
#   port     = 55432                      portaddr = 5533
#
# The LOGICAL pair (host, port) is how you know the server, and what the
.pgpass
# lookup uses; the PHYSICAL pair (hostaddr, portaddr) is where the
socket really
# goes. Without portaddr, hostaddr alone is not enough: you can lie
about the
# host, but the port still gives you away.

daf(at)t:postgres$ unset PGPASSFILE
daf(at)t:postgres$
daf(at)t:postgres$ # without patch: hostaddr has been there for years, but
the lookup still uses the tunnel port -> FAILS
LD_LIBRARY_PATH="$LIB_SIN" "$PSQL_SIN" -X -w "host=$REAL_HOST
hostaddr=127.0.0.1 port=$LOCAL_PORT user=daf dbname=daf" -tAc "select
'AUTENTICO-OK'"
psql: error: connection to server at "127.0.0.1", port 5533 failed:
fe_sendauth: no password supplied
daf(at)t:postgres$
daf(at)t:postgres$ # WITH patch: (host,port) for the lookup +
(hostaddr,portaddr) for the socket -> AUTHENTICATES
LD_LIBRARY_PATH="$LIB_CON" "$PSQL_CON" -X -w "host=$REAL_HOST
hostaddr=127.0.0.1 port=$REAL_PORT portaddr=$LOCAL_PORT user=daf
dbname=daf" -tAc "select 'AUTENTICO-OK'"
AUTENTICO-OK
daf(at)t:postgres$
daf(at)t:postgres$ # \conninfo shows the 4 rows together: Host / Host
Address / Server Port / Port Address
LD_LIBRARY_PATH="$LIB_CON" "$PSQL_CON" -X -w "host=$REAL_HOST
hostaddr=127.0.0.1 port=$REAL_PORT portaddr=$LOCAL_PORT user=daf
dbname=daf" -c "\conninfo"
             Connection Information
      Parameter       |          Value
----------------------+--------------------------
 Database             | daf
 Client User          | daf
 Host                 | beta.xxxx.yyyy.zzz
 Host Address         | 127.0.0.1
 Server Port          | 55432
 Port Address         | 5533
 Options              |
 Protocol Version     | 3.0
 Password Used        | true
 GSSAPI Authenticated | false
 Backend PID          | 290265
 SSL Connection       | false
 Superuser            | on
 Hot Standby          | off
(14 rows)

And that's all folks.
Please, feel free to send feedback.

[1]
https://www.postgresql.org/message-id/flat/001a6f1d-4adb-42b2-8bf6-44154ed0ab97%40gmail.com
[2] https://postgr.es/m/ak6FwxXWcBTvvpPo@msg.df7cb.de

Thank you all,
BR,
Diego