Dear Fujii-san, Jacob, Thanks for giving feedbacks. I'm confusing and let me confirm few points. The situation might be different before and after the PG19.
For PG17 and 18, FOR ALL TABLES publications for publisher databases, it meant superuser is required for the connection. IIUC superuser can bypass all permission checks thus it might be OK not to check these points. (We may have to check it though). As for the PG19 and HEAD, we allowed to re-use existing publications [1], it effectively allowed to use non-superuser for connecting to the publisher. However it meant additional permission checks might be needed like Jacob pointed out. In this thread I do not want to broaden for other points and focus on the added GUC, thought? In other threads we can improve --check option more. Based on that, permission check is actually needed for PG19/HEAD same check Fujii-san pointed out. For PG18/17, we may not have to take care the usage of current_setting() but same code is used for better understanding. Attached patch set does accordingly. v5-0001 also has a test for the case just in case, but not sure it should be pushed. [1]: https://github.com/postgres/postgres/commit/85ddcc2f4cdef490276d151c80459e287bceb782 Best regards, Hayato Kuroda FUJITSU LIMITED
From 8a1fcb7b9170f477b602e9bcb9804512e5c99f0b Mon Sep 17 00:00:00 2001 From: Hayato Kuroda <[email protected]> Date: Fri, 4 Sep 2026 12:35:21 +0900 Subject: [PATCH v5-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 | 91 +++++++++++++++++++ .../t/040_pg_createsubscriber.pl | 22 +++++ 4 files changed, 117 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..92820dd60b8 100644 --- a/src/bin/pg_basebackup/pg_createsubscriber.c +++ b/src/bin/pg_basebackup/pg_createsubscriber.c @@ -832,6 +832,94 @@ 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 setting FROM pg_catalog.pg_settings " + "WHERE name = 'output_plugin_libraries'"); + + 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 (PQntuples(res) != 1) + { + 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 +1032,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 a8c927072f49dfad6942ef28d030ee3eb1d6d8f6 Mon Sep 17 00:00:00 2001 From: Hayato Kuroda <[email protected]> Date: Fri, 4 Sep 2026 12:35:21 +0900 Subject: [PATCH v5-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 | 91 +++++++++++++++++++ .../t/040_pg_createsubscriber.pl | 22 +++++ 4 files changed, 117 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..2a1600269ff 100644 --- a/src/bin/pg_basebackup/pg_createsubscriber.c +++ b/src/bin/pg_basebackup/pg_createsubscriber.c @@ -867,6 +867,94 @@ 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 setting FROM pg_catalog.pg_settings " + "WHERE name = 'output_plugin_libraries'"); + + 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 (PQntuples(res) != 1) + { + 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 +1086,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 42acba3e7d769fea3040a4210d678d5a7f545750 Mon Sep 17 00:00:00 2001 From: Hayato Kuroda <[email protected]> Date: Fri, 4 Sep 2026 12:00:04 +0900 Subject: [PATCH v5-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 2a1600269ff..22f15d28efa 100644 --- a/src/bin/pg_basebackup/pg_createsubscriber.c +++ b/src/bin/pg_basebackup/pg_createsubscriber.c @@ -1083,6 +1083,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
v5-0001-pg_createsubscriber-ensure-output_plugin_librarie.patch
Description: v5-0001-pg_createsubscriber-ensure-output_plugin_librarie.patch
v5-0002-free-allocated-memory-for-max_slot_wal_keep_size.patch
Description: v5-0002-free-allocated-memory-for-max_slot_wal_keep_size.patch
