Hi,
When a connection string lists several hosts,
target_session_attrs=standby (or prefer-standby) picks the first host
that reports pg_is_in_recovery(). That confirms the server is a standby,
but says nothing about whether it still has a live WAL receiver attached
to its upstream. A standby whose primary connection was lost still
reports itself as a standby and is pretty much indistinguishable from a
healthy one, so libpq happily routes read-only traffic to a server that
potentially stopped receiving WAL for quite some time.
I'd like to propose a parameter that makes libpq look at
pg_stat_wal_receiver on the candidate standby and move on to the next
host when no WAL receiver is running there:
psql "host=h1,h2,h3 port=5111,5222,5333 dbname=db \
target_session_attrs=standby require_wal_receiver=1"
It applies on top of target_session_attrs rather than replacing it, and
is skipped where it is meaningless: on a primary, when
target_session_attrs requires a primary/read-write session, on
replication connections, and on the prefer-standby fallback pass, so
prefer-standby still degrades to "any server" instead of failing outright.
The current PoC is quite simplistic. The CONNECTION_CHECK_STANDBY state
already runs "SELECT pg_catalog.pg_is_in_recovery()" for
standby/prefer-standby, so the check rides along on that query as a
second column -- an EXISTS over pg_stat_wal_receiver. The obvious
alternative -- comparing pg_last_wal_receive_lsn() with
pg_last_wal_replay_lsn() -- does not cover the same case. A standby that
lost its upstream received everything it will ever receive and replayed
all of it, so its lag is exactly zero and it passes any threshold.
Two things I'd like input on before going forward:
1) The EXISTS test is deliberately weak. pg_stat_wal_receiver might
simply report a connection attempt, so a WAL receiver stuck connecting
to an unreachable primary still produces a row and passes the current
test. Testing status = 'streaming' would close that, but every column
except pid is NULL for roles without pg_read_all_stats, so the strict
form only works for privileged users. Which trade-off is preferable? I'd
rather not make the semantics privilege-dependent.
2) Is a separate boolean the right approach, or should this be a new
target_session_attrs value? I went with a separate parameter because
otherwise standby, prefer-standby, read-only and any would each need a
paired variant.
It's important to note that this is a "liveness check", not a freshness
guarantee: a standby that passes may still be far behind in replay, and
a standby recovering only from archive is rejected even if current.
Is this the right direction? Any feedback is much appreciated!
Best, JimFrom ac51edb5a1b1a154cd89c6b8b6e0f67e0fdeb454 Mon Sep 17 00:00:00 2001
From: Jim Jones <[email protected]>
Date: Sun, 6 Sep 2026 00:10:07 +0200
Subject: [PATCH v1] Add require_wal_receiver connection parameter to libpq
Add a new connection parameter, require_wal_receiver (accepting 0 or
1, default 0), which verifies that a candidate standby currently has
an active WAL receiver process before libpq accepts a connection to
it. A standby with no live WAL receiver -- for example one whose
upstream connection has been lost, or one that has not yet connected
since starting -- is rejected, and the next host in the list is tried.
This closes a gap in target_session_attrs: pg_is_in_recovery() alone
confirms a server is a standby, but says nothing about whether it is
actually receiving WAL right now. A standby that has lost contact
with its primary still reports itself as a standby and is otherwise
indistinguishable from a healthy one -- exactly the case a caller
using target_session_attrs=standby or prefer-standby for load
balancing is trying to avoid.
The check is applied on top of, not in place of, the existing
target_session_attrs validation. It checks only makes sense for a
standby, and so is skipped when connecting to a primary and when
target_session_attrs requires a primary/read-write session.
It is also skipped in two further cases:
* On replication connections. A physical replication connection
cannot execute SQL at all, so applying the check would break every
such connection -- including those made by a cascading standby's own
WAL receiver, pg_basebackup and pg_receivewal, which inherit the
setting from PGREQUIREWALRECEIVER.
* On the second pass of prefer-standby, which accepts any server at
all. That fallback exists so prefer-standby degrades gracefully
rather than failing outright, and must not be narrowed by a
standby-quality filter.
The check relies on pg_stat_wal_receiver, which was added in
PostgreSQL 9.6. A standby on an older server cannot be verified and
is therefore rejected; a primary on an older server is unaffected,
since the check does not apply to it.
This is a liveness check, not a freshness guarantee: a standby that
passes may still lag, and a standby recovering only from archived WAL
(with no WAL receiver) is rejected even if current.
---
doc/src/sgml/libpq.sgml | 59 +++
src/interfaces/libpq/fe-connect.c | 175 +++++++-
src/interfaces/libpq/libpq-int.h | 6 +
src/test/perl/PostgreSQL/Test/Utils.pm | 1 +
src/test/recovery/meson.build | 1 +
.../recovery/t/057_require_wal_receiver.pl | 384 ++++++++++++++++++
src/test/regress/pg_regress.c | 1 +
7 files changed, 624 insertions(+), 3 deletions(-)
create mode 100644 src/test/recovery/t/057_require_wal_receiver.pl
diff --git a/doc/src/sgml/libpq.sgml b/doc/src/sgml/libpq.sgml
index 68487a3954f..35abf2efa5e 100644
--- a/doc/src/sgml/libpq.sgml
+++ b/doc/src/sgml/libpq.sgml
@@ -2406,6 +2406,55 @@ postgresql://%2Fvar%2Flib%2Fpostgresql/dbname
</para>
</listitem>
</varlistentry>
+ <varlistentry id="libpq-connect-require-wal-receiver" xreflabel="require_wal_receiver">
+ <term><literal>require_wal_receiver</literal></term>
+ <listitem>
+ <para>
+ If set to <literal>1</literal> (the default is <literal>0</literal>),
+ and the candidate host is a standby,
+ <productname>libpq</productname> verifies that the standby currently
+ has an active WAL receiver process before accepting the connection.
+ A standby with no active WAL receiver — for example one whose
+ upstream connection has been lost, or that has not yet connected
+ since starting — is skipped, and the next host in the list is
+ tried.
+ </para>
+ <para>
+ The check is skipped when connecting to a primary, or when
+ <literal>target_session_attrs</literal> is set to
+ <literal>primary</literal> or <literal>read-write</literal>. It
+ applies with <literal>any</literal>, <literal>read-only</literal>,
+ <literal>standby</literal>, and <literal>prefer-standby</literal>.
+ </para>
+ <para>
+ With <literal>prefer-standby</literal>, the check applies only while
+ <productname>libpq</productname> is looking for a standby. If no
+ host passes it, the fallback pass that accepts any server accepts a
+ standby without a WAL receiver too, so that
+ <literal>prefer-standby</literal> keeps degrading gracefully rather
+ than failing outright.
+ </para>
+ <para>
+ The check is also skipped on replication connections (see
+ <xref linkend="protocol-replication"/>), for which it is not
+ meaningful.
+ </para>
+ <para>
+ The check relies on <link
+ linkend="monitoring-pg-stat-wal-receiver-view"><structname>pg_stat_wal_receiver</structname></link>,
+ which was added in <productname>PostgreSQL</productname> 9.6. A
+ standby running an older server cannot be verified, and is therefore
+ rejected; a primary running an older server is unaffected, since the
+ check does not apply to it.
+ </para>
+ <para>
+ This is a <emphasis>liveness</emphasis> check, not a freshness
+ guarantee: a standby that passes may still lag behind the primary,
+ and a standby recovering only from archived WAL (with no WAL
+ receiver) is rejected even though it may be current.
+ </para>
+ </listitem>
+ </varlistentry>
<varlistentry id="libpq-connect-load-balance-hosts" xreflabel="load_balance_hosts">
<term><literal>load_balance_hosts</literal></term>
@@ -9283,6 +9332,16 @@ myEventProc(PGEventId evtId, void *evtInfo, void *passThrough)
</para>
</listitem>
+ <listitem>
+ <para>
+ <indexterm>
+ <primary><envar>PGREQUIREWALRECEIVER</envar></primary>
+ </indexterm>
+ <envar>PGREQUIREWALRECEIVER</envar> behaves the same as the <xref
+ linkend="libpq-connect-require-wal-receiver"/> connection parameter.
+ </para>
+ </listitem>
+
<listitem>
<para>
<indexterm>
diff --git a/src/interfaces/libpq/fe-connect.c b/src/interfaces/libpq/fe-connect.c
index ec98c58a407..2c7e101b407 100644
--- a/src/interfaces/libpq/fe-connect.c
+++ b/src/interfaces/libpq/fe-connect.c
@@ -384,6 +384,11 @@ static const internalPQconninfoOption PQconninfoOptions[] = {
"Target-Session-Attrs", "", 15, /* sizeof("prefer-standby") = 15 */
offsetof(struct pg_conn, target_session_attrs)},
+ {"require_wal_receiver", "PGREQUIREWALRECEIVER",
+ "0", NULL,
+ "Require-WAL-Receiver", "", 1,
+ offsetof(struct pg_conn, require_wal_receiver)},
+
{"load_balance_hosts", "PGLOADBALANCEHOSTS",
DefaultLoadBalanceHosts, NULL,
"Load-Balance-Hosts", "", 8, /* sizeof("disable") = 8 */
@@ -514,6 +519,7 @@ static void default_threadlock(int acquire);
static bool sslVerifyProtocolVersion(const char *version);
static bool sslVerifyProtocolRange(const char *min, const char *max);
static bool pqParseProtocolVersion(const char *value, ProtocolVersion *result, PGconn *conn, const char *context);
+static bool connRequestsReplication(const PGconn *conn);
/* global variable because fe-auth.c needs to access it */
@@ -707,6 +713,7 @@ pqDropServerData(PGconn *conn)
free(conn->write_err_msg);
conn->write_err_msg = NULL;
conn->oauth_want_retry = false;
+ conn->wal_receiver_checked = false;
/*
* Cancel connections need to retain their be_pid and be_cancel_key across
@@ -2038,6 +2045,24 @@ pqConnectOptions2(PGconn *conn)
else
conn->target_server_type = SERVER_TYPE_ANY;
+ if (conn->require_wal_receiver)
+ {
+ if (strcmp(conn->require_wal_receiver, "1") == 0)
+ conn->wal_receiver_required = true;
+ else if (strcmp(conn->require_wal_receiver, "0") == 0)
+ conn->wal_receiver_required = false;
+ else
+ {
+ conn->status = CONNECTION_BAD;
+ libpq_append_conn_error(conn, "invalid %s value: \"%s\"",
+ "require_wal_receiver",
+ conn->require_wal_receiver);
+ return false;
+ }
+ }
+ else
+ conn->wal_receiver_required = false;
+
if (conn->scram_client_key)
{
int len;
@@ -2898,6 +2923,36 @@ pqConnectDBComplete(PGconn *conn)
}
}
+/*
+ * connRequestsReplication
+ *
+ * Did the caller ask for a replication connection? libpq does not define the
+ * "replication" option, it just passes the value through to the server, which
+ * reads it as a boolean plus the special value "database" for logical
+ * replication. So we only look for the spellings that mean "no".
+ *
+ * Anything else is assumed to request replication, including the rarer false
+ * spellings parse_bool() accepts ("f", "of", ...). Erring in that direction
+ * merely skips an optional check, while erring in the other direction would
+ * break the connection outright.
+ */
+static bool
+connRequestsReplication(const PGconn *conn)
+{
+ const char *val = conn->replication;
+
+ if (val == NULL || val[0] == '\0')
+ return false;
+
+ if (strcmp(val, "0") == 0 ||
+ pg_strcasecmp(val, "false") == 0 ||
+ pg_strcasecmp(val, "off") == 0 ||
+ pg_strcasecmp(val, "no") == 0)
+ return false;
+
+ return true;
+}
+
/* ----------------
* PQconnectPoll
*
@@ -4400,6 +4455,92 @@ keep_going: /* We will come back to here until there is
case CONNECTION_CHECK_TARGET:
{
+ /*
+ * If require_wal_receiver is enabled, we must confirm that
+ * this server has a live WAL receiver before accepting it.
+ *
+ * That is only meaningful for a standby, so the check is
+ * skipped when target_session_attrs requires a
+ * primary/read-write session, and when the server is already
+ * known not to be in hot standby. It is likewise skipped on
+ * the second pass of prefer-standby, which accepts any
+ * server at all and so must not be narrowed by a
+ * standby-quality filter.
+ *
+ * Replication connections are skipped as well. The check is
+ * meaningless for them, and a physical replication connection
+ * cannot execute the SQL it needs, so applying it would just
+ * break every such connection --- including the ones made by
+ * a cascading standby's WAL receiver, pg_basebackup and
+ * pg_receivewal, which inherit the setting from
+ * PGREQUIREWALRECEIVER.
+ */
+ if (conn->wal_receiver_required &&
+ !conn->wal_receiver_checked &&
+ !connRequestsReplication(conn) &&
+ conn->target_server_type != SERVER_TYPE_PRIMARY &&
+ conn->target_server_type != SERVER_TYPE_READ_WRITE &&
+ conn->target_server_type != SERVER_TYPE_PREFER_STANDBY_PASS2 &&
+ conn->in_hot_standby != PG_BOOL_NO)
+ {
+ /*
+ * pg_stat_wal_receiver, which the check relies on, was
+ * added in 9.6. We can't verify WAL receiver liveness on
+ * an older server, so treat a standby there as failing
+ * the check rather than silently skipping it. A primary
+ * is unaffected either way, so find out which one we have
+ * first; servers before 9.0 have no standby mode at all.
+ */
+ if (conn->sversion < 90000)
+ conn->in_hot_standby = PG_BOOL_NO;
+ else if (conn->sversion < 90600)
+ {
+ if (conn->in_hot_standby == PG_BOOL_YES)
+ {
+ libpq_append_conn_error(conn,
+ "%s is not supported by servers older than 9.6",
+ "require_wal_receiver");
+ conn->status = CONNECTION_OK;
+ sendTerminateConn(conn);
+ conn->try_next_host = true;
+ goto keep_going;
+ }
+
+ /*
+ * Still unknown; the query below settles it, and
+ * we'll come back here to reject or accept the host.
+ */
+ }
+
+ if (conn->in_hot_standby != PG_BOOL_NO)
+ {
+ /*
+ * Ask whether the server is in recovery and, unless
+ * it's too old to tell us, whether it currently has a
+ * live WAL receiver. This is the same question
+ * CONNECTION_CHECK_STANDBY asks below, so let that
+ * state collect both answers in one round trip.
+ */
+ conn->status = CONNECTION_OK;
+ if (conn->sversion < 90600)
+ {
+ if (!PQsendQueryContinue(conn,
+ "SELECT pg_catalog.pg_is_in_recovery()"))
+ goto error_return;
+ }
+ else
+ {
+ if (!PQsendQueryContinue(conn,
+ "SELECT pg_catalog.pg_is_in_recovery(),"
+ " EXISTS (SELECT 1 FROM pg_catalog.pg_stat_wal_receiver)"))
+ goto error_return;
+ conn->wal_receiver_checked = true;
+ }
+ conn->status = CONNECTION_CHECK_STANDBY;
+ return PGRES_POLLING_READING;
+ }
+ }
+
/*
* If a read-write, read-only, primary, or standby connection
* is required, see if we have one.
@@ -4641,9 +4782,11 @@ keep_going: /* We will come back to here until there is
case CONNECTION_CHECK_STANDBY:
{
/*
- * Waiting for result of "SELECT pg_is_in_recovery()". We
- * must transiently set status = CONNECTION_OK in order to use
- * the result-consuming subroutines.
+ * Waiting for result of "SELECT pg_is_in_recovery()", which
+ * carries a second column reporting whether a WAL receiver
+ * is running when require_wal_receiver asked for it. We must
+ * transiently set status = CONNECTION_OK in order to use the
+ * result-consuming subroutines.
*/
conn->status = CONNECTION_OK;
if (!PQconsumeInput(conn))
@@ -4665,6 +4808,30 @@ keep_going: /* We will come back to here until there is
conn->in_hot_standby = PG_BOOL_YES;
else
conn->in_hot_standby = PG_BOOL_NO;
+
+ /*
+ * A standby that has no WAL receiver running fails
+ * require_wal_receiver; reject it and try the next host.
+ * A primary is not subject to the check, whether or not
+ * we asked the question.
+ */
+ if (PQnfields(res) > 1 &&
+ conn->in_hot_standby == PG_BOOL_YES &&
+ strncmp(PQgetvalue(res, 0, 1), "t", 1) != 0)
+ {
+ PQclear(res);
+
+ libpq_append_conn_error(conn, "standby has no active WAL receiver");
+
+ /* Close connection politely. */
+ conn->status = CONNECTION_OK;
+ sendTerminateConn(conn);
+
+ /* Try next host. */
+ conn->try_next_host = true;
+ goto keep_going;
+ }
+
PQclear(res);
/* Finish reading messages before continuing */
@@ -5007,6 +5174,7 @@ pqMakeEmptyPGconn(void)
conn->std_strings = false; /* unless server says differently */
conn->default_transaction_read_only = PG_BOOL_UNKNOWN;
conn->in_hot_standby = PG_BOOL_UNKNOWN;
+ conn->wal_receiver_checked = false;
conn->scram_sha_256_iterations = SCRAM_SHA_256_DEFAULT_ITERATIONS;
conn->verbosity = PQERRORS_DEFAULT;
conn->show_context = PQSHOW_CONTEXT_ERRORS;
@@ -5122,6 +5290,7 @@ freePGconn(PGconn *conn)
free(conn->ssl_min_protocol_version);
free(conn->ssl_max_protocol_version);
free(conn->target_session_attrs);
+ free(conn->require_wal_receiver);
free(conn->require_auth);
free(conn->load_balance_hosts);
free(conn->scram_client_key);
diff --git a/src/interfaces/libpq/libpq-int.h b/src/interfaces/libpq/libpq-int.h
index 933ba0d99d5..58cb64519bf 100644
--- a/src/interfaces/libpq/libpq-int.h
+++ b/src/interfaces/libpq/libpq-int.h
@@ -427,6 +427,8 @@ struct pg_conn
char *ssl_min_protocol_version; /* minimum TLS protocol version */
char *ssl_max_protocol_version; /* maximum TLS protocol version */
char *target_session_attrs; /* desired session properties */
+ char *require_wal_receiver; /* require a live WAL receiver on a
+ * standby? */
char *require_auth; /* name of the expected auth method */
char *load_balance_hosts; /* load balance over hosts */
char *scram_client_key; /* base64-encoded SCRAM client key */
@@ -535,6 +537,8 @@ struct pg_conn
/* Transient state needed while establishing connection */
PGTargetServerType target_server_type; /* desired session properties */
+ bool wal_receiver_required; /* decoded value of
+ * require_wal_receiver */
PGLoadBalanceType load_balance_type; /* desired load balancing
* algorithm */
bool try_next_addr; /* time to advance to next address/host? */
@@ -548,6 +552,8 @@ struct pg_conn
uint8 *scram_server_key_binary; /* binary SCRAM server key */
ProtocolVersion min_pversion; /* protocol version to request */
ProtocolVersion max_pversion; /* protocol version to request */
+ bool wal_receiver_checked; /* did we already ask this host about
+ * its WAL receiver? */
/* Miscellaneous stuff */
int be_pid; /* PID of backend --- needed for cancels */
diff --git a/src/test/perl/PostgreSQL/Test/Utils.pm b/src/test/perl/PostgreSQL/Test/Utils.pm
index d3e6abf7a68..a44de965c1b 100644
--- a/src/test/perl/PostgreSQL/Test/Utils.pm
+++ b/src/test/perl/PostgreSQL/Test/Utils.pm
@@ -136,6 +136,7 @@ BEGIN
PGPASSWORD
PGREQUIREPEER
PGREQUIRESSL
+ PGREQUIREWALRECEIVER
PGSERVICE
PGSERVICEFILE
PGSSLCERT
diff --git a/src/test/recovery/meson.build b/src/test/recovery/meson.build
index 72113c5ac6e..e32643be545 100644
--- a/src/test/recovery/meson.build
+++ b/src/test/recovery/meson.build
@@ -65,6 +65,7 @@ tests += {
't/054_unlogged_sequence_promotion.pl',
't/055_cascade_reconnect.pl',
't/056_standby_snapshot_export.pl',
+ 't/057_require_wal_receiver.pl',
],
},
}
diff --git a/src/test/recovery/t/057_require_wal_receiver.pl b/src/test/recovery/t/057_require_wal_receiver.pl
new file mode 100644
index 00000000000..9f3138ecc87
--- /dev/null
+++ b/src/test/recovery/t/057_require_wal_receiver.pl
@@ -0,0 +1,384 @@
+# Copyright (c) 2026, PostgreSQL Global Development Group
+
+# Tests for the require_wal_receiver parameter with target_session_attrs.
+#
+# The parameter rejects a standby that has no live WAL receiver process
+# (pg_stat_wal_receiver returns no row).
+
+use strict;
+use warnings;
+use PostgreSQL::Test::Utils;
+use PostgreSQL::Test::Cluster;
+use Test::More;
+
+# Primary
+my $primary = PostgreSQL::Test::Cluster->new('primary');
+$primary->init(allows_streaming => 1);
+$primary->append_conf('postgresql.conf', "listen_addresses = 'localhost'");
+$primary->start;
+
+# create a user to test the feature without superuser rights
+$primary->safe_psql('postgres',
+ "CREATE ROLE regress_walrcv_user LOGIN");
+
+# standby_live: normal streaming standby.
+$primary->backup('backup_live');
+my $standby_live = PostgreSQL::Test::Cluster->new('standby_live');
+$standby_live->init_from_backup($primary, 'backup_live', has_streaming => 1);
+$standby_live->append_conf('postgresql.conf', "listen_addresses = 'localhost'");
+$standby_live->append_conf('postgresql.conf', "wal_retrieve_retry_interval = '60s'");
+$standby_live->start;
+
+# standby_norecv: in recovery but with no primary_conninfo
+$primary->backup('backup_norecv');
+my $standby_norecv = PostgreSQL::Test::Cluster->new('standby_norecv');
+$standby_norecv->init_from_backup($primary, 'backup_norecv');
+$standby_norecv->append_conf('postgresql.conf', "listen_addresses = 'localhost'");
+$standby_norecv->set_standby_mode();
+$standby_norecv->start;
+
+# make sure standby_live is actually streaming before we rely on it.
+$standby_live->poll_query_until('postgres',
+ "SELECT EXISTS (SELECT 1 FROM pg_stat_wal_receiver)")
+ or die "standby_live never started a WAL receiver";
+
+# make sure standby_norecv is in recovery but not streaming.
+is( $standby_norecv->safe_psql('postgres', 'SELECT pg_is_in_recovery()'),
+ 't',
+ 'standby_norecv is in recovery');
+
+is( $standby_norecv->safe_psql('postgres',
+ 'SELECT EXISTS (SELECT 1 FROM pg_stat_wal_receiver)'),
+ 'f',
+ 'standby_norecv has no WAL receiver');
+
+my ($stdout, $stderr);
+my $port_primary = $primary->port;
+my $port_live = $standby_live->port;
+my $port_norecv = $standby_norecv->port;
+
+# streaming standby is accepted
+$standby_live->psql(
+ 'postgres',
+ 'SELECT inet_server_port()',
+ connstr => "dbname=postgres host=localhost port=$port_live "
+ . "target_session_attrs=standby require_wal_receiver=1",
+ stdout => \$stdout,
+ stderr => \$stderr,
+);
+is($stdout, $port_live, "streaming standby accepted");
+
+# standby with no WAL receiver is rejected
+$standby_norecv->psql(
+ 'postgres',
+ 'SELECT inet_server_port()',
+ connstr => "dbname=postgres host=localhost port=$port_norecv "
+ . "target_session_attrs=standby require_wal_receiver=1",
+ stdout => \$stdout,
+ stderr => \$stderr,
+);
+like($stderr, qr/standby has no active WAL receiver/,
+ "standby without WAL receiver rejected");
+
+# without require_wal_receiver, the dead standby IS accepted
+$standby_norecv->psql(
+ 'postgres',
+ 'SELECT current_setting(\'port\')',
+ connstr => "dbname=postgres host=localhost port=$port_norecv "
+ . "target_session_attrs=standby",
+ stdout => \$stdout,
+ stderr => \$stderr,
+);
+is($stdout, $port_norecv,
+ "dead standby accepted when require_wal_receiver is omitted");
+
+# with require_wal_receiver disabled, the dead standby is accepted
+$standby_norecv->psql(
+ 'postgres',
+ 'SELECT current_setting(\'port\')',
+ connstr => "dbname=postgres host=localhost port=$port_norecv "
+ . "target_session_attrs=standby require_wal_receiver=0",
+ stdout => \$stdout,
+ stderr => \$stderr,
+);
+is($stdout, $port_norecv,
+ "dead standby accepted when require_wal_receiver is set to 0");
+
+# prefer-standby: dead standby skipped, falls back to primary.
+$primary->psql(
+ 'postgres',
+ 'SELECT inet_server_port()',
+ connstr => "dbname=postgres host=localhost,localhost "
+ . "port=$port_primary,$port_norecv "
+ . "target_session_attrs=prefer-standby require_wal_receiver=1",
+ stdout => \$stdout,
+ stderr => \$stderr,
+);
+is($stdout, $port_primary,
+ "prefer-standby: skips dead standby, connects to primary");
+
+# prefer-standby: when no host passes the check, the fallback pass accepts
+# any server, including a standby with no WAL receiver. This is what
+# distinguishes prefer-standby from "any", which fails outright above.
+$standby_norecv->psql(
+ 'postgres',
+ 'SELECT inet_server_port()',
+ connstr => "dbname=postgres host=localhost port=$port_norecv "
+ . "target_session_attrs=prefer-standby require_wal_receiver=1",
+ stdout => \$stdout,
+ stderr => \$stderr,
+);
+is($stdout, $port_norecv,
+ "prefer-standby: fallback pass accepts a standby with no WAL receiver");
+
+# any: dead standby skipped, connects to primary.
+$primary->psql(
+ 'postgres',
+ 'SELECT inet_server_port()',
+ connstr => "dbname=postgres host=localhost,localhost "
+ . "port=$port_norecv,$port_primary "
+ . "target_session_attrs=any require_wal_receiver=1",
+ stdout => \$stdout,
+ stderr => \$stderr,
+);
+is($stdout, $port_primary,
+ "any: skips dead standby, connects to primary");
+
+# any: dead standby skipped, live standby accepted.
+$standby_live->psql(
+ 'postgres',
+ 'SELECT inet_server_port()',
+ connstr => "dbname=postgres host=localhost,localhost "
+ . "port=$port_norecv,$port_live "
+ . "target_session_attrs=any require_wal_receiver=1",
+ stdout => \$stdout,
+ stderr => \$stderr,
+);
+is($stdout, $port_live,
+ "any: skips dead standby, connects to live standby");
+
+# any: connection fails if there are only standby servers in the
+# connection string and they have no WAL receiver running
+$standby_live->psql(
+ 'postgres',
+ 'SELECT inet_server_port()',
+ connstr => "dbname=postgres host=localhost "
+ . "port=$port_norecv "
+ . "target_session_attrs=any require_wal_receiver=1",
+ stdout => \$stdout,
+ stderr => \$stderr,
+);
+like($stderr, qr/standby has no active WAL receiver/,
+ "any: with no live standby fails");
+
+# read-only: dead standby skipped, live standby accepted (primary is
+# read-write so skipped by the read-only filter).
+$standby_live->psql(
+ 'postgres',
+ 'SELECT inet_server_port()',
+ connstr => "dbname=postgres host=localhost,localhost,localhost "
+ . "port=$port_primary,$port_norecv,$port_live "
+ . "target_session_attrs=read-only require_wal_receiver=1",
+ stdout => \$stdout,
+ stderr => \$stderr,
+);
+is($stdout, $port_live,
+ "read-only: skips dead standby, connects to live standby");
+
+# standby only, all standbys dead: connection fails.
+$standby_norecv->psql(
+ 'postgres',
+ 'SELECT inet_server_port()',
+ connstr => "dbname=postgres host=localhost port=$port_norecv "
+ . "target_session_attrs=standby require_wal_receiver=1",
+ stdout => \$stdout,
+ stderr => \$stderr,
+);
+like($stderr, qr/standby has no active WAL receiver/,
+ "standby-only with no live standby fails");
+
+# read-write: check is bypassed entirely; connects to primary even when a
+# dead standby is listed first.
+$primary->psql(
+ 'postgres',
+ 'SELECT inet_server_port()',
+ connstr => "dbname=postgres host=localhost,localhost "
+ . "port=$port_norecv,$port_primary "
+ . "target_session_attrs=read-write require_wal_receiver=1",
+ stdout => \$stdout,
+ stderr => \$stderr,
+);
+is($stdout, $port_primary,
+ "read-write: check bypassed, connects to primary");
+
+# primary: connecting directly to a primary with the check enabled is fine
+# (pg_is_in_recovery() is false, so require_wal_receiver is ignored).
+$primary->psql(
+ 'postgres',
+ 'SELECT inet_server_port()',
+ connstr => "dbname=postgres host=localhost port=$port_primary "
+ . "require_wal_receiver=1",
+ stdout => \$stdout,
+ stderr => \$stderr,
+);
+is($stdout, $port_primary,
+ "primary accepted (require_wal_receiver is ignored)");
+
+# invalid value is rejected during option parsing.
+$standby_live->psql(
+ 'postgres',
+ 'SELECT 1',
+ connstr => "dbname=postgres host=localhost port=$port_live "
+ . "require_wal_receiver=foo",
+ stdout => \$stdout,
+ stderr => \$stderr,
+);
+like($stderr, qr/invalid require_wal_receiver value: "foo"/,
+ "invalid boolean value rejected");
+
+# non-superuser: a live standby is accepted. pg_stat_wal_receiver exposes
+# the walreceiver pid (hence a row) even to unprivileged roles, while
+# masking every other column. The check uses EXISTS, so it must work
+# without pg_read_all_stats. We probe with current_setting('port')
+# rather than inet_server_port(), so the query itself needs no special
+# privilege.
+$standby_live->psql(
+ 'postgres',
+ "SELECT current_setting('port')",
+ connstr => "dbname=postgres host=localhost port=$port_live "
+ . "user=regress_walrcv_user "
+ . "target_session_attrs=standby require_wal_receiver=1",
+ stdout => \$stdout,
+ stderr => \$stderr,
+);
+is($stdout, $port_live, "non-superuser: live standby accepted");
+
+# non-superuser: a standby with no WAL receiver is still rejected.
+$standby_norecv->psql(
+ 'postgres',
+ "SELECT current_setting('port')",
+ connstr => "dbname=postgres host=localhost port=$port_norecv "
+ . "user=regress_walrcv_user "
+ . "target_session_attrs=standby require_wal_receiver=1",
+ stdout => \$stdout,
+ stderr => \$stderr,
+);
+like($stderr, qr/standby has no active WAL receiver/,
+ "non-superuser: standby without WAL receiver rejected");
+
+# Replication connections bypass the check entirely: a physical replication
+# connection cannot execute SQL at all, so applying the check would break
+# every one of them (including a cascading standby's own WAL receiver). Use
+# the standby with no WAL receiver, which would be rejected on an ordinary
+# connection, and SHOW, which a WAL sender does accept.
+$standby_norecv->psql(
+ 'postgres',
+ 'SHOW port',
+ connstr => "dbname=postgres host=localhost port=$port_norecv "
+ . "target_session_attrs=standby replication=1 require_wal_receiver=1",
+ stdout => \$stdout,
+ stderr => \$stderr,
+);
+is($stdout, $port_norecv,
+ "physical replication connection bypasses the check");
+
+# "true" rather than "1": this is the spelling the WAL receiver itself and
+# pg_basebackup use, so it is the one that matters in practice.
+$standby_norecv->psql(
+ 'postgres',
+ 'SHOW port',
+ connstr => "dbname=postgres host=localhost port=$port_norecv "
+ . "target_session_attrs=standby replication=true "
+ . "require_wal_receiver=1",
+ stdout => \$stdout,
+ stderr => \$stderr,
+);
+is($stdout, $port_norecv,
+ "replication=true bypasses the check");
+
+# The same for a logical replication connection.
+$standby_norecv->psql(
+ 'postgres',
+ 'SHOW port',
+ connstr => "dbname=postgres host=localhost port=$port_norecv "
+ . "target_session_attrs=standby replication=database "
+ . "require_wal_receiver=1",
+ stdout => \$stdout,
+ stderr => \$stderr,
+);
+is($stdout, $port_norecv,
+ "logical replication connection bypasses the check");
+
+# An explicitly false "replication" value is an ordinary connection, so the
+# check still applies.
+$standby_norecv->psql(
+ 'postgres',
+ 'SELECT inet_server_port()',
+ connstr => "dbname=postgres host=localhost port=$port_norecv "
+ . "target_session_attrs=standby replication=off require_wal_receiver=1",
+ stdout => \$stdout,
+ stderr => \$stderr,
+);
+like($stderr, qr/standby has no active WAL receiver/,
+ "replication=off is an ordinary connection, check applies");
+
+{
+ # PGREQUIREWALRECEIVER environment variable enabled
+ local $ENV{PGREQUIREWALRECEIVER} = '1';
+ $standby_norecv->psql(
+ 'postgres',
+ 'SELECT 1',
+ connstr => "dbname=postgres host=localhost port=$port_norecv "
+ . "target_session_attrs=standby",
+ stdout => \$stdout,
+ stderr => \$stderr,
+ );
+ like($stderr, qr/standby has no active WAL receiver/,
+ "PGREQUIREWALRECEIVER set to 1");
+
+ # PGREQUIREWALRECEIVER environment variable override via require_wal_receiver
+ $standby_norecv->psql(
+ 'postgres',
+ 'SELECT inet_server_port()',
+ connstr => "dbname=postgres host=localhost port=$port_norecv "
+ . "target_session_attrs=standby require_wal_receiver=0",
+ stdout => \$stdout,
+ stderr => \$stderr,
+ );
+ is($stdout, $port_norecv,
+ "PGREQUIREWALRECEIVER override via require_wal_receiver");
+
+ # PGREQUIREWALRECEIVER environment variable disabled
+ local $ENV{PGREQUIREWALRECEIVER} = '0';
+ $standby_norecv->psql(
+ 'postgres',
+ 'SELECT inet_server_port()',
+ connstr => "dbname=postgres host=localhost port=$port_norecv "
+ . "target_session_attrs=standby",
+ stdout => \$stdout,
+ stderr => \$stderr,
+ );
+ is($stdout, $port_norecv,
+ "PGREQUIREWALRECEIVER set to 0");
+}
+
+# upstream lost: after the primary stops, standby_live's WAL receiver exits
+# and (thanks to the long retry interval) stays gone, so the host is
+# rejected. Run last because it stops the primary.
+$primary->stop;
+$standby_live->poll_query_until('postgres',
+ "SELECT NOT EXISTS (SELECT 1 FROM pg_stat_wal_receiver)")
+ or die "standby_live WAL receiver did not exit after primary stop";
+
+$standby_live->psql(
+ 'postgres',
+ 'SELECT inet_server_port()',
+ connstr => "dbname=postgres host=localhost port=$port_live "
+ . "target_session_attrs=standby require_wal_receiver=1",
+ stdout => \$stdout,
+ stderr => \$stderr,
+);
+like($stderr, qr/standby has no active WAL receiver/,
+ "standby rejected after losing upstream");
+
+done_testing();
diff --git a/src/test/regress/pg_regress.c b/src/test/regress/pg_regress.c
index 13944701bc7..ca84ec6273b 100644
--- a/src/test/regress/pg_regress.c
+++ b/src/test/regress/pg_regress.c
@@ -839,6 +839,7 @@ initialize_environment(void)
unsetenv("PGPASSWORD");
unsetenv("PGREQUIREPEER");
unsetenv("PGREQUIRESSL");
+ unsetenv("PGREQUIREWALRECEIVER");
unsetenv("PGSERVICE");
unsetenv("PGSERVICEFILE");
unsetenv("PGSSLCERT");
--
2.55.0
