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@t:postgres$ test=/home/daf/scripts/postgres/portaddr-demo
daf@t:postgres$ BIN_CON="$test/con-el-patch/usr/local/bin"
daf@t:postgres$ LIB_CON="$test/con-el-patch/usr/local/lib/x86_64-linux-gnu"
daf@t:postgres$ PSQL_CON="$test/con-el-patch/usr/local/bin/psql"
daf@t:postgres$ LIB_SIN="$test/sin-patch/usr/local/lib/x86_64-linux-gnu"
daf@t:postgres$ PSQL_SIN="$test/sin-patch/usr/local/bin/psql"
daf@t:postgres$ unset PGPASSWORD PGPORTADDR
daf@t:postgres$ export LD_LIBRARY_PATH="$LIB_CON"
daf@t:postgres$ export PGDATA=/tmp/pgd-test-portaddr
daf@t:postgres$ export PGPASSFILE=/tmp/pgpass-test-portaddr
daf@t:postgres$
daf@t:postgres$ # init
daf@t:postgres$
daf@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@t:postgres$ printf 'local all all trust\nhost all all 127.0.0.1/32
scram-sha-256\n' > "$PGDATA/pg_hba.conf"
daf@t:postgres$ cat "$PGDATA/pg_hba.conf"
local all all trust
host all all 127.0.0.1/32 scram-sha-256
daf@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@t:postgres$ "$BIN_CON/psql" -X -p 5441 -U postgres -h /tmp -d
postgres -c "create role tuser login password 'sekret';"
CREATE ROLE
daf@t:postgres$
daf@t:postgres$ echo "127.0.0.1:5440:postgres:tuser:sekret" > "$PGPASSFILE"
daf@t:postgres$ chmod 600 "$PGPASSFILE"
daf@t:postgres$
daf@t:postgres$
daf@t:postgres$ cat "$PGPASSFILE"
127.0.0.1:5440:postgres:tuser:sekret
daf@t:postgres$
daf@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@t:postgres$
daf@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@t:postgres$
daf@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@t:postgres$
daf@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@t:postgres$
daf@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@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@t:postgres$
daf@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@t:postgres$ # ---- setup
daf@t:postgres$ test=/home/daf/scripts/postgres/portaddr-demo
daf@t:postgres$ LIB_CON="$test/con-el-patch/usr/local/lib/x86_64-linux-gnu"
daf@t:postgres$ PSQL_CON="$test/con-el-patch/usr/local/bin/psql"
daf@t:postgres$ LIB_SIN="$test/sin-patch/usr/local/lib/x86_64-linux-gnu"
daf@t:postgres$ PSQL_SIN="$test/sin-patch/usr/local/bin/psql"
daf@t:postgres$ SSH_HOST=beta
daf@t:postgres$ REAL_HOST=beta.xxxx.yyyy.zzz
daf@t:postgres$ REAL_PORT=55432
daf@t:postgres$ LOCAL_PORT=5533
daf@t:postgres$ SSH_SOCK=/tmp/portaddr-beta-ssh.sock
daf@t:postgres$
daf@t:postgres$ unset PGPASSWORD PGPORTADDR
daf@t:postgres$ export PGPASSFILE=/tmp/pgpass-demo-portaddr-beta
daf@t:postgres$
daf@t:postgres$ ssh -M -S "$SSH_SOCK" -f -N "$SSH_HOST"
daf@t:postgres$ ssh -S "$SSH_SOCK" -O forward -L
$LOCAL_PORT:localhost:$REAL_PORT "$SSH_HOST"
daf@t:postgres$ ss -ltn | grep $LOCAL_PORT
LISTEN 0 128 127.0.0.1:5533 0.0.0.0:*
daf@t:postgres$
daf@t:postgres$ cat "$PGPASSFILE"
127.0.0.1:55432:*:daf:sekret
daf@t:postgres$ chmod 600 "$PGPASSFILE"
daf@t:postgres$
daf@t:postgres$
daf@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@t:postgres$
daf@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@t:postgres$
daf@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@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@t:postgres$
daf@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@t:postgres$
daf@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@t:postgres$
daf@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@t:postgres$ unset PGPASSFILE
daf@t:postgres$
daf@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@t:postgres$
daf@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@t:postgres$
daf@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/[email protected]
Thank you all,
BR,
Diego
From 95b18284b16a196fb51505efaedac0d4eba980df Mon Sep 17 00:00:00 2001
From: Diego <[email protected]>
Date: Mon, 27 Jul 2026 14:06:48 -0300
Subject: [PATCH v2 2/2] libpq: add PQportaddr(), and show the port address in
psql \conninfo
portaddr allows the port a connection is made to to differ from the port
that identifies the server, but there was no way to ask libpq which port
it actually used. Add PQportaddr(), which reports the port of the current
connection just as PQhostaddr() reports its IP address; like that
function, it reports nothing for Unix-domain socket connections, which
have no port.
Use it in psql's \conninfo, which grows a "Port Address" row displayed
only when the port connected to differs from the port identifying the
server -- the same rule the existing "Host Address" row follows.
Discussion: https://postgr.es/m/[email protected]
---
doc/src/sgml/libpq.sgml | 24 ++++++++++++
src/bin/psql/command.c | 18 ++++++++-
src/interfaces/libpq/exports.txt | 1 +
src/interfaces/libpq/fe-connect.c | 52 +++++++++++++++++++++++++-
src/interfaces/libpq/libpq-fe.h | 1 +
src/interfaces/libpq/libpq-int.h | 1 +
src/interfaces/libpq/t/007_portaddr.pl | 26 +++++++++++++
7 files changed, 120 insertions(+), 3 deletions(-)
diff --git a/doc/src/sgml/libpq.sgml b/doc/src/sgml/libpq.sgml
index c2b078bd6a9..3f94d39bd33 100644
--- a/doc/src/sgml/libpq.sgml
+++ b/doc/src/sgml/libpq.sgml
@@ -2860,6 +2860,30 @@ char *PQport(const PGconn *conn);
</listitem>
</varlistentry>
+ <varlistentry id="libpq-PQportaddr">
+ <term><function>PQportaddr</function><indexterm><primary>PQportaddr</primary></indexterm></term>
+
+ <listitem>
+ <para>
+ Returns the port that the active connection was actually made to.
+ This can be the port given by the <literal>port</literal> parameter,
+ or a port provided through the <literal>portaddr</literal> parameter.
+<synopsis>
+char *PQportaddr(const PGconn *conn);
+</synopsis>
+ </para>
+
+ <para>
+ <xref linkend="libpq-PQportaddr"/> returns <symbol>NULL</symbol> if the
+ <parameter>conn</parameter> argument is <symbol>NULL</symbol>.
+ Otherwise, if there is an error producing the port information (perhaps
+ if the connection has not been fully established or there was an
+ error), or if the connection is made over a Unix-domain socket, it
+ returns an empty string.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry id="libpq-PQtty">
<term><function>PQtty</function><indexterm><primary>PQtty</primary></indexterm></term>
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index 7f4090e0d8d..af428dbf2f0 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -794,6 +794,9 @@ exec_command_conninfo(PsqlScanState scan_state, bool active_branch)
char *host;
bool print_hostaddr;
char *hostaddr;
+ bool print_portaddr;
+ char *port,
+ *portaddr;
char *protocol_version,
*backend_pid;
int ssl_in_use,
@@ -815,6 +818,8 @@ exec_command_conninfo(PsqlScanState scan_state, bool active_branch)
/* Get values for the parameters */
host = PQhost(pset.db);
hostaddr = PQhostaddr(pset.db);
+ port = PQport(pset.db);
+ portaddr = PQportaddr(pset.db);
version_num = PQfullProtocolVersion(pset.db);
protocol_version = psprintf("%d.%d", version_num / 10000,
version_num % 10000);
@@ -827,6 +832,10 @@ exec_command_conninfo(PsqlScanState scan_state, bool active_branch)
print_hostaddr = (!is_unixsock_path(host) &&
hostaddr && *hostaddr && strcmp(host, hostaddr) != 0);
+ /* Likewise for the port actually connected to */
+ print_portaddr = (!is_unixsock_path(host) &&
+ portaddr && *portaddr && strcmp(port, portaddr) != 0);
+
/* Determine the exact number of rows to print */
rows = 12;
cols = 2;
@@ -834,6 +843,8 @@ exec_command_conninfo(PsqlScanState scan_state, bool active_branch)
rows += 6;
if (print_hostaddr)
rows++;
+ if (print_portaddr)
+ rows++;
/* Set it all up */
printTableInit(&cont, &pset.popt.topt, _("Connection Information"), cols, rows);
@@ -876,7 +887,12 @@ exec_command_conninfo(PsqlScanState scan_state, bool active_branch)
/* Server Port */
printTableAddCell(&cont, _("Server Port"), false, false);
- printTableAddCell(&cont, PQport(pset.db), false, false);
+ printTableAddCell(&cont, port, false, false);
+ if (print_portaddr)
+ {
+ printTableAddCell(&cont, _("Port Address"), false, false);
+ printTableAddCell(&cont, portaddr, false, false);
+ }
/* Options */
printTableAddCell(&cont, _("Options"), false, false);
diff --git a/src/interfaces/libpq/exports.txt b/src/interfaces/libpq/exports.txt
index 1e3d5bd5867..2ac1198bc00 100644
--- a/src/interfaces/libpq/exports.txt
+++ b/src/interfaces/libpq/exports.txt
@@ -211,3 +211,4 @@ PQdefaultAuthDataHook 208
PQfullProtocolVersion 209
appendPQExpBufferVA 210
PQgetThreadLock 211
+PQportaddr 212
diff --git a/src/interfaces/libpq/fe-connect.c b/src/interfaces/libpq/fe-connect.c
index 69c38c6a367..5546073b770 100644
--- a/src/interfaces/libpq/fe-connect.c
+++ b/src/interfaces/libpq/fe-connect.c
@@ -2464,6 +2464,30 @@ getHostaddr(PGconn *conn, char *host_addr, int host_addr_len)
host_addr[0] = '\0';
}
+/* ----------
+ * getPortaddr -
+ * Form the port number of the current connection, in the same way that
+ * getHostaddr() forms its IP address. conn->raddr must be valid. Nothing
+ * is reported for Unix-domain sockets, which have no port number.
+ * ----------
+ */
+static void
+getPortaddr(PGconn *conn, char *port_str, int port_str_len)
+{
+ struct sockaddr_storage *addr = &conn->raddr.addr;
+
+ if (addr->ss_family == AF_INET || addr->ss_family == AF_INET6)
+ {
+ if (pg_getnameinfo_all(addr, conn->raddr.salen,
+ NULL, 0,
+ port_str, port_str_len,
+ NI_NUMERICSERV) != 0)
+ port_str[0] = '\0';
+ }
+ else
+ port_str[0] = '\0';
+}
+
/*
* emitHostIdentityInfo -
* Speculatively append "connection to server so-and-so failed: " to
@@ -3323,6 +3347,7 @@ keep_going: /* We will come back to here until there is
*/
{
char host_addr[NI_MAXHOST];
+ char port_str[NI_MAXSERV];
int sock_type;
AddrInfo *addr_cur;
@@ -3378,8 +3403,8 @@ keep_going: /* We will come back to here until there is
goto error_return;
/*
- * Set connip, too. Note we purposely ignore strdup
- * failure; not a big problem if it fails.
+ * Set connip and connport, too. Note we purposely ignore
+ * strdup failure; not a big problem if it fails.
*/
if (conn->connip != NULL)
{
@@ -3390,6 +3415,15 @@ keep_going: /* We will come back to here until there is
if (host_addr[0])
conn->connip = strdup(host_addr);
+ if (conn->connport != NULL)
+ {
+ free(conn->connport);
+ conn->connport = NULL;
+ }
+ getPortaddr(conn, port_str, NI_MAXSERV);
+ if (port_str[0])
+ conn->connport = strdup(port_str);
+
/* Try to create the socket */
sock_type = SOCK_STREAM;
#ifdef SOCK_CLOEXEC
@@ -5244,6 +5278,7 @@ freePGconn(PGconn *conn)
free(conn->events);
pqReleaseConnHosts(conn);
free(conn->connip);
+ free(conn->connport);
release_conn_addrinfo(conn);
free(conn->scram_client_key_binary);
free(conn->scram_server_key_binary);
@@ -7744,6 +7779,19 @@ PQport(const PGconn *conn)
return DEF_PGPORT_STR;
}
+char *
+PQportaddr(const PGconn *conn)
+{
+ if (!conn)
+ return NULL;
+
+ /* Return the port actually connected to */
+ if (conn->connhost != NULL && conn->connport != NULL)
+ return conn->connport;
+
+ return "";
+}
+
/*
* No longer does anything, but the function remains for API backwards
* compatibility.
diff --git a/src/interfaces/libpq/libpq-fe.h b/src/interfaces/libpq/libpq-fe.h
index 8ecb9b4a4c7..820fc9aa842 100644
--- a/src/interfaces/libpq/libpq-fe.h
+++ b/src/interfaces/libpq/libpq-fe.h
@@ -415,6 +415,7 @@ extern char *PQpass(const PGconn *conn);
extern char *PQhost(const PGconn *conn);
extern char *PQhostaddr(const PGconn *conn);
extern char *PQport(const PGconn *conn);
+extern char *PQportaddr(const PGconn *conn);
extern char *PQtty(const PGconn *conn);
extern char *PQoptions(const PGconn *conn);
extern ConnStatusType PQstatus(const PGconn *conn);
diff --git a/src/interfaces/libpq/libpq-int.h b/src/interfaces/libpq/libpq-int.h
index 3ccb063d453..d18c9ce18b8 100644
--- a/src/interfaces/libpq/libpq-int.h
+++ b/src/interfaces/libpq/libpq-int.h
@@ -490,6 +490,7 @@ struct pg_conn
int whichhost; /* host we're currently trying/connected to */
pg_conn_host *connhost; /* details about each named host */
char *connip; /* IP address for current network connection */
+ char *connport; /* port number for current network connection */
/*
* The pending command queue as a singly-linked list. Head is the command
diff --git a/src/interfaces/libpq/t/007_portaddr.pl b/src/interfaces/libpq/t/007_portaddr.pl
index 5ecb573d8a7..856101c9541 100644
--- a/src/interfaces/libpq/t/007_portaddr.pl
+++ b/src/interfaces/libpq/t/007_portaddr.pl
@@ -48,6 +48,32 @@ $node->connect_ok(
sql => "\\echo :PORT",
expected_stdout => qr/^$unusedport$/);
+# PQportaddr() reports the port actually connected to, and psql shows it in
+# \conninfo when it differs from the port identifying the server.
+my ($ret, $stdout, $stderr) = $node->psql(
+ 'postgres',
+ "\\conninfo",
+ extra_params => ['-w'],
+ connstr => "host=127.0.0.1 port=$unusedport portaddr=$realport",
+ on_error_stop => 0);
+is($ret, 0, "\\conninfo with portaddr succeeds");
+like($stdout, qr/^Server Port\|$unusedport$/m,
+ "\\conninfo reports port as the server port");
+like($stdout, qr/^Port Address\|$realport$/m,
+ "\\conninfo reports portaddr as the port address");
+
+($ret, $stdout, $stderr) = $node->psql(
+ 'postgres',
+ "\\conninfo",
+ extra_params => ['-w'],
+ connstr => "host=127.0.0.1 port=$realport",
+ on_error_stop => 0);
+is($ret, 0, "\\conninfo without portaddr succeeds");
+like($stdout, qr/^Server Port\|$realport$/m,
+ "\\conninfo without portaddr reports the port");
+unlike($stdout, qr/Port Address/,
+ "\\conninfo omits the port address when it matches port");
+
# An empty portaddr means "connect to port", the historical behavior.
$node->connect_ok(
"host=127.0.0.1 port=$realport portaddr=",
--
2.43.0
From 3882d5952ca1c383b8408530f8a5351562ab1eba Mon Sep 17 00:00:00 2001
From: Diego <[email protected]>
Date: Mon, 27 Jul 2026 14:06:35 -0300
Subject: [PATCH v2 1/2] libpq: add portaddr, the port equivalent of hostaddr
libpq has long allowed the host name that identifies a server to differ
from the address actually connected to: host names the server, hostaddr
gives the address to reach it at, and the password file is searched using
host. Ports have no such separation. The port parameter is both the
port connected to and the port used as the password file search key, so a
connection made through an intermediary listening on a different port --
an SSH tunnel, a connection proxy -- cannot use a password file entry
written for the server itself.
Writing the intermediary's port into the password file works when that
port is fixed and known ahead of time, but not when it is assigned at
connection time, as with "ssh -L 127.0.0.1:0:...", cloud database proxies,
and GUI clients that manage their own tunnels. There the local port is
unknown when the password file is written, and differs between sessions.
Add a portaddr parameter, and a PGPORTADDR environment variable, that
completes the model: when portaddr is given it is the port connected to,
and port only identifies the server, exactly as host does when hostaddr is
given. The password file lookup therefore keeps using port. Like
hostaddr, portaddr applies only to TCP connections; it is ignored for
Unix-domain sockets, whose socket file name is determined by port.
psql's \connect drops a hostaddr inherited from the previous connection
when the host argument changes. Do the same for portaddr when either the
host or the port argument changes, so that reconnecting elsewhere is not
silently redirected through the old address.
PGPORTADDR is added to the environment variables cleared by pg_regress and
PostgreSQL::Test::Utils, since like PGHOSTADDR it would otherwise redirect
test connections away from the temporary cluster.
The new TAP test opens a TCP listen socket, so it runs only when portaddr
is listed in PG_TEST_EXTRA.
Behavior is unchanged when portaddr is not specified.
Discussion: https://postgr.es/m/[email protected]
---
doc/src/sgml/libpq.sgml | 97 ++++++++++++++-
doc/src/sgml/ref/psql-ref.sgml | 8 +-
doc/src/sgml/regress.sgml | 11 ++
src/bin/psql/command.c | 24 +++-
src/interfaces/libpq/fe-cancel.c | 6 +
src/interfaces/libpq/fe-connect.c | 76 ++++++++++-
src/interfaces/libpq/libpq-int.h | 6 +
src/interfaces/libpq/meson.build | 1 +
src/interfaces/libpq/t/001_uri.pl | 6 +
src/interfaces/libpq/t/007_portaddr.pl | 166 +++++++++++++++++++++++++
src/test/perl/PostgreSQL/Test/Utils.pm | 1 +
src/test/regress/pg_regress.c | 2 +
12 files changed, 391 insertions(+), 13 deletions(-)
create mode 100644 src/interfaces/libpq/t/007_portaddr.pl
diff --git a/doc/src/sgml/libpq.sgml b/doc/src/sgml/libpq.sgml
index 7d3c3bb66d8..c2b078bd6a9 100644
--- a/doc/src/sgml/libpq.sgml
+++ b/doc/src/sgml/libpq.sgml
@@ -1085,12 +1085,14 @@ postgresql://%2Fvar%2Flib%2Fpostgresql/dbname
<para>
It is possible to specify multiple hosts to connect to, so that they are
tried in the given order. In the Keyword/Value format, the <literal>host</literal>,
- <literal>hostaddr</literal>, and <literal>port</literal> options accept comma-separated
+ <literal>hostaddr</literal>, <literal>port</literal>, and
+ <literal>portaddr</literal> options accept comma-separated
lists of values. The same number of elements must be given in each
option that is specified, such
that e.g., the first <literal>hostaddr</literal> corresponds to the first host name,
the second <literal>hostaddr</literal> corresponds to the second host name, and so
- forth. As an exception, if only one <literal>port</literal> is specified, it
+ forth. As an exception, if only one <literal>port</literal>
+ or <literal>portaddr</literal> is specified, it
applies to all the hosts.
</para>
@@ -1251,6 +1253,73 @@ postgresql://%2Fvar%2Flib%2Fpostgresql/dbname
</listitem>
</varlistentry>
+ <varlistentry id="libpq-connect-portaddr" xreflabel="portaddr">
+ <term><literal>portaddr</literal></term>
+ <listitem>
+ <para>
+ Port number to actually connect to at the server host. When a nonempty
+ string is specified for this parameter, it determines where the
+ connection is made, and <xref linkend="libpq-connect-port"/> only
+ identifies the server — much as
+ <xref linkend="libpq-connect-host"/> still identifies the server when
+ <xref linkend="libpq-connect-hostaddr"/> is given. If this parameter
+ is not specified, the value of <literal>port</literal> is connected to,
+ which is the historical behavior. This parameter is ignored for
+ Unix-domain socket connections, whose socket file name is always
+ determined by <literal>port</literal>.
+ </para>
+
+ <para>
+ Using <literal>portaddr</literal> allows the connection to be made
+ through an intermediary listening on a different port, such as an
+ <acronym>SSH</acronym> tunnel or a connection proxy, while the other
+ connection parameters continue to describe the server itself. In
+ particular, the port used to identify the connection in a password file
+ (see <xref linkend="libpq-pgpass"/>) is <literal>port</literal>, not
+ <literal>portaddr</literal>, so a password file entry written for the
+ server's own port keeps matching even when the intermediary listens on
+ a port that is not known in advance. The following rules are used:
+ <itemizedlist>
+ <listitem>
+ <para>
+ If <literal>port</literal> is specified
+ without <literal>portaddr</literal>, the connection is made to
+ <literal>port</literal>, which also identifies the connection.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ If <literal>portaddr</literal> is specified
+ without <literal>port</literal>, the value
+ for <literal>portaddr</literal> gives the port to connect to, and
+ the default port number identifies the connection.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ If both <literal>port</literal> and <literal>portaddr</literal> are
+ specified, the value for <literal>portaddr</literal> gives the port
+ to connect to. The value for <literal>port</literal> is used only
+ to identify the connection.
+ </para>
+ </listitem>
+ </itemizedlist>
+ Note that authentication is likely to fail if <literal>port</literal>
+ is not the port of the server reached
+ at <literal>portaddr</literal>, since the password file entry that is
+ selected will then be the wrong one.
+ </para>
+
+ <para>
+ A comma-separated list of <literal>portaddr</literal> values is also
+ accepted, in which case it must have the same length as the host list,
+ or it may specify a single value to be used for all hosts. An empty
+ item in the list causes the corresponding <literal>port</literal> value
+ to be used. See <xref linkend="libpq-multiple-hosts"/> for details.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry id="libpq-connect-dbname" xreflabel="dbname">
<term><literal>dbname</literal></term>
<listitem>
@@ -2766,8 +2835,12 @@ char *PQport(const PGconn *conn);
</para>
<para>
- If multiple ports were specified in the connection parameters,
- <xref linkend="libpq-PQport"/> returns the port actually connected to.
+ If the connection parameters specified both <literal>port</literal> and
+ <literal>portaddr</literal>, then <xref linkend="libpq-PQport"/> will
+ return the <literal>port</literal> information. If multiple ports were
+ specified in the connection parameters,
+ <xref linkend="libpq-PQport"/> returns the port of the host actually
+ connected to.
</para>
<para>
@@ -9107,6 +9180,18 @@ myEventProc(PGEventId evtId, void *evtInfo, void *passThrough)
</para>
</listitem>
+ <listitem>
+ <para>
+ <indexterm>
+ <primary><envar>PGPORTADDR</envar></primary>
+ </indexterm>
+ <envar>PGPORTADDR</envar> behaves the same as the <xref
+ linkend="libpq-connect-portaddr"/> connection parameter.
+ This can be set instead of or in addition to <envar>PGPORT</envar>
+ to connect through an intermediary listening on another port.
+ </para>
+ </listitem>
+
<listitem>
<para>
<indexterm>
@@ -9580,6 +9665,10 @@ myEventProc(PGEventId evtId, void *evtInfo, void *passThrough)
the connection is a Unix-domain socket connection and
the <literal>host</literal> parameter
matches <application>libpq</application>'s default socket directory path.
+ The port field is matched to the <literal>port</literal> connection
+ parameter, never to the <literal>portaddr</literal> parameter; this way an
+ entry written for the server's own port keeps matching when the connection
+ is made through an intermediary listening on another port.
In a standby server, a database field of <literal>replication</literal>
matches streaming replication connections made to the primary server.
The database field is of limited usefulness otherwise, because users have
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 3ec0a3c3b34..edf00ed7e99 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -990,9 +990,13 @@ INSERT INTO tbls1 VALUES ($1, $2) \parse stmt1
exception is that if the <replaceable>host</replaceable> setting
is changed from its previous value using the positional syntax,
any <replaceable>hostaddr</replaceable> setting present in the
- existing connection's parameters is dropped.
+ existing connection's parameters is dropped; likewise, if either the
+ <replaceable>host</replaceable> or the <replaceable>port</replaceable>
+ setting is changed, any <replaceable>portaddr</replaceable> setting is
+ dropped.
Also, any password used for the existing connection will be re-used
- only if the user, host, and port settings are not changed.
+ only if the user, host, hostaddr, port, and portaddr settings are not
+ changed.
When the command neither specifies nor reuses a particular parameter,
the <application>libpq</application> default is used.
</para>
diff --git a/doc/src/sgml/regress.sgml b/doc/src/sgml/regress.sgml
index c74941bfbf2..b6f4a2e0f40 100644
--- a/doc/src/sgml/regress.sgml
+++ b/doc/src/sgml/regress.sgml
@@ -343,6 +343,17 @@ make check-world PG_TEST_EXTRA='kerberos ldap ssl load_balance libpq_encryption'
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>portaddr</literal></term>
+ <listitem>
+ <para>
+ Runs the test <filename>src/interfaces/libpq/t/007_portaddr.pl</filename>.
+ The <literal>portaddr</literal> connection parameter only applies to
+ TCP connections, so this test opens a TCP/IP listen socket.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><literal>regress_dump_restore</literal></term>
<listitem>
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index ee85c05a00d..7f4090e0d8d 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -3920,6 +3920,7 @@ do_connect(enum trivalue reuse_previous_specification,
PQconninfoOption *cinfo;
int nconnopts = 0;
bool same_host = false;
+ bool same_port = false;
char *password = NULL;
char *client_encoding;
bool success = true;
@@ -4011,8 +4012,8 @@ do_connect(enum trivalue reuse_previous_specification,
/*
* Check whether connstring provides options affecting
* password re-use. While any change in user, host,
- * hostaddr, or port causes us to ignore the old
- * connection's password, we don't force that for
+ * hostaddr, port, or portaddr causes us to ignore
+ * the old connection's password, we don't force that for
* dbname, since passwords aren't database-specific.
*/
if (replci->val == NULL ||
@@ -4021,7 +4022,8 @@ do_connect(enum trivalue reuse_previous_specification,
if (strcmp(replci->keyword, "user") == 0 ||
strcmp(replci->keyword, "host") == 0 ||
strcmp(replci->keyword, "hostaddr") == 0 ||
- strcmp(replci->keyword, "port") == 0)
+ strcmp(replci->keyword, "port") == 0 ||
+ strcmp(replci->keyword, "portaddr") == 0)
keep_password = false;
}
/* Also note whether connstring contains a password. */
@@ -4088,7 +4090,7 @@ do_connect(enum trivalue reuse_previous_specification,
* management issues: PQconninfoFree would misbehave on Windows.)
* However, to avoid dependencies on the order in which parameters
* appear in the array, make a preliminary scan to set
- * keep_password and same_host correctly.
+ * keep_password, same_host and same_port correctly.
*
* While any change in user, host, or port causes us to ignore the
* old connection's password, we don't force that for dbname,
@@ -4112,7 +4114,9 @@ do_connect(enum trivalue reuse_previous_specification,
}
else if (port && strcmp(ci->keyword, "port") == 0)
{
- if (!(ci->val && strcmp(port, ci->val) == 0))
+ if (ci->val && strcmp(port, ci->val) == 0)
+ same_port = true;
+ else
keep_password = false;
}
}
@@ -4198,6 +4202,16 @@ do_connect(enum trivalue reuse_previous_specification,
}
else if (port && strcmp(ci->keyword, "port") == 0)
values[paramnum++] = port;
+ else if (((host && !same_host) || (port && !same_port)) &&
+ strcmp(ci->keyword, "portaddr") == 0)
+ {
+ /*
+ * An old portaddr describes where to reach a particular
+ * server, so drop it if either the host or the port value is
+ * changing.
+ */
+ values[paramnum++] = NULL;
+ }
/* If !keep_password, we unconditionally drop old password */
else if ((password || !keep_password) &&
strcmp(ci->keyword, "password") == 0)
diff --git a/src/interfaces/libpq/fe-cancel.c b/src/interfaces/libpq/fe-cancel.c
index 4b5945979c4..f9768a9c1c2 100644
--- a/src/interfaces/libpq/fe-cancel.c
+++ b/src/interfaces/libpq/fe-cancel.c
@@ -156,6 +156,12 @@ PQcancelCreate(PGconn *conn)
if (!cancelConn->connhost[0].port)
goto oom_error;
}
+ if (originalHost.portaddr)
+ {
+ cancelConn->connhost[0].portaddr = strdup(originalHost.portaddr);
+ if (!cancelConn->connhost[0].portaddr)
+ goto oom_error;
+ }
if (originalHost.password)
{
cancelConn->connhost[0].password = strdup(originalHost.password);
diff --git a/src/interfaces/libpq/fe-connect.c b/src/interfaces/libpq/fe-connect.c
index 17c2288e9bc..69c38c6a367 100644
--- a/src/interfaces/libpq/fe-connect.c
+++ b/src/interfaces/libpq/fe-connect.c
@@ -243,6 +243,10 @@ static const internalPQconninfoOption PQconninfoOptions[] = {
"Database-Port", "", 6,
offsetof(struct pg_conn, pgport)},
+ {"portaddr", "PGPORTADDR", NULL, NULL,
+ "Database-Port-Address", "", 6,
+ offsetof(struct pg_conn, pgportaddr)},
+
{"client_encoding", "PGCLIENTENCODING", NULL, NULL,
"Client-Encoding", "", 10,
offsetof(struct pg_conn, client_encoding_initial)},
@@ -1403,6 +1407,48 @@ pqConnectOptions2(PGconn *conn)
}
}
+ /*
+ * Next, work out the port number to actually connect to for each host
+ * name, if portaddr was given. As for port above, these fields may be
+ * left null or empty; we will use the corresponding port field whenever
+ * we read such a portaddr field.
+ */
+ if (conn->pgportaddr != NULL && conn->pgportaddr[0] != '\0')
+ {
+ int i;
+ char *s = conn->pgportaddr;
+ bool more = true;
+
+ for (i = 0; i < conn->nconnhost && more; i++)
+ {
+ conn->connhost[i].portaddr = parse_comma_separated_list(&s, &more);
+ if (conn->connhost[i].portaddr == NULL)
+ goto oom_error;
+ }
+
+ /*
+ * If exactly one portaddr was given, use it for every host.
+ * Otherwise, there must be exactly as many portaddrs as there were
+ * hosts.
+ */
+ if (i == 1 && !more)
+ {
+ for (i = 1; i < conn->nconnhost; i++)
+ {
+ conn->connhost[i].portaddr = strdup(conn->connhost[0].portaddr);
+ if (conn->connhost[i].portaddr == NULL)
+ goto oom_error;
+ }
+ }
+ else if (more || i != conn->nconnhost)
+ {
+ conn->status = CONNECTION_BAD;
+ libpq_append_conn_error(conn, "could not match %d portaddr values to %d hosts",
+ count_comma_separated_elems(conn->pgportaddr), conn->nconnhost);
+ return false;
+ }
+ }
+
/*
* If user name was not given, fetch it. (Most likely, the fetch will
* fail, since the only way we get here is if pg_fe_getauthname() failed
@@ -1459,7 +1505,10 @@ pqConnectOptions2(PGconn *conn)
/*
* Try to get a password for this host from file. We use host
* for the hostname search key if given, else hostaddr (at
- * least one of them is guaranteed nonempty by now).
+ * least one of them is guaranteed nonempty by now). Likewise,
+ * the port search key is always port, never portaddr: the
+ * search keys identify the server we mean to reach, not the
+ * address we happen to reach it at.
*/
const char *pwhost = conn->connhost[i].host;
const char *password_errmsg = NULL;
@@ -2448,7 +2497,9 @@ emitHostIdentityInfo(PGconn *conn, const char *host_addr)
displayed_host = conn->connhost[conn->whichhost].hostaddr;
else
displayed_host = conn->connhost[conn->whichhost].host;
- displayed_port = conn->connhost[conn->whichhost].port;
+ displayed_port = conn->connhost[conn->whichhost].portaddr;
+ if (displayed_port == NULL || displayed_port[0] == '\0')
+ displayed_port = conn->connhost[conn->whichhost].port;
if (displayed_port == NULL || displayed_port[0] == '\0')
displayed_port = DEF_PGPORT_STR;
@@ -3069,6 +3120,25 @@ keep_going: /* We will come back to here until there is
goto keep_going;
}
}
+
+ /*
+ * If portaddr was given, that is the port we actually connect to, and
+ * port only serves to identify the server, just as host does when
+ * hostaddr is given. portaddr is ignored for Unix-domain socket
+ * connections, which are named by port.
+ */
+ if (ch->type != CHT_UNIX_SOCKET &&
+ ch->portaddr != NULL && ch->portaddr[0] != '\0')
+ {
+ if (!pqParseIntParam(ch->portaddr, &thisport, conn, "portaddr"))
+ goto error_return;
+
+ if (thisport < 1 || thisport > 65535)
+ {
+ libpq_append_conn_error(conn, "invalid port number: \"%s\"", ch->portaddr);
+ goto keep_going;
+ }
+ }
snprintf(portstr, sizeof(portstr), "%d", thisport);
/* Use pg_getaddrinfo_all() to resolve the address */
@@ -5110,6 +5180,7 @@ freePGconn(PGconn *conn)
free(conn->pghost);
free(conn->pghostaddr);
free(conn->pgport);
+ free(conn->pgportaddr);
free(conn->connect_timeout);
free(conn->pgtcp_user_timeout);
free(conn->client_encoding_initial);
@@ -5201,6 +5272,7 @@ pqReleaseConnHosts(PGconn *conn)
free(conn->connhost[i].host);
free(conn->connhost[i].hostaddr);
free(conn->connhost[i].port);
+ free(conn->connhost[i].portaddr);
if (conn->connhost[i].password != NULL)
{
explicit_bzero(conn->connhost[i].password,
diff --git a/src/interfaces/libpq/libpq-int.h b/src/interfaces/libpq/libpq-int.h
index 3f921207a14..3ccb063d453 100644
--- a/src/interfaces/libpq/libpq-int.h
+++ b/src/interfaces/libpq/libpq-int.h
@@ -361,6 +361,8 @@ typedef struct pg_conn_host
char *hostaddr; /* host numeric IP address */
char *port; /* port number (if NULL or empty, use
* DEF_PGPORT[_STR]) */
+ char *portaddr; /* port number actually connected to, for TCP
+ * connections (if NULL or empty, use port) */
char *password; /* password for this host, read from the
* password file; NULL if not sought or not
* found in password file. */
@@ -383,6 +385,10 @@ struct pg_conn
* precedence over pghost. */
char *pgport; /* the server's communication port number, or
* a comma-separated list of ports */
+ char *pgportaddr; /* the port number to connect to, or a
+ * comma-separated list of same. Takes
+ * precedence over pgport, except for
+ * Unix-domain socket connections. */
char *connect_timeout; /* connection timeout (numeric string) */
char *pgtcp_user_timeout; /* tcp user timeout (numeric string) */
char *client_encoding_initial; /* encoding to use */
diff --git a/src/interfaces/libpq/meson.build b/src/interfaces/libpq/meson.build
index b0ae72167a1..9087d2972ec 100644
--- a/src/interfaces/libpq/meson.build
+++ b/src/interfaces/libpq/meson.build
@@ -161,6 +161,7 @@ tests += {
't/004_load_balance_dns.pl',
't/005_negotiate_encryption.pl',
't/006_service.pl',
+ 't/007_portaddr.pl',
],
'env': {
'with_ssl': ssl_library,
diff --git a/src/interfaces/libpq/t/001_uri.pl b/src/interfaces/libpq/t/001_uri.pl
index 64f257ae046..613b03e6aec 100644
--- a/src/interfaces/libpq/t/001_uri.pl
+++ b/src/interfaces/libpq/t/001_uri.pl
@@ -56,6 +56,12 @@ my @tests = (
q{host='example.com' hostaddr='63.1.2.4' (inet)},
q{},
],
+ [
+ q{postgresql://host:12345/db?portaddr=6000},
+ q{dbname='db' host='host' port='12345' portaddr='6000' (inet)},
+ q{},
+ ],
+ [ q{postgresql://?portaddr=6000}, q{portaddr='6000' (local)}, q{}, ],
[ q{postgresql://%68ost/}, q{host='host' (inet)}, q{}, ],
[
q{postgresql://host/db?user=uri-user},
diff --git a/src/interfaces/libpq/t/007_portaddr.pl b/src/interfaces/libpq/t/007_portaddr.pl
new file mode 100644
index 00000000000..5ecb573d8a7
--- /dev/null
+++ b/src/interfaces/libpq/t/007_portaddr.pl
@@ -0,0 +1,166 @@
+# Copyright (c) 2026, PostgreSQL Global Development Group
+use strict;
+use warnings FATAL => 'all';
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+use Test::More;
+
+# This tests the portaddr connection parameter, which separates the port that
+# libpq actually connects to from the port that identifies the server.
+#
+# portaddr only applies to TCP connections, so the server has to listen on a
+# TCP port here, which is why this test is not enabled by default.
+
+if (!$ENV{PG_TEST_EXTRA} || $ENV{PG_TEST_EXTRA} !~ /\bportaddr\b/)
+{
+ plan skip_all =>
+ 'Potentially unsafe test portaddr not enabled in PG_TEST_EXTRA';
+}
+
+my $node = PostgreSQL::Test::Cluster->new('node');
+$node->init;
+$node->append_conf('postgresql.conf', "listen_addresses = '127.0.0.1'");
+$node->start;
+
+# The port the server really listens on, plus a port that nothing listens on.
+# Reaching the server while naming the latter is only possible via portaddr.
+my $realport = $node->port;
+my $unusedport = PostgreSQL::Test::Cluster::get_free_port();
+
+# Sanity check: without portaddr, the unused port is unreachable.
+$node->connect_fails(
+ "host=127.0.0.1 port=$unusedport",
+ "connection to an unused port fails without portaddr",
+ expected_stderr =>
+ qr/connection to server at "127\.0\.0\.1", port $unusedport failed/);
+
+# portaddr determines the port we connect to ...
+$node->connect_ok(
+ "host=127.0.0.1 port=$unusedport portaddr=$realport",
+ "portaddr determines the port connected to",
+ sql => "SELECT 'connected'",
+ expected_stdout => qr/^connected$/);
+
+# ... while port still identifies the connection, as reported by PQport.
+$node->connect_ok(
+ "host=127.0.0.1 port=$unusedport portaddr=$realport",
+ "PQport reports port, not portaddr",
+ sql => "\\echo :PORT",
+ expected_stdout => qr/^$unusedport$/);
+
+# An empty portaddr means "connect to port", the historical behavior.
+$node->connect_ok(
+ "host=127.0.0.1 port=$realport portaddr=",
+ "empty portaddr falls back to port",
+ sql => "SELECT 'connected'",
+ expected_stdout => qr/^connected$/);
+
+# A connection failure reports the port we actually tried to reach.
+$node->connect_fails(
+ "host=127.0.0.1 port=$realport portaddr=$unusedport",
+ "connection failure reports the portaddr port",
+ expected_stderr =>
+ qr/connection to server at "127\.0\.0\.1", port $unusedport failed/);
+
+# Invalid values are rejected the same way port is.
+$node->connect_fails(
+ "host=127.0.0.1 port=$realport portaddr=65536",
+ "portaddr must be a valid port number",
+ expected_stderr => qr/invalid port number: "65536"/);
+
+$node->connect_fails(
+ "host=127.0.0.1 port=$realport portaddr=notanumber",
+ "portaddr must be an integer",
+ expected_stderr =>
+ qr/invalid integer value "notanumber" for connection option "portaddr"/);
+
+# The portaddr list must match the host list, unless it has a single element.
+$node->connect_fails(
+ "host=127.0.0.1,127.0.0.1 port=$unusedport portaddr=$realport,$realport,$realport",
+ "portaddr list must match the host list",
+ expected_stderr => qr/could not match 3 portaddr values to 2 hosts/);
+
+# A single portaddr applies to every host. The first host here is a socket
+# path that does not exist, so it fails and the second host is tried; that
+# second host can only be reached if the lone portaddr was copied to it.
+$node->connect_ok(
+ "host=/nonexistent,127.0.0.1 port=$unusedport portaddr=$realport",
+ "a single portaddr applies to all hosts",
+ sql => "SELECT 'connected'",
+ expected_stdout => qr/^connected$/);
+
+# An empty item in the list uses the corresponding port value. The first host
+# is directed at the unused port and fails, so the second one is tried, and it
+# can only succeed by falling back to its port.
+$node->connect_ok(
+ "host=127.0.0.1,127.0.0.1 port=$unusedport,$realport portaddr=$unusedport,",
+ "an empty list item falls back to the corresponding port",
+ sql => "SELECT 'connected'",
+ expected_stdout => qr/^connected$/);
+
+# PGPORTADDR behaves the same as the parameter.
+{
+ local $ENV{PGPORTADDR} = $realport;
+
+ $node->connect_ok(
+ "host=127.0.0.1 port=$unusedport",
+ "PGPORTADDR environment variable is honored",
+ sql => "SELECT 'connected'",
+ expected_stdout => qr/^connected$/);
+}
+
+# portaddr is ignored for Unix-domain socket connections, which are named by
+# port; naming an unused port there must not change anything.
+if ($use_unix_sockets)
+{
+ $node->connect_ok(
+ $node->connstr('postgres') . " portaddr=$unusedport",
+ "portaddr is ignored for Unix-domain socket connections",
+ sql => "SELECT 'connected'",
+ expected_stdout => qr/^connected$/);
+}
+
+# The password file is searched using port, never portaddr. This is the point
+# of the parameter: an entry written for the server's own port keeps matching
+# when the connection is made through an intermediary on another port.
+$node->safe_psql('postgres',
+ "CREATE ROLE portaddr_role LOGIN PASSWORD 'secret'");
+
+unlink($node->data_dir . '/pg_hba.conf');
+$node->append_conf('pg_hba.conf', "local all all trust");
+$node->append_conf('pg_hba.conf',
+ "host all portaddr_role 127.0.0.1/32 scram-sha-256");
+$node->append_conf('pg_hba.conf', "host all all 127.0.0.1/32 trust");
+$node->reload;
+
+my $pgpassfile = "${PostgreSQL::Test::Utils::tmp_check}/pgpass_portaddr";
+$ENV{PGPASSFILE} = $pgpassfile;
+
+# An entry keyed to the port that identifies the server matches, even though
+# the connection is actually made to a different port.
+unlink($pgpassfile);
+append_to_file($pgpassfile,
+ "127.0.0.1:$unusedport:postgres:portaddr_role:secret\n");
+chmod 0600, $pgpassfile or die;
+
+$node->connect_ok(
+ "host=127.0.0.1 port=$unusedport portaddr=$realport user=portaddr_role",
+ "password file is searched using port",
+ sql => "SELECT 'authenticated'",
+ expected_stdout => qr/^authenticated$/);
+
+# Conversely, an entry keyed to the port actually connected to does not match.
+unlink($pgpassfile);
+append_to_file($pgpassfile,
+ "127.0.0.1:$realport:postgres:portaddr_role:secret\n");
+chmod 0600, $pgpassfile or die;
+
+$node->connect_fails(
+ "host=127.0.0.1 port=$unusedport portaddr=$realport user=portaddr_role",
+ "password file is not searched using portaddr",
+ expected_stderr => qr/no password supplied/);
+
+unlink($pgpassfile);
+delete $ENV{PGPASSFILE};
+
+done_testing();
diff --git a/src/test/perl/PostgreSQL/Test/Utils.pm b/src/test/perl/PostgreSQL/Test/Utils.pm
index d3e6abf7a68..0e98a15a9f1 100644
--- a/src/test/perl/PostgreSQL/Test/Utils.pm
+++ b/src/test/perl/PostgreSQL/Test/Utils.pm
@@ -134,6 +134,7 @@ BEGIN
PGKRBSRVNAME
PGPASSFILE
PGPASSWORD
+ PGPORTADDR
PGREQUIREPEER
PGREQUIRESSL
PGSERVICE
diff --git a/src/test/regress/pg_regress.c b/src/test/regress/pg_regress.c
index 21d00f792d2..6a19b50825e 100644
--- a/src/test/regress/pg_regress.c
+++ b/src/test/regress/pg_regress.c
@@ -837,6 +837,7 @@ initialize_environment(void)
unsetenv("PGKRBSRVNAME");
unsetenv("PGPASSFILE");
unsetenv("PGPASSWORD");
+ unsetenv("PGPORTADDR");
unsetenv("PGREQUIREPEER");
unsetenv("PGREQUIRESSL");
unsetenv("PGSERVICE");
@@ -893,6 +894,7 @@ initialize_environment(void)
snprintf(s, sizeof(s), "%d", port);
setenv("PGPORT", s, 1);
+ unsetenv("PGPORTADDR");
}
if (user != NULL)
setenv("PGUSER", user, 1);
--
2.43.0
