Dear Fujii-san, > The patched pg_createsubscriber seems to assume that the server > supports output_plugin_libraries. However, a user may run a newer > version of pg_createsubscriber against a server running an older minor > version that does not yet support output_plugin_libraries (e.g., v18.4). > So, for the v17 and v18 versions of pg_createsubscriber, it should handle > this case? > > > Previously, a non-superuser without permission to access > output_plugin_libraries could run pg_createsubscriber successfully. > But, with the patch, it fails with a permission denied error. I think > we should avoid this, for example by skipping the check when the user > doesn't have sufficient permission, rather than adding a new prerequisite > for running pg_createsubscriber. Thoughts?
Good point, should be fixed. I came up with an idea to spedcify missing_ok := true for current_setting(), and skip checking if it returns NULL. Thought? > + /* Also check per-database settings on the publisher */ > + check_publisher_per_database(dbinfo); > > In the v19 and v20 patches, this check is called from > check_publisher(), whereas in the v17 and v18 patches it is called from > check_subscriber(). Could you tell me why they are different? It was not intended. The git am command on my env put the code at the wrong place, so it was the reason. The function referred dbinfo[i].pubconninfo, so the behavior is the same. Moved to the correct place. Attached new patch set. I also noticed that max_slot_wal_keep_size is not checked in PG17, so 0002 was removed. Best regards, Hayato Kuroda FUJITSU LIMITED
v4-0001-pg_createsubscriber-ensure-output_plugin_librarie.patch
Description: v4-0001-pg_createsubscriber-ensure-output_plugin_librarie.patch
v4-0002-free-allocated-memory-for-max_slot_wal_keep_size.patch
Description: v4-0002-free-allocated-memory-for-max_slot_wal_keep_size.patch
From 05ab10e598b24c92b97a96ce30338c1260e13ecb Mon Sep 17 00:00:00 2001 From: Hayato Kuroda <[email protected]> Date: Fri, 4 Sep 2026 12:35:21 +0900 Subject: [PATCH v4-PG17] pg_createsubscriber: ensure output_plugin_libraries include "pgoutput" pg_createsubscriber always uses pgoutput when creating logical replication slots. Check that pgoutput is included in the publisher's output_plugin_libraries setting before proceeding, and provide a useful error and hint when it is not. This also allows --dry-run to detect the invalid configuration before any replication slots are created. --- doc/src/sgml/ref/pg_createsubscriber.sgml | 3 + src/backend/utils/misc/guc_tables.c | 2 +- src/bin/pg_basebackup/pg_createsubscriber.c | 90 +++++++++++++++++++ .../t/040_pg_createsubscriber.pl | 22 +++++ 4 files changed, 116 insertions(+), 1 deletion(-) diff --git a/doc/src/sgml/ref/pg_createsubscriber.sgml b/doc/src/sgml/ref/pg_createsubscriber.sgml index 26b8e64a4e0..28879b7aa63 100644 --- a/doc/src/sgml/ref/pg_createsubscriber.sgml +++ b/doc/src/sgml/ref/pg_createsubscriber.sgml @@ -312,6 +312,9 @@ PostgreSQL documentation replication slots. The source server must have <xref linkend="guc-max-wal-senders"/> configured to a value greater than or equal to the number of specified databases and existing WAL sender processes. + The source server must also allow the <literal>pgoutput</literal> output + plugin by setting <xref linkend="guc-output-plugin-libraries"/> to include + <literal>pgoutput</literal>. </para> </refsect2> diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c index cf468ba7d10..021c613bee4 100644 --- a/src/backend/utils/misc/guc_tables.c +++ b/src/backend/utils/misc/guc_tables.c @@ -4736,7 +4736,7 @@ struct config_string ConfigureNamesString[] = {"output_plugin_libraries", PGC_SUSET, REPLICATION_SENDING, gettext_noop("Lists libraries that may be named as logical decoding output plugins."), gettext_noop("Users with REPLICATION privileges may only use plugins in this list when creating logical replication slots."), - /* note: src/bin/pg_upgrade/check.c assumes GUC_LIST_QUOTE here */ + /* note: src/bin/pg_upgrade/check.c and src/bin/pg_basebackup/pg_createsubscriber.c assume GUC_LIST_QUOTE here */ GUC_LIST_INPUT | GUC_LIST_QUOTE | GUC_SUPERUSER_ONLY }, &output_plugin_libraries_string, diff --git a/src/bin/pg_basebackup/pg_createsubscriber.c b/src/bin/pg_basebackup/pg_createsubscriber.c index b8f3b7111de..0bdb6805c89 100644 --- a/src/bin/pg_basebackup/pg_createsubscriber.c +++ b/src/bin/pg_basebackup/pg_createsubscriber.c @@ -832,6 +832,93 @@ server_is_in_recovery(PGconn *conn) return ret == 0; } +/* + * Check per-database settings on the publisher + */ +static void +check_publisher_per_database(const struct LogicalRepInfo *dbinfo) +{ + for (int i = 0; i < num_dbs; i++) + { + PGconn *conn; + PGresult *res; + char *output_plugin_libraries; + char *output_plugin_libraries_copy; + char **allowed_plugins; + bool pgoutput_allowed = false; + + conn = connect_database(dbinfo[i].pubconninfo, true); + + /* Check whether output_plugin_libraries includes 'pgoutput' */ + res = PQexec(conn, + "SELECT pg_catalog.current_setting('output_plugin_libraries', true)"); + + if (PQresultStatus(res) != PGRES_TUPLES_OK) + { + pg_log_error("could not obtain publisher settings in database \"%s\": %s", + dbinfo[i].dbname, PQresultErrorMessage(res)); + disconnect_database(conn, true); + } + + /* + * output_plugin_libraries was introduced in a minor release. Older + * publishers do not restrict output plugins, so there is nothing to + * check. + */ + if (PQgetisnull(res, 0, 0)) + { + Assert(i == 0); + + PQclear(res); + disconnect_database(conn, false); + return; + } + + output_plugin_libraries = pg_strdup(PQgetvalue(res, 0, 0)); + + PQclear(res); + pg_log_debug("publisher in database \"%s\": output_plugin_libraries: %s", + dbinfo[i].dbname, output_plugin_libraries); + + disconnect_database(conn, false); + + output_plugin_libraries_copy = pg_strdup(output_plugin_libraries); + + if (!SplitGUCList(output_plugin_libraries_copy, ',', &allowed_plugins)) + { + /* + * Should not happen. (Frontend and backend GUC_LIST_QUOTE parsing + * have to remain compatible for pg_dump at minimum.) + */ + pg_fatal("could not parse \"output_plugin_libraries\" setting '%s' in database \"%s\"", + output_plugin_libraries, dbinfo[i].dbname); + } + + /* Make sure the output_plugin_libraries setting includes "pgoutput" */ + for (char **plugin = allowed_plugins; *plugin; plugin++) + { + if (strcmp(*plugin, "pgoutput") == 0) + { + pgoutput_allowed = true; + break; + } + } + + if (!pgoutput_allowed) + { + pg_log_error("publisher does not allow the \"pgoutput\" output plugin in database \"%s\"", + dbinfo[i].dbname); + pg_log_error_hint("Add \"pgoutput\" to the configuration parameter \"%s\".", + "output_plugin_libraries"); + exit(1); + } + + pg_free(output_plugin_libraries); + pg_free(output_plugin_libraries_copy); + pg_free(allowed_plugins); + } +} + /* * Is the primary server ready for logical replication? * @@ -944,6 +1031,9 @@ check_publisher(const struct LogicalRepInfo *dbinfo) if (failed) exit(1); + + /* Also check per-database settings on the publisher */ + check_publisher_per_database(dbinfo); } /* diff --git a/src/bin/pg_basebackup/t/040_pg_createsubscriber.pl b/src/bin/pg_basebackup/t/040_pg_createsubscriber.pl index 0a900edb656..544481b9322 100644 --- a/src/bin/pg_basebackup/t/040_pg_createsubscriber.pl +++ b/src/bin/pg_basebackup/t/040_pg_createsubscriber.pl @@ -318,6 +318,28 @@ $node_p->safe_psql($db1, $node_p->wait_for_replay_catchup($node_s); $node_s->stop; +# pg_createsubscriber requires that output_plugin_libraries includes 'pgoutput' +# on the publisher. +$node_p->safe_psql($db2, + "ALTER DATABASE \"$db2\" SET output_plugin_libraries = 'test_decoding'"); + +command_fails_like( + [ + 'pg_createsubscriber', + '--dry-run', + '--pgdata' => $node_s->data_dir, + '--publisher-server' => $node_p->connstr($db1), + '--socketdir' => $node_s->host, + '--subscriber-port' => $node_s->port, + '--database' => $db1, + '--database' => $db2, + ], + qr/publisher does not allow the "pgoutput" output plugin in database "\Q$db2\E"/, + 'primary does not allow to load pgoutput plugin'); + +$node_p->safe_psql($db2, + "ALTER DATABASE \"$db2\" RESET output_plugin_libraries"); + # dry run mode on node S command_ok( [ -- 2.52.0
From fab34e9a1345bb3199903f9d55974ceb3c7fa8ba Mon Sep 17 00:00:00 2001 From: Hayato Kuroda <[email protected]> Date: Fri, 4 Sep 2026 12:35:21 +0900 Subject: [PATCH v4-PG18 1/2] pg_createsubscriber: ensure output_plugin_libraries include "pgoutput" pg_createsubscriber always uses pgoutput when creating logical replication slots. Check that pgoutput is included in the publisher's output_plugin_libraries setting before proceeding, and provide a useful error and hint when it is not. This also allows --dry-run to detect the invalid configuration before any replication slots are created. --- doc/src/sgml/ref/pg_createsubscriber.sgml | 3 + src/backend/utils/misc/guc_tables.c | 2 +- src/bin/pg_basebackup/pg_createsubscriber.c | 90 +++++++++++++++++++ .../t/040_pg_createsubscriber.pl | 22 +++++ 4 files changed, 116 insertions(+), 1 deletion(-) diff --git a/doc/src/sgml/ref/pg_createsubscriber.sgml b/doc/src/sgml/ref/pg_createsubscriber.sgml index bb9cc72576c..427df49b692 100644 --- a/doc/src/sgml/ref/pg_createsubscriber.sgml +++ b/doc/src/sgml/ref/pg_createsubscriber.sgml @@ -385,6 +385,9 @@ PostgreSQL documentation replication slots. The source server must have <xref linkend="guc-max-wal-senders"/> configured to a value greater than or equal to the number of specified databases and existing WAL sender processes. + The source server must also allow the <literal>pgoutput</literal> output + plugin by setting <xref linkend="guc-output-plugin-libraries"/> to include + <literal>pgoutput</literal>. </para> </refsect2> diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c index 7cab4c735c2..0d7c18f19ca 100644 --- a/src/backend/utils/misc/guc_tables.c +++ b/src/backend/utils/misc/guc_tables.c @@ -4987,7 +4987,7 @@ struct config_string ConfigureNamesString[] = {"output_plugin_libraries", PGC_SUSET, REPLICATION_SENDING, gettext_noop("Lists libraries that may be named as logical decoding output plugins."), gettext_noop("Users with REPLICATION privileges may only use plugins in this list when creating logical replication slots."), - /* note: src/bin/pg_upgrade/check.c assumes GUC_LIST_QUOTE here */ + /* note: src/bin/pg_upgrade/check.c and src/bin/pg_basebackup/pg_createsubscriber.c assume GUC_LIST_QUOTE here */ GUC_LIST_INPUT | GUC_LIST_QUOTE | GUC_SUPERUSER_ONLY }, &output_plugin_libraries_string, diff --git a/src/bin/pg_basebackup/pg_createsubscriber.c b/src/bin/pg_basebackup/pg_createsubscriber.c index 42a073e921f..a86cf4c792c 100644 --- a/src/bin/pg_basebackup/pg_createsubscriber.c +++ b/src/bin/pg_basebackup/pg_createsubscriber.c @@ -867,6 +867,93 @@ server_is_in_recovery(PGconn *conn) return ret == 0; } +/* + * Check per-database settings on the publisher + */ +static void +check_publisher_per_database(const struct LogicalRepInfo *dbinfo) +{ + for (int i = 0; i < num_dbs; i++) + { + PGconn *conn; + PGresult *res; + char *output_plugin_libraries; + char *output_plugin_libraries_copy; + char **allowed_plugins; + bool pgoutput_allowed = false; + + conn = connect_database(dbinfo[i].pubconninfo, true); + + /* Check whether output_plugin_libraries includes 'pgoutput' */ + res = PQexec(conn, + "SELECT pg_catalog.current_setting('output_plugin_libraries', true)"); + + if (PQresultStatus(res) != PGRES_TUPLES_OK) + { + pg_log_error("could not obtain publisher settings in database \"%s\": %s", + dbinfo[i].dbname, PQresultErrorMessage(res)); + disconnect_database(conn, true); + } + + /* + * output_plugin_libraries was introduced in a minor release. Older + * publishers do not restrict output plugins, so there is nothing to + * check. + */ + if (PQgetisnull(res, 0, 0)) + { + Assert(i == 0); + + PQclear(res); + disconnect_database(conn, false); + return; + } + + output_plugin_libraries = pg_strdup(PQgetvalue(res, 0, 0)); + + PQclear(res); + pg_log_debug("publisher in database \"%s\": output_plugin_libraries: %s", + dbinfo[i].dbname, output_plugin_libraries); + + disconnect_database(conn, false); + + output_plugin_libraries_copy = pg_strdup(output_plugin_libraries); + + if (!SplitGUCList(output_plugin_libraries_copy, ',', &allowed_plugins)) + { + /* + * Should not happen. (Frontend and backend GUC_LIST_QUOTE parsing + * have to remain compatible for pg_dump at minimum.) + */ + pg_fatal("could not parse \"output_plugin_libraries\" setting '%s' in database \"%s\"", + output_plugin_libraries, dbinfo[i].dbname); + } + + /* Make sure the output_plugin_libraries setting includes "pgoutput" */ + for (char **plugin = allowed_plugins; *plugin; plugin++) + { + if (strcmp(*plugin, "pgoutput") == 0) + { + pgoutput_allowed = true; + break; + } + } + + if (!pgoutput_allowed) + { + pg_log_error("publisher does not allow the \"pgoutput\" output plugin in database \"%s\"", + dbinfo[i].dbname); + pg_log_error_hint("Add \"pgoutput\" to the configuration parameter \"%s\".", + "output_plugin_libraries"); + exit(1); + } + + pg_free(output_plugin_libraries); + pg_free(output_plugin_libraries_copy); + pg_free(allowed_plugins); + } +} + /* * Is the primary server ready for logical replication? * @@ -998,6 +1085,9 @@ check_publisher(const struct LogicalRepInfo *dbinfo) if (failed) exit(1); + + /* Also check per-database settings on the publisher */ + check_publisher_per_database(dbinfo); } /* diff --git a/src/bin/pg_basebackup/t/040_pg_createsubscriber.pl b/src/bin/pg_basebackup/t/040_pg_createsubscriber.pl index 09014be1361..3d449f604a6 100644 --- a/src/bin/pg_basebackup/t/040_pg_createsubscriber.pl +++ b/src/bin/pg_basebackup/t/040_pg_createsubscriber.pl @@ -346,6 +346,28 @@ is($node_s->safe_psql($db1, "SELECT COUNT(*) FROM pg_publication"), $node_s->stop; +# pg_createsubscriber requires that output_plugin_libraries includes 'pgoutput' +# on the publisher. +$node_p->safe_psql($db2, + "ALTER DATABASE \"$db2\" SET output_plugin_libraries = 'test_decoding'"); + +command_fails_like( + [ + 'pg_createsubscriber', + '--dry-run', + '--pgdata' => $node_s->data_dir, + '--publisher-server' => $node_p->connstr($db1), + '--socketdir' => $node_s->host, + '--subscriber-port' => $node_s->port, + '--database' => $db1, + '--database' => $db2, + ], + qr/publisher does not allow the "pgoutput" output plugin in database "\Q$db2\E"/, + 'primary does not allow to load pgoutput plugin'); + +$node_p->safe_psql($db2, + "ALTER DATABASE \"$db2\" RESET output_plugin_libraries"); + # dry run mode on node S command_ok( [ -- 2.52.0
From 68947d25e1e72bd38f3d2ae042c29836375ed948 Mon Sep 17 00:00:00 2001 From: Hayato Kuroda <[email protected]> Date: Fri, 4 Sep 2026 12:00:04 +0900 Subject: [PATCH v4-PG18 2/2] free allocated memory for max_slot_wal_keep_size --- src/bin/pg_basebackup/pg_createsubscriber.c | 1 + 1 file changed, 1 insertion(+) diff --git a/src/bin/pg_basebackup/pg_createsubscriber.c b/src/bin/pg_basebackup/pg_createsubscriber.c index a86cf4c792c..ecff0f119fe 100644 --- a/src/bin/pg_basebackup/pg_createsubscriber.c +++ b/src/bin/pg_basebackup/pg_createsubscriber.c @@ -1082,6 +1082,7 @@ check_publisher(const struct LogicalRepInfo *dbinfo) } pg_free(wal_level); + pg_free(max_slot_wal_keep_size); if (failed) exit(1); -- 2.52.0
