[PATCH] Add pg_get_subscription_ddl() function

From 1c3ff3e9e480444ff7386147d62cf5e841e36e3f Mon Sep 17 00:00:00 2001
From: Vaibhav Dalvi <vaibhav.dalvi@enterprisedb.com>
Date: Thu, 6 Nov 2025 13:38:42 +0000
Subject: [PATCH v2 1/1] Add pg_get_subscription_ddl() function

This new SQL-callable function returns the `CREATE SUBSCRIPTION`
statement for a given subscription name or oid.

Like `pg_dump`, the returned DDL explicitly sets `connect = false`.
This is because the original `CONNECT` option value is not cataloged,
and using `connect = false` ensures the DDL can be successfully executed
even if the remote publisher is unreachable.

This function is restricted to users that have the "pg_read_all_data" and/or
"pg_create_subscription" privilege.  This is a security measure because
subscription connection strings often contain sensitive information, such as
passwords.

Author: Vaibhav Dalvi <vaibhav.dalvi@enterprisedb.com>
Reviewers: Akshay Joshi, Ian Barwick
Discussion: https://www.postgresql.org/message-id/CA%2BvB%3DAGG0NoxWW%3D-947RBmba8Pzhj8j7op0Xkv8nLDzVMc2%3D7w%40mail.gmail.com
---
 doc/src/sgml/func/func-info.sgml           |  51 +++++
 src/backend/catalog/pg_subscription.c      |   4 +-
 src/backend/utils/adt/ruleutils.c          | 216 +++++++++++++++++++++
 src/include/catalog/pg_proc.dat            |   6 +
 src/include/catalog/pg_subscription.h      |   2 +
 src/include/utils/ruleutils.h              |   1 +
 src/test/regress/expected/subscription.out |  95 +++++++++
 src/test/regress/sql/subscription.sql      |  60 ++++++
 8 files changed, 432 insertions(+), 3 deletions(-)

diff --git a/doc/src/sgml/func/func-info.sgml b/doc/src/sgml/func/func-info.sgml
index d4508114a48..21d33ee1802 100644
--- a/doc/src/sgml/func/func-info.sgml
+++ b/doc/src/sgml/func/func-info.sgml
@@ -3797,4 +3797,55 @@ acl      | {postgres=arwdDxtm/postgres,foo=r/postgres}
 
   </sect2>
 
+  <sect2 id="functions-get-object-ddl">
+   <title>Get Object DDL Functions</title>
+
+   <para>
+    The functions shown in <xref linkend="functions-get-object-ddl-table"/>
+    print the DDL statements for various database objects.
+    (This is a decompiled reconstruction, not the original text
+    of the command.)
+   </para>
+
+   <table id="functions-get-object-ddl-table">
+    <title>Get Object DDL Functions</title>
+    <tgroup cols="1">
+     <thead>
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        Function
+       </para>
+       <para>
+        Description
+       </para></entry>
+      </row>
+     </thead>
+
+     <tbody>
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>pg_get_subscription_ddl</primary>
+        </indexterm>
+        <function>pg_get_subscription_ddl</function> ( <parameter>subscription</parameter> <type>text</type> )
+        <returnvalue>text</returnvalue>
+       </para>
+       <para>
+        Reconstructs the creating command for a subscription.
+        The result is a complete <command>CREATE SUBSCRIPTION</command>
+        statement. The <literal>connect</literal> option set to
+        <literal>false</literal>.
+       </para>
+       <para>
+        This function is restricted to users that have the
+        <literal>pg_read_all_data</literal> and/or
+        <literal>pg_create_subscription</literal> privilege.
+       </para></entry>
+      </row>
+     </tbody>
+    </tgroup>
+   </table>
+
+  </sect2>
+
   </sect1>
diff --git a/src/backend/catalog/pg_subscription.c b/src/backend/catalog/pg_subscription.c
index 1945627ed88..98dc15e0f41 100644
--- a/src/backend/catalog/pg_subscription.c
+++ b/src/backend/catalog/pg_subscription.c
@@ -32,8 +32,6 @@
 #include "utils/rel.h"
 #include "utils/syscache.h"
 
-static List *textarray_to_stringlist(ArrayType *textarray);
-
 /*
  * Add a comma-separated list of publication names to the 'dest' string.
  */
@@ -240,7 +238,7 @@ DisableSubscription(Oid subid)
  *
  * Note: the resulting list of strings is pallocated here.
  */
-static List *
+List *
 textarray_to_stringlist(ArrayType *textarray)
 {
 	Datum	   *elems;
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 556ab057e5a..52007bcbab7 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -35,6 +35,7 @@
 #include "catalog/pg_partitioned_table.h"
 #include "catalog/pg_proc.h"
 #include "catalog/pg_statistic_ext.h"
+#include "catalog/pg_subscription.h"
 #include "catalog/pg_trigger.h"
 #include "catalog/pg_type.h"
 #include "commands/defrem.h"
@@ -57,6 +58,7 @@
 #include "rewrite/rewriteHandler.h"
 #include "rewrite/rewriteManip.h"
 #include "rewrite/rewriteSupport.h"
+#include "utils/acl.h"
 #include "utils/array.h"
 #include "utils/builtins.h"
 #include "utils/fmgroids.h"
@@ -13743,3 +13745,217 @@ get_range_partbound_string(List *bound_datums)
 
 	return buf.data;
 }
+
+/*
+ * build_subscription_ddl_string - Build CREATE SUBSCRIPTION statement for
+ * a subscription from its OID. This is internal version which helps
+ * pg_get_subscription_ddl_name() and pg_get_subscription_ddl_oid().
+ */
+char *
+build_subscription_ddl_string(const Oid suboid)
+{
+	Form_pg_subscription subForm;
+	StringInfo	pubnames;
+	StringInfoData buf;
+	HeapTuple	tup;
+	char	   *conninfo;
+	List	   *publist;
+	Datum		datum;
+	bool		isnull;
+
+	/*
+	 * To prevent unprivileged users from initiating unauthorized network
+	 * connections, dumping subscription creation is restricted.  A user must
+	 * be specifically authorized (via the appropriate role privilege) to
+	 * create subscriptions and/or to read all data.
+	 */
+	if (!(has_privs_of_role(GetUserId(), ROLE_PG_CREATE_SUBSCRIPTION) ||
+		has_privs_of_role(GetUserId(), ROLE_PG_READ_ALL_DATA)))
+		ereport(ERROR,
+				(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+				 errmsg("permission denied to get the create subscription ddl"),
+				 errdetail("Only roles with privileges of the \"%s\" and/or \"%s\" role may get ddl.",
+						   "pg_create_subscription", "pg_read_all_data")));
+
+	/* Look up the subscription in pg_subscription */
+	tup = SearchSysCache1(SUBSCRIPTIONOID, ObjectIdGetDatum(suboid));
+	if (!HeapTupleIsValid(tup))
+		ereport(ERROR,
+				(errcode(ERRCODE_UNDEFINED_OBJECT),
+				 errmsg("subscription with oid %d does not exist", suboid)));
+
+	/* Get subscription's details from its tuple */
+	subForm = (Form_pg_subscription) GETSTRUCT(tup);
+
+	initStringInfo(&buf);
+
+	/* Build the CREATE SUBSCRIPTION statement */
+	appendStringInfo(&buf, "CREATE SUBSCRIPTION %s ",
+					 quote_identifier(NameStr(subForm->subname)));
+
+	/* Get conninfo */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_subconninfo);
+	conninfo = TextDatumGetCString(datum);
+
+	/* Append connection info to the CREATE SUBSCRIPTION statement */
+	appendStringInfo(&buf, "CONNECTION \'%s\'", conninfo);
+
+	/* Build list of quoted publications and append them to query */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_subpublications);
+	publist = textarray_to_stringlist(DatumGetArrayTypeP(datum));
+	pubnames = makeStringInfo();
+	GetPublicationsStr(publist, pubnames, false);
+	appendStringInfo(&buf, " PUBLICATION %s", pubnames->data);
+
+	/*
+	 * Add options using WITH clause.  The 'connect' option value given at the
+	 * time of subscription creation is not available in the catalog.  When
+	 * creating a subscription, the remote host is not reachable or in an
+	 * unclear state, in that case, the subscription can be created using
+	 * 'connect = false' option.  This is what pg_dump uses.
+	 *
+	 * The status or value of the options 'create_slot' and 'copy_data' not
+	 * available in the catalog table.  We can use default values i.e. TRUE
+	 * for both.  This is what pg_dump uses.
+	 */
+	appendStringInfoString(&buf, " WITH (connect = false");
+
+	/* Get slotname */
+	datum = SysCacheGetAttr(SUBSCRIPTIONOID, tup,
+							Anum_pg_subscription_subslotname,
+							&isnull);
+	if (!isnull)
+		appendStringInfo(&buf, ", slot_name = \'%s\'",
+						 NameStr(*DatumGetName(datum)));
+	else
+	{
+		appendStringInfoString(&buf, ", slot_name = none");
+		/* Setting slot_name to none must set create_slot to false */
+		appendStringInfoString(&buf, ", create_slot = false");
+	}
+
+	/* Get enabled option */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_subenabled);
+	/* Setting 'slot_name' to none must set 'enabled' to false as well */
+	if (!DatumGetBool(datum) || isnull)
+		appendStringInfoString(&buf, ", enabled = false");
+	else
+		appendStringInfoString(&buf, ", enabled = true");
+
+	/* Get binary option */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_subbinary);
+	appendStringInfo(&buf, ", binary = %s",
+					 DatumGetBool(datum) ? "true" : "false");
+
+	/* Get streaming option */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_substream);
+	if (DatumGetChar(datum) == LOGICALREP_STREAM_OFF)
+		appendStringInfoString(&buf, ", streaming = off");
+	else if (DatumGetChar(datum) == LOGICALREP_STREAM_ON)
+		appendStringInfoString(&buf, ", streaming = on");
+	else
+		appendStringInfoString(&buf, ", streaming = parallel");
+
+	/* Get sync commit option */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_subsynccommit);
+	appendStringInfo(&buf, ", synchronous_commit = %s",
+					 TextDatumGetCString(datum));
+
+	/* Get two-phase commit option */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_subtwophasestate);
+	if (DatumGetChar(datum) == LOGICALREP_TWOPHASE_STATE_DISABLED)
+		appendStringInfoString(&buf, ", two_phase = off");
+	else
+		appendStringInfoString(&buf, ", two_phase = on");
+
+	/* Disable on error? */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_subdisableonerr);
+	appendStringInfo(&buf, ", disable_on_error = %s",
+					 DatumGetBool(datum) ? "on" : "off");
+
+	/* Password required? */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_subpasswordrequired);
+	appendStringInfo(&buf, ", password_required = %s",
+					 DatumGetBool(datum) ? "on" : "off");
+
+	/* Run as owner? */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_subrunasowner);
+	appendStringInfo(&buf, ", run_as_owner = %s",
+					 DatumGetBool(datum) ? "on" : "off");
+
+	/* Get origin */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_suborigin);
+	appendStringInfo(&buf, ", origin = %s", TextDatumGetCString(datum));
+
+	/* Failover? */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_subfailover);
+	appendStringInfo(&buf, ", failover = %s",
+					 DatumGetBool(datum) ? "on" : "off");
+
+	/* Retain dead tuples? */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_subretaindeadtuples);
+	appendStringInfo(&buf, ", retain_dead_tuples = %s",
+					 DatumGetBool(datum) ? "on" : "off");
+
+	/* Max retention duration */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_submaxretention);
+	appendStringInfo(&buf, ", max_retention_duration = %lu",
+					 Int32GetDatum(datum));
+
+	/* Finally close parenthesis and add semicolon to the statement */
+	appendStringInfoString(&buf, ");");
+
+	ReleaseSysCache(tup);
+
+	return buf.data;
+}
+
+/*
+ * pg_get_subscription_ddl_name - Get CREATE SUBSCRIPTION statement for a
+ * subscription. This takes name as parameter for pg_get_subscription_ddl().
+ */
+Datum
+pg_get_subscription_ddl_name(PG_FUNCTION_ARGS)
+{
+	Name		subname = PG_GETARG_NAME(0);
+	Oid			suboid;
+	char	   *ddl_stmt;
+
+	/* Get the OID of the subscription from its name */
+	suboid = get_subscription_oid(NameStr(*subname), false);
+
+	/* Get the CREATE SUBSCRIPTION DDL statement from its OID */
+	ddl_stmt = build_subscription_ddl_string(suboid);
+
+	PG_RETURN_TEXT_P(string_to_text(ddl_stmt));
+}
+
+/*
+ * pg_get_subscription_ddl_oid - Get CREATE SUBSCRIPTION statement for a
+ * subscription. This takes oid as parameter for pg_get_subscription_ddl().
+ */
+Datum
+pg_get_subscription_ddl_oid(PG_FUNCTION_ARGS)
+{
+	Oid			suboid = PG_GETARG_OID(0);
+	char	   *ddl_stmt;
+
+	/* Get the CREATE SUBSCRIPTION DDL statement from its OID */
+	ddl_stmt = build_subscription_ddl_string(suboid);
+
+	PG_RETURN_TEXT_P(string_to_text(ddl_stmt));
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 34b7fddb0e7..4b9efd141af 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -3993,6 +3993,12 @@
 { oid => '1387', descr => 'constraint description',
   proname => 'pg_get_constraintdef', provolatile => 's', prorettype => 'text',
   proargtypes => 'oid', prosrc => 'pg_get_constraintdef' },
+{ oid => '8001', descr => 'get CREATE statement for subscription',
+  proname => 'pg_get_subscription_ddl', prorettype => 'text',
+  proargtypes => 'name', prosrc => 'pg_get_subscription_ddl_name' },
+{ oid => '8002', descr => 'get CREATE statement for subscription',
+  proname => 'pg_get_subscription_ddl', prorettype => 'text',
+  proargtypes => 'oid', prosrc => 'pg_get_subscription_ddl_oid' },
 { oid => '1716', descr => 'deparse an encoded expression',
   proname => 'pg_get_expr', provolatile => 's', prorettype => 'text',
   proargtypes => 'pg_node_tree oid', prosrc => 'pg_get_expr' },
diff --git a/src/include/catalog/pg_subscription.h b/src/include/catalog/pg_subscription.h
index 55cb9b1eefa..3082ab8dffc 100644
--- a/src/include/catalog/pg_subscription.h
+++ b/src/include/catalog/pg_subscription.h
@@ -22,6 +22,7 @@
 #include "catalog/pg_subscription_d.h"	/* IWYU pragma: export */
 #include "lib/stringinfo.h"
 #include "nodes/pg_list.h"
+#include "utils/array.h"
 
 /* ----------------
  *		pg_subscription definition. cpp turns this into
@@ -207,5 +208,6 @@ extern int	CountDBSubscriptions(Oid dbid);
 
 extern void GetPublicationsStr(List *publications, StringInfo dest,
 							   bool quote_literal);
+extern List *textarray_to_stringlist(ArrayType *textarray);
 
 #endif							/* PG_SUBSCRIPTION_H */
diff --git a/src/include/utils/ruleutils.h b/src/include/utils/ruleutils.h
index 7ba7d887914..f8f26284489 100644
--- a/src/include/utils/ruleutils.h
+++ b/src/include/utils/ruleutils.h
@@ -53,5 +53,6 @@ extern char *generate_opclass_name(Oid opclass);
 extern char *get_range_partbound_string(List *bound_datums);
 
 extern char *pg_get_statisticsobjdef_string(Oid statextid);
+extern char *build_subscription_ddl_string(const Oid suboid);
 
 #endif							/* RULEUTILS_H */
diff --git a/src/test/regress/expected/subscription.out b/src/test/regress/expected/subscription.out
index 327d1e7731f..1f2ad15c7ff 100644
--- a/src/test/regress/expected/subscription.out
+++ b/src/test/regress/expected/subscription.out
@@ -522,3 +522,98 @@ DROP ROLE regress_subscription_user;
 DROP ROLE regress_subscription_user2;
 DROP ROLE regress_subscription_user3;
 DROP ROLE regress_subscription_user_dummy;
+--
+-- Test pg_get_subscription_ddl() by creating subscriptions with various
+-- configurations and checking the DDL.
+--
+CREATE ROLE createsub_role LOGIN;
+CREATE ROLE readalldata_role LOGIN;
+-- see the pg_get_subscription_ddl output for a NULL and empty input
+SELECT pg_get_subscription_ddl('');
+ERROR:  subscription "" does not exist
+SELECT pg_get_subscription_ddl(NULL);
+ pg_get_subscription_ddl 
+-------------------------
+ 
+(1 row)
+
+-- Create subscription with minimal options
+CREATE SUBSCRIPTION testsub1 CONNECTION 'dbname=db_doesnotexist'
+  PUBLICATION testpub1 WITH (connect=false);
+WARNING:  subscription was created, but is not connected
+HINT:  To initiate replication, you must manually create the replication slot, enable the subscription, and alter the subscription to refresh publications.
+-- Check that the subscription ddl is correctly created
+SELECT pg_get_subscription_ddl('testsub1');
+                                                                                                                                                                                      pg_get_subscription_ddl                                                                                                                                                                                      
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE SUBSCRIPTION testsub1 CONNECTION 'dbname=db_doesnotexist' PUBLICATION "testpub1" WITH (connect = false, slot_name = 'testsub1', enabled = false, binary = false, streaming = parallel, synchronous_commit = off, two_phase = off, disable_on_error = off, password_required = on, run_as_owner = off, origin = any, failover = off, retain_dead_tuples = off, max_retention_duration = 0);
+(1 row)
+
+-- Create subscription with more options
+CREATE SUBSCRIPTION "TestSubddL2" CONNECTION 'host=unknown user=dvd password=pass123'
+  PUBLICATION "testpub2", "TestPub3" WITH (connect=false, slot_name='slot1',
+  enabled=off);
+WARNING:  subscription was created, but is not connected
+HINT:  To initiate replication, you must manually create the replication slot, enable the subscription, and alter the subscription to refresh publications.
+SELECT pg_get_subscription_ddl('TestSubddL2');
+                                                                                                                                                                                                     pg_get_subscription_ddl                                                                                                                                                                                                     
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE SUBSCRIPTION "TestSubddL2" CONNECTION 'host=unknown user=dvd password=pass123' PUBLICATION "testpub2", "TestPub3" WITH (connect = false, slot_name = 'slot1', enabled = false, binary = false, streaming = parallel, synchronous_commit = off, two_phase = off, disable_on_error = off, password_required = on, run_as_owner = off, origin = any, failover = off, retain_dead_tuples = off, max_retention_duration = 0);
+(1 row)
+
+-- Create subscription with all options
+CREATE SUBSCRIPTION testsub3 CONNECTION 'host=unknown user=dvd password=pass12'
+  PUBLICATION testpub4 WITH (connect=false, slot_name=none, enabled=false,
+  create_slot=false, copy_data=false, binary=true, streaming=off,
+  synchronous_commit=local, two_phase=true, disable_on_error=true,
+  password_required=false, run_as_owner=true, origin=none, failover=true,
+  retain_dead_tuples=false, max_retention_duration=100);
+NOTICE:  max_retention_duration is ineffective when retain_dead_tuples is disabled
+WARNING:  subscription was created, but is not connected
+HINT:  To initiate replication, you must manually create the replication slot, enable the subscription, and alter the subscription to refresh publications.
+SELECT pg_get_subscription_ddl('testsub3');
+                                                                                                                                                                                                   pg_get_subscription_ddl                                                                                                                                                                                                   
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE SUBSCRIPTION testsub3 CONNECTION 'host=unknown user=dvd password=pass12' PUBLICATION "testpub4" WITH (connect = false, slot_name = none, create_slot = false, enabled = false, binary = true, streaming = off, synchronous_commit = local, two_phase = on, disable_on_error = on, password_required = off, run_as_owner = on, origin = none, failover = on, retain_dead_tuples = off, max_retention_duration = 100);
+(1 row)
+
+-- Non-superusers and which don't have pg_create_subscription and/or
+-- pg_read_all_data permission can't get ddl
+SET SESSION AUTHORIZATION 'createsub_role';
+SELECT pg_get_subscription_ddl('TestSubddL2');
+ERROR:  permission denied to get the create subscription ddl
+DETAIL:  Only roles with privileges of the "pg_create_subscription" and/or "pg_read_all_data" role may get ddl.
+RESET SESSION AUTHORIZATION;
+SET SESSION AUTHORIZATION 'readalldata_role';
+SELECT pg_get_subscription_ddl('TestSubddL2');
+ERROR:  permission denied to get the create subscription ddl
+DETAIL:  Only roles with privileges of the "pg_create_subscription" and/or "pg_read_all_data" role may get ddl.
+RESET SESSION AUTHORIZATION;
+-- Administrators can change who can access this function
+GRANT pg_create_subscription TO createsub_role;
+GRANT pg_read_all_data TO readalldata_role;
+SET SESSION AUTHORIZATION 'createsub_role';
+SELECT pg_get_subscription_ddl('TestSubddL2');
+                                                                                                                                                                                                     pg_get_subscription_ddl                                                                                                                                                                                                     
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE SUBSCRIPTION "TestSubddL2" CONNECTION 'host=unknown user=dvd password=pass123' PUBLICATION "testpub2", "TestPub3" WITH (connect = false, slot_name = 'slot1', enabled = false, binary = false, streaming = parallel, synchronous_commit = off, two_phase = off, disable_on_error = off, password_required = on, run_as_owner = off, origin = any, failover = off, retain_dead_tuples = off, max_retention_duration = 0);
+(1 row)
+
+RESET SESSION AUTHORIZATION;
+SET SESSION AUTHORIZATION 'readalldata_role';
+SELECT pg_get_subscription_ddl('TestSubddL2');
+                                                                                                                                                                                                     pg_get_subscription_ddl                                                                                                                                                                                                     
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE SUBSCRIPTION "TestSubddL2" CONNECTION 'host=unknown user=dvd password=pass123' PUBLICATION "testpub2", "TestPub3" WITH (connect = false, slot_name = 'slot1', enabled = false, binary = false, streaming = parallel, synchronous_commit = off, two_phase = off, disable_on_error = off, password_required = on, run_as_owner = off, origin = any, failover = off, retain_dead_tuples = off, max_retention_duration = 0);
+(1 row)
+
+RESET SESSION AUTHORIZATION;
+REVOKE pg_create_subscription FROM createsub_role;
+REVOKE pg_read_all_data FROM readalldata_role;
+ALTER SUBSCRIPTION testsub1 SET (slot_name=NONE);
+DROP SUBSCRIPTION testsub1;
+ALTER SUBSCRIPTION "TestSubddL2" SET (slot_name=NONE);
+DROP SUBSCRIPTION "TestSubddL2";
+DROP SUBSCRIPTION testsub3;
+DROP ROLE createsub_role;
+DROP ROLE readalldata_role;
diff --git a/src/test/regress/sql/subscription.sql b/src/test/regress/sql/subscription.sql
index ef0c298d2df..ef3c6c9ca16 100644
--- a/src/test/regress/sql/subscription.sql
+++ b/src/test/regress/sql/subscription.sql
@@ -370,3 +370,63 @@ DROP ROLE regress_subscription_user;
 DROP ROLE regress_subscription_user2;
 DROP ROLE regress_subscription_user3;
 DROP ROLE regress_subscription_user_dummy;
+
+--
+-- Test pg_get_subscription_ddl() by creating subscriptions with various
+-- configurations and checking the DDL.
+--
+CREATE ROLE createsub_role LOGIN;
+CREATE ROLE readalldata_role LOGIN;
+
+-- see the pg_get_subscription_ddl output for a NULL and empty input
+SELECT pg_get_subscription_ddl('');
+SELECT pg_get_subscription_ddl(NULL);
+
+-- Create subscription with minimal options
+CREATE SUBSCRIPTION testsub1 CONNECTION 'dbname=db_doesnotexist'
+  PUBLICATION testpub1 WITH (connect=false);
+-- Check that the subscription ddl is correctly created
+SELECT pg_get_subscription_ddl('testsub1');
+
+-- Create subscription with more options
+CREATE SUBSCRIPTION "TestSubddL2" CONNECTION 'host=unknown user=dvd password=pass123'
+  PUBLICATION "testpub2", "TestPub3" WITH (connect=false, slot_name='slot1',
+  enabled=off);
+SELECT pg_get_subscription_ddl('TestSubddL2');
+
+-- Create subscription with all options
+CREATE SUBSCRIPTION testsub3 CONNECTION 'host=unknown user=dvd password=pass12'
+  PUBLICATION testpub4 WITH (connect=false, slot_name=none, enabled=false,
+  create_slot=false, copy_data=false, binary=true, streaming=off,
+  synchronous_commit=local, two_phase=true, disable_on_error=true,
+  password_required=false, run_as_owner=true, origin=none, failover=true,
+  retain_dead_tuples=false, max_retention_duration=100);
+SELECT pg_get_subscription_ddl('testsub3');
+
+-- Non-superusers and which don't have pg_create_subscription and/or
+-- pg_read_all_data permission can't get ddl
+SET SESSION AUTHORIZATION 'createsub_role';
+SELECT pg_get_subscription_ddl('TestSubddL2');
+RESET SESSION AUTHORIZATION;
+SET SESSION AUTHORIZATION 'readalldata_role';
+SELECT pg_get_subscription_ddl('TestSubddL2');
+RESET SESSION AUTHORIZATION;
+-- Administrators can change who can access this function
+GRANT pg_create_subscription TO createsub_role;
+GRANT pg_read_all_data TO readalldata_role;
+SET SESSION AUTHORIZATION 'createsub_role';
+SELECT pg_get_subscription_ddl('TestSubddL2');
+RESET SESSION AUTHORIZATION;
+SET SESSION AUTHORIZATION 'readalldata_role';
+SELECT pg_get_subscription_ddl('TestSubddL2');
+
+RESET SESSION AUTHORIZATION;
+REVOKE pg_create_subscription FROM createsub_role;
+REVOKE pg_read_all_data FROM readalldata_role;
+ALTER SUBSCRIPTION testsub1 SET (slot_name=NONE);
+DROP SUBSCRIPTION testsub1;
+ALTER SUBSCRIPTION "TestSubddL2" SET (slot_name=NONE);
+DROP SUBSCRIPTION "TestSubddL2";
+DROP SUBSCRIPTION testsub3;
+DROP ROLE createsub_role;
+DROP ROLE readalldata_role;
-- 
2.43.0

From 170f3af9a4e29efa242692551435065b057b9ba8 Mon Sep 17 00:00:00 2001
From: Vaibhav Dalvi <vaibhav.dalvi@enterprisedb.com>
Date: Fri, 7 Nov 2025 10:14:54 +0000
Subject: [PATCH v3 1/1] Add pg_get_subscription_ddl() function

This new SQL-callable function returns the `CREATE SUBSCRIPTION`
statement for a given subscription name or oid.

Like `pg_dump`, the returned DDL explicitly sets `connect = false`.
This is because the original `CONNECT` option value is not cataloged,
and using `connect = false` ensures the DDL can be successfully executed
even if the remote publisher is unreachable.

This function is restricted to users that have the "pg_read_all_data" and/or
"pg_create_subscription" privilege.  This is a security measure because
subscription connection strings often contain sensitive information, such as
passwords.

Author: Vaibhav Dalvi <vaibhav.dalvi@enterprisedb.com>
Reviewers: Akshay Joshi, Nishant Sharma, Ian Barwick & Alvaro Herrera
Discussion: https://www.postgresql.org/message-id/CA%2BvB%3DAGG0NoxWW%3D-947RBmba8Pzhj8j7op0Xkv8nLDzVMc2%3D7w%40mail.gmail.com
---
 doc/src/sgml/func/func-info.sgml           |  51 +++++
 src/backend/utils/adt/ruleutils.c          | 246 +++++++++++++++++++++
 src/include/catalog/pg_proc.dat            |   6 +
 src/include/utils/ruleutils.h              |   1 +
 src/test/regress/expected/subscription.out |  95 ++++++++
 src/test/regress/sql/subscription.sql      |  60 +++++
 6 files changed, 459 insertions(+)

diff --git a/doc/src/sgml/func/func-info.sgml b/doc/src/sgml/func/func-info.sgml
index d4508114a48..21d33ee1802 100644
--- a/doc/src/sgml/func/func-info.sgml
+++ b/doc/src/sgml/func/func-info.sgml
@@ -3797,4 +3797,55 @@ acl      | {postgres=arwdDxtm/postgres,foo=r/postgres}
 
   </sect2>
 
+  <sect2 id="functions-get-object-ddl">
+   <title>Get Object DDL Functions</title>
+
+   <para>
+    The functions shown in <xref linkend="functions-get-object-ddl-table"/>
+    print the DDL statements for various database objects.
+    (This is a decompiled reconstruction, not the original text
+    of the command.)
+   </para>
+
+   <table id="functions-get-object-ddl-table">
+    <title>Get Object DDL Functions</title>
+    <tgroup cols="1">
+     <thead>
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        Function
+       </para>
+       <para>
+        Description
+       </para></entry>
+      </row>
+     </thead>
+
+     <tbody>
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>pg_get_subscription_ddl</primary>
+        </indexterm>
+        <function>pg_get_subscription_ddl</function> ( <parameter>subscription</parameter> <type>text</type> )
+        <returnvalue>text</returnvalue>
+       </para>
+       <para>
+        Reconstructs the creating command for a subscription.
+        The result is a complete <command>CREATE SUBSCRIPTION</command>
+        statement. The <literal>connect</literal> option set to
+        <literal>false</literal>.
+       </para>
+       <para>
+        This function is restricted to users that have the
+        <literal>pg_read_all_data</literal> and/or
+        <literal>pg_create_subscription</literal> privilege.
+       </para></entry>
+      </row>
+     </tbody>
+    </tgroup>
+   </table>
+
+  </sect2>
+
   </sect1>
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 556ab057e5a..f76f21f80dc 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -35,6 +35,7 @@
 #include "catalog/pg_partitioned_table.h"
 #include "catalog/pg_proc.h"
 #include "catalog/pg_statistic_ext.h"
+#include "catalog/pg_subscription.h"
 #include "catalog/pg_trigger.h"
 #include "catalog/pg_type.h"
 #include "commands/defrem.h"
@@ -57,6 +58,7 @@
 #include "rewrite/rewriteHandler.h"
 #include "rewrite/rewriteManip.h"
 #include "rewrite/rewriteSupport.h"
+#include "utils/acl.h"
 #include "utils/array.h"
 #include "utils/builtins.h"
 #include "utils/fmgroids.h"
@@ -546,6 +548,7 @@ static void get_json_table_nested_columns(TableFunc *tf, JsonTablePlan *plan,
 										  deparse_context *context,
 										  bool showimplicit,
 										  bool needcomma);
+static List *text_array_to_string_list(ArrayType *text_array);
 
 #define only_marker(rte)  ((rte)->inh ? "" : "ONLY ")
 
@@ -13743,3 +13746,246 @@ get_range_partbound_string(List *bound_datums)
 
 	return buf.data;
 }
+
+/*
+ * build_subscription_ddl_string - Build CREATE SUBSCRIPTION statement for
+ * a subscription from its OID. This is internal version which helps
+ * pg_get_subscription_ddl_name() and pg_get_subscription_ddl_oid().
+ */
+char *
+build_subscription_ddl_string(const Oid suboid)
+{
+	Form_pg_subscription subForm;
+	StringInfo	pubnames;
+	StringInfoData buf;
+	HeapTuple	tup;
+	char	   *conninfo;
+	List	   *publist;
+	Datum		datum;
+	bool		isnull;
+
+	/*
+	 * To prevent unprivileged users from initiating unauthorized network
+	 * connections, dumping subscription creation is restricted.  A user must
+	 * be specifically authorized (via the appropriate role privilege) to
+	 * create subscriptions and/or to read all data.
+	 */
+	if (!(has_privs_of_role(GetUserId(), ROLE_PG_CREATE_SUBSCRIPTION) ||
+		has_privs_of_role(GetUserId(), ROLE_PG_READ_ALL_DATA)))
+		ereport(ERROR,
+				(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+				 errmsg("permission denied to get the create subscription ddl"),
+				 errdetail("Only roles with privileges of the \"%s\" and/or \"%s\" role may get ddl.",
+						   "pg_create_subscription", "pg_read_all_data")));
+
+	/* Look up the subscription in pg_subscription */
+	tup = SearchSysCache1(SUBSCRIPTIONOID, ObjectIdGetDatum(suboid));
+	if (!HeapTupleIsValid(tup))
+		ereport(ERROR,
+				(errcode(ERRCODE_UNDEFINED_OBJECT),
+				 errmsg("subscription with oid %d does not exist", suboid)));
+
+	/* Get subscription's details from its tuple */
+	subForm = (Form_pg_subscription) GETSTRUCT(tup);
+
+	initStringInfo(&buf);
+
+	/* Build the CREATE SUBSCRIPTION statement */
+	appendStringInfo(&buf, "CREATE SUBSCRIPTION %s ",
+					 quote_identifier(NameStr(subForm->subname)));
+
+	/* Get conninfo */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_subconninfo);
+	conninfo = TextDatumGetCString(datum);
+
+	/* Append connection info to the CREATE SUBSCRIPTION statement */
+	appendStringInfo(&buf, "CONNECTION \'%s\'", conninfo);
+
+	/* Build list of quoted publications and append them to query */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_subpublications);
+	publist = text_array_to_string_list(DatumGetArrayTypeP(datum));
+	pubnames = makeStringInfo();
+	GetPublicationsStr(publist, pubnames, false);
+	appendStringInfo(&buf, " PUBLICATION %s", pubnames->data);
+
+	/*
+	 * Add options using WITH clause.  The 'connect' option value given at the
+	 * time of subscription creation is not available in the catalog.  When
+	 * creating a subscription, the remote host is not reachable or in an
+	 * unclear state, in that case, the subscription can be created using
+	 * 'connect = false' option.  This is what pg_dump uses.
+	 *
+	 * The status or value of the options 'create_slot' and 'copy_data' not
+	 * available in the catalog table.  We can use default values i.e. TRUE
+	 * for both.  This is what pg_dump uses.
+	 */
+	appendStringInfoString(&buf, " WITH (connect = false");
+
+	/* Get slotname */
+	datum = SysCacheGetAttr(SUBSCRIPTIONOID, tup,
+							Anum_pg_subscription_subslotname,
+							&isnull);
+	if (!isnull)
+		appendStringInfo(&buf, ", slot_name = \'%s\'",
+						 NameStr(*DatumGetName(datum)));
+	else
+	{
+		appendStringInfoString(&buf, ", slot_name = none");
+		/* Setting slot_name to none must set create_slot to false */
+		appendStringInfoString(&buf, ", create_slot = false");
+	}
+
+	/* Get enabled option */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_subenabled);
+	/* Setting 'slot_name' to none must set 'enabled' to false as well */
+	if (!DatumGetBool(datum) || isnull)
+		appendStringInfoString(&buf, ", enabled = false");
+	else
+		appendStringInfoString(&buf, ", enabled = true");
+
+	/* Get binary option */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_subbinary);
+	appendStringInfo(&buf, ", binary = %s",
+					 DatumGetBool(datum) ? "true" : "false");
+
+	/* Get streaming option */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_substream);
+	if (DatumGetChar(datum) == LOGICALREP_STREAM_OFF)
+		appendStringInfoString(&buf, ", streaming = off");
+	else if (DatumGetChar(datum) == LOGICALREP_STREAM_ON)
+		appendStringInfoString(&buf, ", streaming = on");
+	else
+		appendStringInfoString(&buf, ", streaming = parallel");
+
+	/* Get sync commit option */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_subsynccommit);
+	appendStringInfo(&buf, ", synchronous_commit = %s",
+					 TextDatumGetCString(datum));
+
+	/* Get two-phase commit option */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_subtwophasestate);
+	if (DatumGetChar(datum) == LOGICALREP_TWOPHASE_STATE_DISABLED)
+		appendStringInfoString(&buf, ", two_phase = off");
+	else
+		appendStringInfoString(&buf, ", two_phase = on");
+
+	/* Disable on error? */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_subdisableonerr);
+	appendStringInfo(&buf, ", disable_on_error = %s",
+					 DatumGetBool(datum) ? "on" : "off");
+
+	/* Password required? */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_subpasswordrequired);
+	appendStringInfo(&buf, ", password_required = %s",
+					 DatumGetBool(datum) ? "on" : "off");
+
+	/* Run as owner? */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_subrunasowner);
+	appendStringInfo(&buf, ", run_as_owner = %s",
+					 DatumGetBool(datum) ? "on" : "off");
+
+	/* Get origin */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_suborigin);
+	appendStringInfo(&buf, ", origin = %s", TextDatumGetCString(datum));
+
+	/* Failover? */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_subfailover);
+	appendStringInfo(&buf, ", failover = %s",
+					 DatumGetBool(datum) ? "on" : "off");
+
+	/* Retain dead tuples? */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_subretaindeadtuples);
+	appendStringInfo(&buf, ", retain_dead_tuples = %s",
+					 DatumGetBool(datum) ? "on" : "off");
+
+	/* Max retention duration */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_submaxretention);
+	appendStringInfo(&buf, ", max_retention_duration = %d",
+					 DatumGetInt32(datum));
+
+	/* Finally close parenthesis and add semicolon to the statement */
+	appendStringInfoString(&buf, ");");
+
+	ReleaseSysCache(tup);
+
+	return buf.data;
+}
+
+/*
+ * pg_get_subscription_ddl_name
+ *		Get CREATE SUBSCRIPTION statement for a subscription.
+ *
+ * This takes name as parameter for pg_get_subscription_ddl().
+ */
+Datum
+pg_get_subscription_ddl_name(PG_FUNCTION_ARGS)
+{
+	Name		subname = PG_GETARG_NAME(0);
+	Oid			suboid;
+	char	   *ddl_stmt;
+
+	/* Get the OID of the subscription from its name */
+	suboid = get_subscription_oid(NameStr(*subname), false);
+
+	/* Get the CREATE SUBSCRIPTION DDL statement from its OID */
+	ddl_stmt = build_subscription_ddl_string(suboid);
+
+	PG_RETURN_TEXT_P(string_to_text(ddl_stmt));
+}
+
+/*
+ * pg_get_subscription_ddl_oid
+ *		Get CREATE SUBSCRIPTION statement for a subscription.
+ *
+ * This takes oid as parameter for pg_get_subscription_ddl().
+ */
+Datum
+pg_get_subscription_ddl_oid(PG_FUNCTION_ARGS)
+{
+	Oid			suboid = PG_GETARG_OID(0);
+	char	   *ddl_stmt;
+
+	/* Get the CREATE SUBSCRIPTION DDL statement from its OID */
+	ddl_stmt = build_subscription_ddl_string(suboid);
+
+	PG_RETURN_TEXT_P(string_to_text(ddl_stmt));
+}
+
+/*
+ * text_array_to_string_list
+ *		Convert text array to list of strings.
+ *
+ * The resulting list of strings is pallocated here.
+ */
+static List *
+text_array_to_string_list(ArrayType *text_array)
+{
+	List	   *result = NIL;
+	Datum	   *elems;
+	int			nelems,
+				i;
+
+	deconstruct_array_builtin(text_array, TEXTOID, &elems, NULL, &nelems);
+
+	if (nelems == 0)
+		return NIL;
+
+	for (i = 0; i < nelems; i++)
+		result = lappend(result, makeString(TextDatumGetCString(elems[i])));
+
+	return result;
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 34b7fddb0e7..4b9efd141af 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -3993,6 +3993,12 @@
 { oid => '1387', descr => 'constraint description',
   proname => 'pg_get_constraintdef', provolatile => 's', prorettype => 'text',
   proargtypes => 'oid', prosrc => 'pg_get_constraintdef' },
+{ oid => '8001', descr => 'get CREATE statement for subscription',
+  proname => 'pg_get_subscription_ddl', prorettype => 'text',
+  proargtypes => 'name', prosrc => 'pg_get_subscription_ddl_name' },
+{ oid => '8002', descr => 'get CREATE statement for subscription',
+  proname => 'pg_get_subscription_ddl', prorettype => 'text',
+  proargtypes => 'oid', prosrc => 'pg_get_subscription_ddl_oid' },
 { oid => '1716', descr => 'deparse an encoded expression',
   proname => 'pg_get_expr', provolatile => 's', prorettype => 'text',
   proargtypes => 'pg_node_tree oid', prosrc => 'pg_get_expr' },
diff --git a/src/include/utils/ruleutils.h b/src/include/utils/ruleutils.h
index 7ba7d887914..f8f26284489 100644
--- a/src/include/utils/ruleutils.h
+++ b/src/include/utils/ruleutils.h
@@ -53,5 +53,6 @@ extern char *generate_opclass_name(Oid opclass);
 extern char *get_range_partbound_string(List *bound_datums);
 
 extern char *pg_get_statisticsobjdef_string(Oid statextid);
+extern char *build_subscription_ddl_string(const Oid suboid);
 
 #endif							/* RULEUTILS_H */
diff --git a/src/test/regress/expected/subscription.out b/src/test/regress/expected/subscription.out
index 327d1e7731f..1f2ad15c7ff 100644
--- a/src/test/regress/expected/subscription.out
+++ b/src/test/regress/expected/subscription.out
@@ -522,3 +522,98 @@ DROP ROLE regress_subscription_user;
 DROP ROLE regress_subscription_user2;
 DROP ROLE regress_subscription_user3;
 DROP ROLE regress_subscription_user_dummy;
+--
+-- Test pg_get_subscription_ddl() by creating subscriptions with various
+-- configurations and checking the DDL.
+--
+CREATE ROLE createsub_role LOGIN;
+CREATE ROLE readalldata_role LOGIN;
+-- see the pg_get_subscription_ddl output for a NULL and empty input
+SELECT pg_get_subscription_ddl('');
+ERROR:  subscription "" does not exist
+SELECT pg_get_subscription_ddl(NULL);
+ pg_get_subscription_ddl 
+-------------------------
+ 
+(1 row)
+
+-- Create subscription with minimal options
+CREATE SUBSCRIPTION testsub1 CONNECTION 'dbname=db_doesnotexist'
+  PUBLICATION testpub1 WITH (connect=false);
+WARNING:  subscription was created, but is not connected
+HINT:  To initiate replication, you must manually create the replication slot, enable the subscription, and alter the subscription to refresh publications.
+-- Check that the subscription ddl is correctly created
+SELECT pg_get_subscription_ddl('testsub1');
+                                                                                                                                                                                      pg_get_subscription_ddl                                                                                                                                                                                      
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE SUBSCRIPTION testsub1 CONNECTION 'dbname=db_doesnotexist' PUBLICATION "testpub1" WITH (connect = false, slot_name = 'testsub1', enabled = false, binary = false, streaming = parallel, synchronous_commit = off, two_phase = off, disable_on_error = off, password_required = on, run_as_owner = off, origin = any, failover = off, retain_dead_tuples = off, max_retention_duration = 0);
+(1 row)
+
+-- Create subscription with more options
+CREATE SUBSCRIPTION "TestSubddL2" CONNECTION 'host=unknown user=dvd password=pass123'
+  PUBLICATION "testpub2", "TestPub3" WITH (connect=false, slot_name='slot1',
+  enabled=off);
+WARNING:  subscription was created, but is not connected
+HINT:  To initiate replication, you must manually create the replication slot, enable the subscription, and alter the subscription to refresh publications.
+SELECT pg_get_subscription_ddl('TestSubddL2');
+                                                                                                                                                                                                     pg_get_subscription_ddl                                                                                                                                                                                                     
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE SUBSCRIPTION "TestSubddL2" CONNECTION 'host=unknown user=dvd password=pass123' PUBLICATION "testpub2", "TestPub3" WITH (connect = false, slot_name = 'slot1', enabled = false, binary = false, streaming = parallel, synchronous_commit = off, two_phase = off, disable_on_error = off, password_required = on, run_as_owner = off, origin = any, failover = off, retain_dead_tuples = off, max_retention_duration = 0);
+(1 row)
+
+-- Create subscription with all options
+CREATE SUBSCRIPTION testsub3 CONNECTION 'host=unknown user=dvd password=pass12'
+  PUBLICATION testpub4 WITH (connect=false, slot_name=none, enabled=false,
+  create_slot=false, copy_data=false, binary=true, streaming=off,
+  synchronous_commit=local, two_phase=true, disable_on_error=true,
+  password_required=false, run_as_owner=true, origin=none, failover=true,
+  retain_dead_tuples=false, max_retention_duration=100);
+NOTICE:  max_retention_duration is ineffective when retain_dead_tuples is disabled
+WARNING:  subscription was created, but is not connected
+HINT:  To initiate replication, you must manually create the replication slot, enable the subscription, and alter the subscription to refresh publications.
+SELECT pg_get_subscription_ddl('testsub3');
+                                                                                                                                                                                                   pg_get_subscription_ddl                                                                                                                                                                                                   
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE SUBSCRIPTION testsub3 CONNECTION 'host=unknown user=dvd password=pass12' PUBLICATION "testpub4" WITH (connect = false, slot_name = none, create_slot = false, enabled = false, binary = true, streaming = off, synchronous_commit = local, two_phase = on, disable_on_error = on, password_required = off, run_as_owner = on, origin = none, failover = on, retain_dead_tuples = off, max_retention_duration = 100);
+(1 row)
+
+-- Non-superusers and which don't have pg_create_subscription and/or
+-- pg_read_all_data permission can't get ddl
+SET SESSION AUTHORIZATION 'createsub_role';
+SELECT pg_get_subscription_ddl('TestSubddL2');
+ERROR:  permission denied to get the create subscription ddl
+DETAIL:  Only roles with privileges of the "pg_create_subscription" and/or "pg_read_all_data" role may get ddl.
+RESET SESSION AUTHORIZATION;
+SET SESSION AUTHORIZATION 'readalldata_role';
+SELECT pg_get_subscription_ddl('TestSubddL2');
+ERROR:  permission denied to get the create subscription ddl
+DETAIL:  Only roles with privileges of the "pg_create_subscription" and/or "pg_read_all_data" role may get ddl.
+RESET SESSION AUTHORIZATION;
+-- Administrators can change who can access this function
+GRANT pg_create_subscription TO createsub_role;
+GRANT pg_read_all_data TO readalldata_role;
+SET SESSION AUTHORIZATION 'createsub_role';
+SELECT pg_get_subscription_ddl('TestSubddL2');
+                                                                                                                                                                                                     pg_get_subscription_ddl                                                                                                                                                                                                     
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE SUBSCRIPTION "TestSubddL2" CONNECTION 'host=unknown user=dvd password=pass123' PUBLICATION "testpub2", "TestPub3" WITH (connect = false, slot_name = 'slot1', enabled = false, binary = false, streaming = parallel, synchronous_commit = off, two_phase = off, disable_on_error = off, password_required = on, run_as_owner = off, origin = any, failover = off, retain_dead_tuples = off, max_retention_duration = 0);
+(1 row)
+
+RESET SESSION AUTHORIZATION;
+SET SESSION AUTHORIZATION 'readalldata_role';
+SELECT pg_get_subscription_ddl('TestSubddL2');
+                                                                                                                                                                                                     pg_get_subscription_ddl                                                                                                                                                                                                     
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE SUBSCRIPTION "TestSubddL2" CONNECTION 'host=unknown user=dvd password=pass123' PUBLICATION "testpub2", "TestPub3" WITH (connect = false, slot_name = 'slot1', enabled = false, binary = false, streaming = parallel, synchronous_commit = off, two_phase = off, disable_on_error = off, password_required = on, run_as_owner = off, origin = any, failover = off, retain_dead_tuples = off, max_retention_duration = 0);
+(1 row)
+
+RESET SESSION AUTHORIZATION;
+REVOKE pg_create_subscription FROM createsub_role;
+REVOKE pg_read_all_data FROM readalldata_role;
+ALTER SUBSCRIPTION testsub1 SET (slot_name=NONE);
+DROP SUBSCRIPTION testsub1;
+ALTER SUBSCRIPTION "TestSubddL2" SET (slot_name=NONE);
+DROP SUBSCRIPTION "TestSubddL2";
+DROP SUBSCRIPTION testsub3;
+DROP ROLE createsub_role;
+DROP ROLE readalldata_role;
diff --git a/src/test/regress/sql/subscription.sql b/src/test/regress/sql/subscription.sql
index ef0c298d2df..ef3c6c9ca16 100644
--- a/src/test/regress/sql/subscription.sql
+++ b/src/test/regress/sql/subscription.sql
@@ -370,3 +370,63 @@ DROP ROLE regress_subscription_user;
 DROP ROLE regress_subscription_user2;
 DROP ROLE regress_subscription_user3;
 DROP ROLE regress_subscription_user_dummy;
+
+--
+-- Test pg_get_subscription_ddl() by creating subscriptions with various
+-- configurations and checking the DDL.
+--
+CREATE ROLE createsub_role LOGIN;
+CREATE ROLE readalldata_role LOGIN;
+
+-- see the pg_get_subscription_ddl output for a NULL and empty input
+SELECT pg_get_subscription_ddl('');
+SELECT pg_get_subscription_ddl(NULL);
+
+-- Create subscription with minimal options
+CREATE SUBSCRIPTION testsub1 CONNECTION 'dbname=db_doesnotexist'
+  PUBLICATION testpub1 WITH (connect=false);
+-- Check that the subscription ddl is correctly created
+SELECT pg_get_subscription_ddl('testsub1');
+
+-- Create subscription with more options
+CREATE SUBSCRIPTION "TestSubddL2" CONNECTION 'host=unknown user=dvd password=pass123'
+  PUBLICATION "testpub2", "TestPub3" WITH (connect=false, slot_name='slot1',
+  enabled=off);
+SELECT pg_get_subscription_ddl('TestSubddL2');
+
+-- Create subscription with all options
+CREATE SUBSCRIPTION testsub3 CONNECTION 'host=unknown user=dvd password=pass12'
+  PUBLICATION testpub4 WITH (connect=false, slot_name=none, enabled=false,
+  create_slot=false, copy_data=false, binary=true, streaming=off,
+  synchronous_commit=local, two_phase=true, disable_on_error=true,
+  password_required=false, run_as_owner=true, origin=none, failover=true,
+  retain_dead_tuples=false, max_retention_duration=100);
+SELECT pg_get_subscription_ddl('testsub3');
+
+-- Non-superusers and which don't have pg_create_subscription and/or
+-- pg_read_all_data permission can't get ddl
+SET SESSION AUTHORIZATION 'createsub_role';
+SELECT pg_get_subscription_ddl('TestSubddL2');
+RESET SESSION AUTHORIZATION;
+SET SESSION AUTHORIZATION 'readalldata_role';
+SELECT pg_get_subscription_ddl('TestSubddL2');
+RESET SESSION AUTHORIZATION;
+-- Administrators can change who can access this function
+GRANT pg_create_subscription TO createsub_role;
+GRANT pg_read_all_data TO readalldata_role;
+SET SESSION AUTHORIZATION 'createsub_role';
+SELECT pg_get_subscription_ddl('TestSubddL2');
+RESET SESSION AUTHORIZATION;
+SET SESSION AUTHORIZATION 'readalldata_role';
+SELECT pg_get_subscription_ddl('TestSubddL2');
+
+RESET SESSION AUTHORIZATION;
+REVOKE pg_create_subscription FROM createsub_role;
+REVOKE pg_read_all_data FROM readalldata_role;
+ALTER SUBSCRIPTION testsub1 SET (slot_name=NONE);
+DROP SUBSCRIPTION testsub1;
+ALTER SUBSCRIPTION "TestSubddL2" SET (slot_name=NONE);
+DROP SUBSCRIPTION "TestSubddL2";
+DROP SUBSCRIPTION testsub3;
+DROP ROLE createsub_role;
+DROP ROLE readalldata_role;
-- 
2.43.0

From af7b37d2eff942e4a3a9563a9340fd3d7853e5ba Mon Sep 17 00:00:00 2001
From: Vaibhav Dalvi <vaibhav.dalvi@enterprisedb.com>
Date: Tue, 11 Nov 2025 04:58:59 +0000
Subject: [PATCH v4 1/2] Use list of C strings for publications

Currently, the structure Subscription has member
publications and it is list of String node rather
than a list of C strings. Subscription is not a
not a node, so this seems wasteful and pointless.

This patch tries to get rid of the String node
around Subscription->publications.

Vaibhav Dalvi
---
 src/backend/catalog/pg_subscription.c         | 15 ++++++++-----
 src/backend/commands/subscriptioncmds.c       | 21 +++++++++++++++----
 .../libpqwalreceiver/libpqwalreceiver.c       |  4 ++--
 3 files changed, 29 insertions(+), 11 deletions(-)

diff --git a/src/backend/catalog/pg_subscription.c b/src/backend/catalog/pg_subscription.c
index 1945627ed88..09e9dc98167 100644
--- a/src/backend/catalog/pg_subscription.c
+++ b/src/backend/catalog/pg_subscription.c
@@ -32,7 +32,7 @@
 #include "utils/rel.h"
 #include "utils/syscache.h"
 
-static List *textarray_to_stringlist(ArrayType *textarray);
+static List *textarray_to_cstringlist(ArrayType *textarray);
 
 /*
  * Add a comma-separated list of publication names to the 'dest' string.
@@ -47,7 +47,12 @@ GetPublicationsStr(List *publications, StringInfo dest, bool quote_literal)
 
 	foreach(lc, publications)
 	{
-		char	   *pubname = strVal(lfirst(lc));
+		char	   *pubname;
+
+		if (IsA((Node *) lfirst(lc), String))
+			pubname = strVal(lfirst(lc));
+		else
+			pubname = (char *) lfirst(lc);
 
 		if (first)
 			first = false;
@@ -133,7 +138,7 @@ GetSubscription(Oid subid, bool missing_ok)
 	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID,
 								   tup,
 								   Anum_pg_subscription_subpublications);
-	sub->publications = textarray_to_stringlist(DatumGetArrayTypeP(datum));
+	sub->publications = textarray_to_cstringlist(DatumGetArrayTypeP(datum));
 
 	/* Get origin */
 	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID,
@@ -241,7 +246,7 @@ DisableSubscription(Oid subid)
  * Note: the resulting list of strings is pallocated here.
  */
 static List *
-textarray_to_stringlist(ArrayType *textarray)
+textarray_to_cstringlist(ArrayType *textarray)
 {
 	Datum	   *elems;
 	int			nelems,
@@ -254,7 +259,7 @@ textarray_to_stringlist(ArrayType *textarray)
 		return NIL;
 
 	for (i = 0; i < nelems; i++)
-		res = lappend(res, makeString(TextDatumGetCString(elems[i])));
+		res = lappend(res, TextDatumGetCString(elems[i]));
 
 	return res;
 }
diff --git a/src/backend/commands/subscriptioncmds.c b/src/backend/commands/subscriptioncmds.c
index 3d29818badd..f55230292a6 100644
--- a/src/backend/commands/subscriptioncmds.c
+++ b/src/backend/commands/subscriptioncmds.c
@@ -3043,6 +3043,9 @@ ReportSlotConnectionError(List *rstates, Oid subid, char *slotname, char *err)
  * Check for duplicates in the given list of publications and error out if
  * found one.  Add publications to datums as text datums, if datums is not
  * NULL.
+ *
+ * Note that publications can be either list of string nodes or list of the
+ * C strings.
  */
 static void
 check_duplicates_in_publist(List *publist, Datum *datums)
@@ -3052,12 +3055,22 @@ check_duplicates_in_publist(List *publist, Datum *datums)
 
 	foreach(cell, publist)
 	{
-		char	   *name = strVal(lfirst(cell));
+		char	   *name;
 		ListCell   *pcell;
 
+		if (IsA((Node *) lfirst(cell), String))
+			name = strVal(lfirst(cell));
+		else
+			name = (char *) lfirst(cell);
+
 		foreach(pcell, publist)
 		{
-			char	   *pname = strVal(lfirst(pcell));
+			char	   *pname;
+
+			if (IsA((Node *) lfirst(pcell), String))
+				pname = strVal(lfirst(pcell));
+			else
+				pname = (char *) lfirst(pcell);
 
 			if (pcell == cell)
 				break;
@@ -3101,7 +3114,7 @@ merge_publications(List *oldpublist, List *newpublist, bool addpub, const char *
 
 		foreach(lc2, oldpublist)
 		{
-			char	   *pubname = strVal(lfirst(lc2));
+			char	   *pubname = (char *) (lfirst(lc2));
 
 			if (strcmp(name, pubname) == 0)
 			{
@@ -3119,7 +3132,7 @@ merge_publications(List *oldpublist, List *newpublist, bool addpub, const char *
 		}
 
 		if (addpub && !found)
-			oldpublist = lappend(oldpublist, makeString(name));
+			oldpublist = lappend(oldpublist, name);
 		else if (!addpub && !found)
 			ereport(ERROR,
 					(errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
diff --git a/src/backend/replication/libpqwalreceiver/libpqwalreceiver.c b/src/backend/replication/libpqwalreceiver/libpqwalreceiver.c
index 239641bfbb6..8b97619db5c 100644
--- a/src/backend/replication/libpqwalreceiver/libpqwalreceiver.c
+++ b/src/backend/replication/libpqwalreceiver/libpqwalreceiver.c
@@ -1172,7 +1172,7 @@ libpqrcv_exec(WalReceiverConn *conn, const char *query,
 }
 
 /*
- * Given a List of strings, return it as single comma separated
+ * Given a List of C strings, return it as single comma separated
  * string, quoting identifiers as needed.
  *
  * This is essentially the reverse of SplitIdentifierString.
@@ -1190,7 +1190,7 @@ stringlist_to_identifierstr(PGconn *conn, List *strings)
 
 	foreach(lc, strings)
 	{
-		char	   *val = strVal(lfirst(lc));
+		char	   *val = (char *) (lfirst(lc));
 		char	   *val_escaped;
 
 		if (first)
-- 
2.43.0

From ff9e5d8cdf8944888d1d4c3dcf225dcb8e6a2ad5 Mon Sep 17 00:00:00 2001
From: Vaibhav Dalvi <vaibhav.dalvi@enterprisedb.com>
Date: Tue, 11 Nov 2025 05:43:21 +0000
Subject: [PATCH v4 2/2] Add pg_get_subscription_ddl() function

This new SQL-callable function returns the `CREATE SUBSCRIPTION`
statement for a given subscription name or oid.

Like `pg_dump`, the returned DDL explicitly sets `connect = false`.
This is because the original `CONNECT` option value is not cataloged,
and using `connect = false` ensures the DDL can be successfully executed
even if the remote publisher is unreachable.

This function is restricted to users that have the "pg_read_all_data" and/or
"pg_create_subscription" privilege.  This is a security measure because
subscription connection strings often contain sensitive information, such as
passwords.

Author: Vaibhav Dalvi <vaibhav.dalvi@enterprisedb.com>
Reviewers: Akshay Joshi, Nishant Sharma, Ian Barwick & Alvaro Herrera
Discussion: https://www.postgresql.org/message-id/CA%2BvB%3DAGG0NoxWW%3D-947RBmba8Pzhj8j7op0Xkv8nLDzVMc2%3D7w%40mail.gmail.com
---
 doc/src/sgml/func/func-info.sgml           |  51 +++++
 src/backend/utils/adt/ruleutils.c          | 244 +++++++++++++++++++++
 src/include/catalog/pg_proc.dat            |   6 +
 src/include/utils/ruleutils.h              |   1 +
 src/test/regress/expected/subscription.out | 103 ++++++++-
 src/test/regress/sql/subscription.sql      |  68 +++++-
 6 files changed, 465 insertions(+), 8 deletions(-)

diff --git a/doc/src/sgml/func/func-info.sgml b/doc/src/sgml/func/func-info.sgml
index d4508114a48..21d33ee1802 100644
--- a/doc/src/sgml/func/func-info.sgml
+++ b/doc/src/sgml/func/func-info.sgml
@@ -3797,4 +3797,55 @@ acl      | {postgres=arwdDxtm/postgres,foo=r/postgres}
 
   </sect2>
 
+  <sect2 id="functions-get-object-ddl">
+   <title>Get Object DDL Functions</title>
+
+   <para>
+    The functions shown in <xref linkend="functions-get-object-ddl-table"/>
+    print the DDL statements for various database objects.
+    (This is a decompiled reconstruction, not the original text
+    of the command.)
+   </para>
+
+   <table id="functions-get-object-ddl-table">
+    <title>Get Object DDL Functions</title>
+    <tgroup cols="1">
+     <thead>
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        Function
+       </para>
+       <para>
+        Description
+       </para></entry>
+      </row>
+     </thead>
+
+     <tbody>
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>pg_get_subscription_ddl</primary>
+        </indexterm>
+        <function>pg_get_subscription_ddl</function> ( <parameter>subscription</parameter> <type>text</type> )
+        <returnvalue>text</returnvalue>
+       </para>
+       <para>
+        Reconstructs the creating command for a subscription.
+        The result is a complete <command>CREATE SUBSCRIPTION</command>
+        statement. The <literal>connect</literal> option set to
+        <literal>false</literal>.
+       </para>
+       <para>
+        This function is restricted to users that have the
+        <literal>pg_read_all_data</literal> and/or
+        <literal>pg_create_subscription</literal> privilege.
+       </para></entry>
+      </row>
+     </tbody>
+    </tgroup>
+   </table>
+
+  </sect2>
+
   </sect1>
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 556ab057e5a..59affbd9d1c 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -35,6 +35,7 @@
 #include "catalog/pg_partitioned_table.h"
 #include "catalog/pg_proc.h"
 #include "catalog/pg_statistic_ext.h"
+#include "catalog/pg_subscription.h"
 #include "catalog/pg_trigger.h"
 #include "catalog/pg_type.h"
 #include "commands/defrem.h"
@@ -57,6 +58,7 @@
 #include "rewrite/rewriteHandler.h"
 #include "rewrite/rewriteManip.h"
 #include "rewrite/rewriteSupport.h"
+#include "utils/acl.h"
 #include "utils/array.h"
 #include "utils/builtins.h"
 #include "utils/fmgroids.h"
@@ -546,6 +548,7 @@ static void get_json_table_nested_columns(TableFunc *tf, JsonTablePlan *plan,
 										  deparse_context *context,
 										  bool showimplicit,
 										  bool needcomma);
+static List *text_array_to_cstring_list(ArrayType *text_array);
 
 #define only_marker(rte)  ((rte)->inh ? "" : "ONLY ")
 
@@ -13743,3 +13746,244 @@ get_range_partbound_string(List *bound_datums)
 
 	return buf.data;
 }
+
+/*
+ * build_subscription_ddl_string - Build CREATE SUBSCRIPTION statement for
+ * a subscription from its OID. This is internal version which helps
+ * pg_get_subscription_ddl_name() and pg_get_subscription_ddl_oid().
+ */
+char *
+build_subscription_ddl_string(const Oid suboid)
+{
+	Form_pg_subscription subForm;
+	StringInfo	pubnames;
+	StringInfoData buf;
+	HeapTuple	tup;
+	char	   *conninfo;
+	List	   *publist;
+	Datum		datum;
+	bool		isnull;
+
+	/*
+	 * To prevent unprivileged users from initiating unauthorized network
+	 * connections, dumping subscription creation is restricted.  A user must
+	 * be specifically authorized (via the appropriate role privilege) to
+	 * create subscriptions and/or to read all data.
+	 */
+	if (!(has_privs_of_role(GetUserId(), ROLE_PG_CREATE_SUBSCRIPTION) ||
+		has_privs_of_role(GetUserId(), ROLE_PG_READ_ALL_DATA)))
+		ereport(ERROR,
+				(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+				 errmsg("permission denied to get the create subscription ddl"),
+				 errdetail("Only roles with privileges of the \"%s\" and/or \"%s\" role may get ddl.",
+						   "pg_create_subscription", "pg_read_all_data")));
+
+	/* Look up the subscription in pg_subscription */
+	tup = SearchSysCache1(SUBSCRIPTIONOID, ObjectIdGetDatum(suboid));
+	if (!HeapTupleIsValid(tup))
+		ereport(ERROR,
+				(errcode(ERRCODE_UNDEFINED_OBJECT),
+				 errmsg("subscription with oid %d does not exist", suboid)));
+
+	/* Get subscription's details from its tuple */
+	subForm = (Form_pg_subscription) GETSTRUCT(tup);
+
+	initStringInfo(&buf);
+
+	/* Build the CREATE SUBSCRIPTION statement */
+	appendStringInfo(&buf, "CREATE SUBSCRIPTION %s ",
+					 quote_identifier(NameStr(subForm->subname)));
+
+	/* Get conninfo */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_subconninfo);
+	conninfo = TextDatumGetCString(datum);
+
+	/* Append connection info to the CREATE SUBSCRIPTION statement */
+	appendStringInfo(&buf, "CONNECTION \'%s\'", conninfo);
+
+	/* Build list of quoted publications and append them to query */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_subpublications);
+	publist = text_array_to_cstring_list(DatumGetArrayTypeP(datum));
+	pubnames = makeStringInfo();
+	GetPublicationsStr(publist, pubnames, false);
+	appendStringInfo(&buf, " PUBLICATION %s", pubnames->data);
+
+	/*
+	 * Add options using WITH clause.  The 'connect' option value given at the
+	 * time of subscription creation is not available in the catalog.  When
+	 * creating a subscription, the remote host is not reachable or in an
+	 * unclear state, in that case, the subscription can be created using
+	 * 'connect = false' option.  This is what pg_dump uses.
+	 *
+	 * The status or value of the options 'create_slot' and 'copy_data' not
+	 * available in the catalog table.  We can use default values i.e. TRUE
+	 * for both.  This is what pg_dump uses.
+	 */
+	appendStringInfoString(&buf, " WITH (connect = false");
+
+	/* Get slotname */
+	datum = SysCacheGetAttr(SUBSCRIPTIONOID, tup,
+							Anum_pg_subscription_subslotname,
+							&isnull);
+	if (!isnull)
+		appendStringInfo(&buf, ", slot_name = \'%s\'",
+						 NameStr(*DatumGetName(datum)));
+	else
+	{
+		appendStringInfoString(&buf, ", slot_name = none");
+		/* Setting slot_name to none must set create_slot to false */
+		appendStringInfoString(&buf, ", create_slot = false");
+	}
+
+	/* Get enabled option */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_subenabled);
+	/* Setting 'slot_name' to none must set 'enabled' to false as well */
+	if (!DatumGetBool(datum) || isnull)
+		appendStringInfoString(&buf, ", enabled = false");
+	else
+		appendStringInfoString(&buf, ", enabled = true");
+
+	/* Get binary option */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_subbinary);
+	appendStringInfo(&buf, ", binary = %s",
+					 DatumGetBool(datum) ? "true" : "false");
+
+	/* Get streaming option */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_substream);
+	if (DatumGetChar(datum) == LOGICALREP_STREAM_OFF)
+		appendStringInfoString(&buf, ", streaming = off");
+	else if (DatumGetChar(datum) == LOGICALREP_STREAM_ON)
+		appendStringInfoString(&buf, ", streaming = on");
+	else
+		appendStringInfoString(&buf, ", streaming = parallel");
+
+	/* Get sync commit option */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_subsynccommit);
+	appendStringInfo(&buf, ", synchronous_commit = %s",
+					 TextDatumGetCString(datum));
+
+	/* Get two-phase commit option */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_subtwophasestate);
+	if (DatumGetChar(datum) == LOGICALREP_TWOPHASE_STATE_DISABLED)
+		appendStringInfoString(&buf, ", two_phase = off");
+	else
+		appendStringInfoString(&buf, ", two_phase = on");
+
+	/* Disable on error? */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_subdisableonerr);
+	appendStringInfo(&buf, ", disable_on_error = %s",
+					 DatumGetBool(datum) ? "on" : "off");
+
+	/* Password required? */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_subpasswordrequired);
+	appendStringInfo(&buf, ", password_required = %s",
+					 DatumGetBool(datum) ? "on" : "off");
+
+	/* Run as owner? */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_subrunasowner);
+	appendStringInfo(&buf, ", run_as_owner = %s",
+					 DatumGetBool(datum) ? "on" : "off");
+
+	/* Get origin */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_suborigin);
+	appendStringInfo(&buf, ", origin = %s", TextDatumGetCString(datum));
+
+	/* Failover? */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_subfailover);
+	appendStringInfo(&buf, ", failover = %s",
+					 DatumGetBool(datum) ? "on" : "off");
+
+	/* Retain dead tuples? */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_subretaindeadtuples);
+	appendStringInfo(&buf, ", retain_dead_tuples = %s",
+					 DatumGetBool(datum) ? "on" : "off");
+
+	/* Max retention duration */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_submaxretention);
+	appendStringInfo(&buf, ", max_retention_duration = %d",
+					 DatumGetInt32(datum));
+
+	/* Finally close parenthesis and add semicolon to the statement */
+	appendStringInfoString(&buf, ");");
+
+	ReleaseSysCache(tup);
+
+	return buf.data;
+}
+
+/*
+ * pg_get_subscription_ddl_name
+ *		Get CREATE SUBSCRIPTION statement for a subscription.
+ *
+ * This takes name as parameter for pg_get_subscription_ddl().
+ */
+Datum
+pg_get_subscription_ddl_name(PG_FUNCTION_ARGS)
+{
+	Name		subname = PG_GETARG_NAME(0);
+	Oid			suboid;
+	char	   *ddl_stmt;
+
+	/* Get the OID of the subscription from its name */
+	suboid = get_subscription_oid(NameStr(*subname), false);
+
+	/* Get the CREATE SUBSCRIPTION DDL statement from its OID */
+	ddl_stmt = build_subscription_ddl_string(suboid);
+
+	PG_RETURN_TEXT_P(string_to_text(ddl_stmt));
+}
+
+/*
+ * pg_get_subscription_ddl_oid
+ *		Get CREATE SUBSCRIPTION statement for a subscription.
+ *
+ * This takes oid as parameter for pg_get_subscription_ddl().
+ */
+Datum
+pg_get_subscription_ddl_oid(PG_FUNCTION_ARGS)
+{
+	Oid			suboid = PG_GETARG_OID(0);
+	char	   *ddl_stmt;
+
+	/* Get the CREATE SUBSCRIPTION DDL statement from its OID */
+	ddl_stmt = build_subscription_ddl_string(suboid);
+
+	PG_RETURN_TEXT_P(string_to_text(ddl_stmt));
+}
+
+/*
+ * text_array_to_string_list
+ *		Convert text array to list of strings.
+ */
+static List *
+text_array_to_cstring_list(ArrayType *text_array)
+{
+	List	   *result = NIL;
+	Datum	   *elems;
+	int			nelems,
+				i;
+
+	deconstruct_array_builtin(text_array, TEXTOID, &elems, NULL, &nelems);
+
+	if (nelems == 0)
+		return NIL;
+
+	for (i = 0; i < nelems; i++)
+		result = lappend(result, TextDatumGetCString(elems[i]));
+
+	return result;
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 34b7fddb0e7..4b9efd141af 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -3993,6 +3993,12 @@
 { oid => '1387', descr => 'constraint description',
   proname => 'pg_get_constraintdef', provolatile => 's', prorettype => 'text',
   proargtypes => 'oid', prosrc => 'pg_get_constraintdef' },
+{ oid => '8001', descr => 'get CREATE statement for subscription',
+  proname => 'pg_get_subscription_ddl', prorettype => 'text',
+  proargtypes => 'name', prosrc => 'pg_get_subscription_ddl_name' },
+{ oid => '8002', descr => 'get CREATE statement for subscription',
+  proname => 'pg_get_subscription_ddl', prorettype => 'text',
+  proargtypes => 'oid', prosrc => 'pg_get_subscription_ddl_oid' },
 { oid => '1716', descr => 'deparse an encoded expression',
   proname => 'pg_get_expr', provolatile => 's', prorettype => 'text',
   proargtypes => 'pg_node_tree oid', prosrc => 'pg_get_expr' },
diff --git a/src/include/utils/ruleutils.h b/src/include/utils/ruleutils.h
index 7ba7d887914..f8f26284489 100644
--- a/src/include/utils/ruleutils.h
+++ b/src/include/utils/ruleutils.h
@@ -53,5 +53,6 @@ extern char *generate_opclass_name(Oid opclass);
 extern char *get_range_partbound_string(List *bound_datums);
 
 extern char *pg_get_statisticsobjdef_string(Oid statextid);
+extern char *build_subscription_ddl_string(const Oid suboid);
 
 #endif							/* RULEUTILS_H */
diff --git a/src/test/regress/expected/subscription.out b/src/test/regress/expected/subscription.out
index 327d1e7731f..9b267240a6e 100644
--- a/src/test/regress/expected/subscription.out
+++ b/src/test/regress/expected/subscription.out
@@ -100,13 +100,13 @@ ERROR:  subscription with slot_name = NONE must also set create_slot = false
 CREATE SUBSCRIPTION regress_testsub2 CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (slot_name = NONE, create_slot = false);
 ERROR:  subscription with slot_name = NONE must also set enabled = false
 -- ok - with slot_name = NONE
-CREATE SUBSCRIPTION regress_testsub3 CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (slot_name = NONE, connect = false);
+CREATE SUBSCRIPTION regress_regress_testsub3 CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (slot_name = NONE, connect = false);
 WARNING:  subscription was created, but is not connected
 HINT:  To initiate replication, you must manually create the replication slot, enable the subscription, and alter the subscription to refresh publications.
 -- fail
-ALTER SUBSCRIPTION regress_testsub3 ENABLE;
+ALTER SUBSCRIPTION regress_regress_testsub3 ENABLE;
 ERROR:  cannot enable subscription that does not have a slot name
-ALTER SUBSCRIPTION regress_testsub3 REFRESH PUBLICATION;
+ALTER SUBSCRIPTION regress_regress_testsub3 REFRESH PUBLICATION;
 ERROR:  ALTER SUBSCRIPTION ... REFRESH PUBLICATION is not allowed for disabled subscriptions
 -- fail - origin must be either none or any
 CREATE SUBSCRIPTION regress_testsub4 CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (slot_name = NONE, connect = false, origin = foo);
@@ -130,7 +130,7 @@ ALTER SUBSCRIPTION regress_testsub4 SET (origin = any);
  regress_testsub4 | regress_subscription_user | f       | {testpub}   | f      | parallel  | d                | f                | any    | t                 | f             | f        | f                  |                      0 | f                | off                | dbname=regress_doesnotexist | 0/00000000
 (1 row)
 
-DROP SUBSCRIPTION regress_testsub3;
+DROP SUBSCRIPTION regress_regress_testsub3;
 DROP SUBSCRIPTION regress_testsub4;
 -- fail, connection string does not parse
 CREATE SUBSCRIPTION regress_testsub5 CONNECTION 'i_dont_exist=param' PUBLICATION testpub;
@@ -522,3 +522,98 @@ DROP ROLE regress_subscription_user;
 DROP ROLE regress_subscription_user2;
 DROP ROLE regress_subscription_user3;
 DROP ROLE regress_subscription_user_dummy;
+--
+-- Test pg_get_subscription_ddl() by creating subscriptions with various
+-- configurations and checking the DDL.
+--
+CREATE ROLE regress_createsub_role LOGIN;
+CREATE ROLE regress_readalldata_role LOGIN;
+-- see the pg_get_subscription_ddl output for a NULL and empty input
+SELECT pg_get_subscription_ddl('');
+ERROR:  subscription "" does not exist
+SELECT pg_get_subscription_ddl(NULL);
+ pg_get_subscription_ddl 
+-------------------------
+ 
+(1 row)
+
+-- Create subscription with minimal options
+CREATE SUBSCRIPTION regress_testsub1 CONNECTION 'dbname=db_doesnotexist'
+  PUBLICATION testpub1 WITH (connect=false);
+WARNING:  subscription was created, but is not connected
+HINT:  To initiate replication, you must manually create the replication slot, enable the subscription, and alter the subscription to refresh publications.
+-- Check that the subscription ddl is correctly created
+SELECT pg_get_subscription_ddl('regress_testsub1');
+                                                                                                                                                                                              pg_get_subscription_ddl                                                                                                                                                                                              
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE SUBSCRIPTION regress_testsub1 CONNECTION 'dbname=db_doesnotexist' PUBLICATION "testpub1" WITH (connect = false, slot_name = 'regress_testsub1', enabled = false, binary = false, streaming = parallel, synchronous_commit = off, two_phase = off, disable_on_error = off, password_required = on, run_as_owner = off, origin = any, failover = off, retain_dead_tuples = off, max_retention_duration = 0);
+(1 row)
+
+-- Create subscription with more options
+CREATE SUBSCRIPTION "regress_TestSubddL2" CONNECTION 'host=unknown user=dvd password=pass123'
+  PUBLICATION "testpub2", "TestPub3" WITH (connect=false, slot_name='slot1',
+  enabled=off);
+WARNING:  subscription was created, but is not connected
+HINT:  To initiate replication, you must manually create the replication slot, enable the subscription, and alter the subscription to refresh publications.
+SELECT pg_get_subscription_ddl('regress_TestSubddL2');
+                                                                                                                                                                                                         pg_get_subscription_ddl                                                                                                                                                                                                         
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE SUBSCRIPTION "regress_TestSubddL2" CONNECTION 'host=unknown user=dvd password=pass123' PUBLICATION "testpub2", "TestPub3" WITH (connect = false, slot_name = 'slot1', enabled = false, binary = false, streaming = parallel, synchronous_commit = off, two_phase = off, disable_on_error = off, password_required = on, run_as_owner = off, origin = any, failover = off, retain_dead_tuples = off, max_retention_duration = 0);
+(1 row)
+
+-- Create subscription with all options
+CREATE SUBSCRIPTION regress_testsub3 CONNECTION 'host=unknown user=dvd password=pass12'
+  PUBLICATION testpub4 WITH (connect=false, slot_name=none, enabled=false,
+  create_slot=false, copy_data=false, binary=true, streaming=off,
+  synchronous_commit=local, two_phase=true, disable_on_error=true,
+  password_required=false, run_as_owner=true, origin=none, failover=true,
+  retain_dead_tuples=false, max_retention_duration=100);
+NOTICE:  max_retention_duration is ineffective when retain_dead_tuples is disabled
+WARNING:  subscription was created, but is not connected
+HINT:  To initiate replication, you must manually create the replication slot, enable the subscription, and alter the subscription to refresh publications.
+SELECT pg_get_subscription_ddl('regress_testsub3');
+                                                                                                                                                                                                       pg_get_subscription_ddl                                                                                                                                                                                                       
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE SUBSCRIPTION regress_testsub3 CONNECTION 'host=unknown user=dvd password=pass12' PUBLICATION "testpub4" WITH (connect = false, slot_name = none, create_slot = false, enabled = false, binary = true, streaming = off, synchronous_commit = local, two_phase = on, disable_on_error = on, password_required = off, run_as_owner = on, origin = none, failover = on, retain_dead_tuples = off, max_retention_duration = 100);
+(1 row)
+
+-- Non-superusers and which don't have pg_create_subscription and/or
+-- pg_read_all_data permission can't get ddl
+SET SESSION AUTHORIZATION 'regress_createsub_role';
+SELECT pg_get_subscription_ddl('regress_TestSubddL2');
+ERROR:  permission denied to get the create subscription ddl
+DETAIL:  Only roles with privileges of the "pg_create_subscription" and/or "pg_read_all_data" role may get ddl.
+RESET SESSION AUTHORIZATION;
+SET SESSION AUTHORIZATION 'regress_readalldata_role';
+SELECT pg_get_subscription_ddl('regress_TestSubddL2');
+ERROR:  permission denied to get the create subscription ddl
+DETAIL:  Only roles with privileges of the "pg_create_subscription" and/or "pg_read_all_data" role may get ddl.
+RESET SESSION AUTHORIZATION;
+-- Administrators can change who can access this function
+GRANT pg_create_subscription TO regress_createsub_role;
+GRANT pg_read_all_data TO regress_readalldata_role;
+SET SESSION AUTHORIZATION 'regress_createsub_role';
+SELECT pg_get_subscription_ddl('regress_TestSubddL2');
+                                                                                                                                                                                                         pg_get_subscription_ddl                                                                                                                                                                                                         
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE SUBSCRIPTION "regress_TestSubddL2" CONNECTION 'host=unknown user=dvd password=pass123' PUBLICATION "testpub2", "TestPub3" WITH (connect = false, slot_name = 'slot1', enabled = false, binary = false, streaming = parallel, synchronous_commit = off, two_phase = off, disable_on_error = off, password_required = on, run_as_owner = off, origin = any, failover = off, retain_dead_tuples = off, max_retention_duration = 0);
+(1 row)
+
+RESET SESSION AUTHORIZATION;
+SET SESSION AUTHORIZATION 'regress_readalldata_role';
+SELECT pg_get_subscription_ddl('regress_TestSubddL2');
+                                                                                                                                                                                                         pg_get_subscription_ddl                                                                                                                                                                                                         
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE SUBSCRIPTION "regress_TestSubddL2" CONNECTION 'host=unknown user=dvd password=pass123' PUBLICATION "testpub2", "TestPub3" WITH (connect = false, slot_name = 'slot1', enabled = false, binary = false, streaming = parallel, synchronous_commit = off, two_phase = off, disable_on_error = off, password_required = on, run_as_owner = off, origin = any, failover = off, retain_dead_tuples = off, max_retention_duration = 0);
+(1 row)
+
+RESET SESSION AUTHORIZATION;
+REVOKE pg_create_subscription FROM regress_createsub_role;
+REVOKE pg_read_all_data FROM regress_readalldata_role;
+ALTER SUBSCRIPTION regress_testsub1 SET (slot_name=NONE);
+DROP SUBSCRIPTION regress_testsub1;
+ALTER SUBSCRIPTION "regress_TestSubddL2" SET (slot_name=NONE);
+DROP SUBSCRIPTION "regress_TestSubddL2";
+DROP SUBSCRIPTION regress_testsub3;
+DROP ROLE regress_createsub_role;
+DROP ROLE regress_readalldata_role;
diff --git a/src/test/regress/sql/subscription.sql b/src/test/regress/sql/subscription.sql
index ef0c298d2df..d2c05445b54 100644
--- a/src/test/regress/sql/subscription.sql
+++ b/src/test/regress/sql/subscription.sql
@@ -61,10 +61,10 @@ CREATE SUBSCRIPTION regress_testsub2 CONNECTION 'dbname=regress_doesnotexist' PU
 CREATE SUBSCRIPTION regress_testsub2 CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (slot_name = NONE, create_slot = false);
 
 -- ok - with slot_name = NONE
-CREATE SUBSCRIPTION regress_testsub3 CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (slot_name = NONE, connect = false);
+CREATE SUBSCRIPTION regress_regress_testsub3 CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (slot_name = NONE, connect = false);
 -- fail
-ALTER SUBSCRIPTION regress_testsub3 ENABLE;
-ALTER SUBSCRIPTION regress_testsub3 REFRESH PUBLICATION;
+ALTER SUBSCRIPTION regress_regress_testsub3 ENABLE;
+ALTER SUBSCRIPTION regress_regress_testsub3 REFRESH PUBLICATION;
 
 -- fail - origin must be either none or any
 CREATE SUBSCRIPTION regress_testsub4 CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (slot_name = NONE, connect = false, origin = foo);
@@ -75,7 +75,7 @@ CREATE SUBSCRIPTION regress_testsub4 CONNECTION 'dbname=regress_doesnotexist' PU
 ALTER SUBSCRIPTION regress_testsub4 SET (origin = any);
 \dRs+ regress_testsub4
 
-DROP SUBSCRIPTION regress_testsub3;
+DROP SUBSCRIPTION regress_regress_testsub3;
 DROP SUBSCRIPTION regress_testsub4;
 
 -- fail, connection string does not parse
@@ -370,3 +370,63 @@ DROP ROLE regress_subscription_user;
 DROP ROLE regress_subscription_user2;
 DROP ROLE regress_subscription_user3;
 DROP ROLE regress_subscription_user_dummy;
+
+--
+-- Test pg_get_subscription_ddl() by creating subscriptions with various
+-- configurations and checking the DDL.
+--
+CREATE ROLE regress_createsub_role LOGIN;
+CREATE ROLE regress_readalldata_role LOGIN;
+
+-- see the pg_get_subscription_ddl output for a NULL and empty input
+SELECT pg_get_subscription_ddl('');
+SELECT pg_get_subscription_ddl(NULL);
+
+-- Create subscription with minimal options
+CREATE SUBSCRIPTION regress_testsub1 CONNECTION 'dbname=db_doesnotexist'
+  PUBLICATION testpub1 WITH (connect=false);
+-- Check that the subscription ddl is correctly created
+SELECT pg_get_subscription_ddl('regress_testsub1');
+
+-- Create subscription with more options
+CREATE SUBSCRIPTION "regress_TestSubddL2" CONNECTION 'host=unknown user=dvd password=pass123'
+  PUBLICATION "testpub2", "TestPub3" WITH (connect=false, slot_name='slot1',
+  enabled=off);
+SELECT pg_get_subscription_ddl('regress_TestSubddL2');
+
+-- Create subscription with all options
+CREATE SUBSCRIPTION regress_testsub3 CONNECTION 'host=unknown user=dvd password=pass12'
+  PUBLICATION testpub4 WITH (connect=false, slot_name=none, enabled=false,
+  create_slot=false, copy_data=false, binary=true, streaming=off,
+  synchronous_commit=local, two_phase=true, disable_on_error=true,
+  password_required=false, run_as_owner=true, origin=none, failover=true,
+  retain_dead_tuples=false, max_retention_duration=100);
+SELECT pg_get_subscription_ddl('regress_testsub3');
+
+-- Non-superusers and which don't have pg_create_subscription and/or
+-- pg_read_all_data permission can't get ddl
+SET SESSION AUTHORIZATION 'regress_createsub_role';
+SELECT pg_get_subscription_ddl('regress_TestSubddL2');
+RESET SESSION AUTHORIZATION;
+SET SESSION AUTHORIZATION 'regress_readalldata_role';
+SELECT pg_get_subscription_ddl('regress_TestSubddL2');
+RESET SESSION AUTHORIZATION;
+-- Administrators can change who can access this function
+GRANT pg_create_subscription TO regress_createsub_role;
+GRANT pg_read_all_data TO regress_readalldata_role;
+SET SESSION AUTHORIZATION 'regress_createsub_role';
+SELECT pg_get_subscription_ddl('regress_TestSubddL2');
+RESET SESSION AUTHORIZATION;
+SET SESSION AUTHORIZATION 'regress_readalldata_role';
+SELECT pg_get_subscription_ddl('regress_TestSubddL2');
+
+RESET SESSION AUTHORIZATION;
+REVOKE pg_create_subscription FROM regress_createsub_role;
+REVOKE pg_read_all_data FROM regress_readalldata_role;
+ALTER SUBSCRIPTION regress_testsub1 SET (slot_name=NONE);
+DROP SUBSCRIPTION regress_testsub1;
+ALTER SUBSCRIPTION "regress_TestSubddL2" SET (slot_name=NONE);
+DROP SUBSCRIPTION "regress_TestSubddL2";
+DROP SUBSCRIPTION regress_testsub3;
+DROP ROLE regress_createsub_role;
+DROP ROLE regress_readalldata_role;
-- 
2.43.0

From 8799c9426d8e25f6b2dfd132d5acc092f34724ab Mon Sep 17 00:00:00 2001
From: Vaibhav Dalvi <vaibhav.dalvi@enterprisedb.com>
Date: Tue, 11 Nov 2025 15:40:55 +0000
Subject: [PATCH v5 1/1] Add pg_get_subscription_ddl() function

This new SQL-callable function returns the `CREATE SUBSCRIPTION`
statement for a given subscription name or oid.

Like `pg_dump`, the returned DDL explicitly sets `connect = false`.
This is because the original `CONNECT` option value is not cataloged,
and using `connect = false` ensures the DDL can be successfully executed
even if the remote publisher is unreachable.

This function is restricted to users that have the "pg_read_all_data" and/or
"pg_create_subscription" privilege.  This is a security measure because
subscription connection strings often contain sensitive information, such as
passwords.

Author: Vaibhav Dalvi <vaibhav.dalvi@enterprisedb.com>
Reviewers: Akshay Joshi, Nishant Sharma, Ian Barwick & Alvaro Herrera
Discussion: https://www.postgresql.org/message-id/CA%2BvB%3DAGG0NoxWW%3D-947RBmba8Pzhj8j7op0Xkv8nLDzVMc2%3D7w%40mail.gmail.com
---
 doc/src/sgml/func/func-info.sgml           |  51 +++++
 src/backend/utils/adt/ruleutils.c          | 244 +++++++++++++++++++++
 src/include/catalog/pg_proc.dat            |   6 +
 src/include/utils/ruleutils.h              |   1 +
 src/test/regress/expected/subscription.out |  95 ++++++++
 src/test/regress/sql/subscription.sql      |  60 +++++
 6 files changed, 457 insertions(+)

diff --git a/doc/src/sgml/func/func-info.sgml b/doc/src/sgml/func/func-info.sgml
index d4508114a48..21d33ee1802 100644
--- a/doc/src/sgml/func/func-info.sgml
+++ b/doc/src/sgml/func/func-info.sgml
@@ -3797,4 +3797,55 @@ acl      | {postgres=arwdDxtm/postgres,foo=r/postgres}
 
   </sect2>
 
+  <sect2 id="functions-get-object-ddl">
+   <title>Get Object DDL Functions</title>
+
+   <para>
+    The functions shown in <xref linkend="functions-get-object-ddl-table"/>
+    print the DDL statements for various database objects.
+    (This is a decompiled reconstruction, not the original text
+    of the command.)
+   </para>
+
+   <table id="functions-get-object-ddl-table">
+    <title>Get Object DDL Functions</title>
+    <tgroup cols="1">
+     <thead>
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        Function
+       </para>
+       <para>
+        Description
+       </para></entry>
+      </row>
+     </thead>
+
+     <tbody>
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>pg_get_subscription_ddl</primary>
+        </indexterm>
+        <function>pg_get_subscription_ddl</function> ( <parameter>subscription</parameter> <type>text</type> )
+        <returnvalue>text</returnvalue>
+       </para>
+       <para>
+        Reconstructs the creating command for a subscription.
+        The result is a complete <command>CREATE SUBSCRIPTION</command>
+        statement. The <literal>connect</literal> option set to
+        <literal>false</literal>.
+       </para>
+       <para>
+        This function is restricted to users that have the
+        <literal>pg_read_all_data</literal> and/or
+        <literal>pg_create_subscription</literal> privilege.
+       </para></entry>
+      </row>
+     </tbody>
+    </tgroup>
+   </table>
+
+  </sect2>
+
   </sect1>
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 556ab057e5a..d1c3e0f7e38 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -35,6 +35,7 @@
 #include "catalog/pg_partitioned_table.h"
 #include "catalog/pg_proc.h"
 #include "catalog/pg_statistic_ext.h"
+#include "catalog/pg_subscription.h"
 #include "catalog/pg_trigger.h"
 #include "catalog/pg_type.h"
 #include "commands/defrem.h"
@@ -57,6 +58,7 @@
 #include "rewrite/rewriteHandler.h"
 #include "rewrite/rewriteManip.h"
 #include "rewrite/rewriteSupport.h"
+#include "utils/acl.h"
 #include "utils/array.h"
 #include "utils/builtins.h"
 #include "utils/fmgroids.h"
@@ -546,6 +548,7 @@ static void get_json_table_nested_columns(TableFunc *tf, JsonTablePlan *plan,
 										  deparse_context *context,
 										  bool showimplicit,
 										  bool needcomma);
+static List *text_array_to_string_list(ArrayType *text_array);
 
 #define only_marker(rte)  ((rte)->inh ? "" : "ONLY ")
 
@@ -13743,3 +13746,244 @@ get_range_partbound_string(List *bound_datums)
 
 	return buf.data;
 }
+
+/*
+ * build_subscription_ddl_string - Build CREATE SUBSCRIPTION statement for
+ * a subscription from its OID. This is internal version which helps
+ * pg_get_subscription_ddl_name() and pg_get_subscription_ddl_oid().
+ */
+char *
+build_subscription_ddl_string(const Oid suboid)
+{
+	Form_pg_subscription subForm;
+	StringInfo	pubnames;
+	StringInfoData buf;
+	HeapTuple	tup;
+	char	   *conninfo;
+	List	   *publist;
+	Datum		datum;
+	bool		isnull;
+
+	/*
+	 * To prevent unprivileged users from initiating unauthorized network
+	 * connections, dumping subscription creation is restricted.  A user must
+	 * be specifically authorized (via the appropriate role privilege) to
+	 * create subscriptions and/or to read all data.
+	 */
+	if (!(has_privs_of_role(GetUserId(), ROLE_PG_CREATE_SUBSCRIPTION) ||
+		has_privs_of_role(GetUserId(), ROLE_PG_READ_ALL_DATA)))
+		ereport(ERROR,
+				(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+				 errmsg("permission denied to get the create subscription ddl"),
+				 errdetail("Only roles with privileges of the \"%s\" and/or \"%s\" role may get ddl.",
+						   "pg_create_subscription", "pg_read_all_data")));
+
+	/* Look up the subscription in pg_subscription */
+	tup = SearchSysCache1(SUBSCRIPTIONOID, ObjectIdGetDatum(suboid));
+	if (!HeapTupleIsValid(tup))
+		ereport(ERROR,
+				(errcode(ERRCODE_UNDEFINED_OBJECT),
+				 errmsg("subscription with oid %d does not exist", suboid)));
+
+	/* Get subscription's details from its tuple */
+	subForm = (Form_pg_subscription) GETSTRUCT(tup);
+
+	initStringInfo(&buf);
+
+	/* Build the CREATE SUBSCRIPTION statement */
+	appendStringInfo(&buf, "CREATE SUBSCRIPTION %s ",
+					 quote_identifier(NameStr(subForm->subname)));
+
+	/* Get conninfo */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_subconninfo);
+	conninfo = TextDatumGetCString(datum);
+
+	/* Append connection info to the CREATE SUBSCRIPTION statement */
+	appendStringInfo(&buf, "CONNECTION \'%s\'", conninfo);
+
+	/* Build list of quoted publications and append them to query */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_subpublications);
+	publist = text_array_to_string_list(DatumGetArrayTypeP(datum));
+	pubnames = makeStringInfo();
+	GetPublicationsStr(publist, pubnames, false);
+	appendStringInfo(&buf, " PUBLICATION %s", pubnames->data);
+
+	/*
+	 * Add options using WITH clause.  The 'connect' option value given at the
+	 * time of subscription creation is not available in the catalog.  When
+	 * creating a subscription, the remote host is not reachable or in an
+	 * unclear state, in that case, the subscription can be created using
+	 * 'connect = false' option.  This is what pg_dump uses.
+	 *
+	 * The status or value of the options 'create_slot' and 'copy_data' not
+	 * available in the catalog table.  We can use default values i.e. TRUE
+	 * for both.  This is what pg_dump uses.
+	 */
+	appendStringInfoString(&buf, " WITH (connect = false");
+
+	/* Get slotname */
+	datum = SysCacheGetAttr(SUBSCRIPTIONOID, tup,
+							Anum_pg_subscription_subslotname,
+							&isnull);
+	if (!isnull)
+		appendStringInfo(&buf, ", slot_name = \'%s\'",
+						 NameStr(*DatumGetName(datum)));
+	else
+	{
+		appendStringInfoString(&buf, ", slot_name = none");
+		/* Setting slot_name to none must set create_slot to false */
+		appendStringInfoString(&buf, ", create_slot = false");
+	}
+
+	/* Get enabled option */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_subenabled);
+	/* Setting 'slot_name' to none must set 'enabled' to false as well */
+	if (!DatumGetBool(datum) || isnull)
+		appendStringInfoString(&buf, ", enabled = false");
+	else
+		appendStringInfoString(&buf, ", enabled = true");
+
+	/* Get binary option */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_subbinary);
+	appendStringInfo(&buf, ", binary = %s",
+					 DatumGetBool(datum) ? "true" : "false");
+
+	/* Get streaming option */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_substream);
+	if (DatumGetChar(datum) == LOGICALREP_STREAM_OFF)
+		appendStringInfoString(&buf, ", streaming = off");
+	else if (DatumGetChar(datum) == LOGICALREP_STREAM_ON)
+		appendStringInfoString(&buf, ", streaming = on");
+	else
+		appendStringInfoString(&buf, ", streaming = parallel");
+
+	/* Get sync commit option */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_subsynccommit);
+	appendStringInfo(&buf, ", synchronous_commit = %s",
+					 TextDatumGetCString(datum));
+
+	/* Get two-phase commit option */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_subtwophasestate);
+	if (DatumGetChar(datum) == LOGICALREP_TWOPHASE_STATE_DISABLED)
+		appendStringInfoString(&buf, ", two_phase = off");
+	else
+		appendStringInfoString(&buf, ", two_phase = on");
+
+	/* Disable on error? */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_subdisableonerr);
+	appendStringInfo(&buf, ", disable_on_error = %s",
+					 DatumGetBool(datum) ? "on" : "off");
+
+	/* Password required? */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_subpasswordrequired);
+	appendStringInfo(&buf, ", password_required = %s",
+					 DatumGetBool(datum) ? "on" : "off");
+
+	/* Run as owner? */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_subrunasowner);
+	appendStringInfo(&buf, ", run_as_owner = %s",
+					 DatumGetBool(datum) ? "on" : "off");
+
+	/* Get origin */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_suborigin);
+	appendStringInfo(&buf, ", origin = %s", TextDatumGetCString(datum));
+
+	/* Failover? */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_subfailover);
+	appendStringInfo(&buf, ", failover = %s",
+					 DatumGetBool(datum) ? "on" : "off");
+
+	/* Retain dead tuples? */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_subretaindeadtuples);
+	appendStringInfo(&buf, ", retain_dead_tuples = %s",
+					 DatumGetBool(datum) ? "on" : "off");
+
+	/* Max retention duration */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_submaxretention);
+	appendStringInfo(&buf, ", max_retention_duration = %d",
+					 DatumGetInt32(datum));
+
+	/* Finally close parenthesis and add semicolon to the statement */
+	appendStringInfoString(&buf, ");");
+
+	ReleaseSysCache(tup);
+
+	return buf.data;
+}
+
+/*
+ * pg_get_subscription_ddl_name
+ *		Get CREATE SUBSCRIPTION statement for a subscription.
+ *
+ * This takes name as parameter for pg_get_subscription_ddl().
+ */
+Datum
+pg_get_subscription_ddl_name(PG_FUNCTION_ARGS)
+{
+	Name		subname = PG_GETARG_NAME(0);
+	Oid			suboid;
+	char	   *ddl_stmt;
+
+	/* Get the OID of the subscription from its name */
+	suboid = get_subscription_oid(NameStr(*subname), false);
+
+	/* Get the CREATE SUBSCRIPTION DDL statement from its OID */
+	ddl_stmt = build_subscription_ddl_string(suboid);
+
+	PG_RETURN_TEXT_P(string_to_text(ddl_stmt));
+}
+
+/*
+ * pg_get_subscription_ddl_oid
+ *		Get CREATE SUBSCRIPTION statement for a subscription.
+ *
+ * This takes oid as parameter for pg_get_subscription_ddl().
+ */
+Datum
+pg_get_subscription_ddl_oid(PG_FUNCTION_ARGS)
+{
+	Oid			suboid = PG_GETARG_OID(0);
+	char	   *ddl_stmt;
+
+	/* Get the CREATE SUBSCRIPTION DDL statement from its OID */
+	ddl_stmt = build_subscription_ddl_string(suboid);
+
+	PG_RETURN_TEXT_P(string_to_text(ddl_stmt));
+}
+
+/*
+ * text_array_to_string_list
+ *		Convert text array to list of strings.
+ */
+static List *
+text_array_to_string_list(ArrayType *text_array)
+{
+	List	   *result = NIL;
+	Datum	   *elems;
+	int			nelems,
+				i;
+
+	deconstruct_array_builtin(text_array, TEXTOID, &elems, NULL, &nelems);
+
+	if (nelems == 0)
+		return NIL;
+
+	for (i = 0; i < nelems; i++)
+		result = lappend(result, makeString(TextDatumGetCString(elems[i])));
+
+	return result;
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 5cf9e12fcb9..a35657ab9ae 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -3993,6 +3993,12 @@
 { oid => '1387', descr => 'constraint description',
   proname => 'pg_get_constraintdef', provolatile => 's', prorettype => 'text',
   proargtypes => 'oid', prosrc => 'pg_get_constraintdef' },
+{ oid => '8001', descr => 'get CREATE statement for subscription',
+  proname => 'pg_get_subscription_ddl', prorettype => 'text',
+  proargtypes => 'name', prosrc => 'pg_get_subscription_ddl_name' },
+{ oid => '8002', descr => 'get CREATE statement for subscription',
+  proname => 'pg_get_subscription_ddl', prorettype => 'text',
+  proargtypes => 'oid', prosrc => 'pg_get_subscription_ddl_oid' },
 { oid => '1716', descr => 'deparse an encoded expression',
   proname => 'pg_get_expr', provolatile => 's', prorettype => 'text',
   proargtypes => 'pg_node_tree oid', prosrc => 'pg_get_expr' },
diff --git a/src/include/utils/ruleutils.h b/src/include/utils/ruleutils.h
index 7ba7d887914..f8f26284489 100644
--- a/src/include/utils/ruleutils.h
+++ b/src/include/utils/ruleutils.h
@@ -53,5 +53,6 @@ extern char *generate_opclass_name(Oid opclass);
 extern char *get_range_partbound_string(List *bound_datums);
 
 extern char *pg_get_statisticsobjdef_string(Oid statextid);
+extern char *build_subscription_ddl_string(const Oid suboid);
 
 #endif							/* RULEUTILS_H */
diff --git a/src/test/regress/expected/subscription.out b/src/test/regress/expected/subscription.out
index 327d1e7731f..ec2ef27a4f1 100644
--- a/src/test/regress/expected/subscription.out
+++ b/src/test/regress/expected/subscription.out
@@ -522,3 +522,98 @@ DROP ROLE regress_subscription_user;
 DROP ROLE regress_subscription_user2;
 DROP ROLE regress_subscription_user3;
 DROP ROLE regress_subscription_user_dummy;
+--
+-- Test pg_get_subscription_ddl() by creating subscriptions with various
+-- configurations and checking the DDL.
+--
+CREATE ROLE regress_createsub_role LOGIN;
+CREATE ROLE regress_readalldata_role LOGIN;
+-- see the pg_get_subscription_ddl output for a NULL and empty input
+SELECT pg_get_subscription_ddl('');
+ERROR:  subscription "" does not exist
+SELECT pg_get_subscription_ddl(NULL);
+ pg_get_subscription_ddl 
+-------------------------
+ 
+(1 row)
+
+-- Create subscription with minimal options
+CREATE SUBSCRIPTION regress_testsub1 CONNECTION 'dbname=db_doesnotexist'
+  PUBLICATION testpub1 WITH (connect=false);
+WARNING:  subscription was created, but is not connected
+HINT:  To initiate replication, you must manually create the replication slot, enable the subscription, and alter the subscription to refresh publications.
+-- Check that the subscription ddl is correctly created
+SELECT pg_get_subscription_ddl('regress_testsub1');
+                                                                                                                                                                                              pg_get_subscription_ddl                                                                                                                                                                                              
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE SUBSCRIPTION regress_testsub1 CONNECTION 'dbname=db_doesnotexist' PUBLICATION "testpub1" WITH (connect = false, slot_name = 'regress_testsub1', enabled = false, binary = false, streaming = parallel, synchronous_commit = off, two_phase = off, disable_on_error = off, password_required = on, run_as_owner = off, origin = any, failover = off, retain_dead_tuples = off, max_retention_duration = 0);
+(1 row)
+
+-- Create subscription with more options
+CREATE SUBSCRIPTION "regress_TestSubddL2" CONNECTION 'host=unknown user=dvd password=pass123'
+  PUBLICATION "testpub2", "TestPub3" WITH (connect=false, slot_name='slot1',
+  enabled=off);
+WARNING:  subscription was created, but is not connected
+HINT:  To initiate replication, you must manually create the replication slot, enable the subscription, and alter the subscription to refresh publications.
+SELECT pg_get_subscription_ddl('regress_TestSubddL2');
+                                                                                                                                                                                                         pg_get_subscription_ddl                                                                                                                                                                                                         
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE SUBSCRIPTION "regress_TestSubddL2" CONNECTION 'host=unknown user=dvd password=pass123' PUBLICATION "testpub2", "TestPub3" WITH (connect = false, slot_name = 'slot1', enabled = false, binary = false, streaming = parallel, synchronous_commit = off, two_phase = off, disable_on_error = off, password_required = on, run_as_owner = off, origin = any, failover = off, retain_dead_tuples = off, max_retention_duration = 0);
+(1 row)
+
+-- Create subscription with all options
+CREATE SUBSCRIPTION regress_testsub3 CONNECTION 'host=unknown user=dvd password=pass12'
+  PUBLICATION testpub4 WITH (connect=false, slot_name=none, enabled=false,
+  create_slot=false, copy_data=false, binary=true, streaming=off,
+  synchronous_commit=local, two_phase=true, disable_on_error=true,
+  password_required=false, run_as_owner=true, origin=none, failover=true,
+  retain_dead_tuples=false, max_retention_duration=100);
+NOTICE:  max_retention_duration is ineffective when retain_dead_tuples is disabled
+WARNING:  subscription was created, but is not connected
+HINT:  To initiate replication, you must manually create the replication slot, enable the subscription, and alter the subscription to refresh publications.
+SELECT pg_get_subscription_ddl('regress_testsub3');
+                                                                                                                                                                                                       pg_get_subscription_ddl                                                                                                                                                                                                       
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE SUBSCRIPTION regress_testsub3 CONNECTION 'host=unknown user=dvd password=pass12' PUBLICATION "testpub4" WITH (connect = false, slot_name = none, create_slot = false, enabled = false, binary = true, streaming = off, synchronous_commit = local, two_phase = on, disable_on_error = on, password_required = off, run_as_owner = on, origin = none, failover = on, retain_dead_tuples = off, max_retention_duration = 100);
+(1 row)
+
+-- Non-superusers and which don't have pg_create_subscription and/or
+-- pg_read_all_data permission can't get ddl
+SET SESSION AUTHORIZATION 'regress_createsub_role';
+SELECT pg_get_subscription_ddl('regress_TestSubddL2');
+ERROR:  permission denied to get the create subscription ddl
+DETAIL:  Only roles with privileges of the "pg_create_subscription" and/or "pg_read_all_data" role may get ddl.
+RESET SESSION AUTHORIZATION;
+SET SESSION AUTHORIZATION 'regress_readalldata_role';
+SELECT pg_get_subscription_ddl('regress_TestSubddL2');
+ERROR:  permission denied to get the create subscription ddl
+DETAIL:  Only roles with privileges of the "pg_create_subscription" and/or "pg_read_all_data" role may get ddl.
+RESET SESSION AUTHORIZATION;
+-- Administrators can change who can access this function
+GRANT pg_create_subscription TO regress_createsub_role;
+GRANT pg_read_all_data TO regress_readalldata_role;
+SET SESSION AUTHORIZATION 'regress_createsub_role';
+SELECT pg_get_subscription_ddl('regress_TestSubddL2');
+                                                                                                                                                                                                         pg_get_subscription_ddl                                                                                                                                                                                                         
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE SUBSCRIPTION "regress_TestSubddL2" CONNECTION 'host=unknown user=dvd password=pass123' PUBLICATION "testpub2", "TestPub3" WITH (connect = false, slot_name = 'slot1', enabled = false, binary = false, streaming = parallel, synchronous_commit = off, two_phase = off, disable_on_error = off, password_required = on, run_as_owner = off, origin = any, failover = off, retain_dead_tuples = off, max_retention_duration = 0);
+(1 row)
+
+RESET SESSION AUTHORIZATION;
+SET SESSION AUTHORIZATION 'regress_readalldata_role';
+SELECT pg_get_subscription_ddl('regress_TestSubddL2');
+                                                                                                                                                                                                         pg_get_subscription_ddl                                                                                                                                                                                                         
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE SUBSCRIPTION "regress_TestSubddL2" CONNECTION 'host=unknown user=dvd password=pass123' PUBLICATION "testpub2", "TestPub3" WITH (connect = false, slot_name = 'slot1', enabled = false, binary = false, streaming = parallel, synchronous_commit = off, two_phase = off, disable_on_error = off, password_required = on, run_as_owner = off, origin = any, failover = off, retain_dead_tuples = off, max_retention_duration = 0);
+(1 row)
+
+RESET SESSION AUTHORIZATION;
+REVOKE pg_create_subscription FROM regress_createsub_role;
+REVOKE pg_read_all_data FROM regress_readalldata_role;
+ALTER SUBSCRIPTION regress_testsub1 SET (slot_name=NONE);
+DROP SUBSCRIPTION regress_testsub1;
+ALTER SUBSCRIPTION "regress_TestSubddL2" SET (slot_name=NONE);
+DROP SUBSCRIPTION "regress_TestSubddL2";
+DROP SUBSCRIPTION regress_testsub3;
+DROP ROLE regress_createsub_role;
+DROP ROLE regress_readalldata_role;
diff --git a/src/test/regress/sql/subscription.sql b/src/test/regress/sql/subscription.sql
index ef0c298d2df..43cf74967fe 100644
--- a/src/test/regress/sql/subscription.sql
+++ b/src/test/regress/sql/subscription.sql
@@ -370,3 +370,63 @@ DROP ROLE regress_subscription_user;
 DROP ROLE regress_subscription_user2;
 DROP ROLE regress_subscription_user3;
 DROP ROLE regress_subscription_user_dummy;
+
+--
+-- Test pg_get_subscription_ddl() by creating subscriptions with various
+-- configurations and checking the DDL.
+--
+CREATE ROLE regress_createsub_role LOGIN;
+CREATE ROLE regress_readalldata_role LOGIN;
+
+-- see the pg_get_subscription_ddl output for a NULL and empty input
+SELECT pg_get_subscription_ddl('');
+SELECT pg_get_subscription_ddl(NULL);
+
+-- Create subscription with minimal options
+CREATE SUBSCRIPTION regress_testsub1 CONNECTION 'dbname=db_doesnotexist'
+  PUBLICATION testpub1 WITH (connect=false);
+-- Check that the subscription ddl is correctly created
+SELECT pg_get_subscription_ddl('regress_testsub1');
+
+-- Create subscription with more options
+CREATE SUBSCRIPTION "regress_TestSubddL2" CONNECTION 'host=unknown user=dvd password=pass123'
+  PUBLICATION "testpub2", "TestPub3" WITH (connect=false, slot_name='slot1',
+  enabled=off);
+SELECT pg_get_subscription_ddl('regress_TestSubddL2');
+
+-- Create subscription with all options
+CREATE SUBSCRIPTION regress_testsub3 CONNECTION 'host=unknown user=dvd password=pass12'
+  PUBLICATION testpub4 WITH (connect=false, slot_name=none, enabled=false,
+  create_slot=false, copy_data=false, binary=true, streaming=off,
+  synchronous_commit=local, two_phase=true, disable_on_error=true,
+  password_required=false, run_as_owner=true, origin=none, failover=true,
+  retain_dead_tuples=false, max_retention_duration=100);
+SELECT pg_get_subscription_ddl('regress_testsub3');
+
+-- Non-superusers and which don't have pg_create_subscription and/or
+-- pg_read_all_data permission can't get ddl
+SET SESSION AUTHORIZATION 'regress_createsub_role';
+SELECT pg_get_subscription_ddl('regress_TestSubddL2');
+RESET SESSION AUTHORIZATION;
+SET SESSION AUTHORIZATION 'regress_readalldata_role';
+SELECT pg_get_subscription_ddl('regress_TestSubddL2');
+RESET SESSION AUTHORIZATION;
+-- Administrators can change who can access this function
+GRANT pg_create_subscription TO regress_createsub_role;
+GRANT pg_read_all_data TO regress_readalldata_role;
+SET SESSION AUTHORIZATION 'regress_createsub_role';
+SELECT pg_get_subscription_ddl('regress_TestSubddL2');
+RESET SESSION AUTHORIZATION;
+SET SESSION AUTHORIZATION 'regress_readalldata_role';
+SELECT pg_get_subscription_ddl('regress_TestSubddL2');
+
+RESET SESSION AUTHORIZATION;
+REVOKE pg_create_subscription FROM regress_createsub_role;
+REVOKE pg_read_all_data FROM regress_readalldata_role;
+ALTER SUBSCRIPTION regress_testsub1 SET (slot_name=NONE);
+DROP SUBSCRIPTION regress_testsub1;
+ALTER SUBSCRIPTION "regress_TestSubddL2" SET (slot_name=NONE);
+DROP SUBSCRIPTION "regress_TestSubddL2";
+DROP SUBSCRIPTION regress_testsub3;
+DROP ROLE regress_createsub_role;
+DROP ROLE regress_readalldata_role;
-- 
2.43.0

From 4d33dd0d5e6228457d4512f126e496888c3528c3 Mon Sep 17 00:00:00 2001
From: Vaibhav Dalvi <vaibhav.dalvi@enterprisedb.com>
Date: Mon, 17 Nov 2025 14:10:22 +0000
Subject: [PATCH v6 1/1] Add pg_get_subscription_ddl() function

This new SQL-callable function returns the `CREATE SUBSCRIPTION`
statement for a given subscription name or oid.

Like `pg_dump`, the returned DDL explicitly sets `connect = false`.
This is because the original `CONNECT` option value is not cataloged,
and using `connect = false` ensures the DDL can be successfully executed
even if the remote publisher is unreachable.

This function is restricted to users that have the "pg_read_all_data" and/or
"pg_create_subscription" privilege.  This is a security measure because
subscription connection strings often contain sensitive information, such as
passwords.

Author: Vaibhav Dalvi <vaibhav.dalvi@enterprisedb.com>
Reviewers: Akshay Joshi, Nishant Sharma, Ian Barwick, Chao Li, Peter Smith & Alvaro Herrera
Discussion: https://www.postgresql.org/message-id/CA%2BvB%3DAGG0NoxWW%3D-947RBmba8Pzhj8j7op0Xkv8nLDzVMc2%3D7w%40mail.gmail.com
---
 doc/src/sgml/func/func-info.sgml           |  50 +++++
 src/backend/utils/adt/ruleutils.c          | 239 +++++++++++++++++++++
 src/include/catalog/pg_proc.dat            |   6 +
 src/test/regress/expected/subscription.out |  95 ++++++++
 src/test/regress/sql/subscription.sql      |  68 ++++++
 5 files changed, 458 insertions(+)

diff --git a/doc/src/sgml/func/func-info.sgml b/doc/src/sgml/func/func-info.sgml
index d4508114a48..469c0b02d90 100644
--- a/doc/src/sgml/func/func-info.sgml
+++ b/doc/src/sgml/func/func-info.sgml
@@ -3797,4 +3797,54 @@ acl      | {postgres=arwdDxtm/postgres,foo=r/postgres}
 
   </sect2>
 
+  <sect2 id="functions-get-object-ddl">
+   <title>Object DDL Functions</title>
+
+   <para>
+    The functions shown in <xref linkend="functions-get-object-ddl-table"/>
+    return the DDL statements for various database objects.
+    (This is a decompiled reconstruction, not the original text
+    of the command.)
+   </para>
+
+   <table id="functions-get-object-ddl-table">
+    <title>Object DDL Functions</title>
+    <tgroup cols="1">
+     <thead>
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        Function
+       </para>
+       <para>
+        Description
+       </para></entry>
+      </row>
+     </thead>
+
+     <tbody>
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>pg_get_subscription_ddl</primary>
+        </indexterm>
+        <function>pg_get_subscription_ddl</function> ( <parameter>subscription</parameter> <type>name</type> or <type>oid</type> )
+        <returnvalue>text</returnvalue>
+       </para>
+       <para>
+        Returns the <command>CREATE SUBSCRIPTION</command> command that would
+        create this subscription. The <literal>connect</literal> option set to
+        <literal>false</literal>.
+       </para>
+       <para>
+        This function is restricted to users that have the
+        <literal>pg_read_all_data</literal> and/or
+        <literal>pg_create_subscription</literal> privilege.
+       </para></entry>
+      </row>
+     </tbody>
+    </tgroup>
+   </table>
+
+  </sect2>
+
   </sect1>
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 556ab057e5a..46e3c295766 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -35,6 +35,7 @@
 #include "catalog/pg_partitioned_table.h"
 #include "catalog/pg_proc.h"
 #include "catalog/pg_statistic_ext.h"
+#include "catalog/pg_subscription.h"
 #include "catalog/pg_trigger.h"
 #include "catalog/pg_type.h"
 #include "commands/defrem.h"
@@ -57,6 +58,7 @@
 #include "rewrite/rewriteHandler.h"
 #include "rewrite/rewriteManip.h"
 #include "rewrite/rewriteSupport.h"
+#include "utils/acl.h"
 #include "utils/array.h"
 #include "utils/builtins.h"
 #include "utils/fmgroids.h"
@@ -546,6 +548,8 @@ static void get_json_table_nested_columns(TableFunc *tf, JsonTablePlan *plan,
 										  deparse_context *context,
 										  bool showimplicit,
 										  bool needcomma);
+static char *pg_get_subscription_string(const Oid suboid);
+static List *text_array_to_string_list(ArrayType *text_array);
 
 #define only_marker(rte)  ((rte)->inh ? "" : "ONLY ")
 
@@ -13743,3 +13747,238 @@ get_range_partbound_string(List *bound_datums)
 
 	return buf.data;
 }
+
+/*
+ * pg_get_subscription_string
+ *		Build CREATE SUBSCRIPTION statement for a subscription from its OID.
+ *
+ * This is internal version which helps pg_get_subscription_ddl_by_name() and
+ * pg_get_subscription_ddl_by_oid().
+ */
+static char *
+pg_get_subscription_string(const Oid suboid)
+{
+	Form_pg_subscription subForm;
+	StringInfoData pubnames,
+				   buf;
+	HeapTuple	tup;
+	char	   *conninfo;
+	List	   *publist;
+	Datum		datum;
+	bool		isnull;
+
+	/*
+	 * To prevent unprivileged users from initiating unauthorized network
+	 * connections, dumping subscription creation is restricted.  A user must
+	 * be specifically authorized (via the appropriate role privilege) to
+	 * create subscriptions and/or to read all data.
+	 */
+	if (!(has_privs_of_role(GetUserId(), ROLE_PG_CREATE_SUBSCRIPTION) ||
+		has_privs_of_role(GetUserId(), ROLE_PG_READ_ALL_DATA)))
+		ereport(ERROR,
+				(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+				 errmsg("permission denied to get the create subscription ddl"),
+				 errdetail("Only roles with privileges of the \"%s\" and/or \"%s\" role may view subscription DDL.",
+						   "pg_create_subscription", "pg_read_all_data")));
+
+	/* Look up the subscription in pg_subscription */
+	tup = SearchSysCache1(SUBSCRIPTIONOID, ObjectIdGetDatum(suboid));
+	if (!HeapTupleIsValid(tup))
+		ereport(ERROR,
+				(errcode(ERRCODE_UNDEFINED_OBJECT),
+				 errmsg("subscription with oid %d does not exist", suboid)));
+
+	/* Get subscription's details from its tuple */
+	subForm = (Form_pg_subscription) GETSTRUCT(tup);
+
+	initStringInfo(&buf);
+
+	/* Build the CREATE SUBSCRIPTION statement */
+	appendStringInfo(&buf, "CREATE SUBSCRIPTION %s ",
+					 quote_identifier(NameStr(subForm->subname)));
+
+	/* Get conninfo */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_subconninfo);
+	conninfo = TextDatumGetCString(datum);
+
+	/* Append connection info to the CREATE SUBSCRIPTION statement */
+	appendStringInfo(&buf, "CONNECTION \'%s\'", conninfo);
+
+	/* Build list of quoted publications and append them to query */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_subpublications);
+	publist = text_array_to_string_list(DatumGetArrayTypeP(datum));
+	initStringInfo(&pubnames);
+	GetPublicationsStr(publist, &pubnames, false);
+	appendStringInfo(&buf, " PUBLICATION %s", pubnames.data);
+
+	/*
+	 * Add options using WITH clause.  The 'connect' option value given at the
+	 * time of subscription creation is not available in the catalog.  When
+	 * creating a subscription, the remote host is not reachable or in an
+	 * unclear state, in that case, the subscription can be created using
+	 * 'connect = false' option.  This is what pg_dump uses.
+	 *
+	 * The status or value of the options 'create_slot' and 'copy_data' not
+	 * available in the catalog table.  We can use default values i.e. TRUE
+	 * for both.  This is what pg_dump uses.
+	 */
+	appendStringInfoString(&buf, " WITH (connect=false");
+
+	/* Get slotname */
+	datum = SysCacheGetAttr(SUBSCRIPTIONOID, tup,
+							Anum_pg_subscription_subslotname,
+							&isnull);
+	if (!isnull)
+		appendStringInfo(&buf, ", slot_name=%s",
+						 quote_literal_cstr(NameStr(*DatumGetName(datum))));
+	else
+	{
+		appendStringInfoString(&buf, ", slot_name=none");
+		/* Setting slot_name to none must set create_slot to false */
+		appendStringInfoString(&buf, ", create_slot=false");
+	}
+
+	/* Get enabled option */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_subenabled);
+	/* Setting 'slot_name' to none must set 'enabled' to false as well */
+	appendStringInfo(&buf, ", enabled=%s",
+					 (!DatumGetBool(datum) || isnull) ? "false" : "true");
+
+	/* Get binary option */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_subbinary);
+	appendStringInfo(&buf, ", binary=%s",
+					 DatumGetBool(datum) ? "true" : "false");
+
+	/* Get streaming option */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_substream);
+	if (DatumGetChar(datum) == LOGICALREP_STREAM_OFF)
+		appendStringInfoString(&buf, ", streaming=off");
+	else if (DatumGetChar(datum) == LOGICALREP_STREAM_ON)
+		appendStringInfoString(&buf, ", streaming=on");
+	else
+		appendStringInfoString(&buf, ", streaming=parallel");
+
+	/* Get sync commit option */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_subsynccommit);
+	appendStringInfo(&buf, ", synchronous_commit=%s",
+					 TextDatumGetCString(datum));
+
+	/* Get two-phase commit option */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_subtwophasestate);
+	appendStringInfo(&buf, ", two_phase=%s",
+					 DatumGetChar(datum) == LOGICALREP_TWOPHASE_STATE_DISABLED ? "off" : "on");
+
+	/* Disable on error? */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_subdisableonerr);
+	appendStringInfo(&buf, ", disable_on_error=%s",
+					 DatumGetBool(datum) ? "on" : "off");
+
+	/* Password required? */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_subpasswordrequired);
+	appendStringInfo(&buf, ", password_required=%s",
+					 DatumGetBool(datum) ? "on" : "off");
+
+	/* Run as owner? */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_subrunasowner);
+	appendStringInfo(&buf, ", run_as_owner=%s",
+					 DatumGetBool(datum) ? "on" : "off");
+
+	/* Get origin */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_suborigin);
+	appendStringInfo(&buf, ", origin=%s", TextDatumGetCString(datum));
+
+	/* Failover? */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_subfailover);
+	appendStringInfo(&buf, ", failover=%s",
+					 DatumGetBool(datum) ? "on" : "off");
+
+	/* Retain dead tuples? */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_subretaindeadtuples);
+	appendStringInfo(&buf, ", retain_dead_tuples=%s",
+					 DatumGetBool(datum) ? "on" : "off");
+
+	/* Max retention duration */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_submaxretention);
+	appendStringInfo(&buf, ", max_retention_duration=%d",
+					 DatumGetInt32(datum));
+
+	/* Finally close parenthesis and add semicolon to the statement */
+	appendStringInfoString(&buf, ");");
+
+	ReleaseSysCache(tup);
+
+	return buf.data;
+}
+
+/*
+ * pg_get_subscription_ddl_by_name
+ *		Get CREATE SUBSCRIPTION statement for a subscription.
+ *
+ * This takes name as parameter for pg_get_subscription_ddl().
+ */
+Datum
+pg_get_subscription_ddl_by_name(PG_FUNCTION_ARGS)
+{
+	Name		subname = PG_GETARG_NAME(0);
+	Oid			suboid;
+	char	   *ddl_stmt;
+
+	/* Get the OID of the subscription from its name */
+	suboid = get_subscription_oid(NameStr(*subname), false);
+
+	/* Get the CREATE SUBSCRIPTION DDL statement from its OID */
+	ddl_stmt = pg_get_subscription_string(suboid);
+
+	PG_RETURN_TEXT_P(string_to_text(ddl_stmt));
+}
+
+/*
+ * pg_get_subscription_ddl_by_oid
+ *		Get CREATE SUBSCRIPTION statement for a subscription.
+ *
+ * This takes oid as parameter for pg_get_subscription_ddl().
+ */
+Datum
+pg_get_subscription_ddl_by_oid(PG_FUNCTION_ARGS)
+{
+	Oid			suboid = PG_GETARG_OID(0);
+	char	   *ddl_stmt;
+
+	/* Get the CREATE SUBSCRIPTION DDL statement from its OID */
+	ddl_stmt = pg_get_subscription_string(suboid);
+
+	PG_RETURN_TEXT_P(string_to_text(ddl_stmt));
+}
+
+/*
+ * text_array_to_string_list
+ *		Convert text array to list of strings.
+ */
+static List *
+text_array_to_string_list(ArrayType *text_array)
+{
+	List	   *result = NIL;
+	Datum	   *elems;
+	int			nelems;
+
+	deconstruct_array_builtin(text_array, TEXTOID, &elems, NULL, &nelems);
+
+	for (int i = 0; i < nelems; i++)
+		result = lappend(result, makeString(TextDatumGetCString(elems[i])));
+
+	return result;
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 5cf9e12fcb9..cc6272b91af 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -3993,6 +3993,12 @@
 { oid => '1387', descr => 'constraint description',
   proname => 'pg_get_constraintdef', provolatile => 's', prorettype => 'text',
   proargtypes => 'oid', prosrc => 'pg_get_constraintdef' },
+{ oid => '8001', descr => 'get CREATE statement for subscription',
+  proname => 'pg_get_subscription_ddl', prorettype => 'text',
+  proargtypes => 'name', prosrc => 'pg_get_subscription_ddl_by_name' },
+{ oid => '8002', descr => 'get CREATE statement for subscription',
+  proname => 'pg_get_subscription_ddl', prorettype => 'text',
+  proargtypes => 'oid', prosrc => 'pg_get_subscription_ddl_by_oid' },
 { oid => '1716', descr => 'deparse an encoded expression',
   proname => 'pg_get_expr', provolatile => 's', prorettype => 'text',
   proargtypes => 'pg_node_tree oid', prosrc => 'pg_get_expr' },
diff --git a/src/test/regress/expected/subscription.out b/src/test/regress/expected/subscription.out
index 327d1e7731f..be6a9bff7f3 100644
--- a/src/test/regress/expected/subscription.out
+++ b/src/test/regress/expected/subscription.out
@@ -522,3 +522,98 @@ DROP ROLE regress_subscription_user;
 DROP ROLE regress_subscription_user2;
 DROP ROLE regress_subscription_user3;
 DROP ROLE regress_subscription_user_dummy;
+--
+-- Test pg_get_subscription_ddl() by creating subscriptions with various
+-- configurations and checking the DDL.
+--
+CREATE ROLE regress_createsub_role LOGIN;
+CREATE ROLE regress_readalldata_role LOGIN;
+-- see the pg_get_subscription_ddl output for a NULL and empty input
+SELECT pg_get_subscription_ddl('');
+ERROR:  subscription "" does not exist
+SELECT pg_get_subscription_ddl(NULL);
+ pg_get_subscription_ddl 
+-------------------------
+ 
+(1 row)
+
+-- Suppress WARNINGS when creating subscription
+SET client_min_messages = 'error';
+-- Create subscription with minimal options
+CREATE SUBSCRIPTION regress_testsub1 CONNECTION 'dbname=db_doesnotexist'
+  PUBLICATION testpub1 WITH (connect=false);
+RESET client_min_messages;
+-- Check that the subscription ddl is correctly created
+SELECT pg_get_subscription_ddl('regress_testsub1');
+                                                                                                                                                                                pg_get_subscription_ddl                                                                                                                                                                                
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE SUBSCRIPTION regress_testsub1 CONNECTION 'dbname=db_doesnotexist' PUBLICATION "testpub1" WITH (connect=false, slot_name='regress_testsub1', enabled=false, binary=false, streaming=parallel, synchronous_commit=off, two_phase=off, disable_on_error=off, password_required=on, run_as_owner=off, origin=any, failover=off, retain_dead_tuples=off, max_retention_duration=0);
+(1 row)
+
+-- Create subscription with more options
+SET client_min_messages = 'error';
+CREATE SUBSCRIPTION "regress_TestSub2" CONNECTION 'host=unknown user=dvd password=pass123'
+  PUBLICATION "testpub2", "TestPub3" WITH (connect=false, slot_name='slot1',
+  enabled=off);
+RESET client_min_messages;
+SELECT pg_get_subscription_ddl('regress_TestSub2');
+                                                                                                                                                                                         pg_get_subscription_ddl                                                                                                                                                                                          
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE SUBSCRIPTION "regress_TestSub2" CONNECTION 'host=unknown user=dvd password=pass123' PUBLICATION "testpub2", "TestPub3" WITH (connect=false, slot_name='slot1', enabled=false, binary=false, streaming=parallel, synchronous_commit=off, two_phase=off, disable_on_error=off, password_required=on, run_as_owner=off, origin=any, failover=off, retain_dead_tuples=off, max_retention_duration=0);
+(1 row)
+
+-- Create subscription with all options
+SET client_min_messages = 'error';
+CREATE SUBSCRIPTION regress_testsub3 CONNECTION 'host=unknown user=dvd password=pass12'
+  PUBLICATION testpub4 WITH (connect=false, slot_name=none, enabled=false,
+  create_slot=false, copy_data=false, binary=true, streaming=off,
+  synchronous_commit=local, two_phase=true, disable_on_error=true,
+  password_required=false, run_as_owner=true, origin=none, failover=true,
+  retain_dead_tuples=false, max_retention_duration=100);
+RESET client_min_messages;
+SELECT pg_get_subscription_ddl('regress_testsub3');
+                                                                                                                                                                                        pg_get_subscription_ddl                                                                                                                                                                                        
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE SUBSCRIPTION regress_testsub3 CONNECTION 'host=unknown user=dvd password=pass12' PUBLICATION "testpub4" WITH (connect=false, slot_name=none, create_slot=false, enabled=false, binary=true, streaming=off, synchronous_commit=local, two_phase=on, disable_on_error=on, password_required=off, run_as_owner=on, origin=none, failover=on, retain_dead_tuples=off, max_retention_duration=100);
+(1 row)
+
+-- Non-superusers without pg_create_subscription and/or pg_read_all_data
+-- permissions cannot retrieve the DDL.
+SET SESSION AUTHORIZATION 'regress_createsub_role';
+SELECT pg_get_subscription_ddl('regress_TestSub2');
+ERROR:  permission denied to get the create subscription ddl
+DETAIL:  Only roles with privileges of the "pg_create_subscription" and/or "pg_read_all_data" role may view subscription DDL.
+RESET SESSION AUTHORIZATION;
+SET SESSION AUTHORIZATION 'regress_readalldata_role';
+SELECT pg_get_subscription_ddl('regress_TestSub2');
+ERROR:  permission denied to get the create subscription ddl
+DETAIL:  Only roles with privileges of the "pg_create_subscription" and/or "pg_read_all_data" role may view subscription DDL.
+RESET SESSION AUTHORIZATION;
+-- Administrators can change who can access this function
+GRANT pg_create_subscription TO regress_createsub_role;
+GRANT pg_read_all_data TO regress_readalldata_role;
+SET SESSION AUTHORIZATION 'regress_createsub_role';
+SELECT pg_get_subscription_ddl('regress_TestSub2');
+                                                                                                                                                                                         pg_get_subscription_ddl                                                                                                                                                                                          
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE SUBSCRIPTION "regress_TestSub2" CONNECTION 'host=unknown user=dvd password=pass123' PUBLICATION "testpub2", "TestPub3" WITH (connect=false, slot_name='slot1', enabled=false, binary=false, streaming=parallel, synchronous_commit=off, two_phase=off, disable_on_error=off, password_required=on, run_as_owner=off, origin=any, failover=off, retain_dead_tuples=off, max_retention_duration=0);
+(1 row)
+
+RESET SESSION AUTHORIZATION;
+SET SESSION AUTHORIZATION 'regress_readalldata_role';
+SELECT pg_get_subscription_ddl('regress_TestSub2');
+                                                                                                                                                                                         pg_get_subscription_ddl                                                                                                                                                                                          
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE SUBSCRIPTION "regress_TestSub2" CONNECTION 'host=unknown user=dvd password=pass123' PUBLICATION "testpub2", "TestPub3" WITH (connect=false, slot_name='slot1', enabled=false, binary=false, streaming=parallel, synchronous_commit=off, two_phase=off, disable_on_error=off, password_required=on, run_as_owner=off, origin=any, failover=off, retain_dead_tuples=off, max_retention_duration=0);
+(1 row)
+
+RESET SESSION AUTHORIZATION;
+REVOKE pg_create_subscription FROM regress_createsub_role;
+REVOKE pg_read_all_data FROM regress_readalldata_role;
+ALTER SUBSCRIPTION regress_testsub1 SET (slot_name=NONE);
+DROP SUBSCRIPTION regress_testsub1;
+ALTER SUBSCRIPTION "regress_TestSub2" SET (slot_name=NONE);
+DROP SUBSCRIPTION "regress_TestSub2";
+DROP SUBSCRIPTION regress_testsub3;
+DROP ROLE regress_createsub_role;
+DROP ROLE regress_readalldata_role;
diff --git a/src/test/regress/sql/subscription.sql b/src/test/regress/sql/subscription.sql
index ef0c298d2df..4d1b95999cd 100644
--- a/src/test/regress/sql/subscription.sql
+++ b/src/test/regress/sql/subscription.sql
@@ -370,3 +370,71 @@ DROP ROLE regress_subscription_user;
 DROP ROLE regress_subscription_user2;
 DROP ROLE regress_subscription_user3;
 DROP ROLE regress_subscription_user_dummy;
+
+--
+-- Test pg_get_subscription_ddl() by creating subscriptions with various
+-- configurations and checking the DDL.
+--
+CREATE ROLE regress_createsub_role LOGIN;
+CREATE ROLE regress_readalldata_role LOGIN;
+
+-- see the pg_get_subscription_ddl output for a NULL and empty input
+SELECT pg_get_subscription_ddl('');
+SELECT pg_get_subscription_ddl(NULL);
+
+-- Suppress WARNINGS when creating subscription
+SET client_min_messages = 'error';
+-- Create subscription with minimal options
+CREATE SUBSCRIPTION regress_testsub1 CONNECTION 'dbname=db_doesnotexist'
+  PUBLICATION testpub1 WITH (connect=false);
+RESET client_min_messages;
+
+-- Check that the subscription ddl is correctly created
+SELECT pg_get_subscription_ddl('regress_testsub1');
+
+-- Create subscription with more options
+SET client_min_messages = 'error';
+CREATE SUBSCRIPTION "regress_TestSub2" CONNECTION 'host=unknown user=dvd password=pass123'
+  PUBLICATION "testpub2", "TestPub3" WITH (connect=false, slot_name='slot1',
+  enabled=off);
+RESET client_min_messages;
+SELECT pg_get_subscription_ddl('regress_TestSub2');
+
+-- Create subscription with all options
+SET client_min_messages = 'error';
+CREATE SUBSCRIPTION regress_testsub3 CONNECTION 'host=unknown user=dvd password=pass12'
+  PUBLICATION testpub4 WITH (connect=false, slot_name=none, enabled=false,
+  create_slot=false, copy_data=false, binary=true, streaming=off,
+  synchronous_commit=local, two_phase=true, disable_on_error=true,
+  password_required=false, run_as_owner=true, origin=none, failover=true,
+  retain_dead_tuples=false, max_retention_duration=100);
+RESET client_min_messages;
+SELECT pg_get_subscription_ddl('regress_testsub3');
+
+-- Non-superusers without pg_create_subscription and/or pg_read_all_data
+-- permissions cannot retrieve the DDL.
+SET SESSION AUTHORIZATION 'regress_createsub_role';
+SELECT pg_get_subscription_ddl('regress_TestSub2');
+RESET SESSION AUTHORIZATION;
+SET SESSION AUTHORIZATION 'regress_readalldata_role';
+SELECT pg_get_subscription_ddl('regress_TestSub2');
+RESET SESSION AUTHORIZATION;
+-- Administrators can change who can access this function
+GRANT pg_create_subscription TO regress_createsub_role;
+GRANT pg_read_all_data TO regress_readalldata_role;
+SET SESSION AUTHORIZATION 'regress_createsub_role';
+SELECT pg_get_subscription_ddl('regress_TestSub2');
+RESET SESSION AUTHORIZATION;
+SET SESSION AUTHORIZATION 'regress_readalldata_role';
+SELECT pg_get_subscription_ddl('regress_TestSub2');
+
+RESET SESSION AUTHORIZATION;
+REVOKE pg_create_subscription FROM regress_createsub_role;
+REVOKE pg_read_all_data FROM regress_readalldata_role;
+ALTER SUBSCRIPTION regress_testsub1 SET (slot_name=NONE);
+DROP SUBSCRIPTION regress_testsub1;
+ALTER SUBSCRIPTION "regress_TestSub2" SET (slot_name=NONE);
+DROP SUBSCRIPTION "regress_TestSub2";
+DROP SUBSCRIPTION regress_testsub3;
+DROP ROLE regress_createsub_role;
+DROP ROLE regress_readalldata_role;
-- 
2.43.0

From 97252adc9426673c01eec3b6bd54f699bc0ae60e Mon Sep 17 00:00:00 2001
From: Vaibhav Dalvi <vaibhav.dalvi@enterprisedb.com>
Date: Tue, 18 Nov 2025 05:36:03 +0000
Subject: [PATCH v7 1/1] Add pg_get_subscription_ddl() function

This new SQL-callable function returns the `CREATE SUBSCRIPTION`
statement for a given subscription name or oid.

Like `pg_dump`, the returned DDL explicitly sets `connect = false`.
This is because the original `CONNECT` option value is not cataloged,
and using `connect = false` ensures the DDL can be successfully executed
even if the remote publisher is unreachable.

This function is restricted to users that have the "pg_read_all_data" and/or
"pg_create_subscription" privilege.  This is a security measure because
subscription connection strings often contain sensitive information, such as
passwords.

Author: Vaibhav Dalvi <vaibhav.dalvi@enterprisedb.com>
Reviewers: Akshay Joshi, Nishant Sharma, Ian Barwick, Chao Li, Peter Smith & Alvaro Herrera
Discussion: https://www.postgresql.org/message-id/CA%2BvB%3DAGG0NoxWW%3D-947RBmba8Pzhj8j7op0Xkv8nLDzVMc2%3D7w%40mail.gmail.com
---
 doc/src/sgml/func/func-info.sgml           |  50 +++++
 src/backend/utils/adt/ruleutils.c          | 238 +++++++++++++++++++++
 src/include/catalog/pg_proc.dat            |   6 +
 src/test/regress/expected/subscription.out |  95 ++++++++
 src/test/regress/sql/subscription.sql      |  68 ++++++
 5 files changed, 457 insertions(+)

diff --git a/doc/src/sgml/func/func-info.sgml b/doc/src/sgml/func/func-info.sgml
index d4508114a48..469c0b02d90 100644
--- a/doc/src/sgml/func/func-info.sgml
+++ b/doc/src/sgml/func/func-info.sgml
@@ -3797,4 +3797,54 @@ acl      | {postgres=arwdDxtm/postgres,foo=r/postgres}
 
   </sect2>
 
+  <sect2 id="functions-get-object-ddl">
+   <title>Object DDL Functions</title>
+
+   <para>
+    The functions shown in <xref linkend="functions-get-object-ddl-table"/>
+    return the DDL statements for various database objects.
+    (This is a decompiled reconstruction, not the original text
+    of the command.)
+   </para>
+
+   <table id="functions-get-object-ddl-table">
+    <title>Object DDL Functions</title>
+    <tgroup cols="1">
+     <thead>
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        Function
+       </para>
+       <para>
+        Description
+       </para></entry>
+      </row>
+     </thead>
+
+     <tbody>
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>pg_get_subscription_ddl</primary>
+        </indexterm>
+        <function>pg_get_subscription_ddl</function> ( <parameter>subscription</parameter> <type>name</type> or <type>oid</type> )
+        <returnvalue>text</returnvalue>
+       </para>
+       <para>
+        Returns the <command>CREATE SUBSCRIPTION</command> command that would
+        create this subscription. The <literal>connect</literal> option set to
+        <literal>false</literal>.
+       </para>
+       <para>
+        This function is restricted to users that have the
+        <literal>pg_read_all_data</literal> and/or
+        <literal>pg_create_subscription</literal> privilege.
+       </para></entry>
+      </row>
+     </tbody>
+    </tgroup>
+   </table>
+
+  </sect2>
+
   </sect1>
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 556ab057e5a..54e6c0dd831 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -35,6 +35,7 @@
 #include "catalog/pg_partitioned_table.h"
 #include "catalog/pg_proc.h"
 #include "catalog/pg_statistic_ext.h"
+#include "catalog/pg_subscription.h"
 #include "catalog/pg_trigger.h"
 #include "catalog/pg_type.h"
 #include "commands/defrem.h"
@@ -57,6 +58,7 @@
 #include "rewrite/rewriteHandler.h"
 #include "rewrite/rewriteManip.h"
 #include "rewrite/rewriteSupport.h"
+#include "utils/acl.h"
 #include "utils/array.h"
 #include "utils/builtins.h"
 #include "utils/fmgroids.h"
@@ -546,6 +548,8 @@ static void get_json_table_nested_columns(TableFunc *tf, JsonTablePlan *plan,
 										  deparse_context *context,
 										  bool showimplicit,
 										  bool needcomma);
+static char *pg_get_subscription_string(const Oid suboid);
+static List *text_array_to_string_list(ArrayType *text_array);
 
 #define only_marker(rte)  ((rte)->inh ? "" : "ONLY ")
 
@@ -13743,3 +13747,237 @@ get_range_partbound_string(List *bound_datums)
 
 	return buf.data;
 }
+
+/*
+ * pg_get_subscription_string
+ *		Build CREATE SUBSCRIPTION statement for a subscription from its OID.
+ *
+ * Helper for pg_get_subscription_ddl_by_name() and
+ * pg_get_subscription_ddl_by_oid().
+ */
+static char *
+pg_get_subscription_string(const Oid suboid)
+{
+	Form_pg_subscription subForm;
+	StringInfoData pubnames,
+				   buf;
+	HeapTuple	tup;
+	char	   *conninfo;
+	List	   *publist;
+	Datum		datum;
+	bool		isnull;
+
+	/*
+	 * To prevent unprivileged users from initiating unauthorized network
+	 * connections, dumping subscription creation is restricted.  A user must
+	 * be specifically authorized (via the appropriate role privilege) to
+	 * create subscriptions and/or to read all data.
+	 */
+	if (!(has_privs_of_role(GetUserId(), ROLE_PG_CREATE_SUBSCRIPTION) ||
+		has_privs_of_role(GetUserId(), ROLE_PG_READ_ALL_DATA)))
+		ereport(ERROR,
+				(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+				 errmsg("permission denied to get the create subscription ddl"),
+				 errdetail("Only roles with privileges of the \"%s\" and/or \"%s\" role may view subscription DDL.",
+						   "pg_create_subscription", "pg_read_all_data")));
+
+	/* Look up the subscription in pg_subscription */
+	tup = SearchSysCache1(SUBSCRIPTIONOID, ObjectIdGetDatum(suboid));
+	if (!HeapTupleIsValid(tup))
+		ereport(ERROR,
+				(errcode(ERRCODE_UNDEFINED_OBJECT),
+				 errmsg("subscription with oid %d does not exist", suboid)));
+
+	/* Get subscription's details from its tuple */
+	subForm = (Form_pg_subscription) GETSTRUCT(tup);
+
+	initStringInfo(&buf);
+
+	/* Build the CREATE SUBSCRIPTION statement */
+	appendStringInfo(&buf, "CREATE SUBSCRIPTION %s ",
+					 quote_identifier(NameStr(subForm->subname)));
+
+	/* Get connection information */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_subconninfo);
+	conninfo = TextDatumGetCString(datum);
+
+	/* Append connection info to the CREATE SUBSCRIPTION statement */
+	appendStringInfo(&buf, "CONNECTION \'%s\'", conninfo);
+
+	/* Build list of quoted publications and append them to query */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_subpublications);
+	publist = text_array_to_string_list(DatumGetArrayTypeP(datum));
+	initStringInfo(&pubnames);
+	GetPublicationsStr(publist, &pubnames, false);
+	appendStringInfo(&buf, " PUBLICATION %s", pubnames.data);
+
+	/*
+	 * Add options using WITH clause.  The 'connect' option value given at the
+	 * time of subscription creation is not available in the catalog.  When
+	 * creating a subscription, the remote host is not reachable or in an
+	 * unclear state, in that case, the subscription can be created using
+	 * 'connect = false' option.  This is what pg_dump uses.
+	 *
+	 * The status or value of the options 'create_slot' and 'copy_data' not
+	 * available in the catalog table.  We can use default values i.e. TRUE
+	 * for both.  This is what pg_dump uses.
+	 */
+	appendStringInfoString(&buf, " WITH (connect=false");
+
+	/* Get enabled option */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_subenabled);
+	/* Setting 'slot_name' to none must set 'enabled' to false as well */
+	appendStringInfo(&buf, ", enabled=%s",
+					 (!DatumGetBool(datum) || isnull) ? "false" : "true");
+
+	/* Get slotname */
+	datum = SysCacheGetAttr(SUBSCRIPTIONOID, tup,
+							Anum_pg_subscription_subslotname,
+							&isnull);
+	if (!isnull)
+		appendStringInfo(&buf, ", slot_name=%s",
+						 quote_literal_cstr(NameStr(*DatumGetName(datum))));
+	else
+	{
+		appendStringInfoString(&buf, ", slot_name=none");
+		/* Setting slot_name to none must set create_slot to false */
+		appendStringInfoString(&buf, ", create_slot=false");
+	}
+
+	/* Get binary option */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_subbinary);
+	appendStringInfo(&buf, ", binary=%s",
+					 DatumGetBool(datum) ? "true" : "false");
+
+	/* Get streaming option */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_substream);
+	if (DatumGetChar(datum) == LOGICALREP_STREAM_OFF)
+		appendStringInfoString(&buf, ", streaming=false");
+	else if (DatumGetChar(datum) == LOGICALREP_STREAM_ON)
+		appendStringInfoString(&buf, ", streaming=true");
+	else
+		appendStringInfoString(&buf, ", streaming=parallel");
+
+	/* Get sync commit option */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_subsynccommit);
+	appendStringInfo(&buf, ", synchronous_commit=%s",
+					 TextDatumGetCString(datum));
+
+	/* Get two-phase commit option */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_subtwophasestate);
+	appendStringInfo(&buf, ", two_phase=%s",
+					 DatumGetChar(datum) == LOGICALREP_TWOPHASE_STATE_DISABLED ? "false" : "true");
+
+	/* Get disable on error option */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_subdisableonerr);
+	appendStringInfo(&buf, ", disable_on_error=%s",
+					 DatumGetBool(datum) ? "true" : "false");
+
+	/* Get password required option */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_subpasswordrequired);
+	appendStringInfo(&buf, ", password_required=%s",
+					 DatumGetBool(datum) ? "true" : "false");
+
+	/* Get run as owner option */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_subrunasowner);
+	appendStringInfo(&buf, ", run_as_owner=%s",
+					 DatumGetBool(datum) ? "true" : "false");
+
+	/* Get origin */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_suborigin);
+	appendStringInfo(&buf, ", origin=%s", TextDatumGetCString(datum));
+
+	/* Get failover option */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_subfailover);
+	appendStringInfo(&buf, ", failover=%s",
+					 DatumGetBool(datum) ? "true" : "false");
+
+	/* Get retain dead tuples option */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_subretaindeadtuples);
+	appendStringInfo(&buf, ", retain_dead_tuples=%s",
+					 DatumGetBool(datum) ? "true" : "false");
+
+	/* Get max retention duration */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_submaxretention);
+	appendStringInfo(&buf, ", max_retention_duration=%d",
+					 DatumGetInt32(datum));
+
+	appendStringInfoString(&buf, ");");
+
+	ReleaseSysCache(tup);
+
+	return buf.data;
+}
+
+/*
+ * pg_get_subscription_ddl_by_name
+ *		Get CREATE SUBSCRIPTION statement for a subscription.
+ *
+ * This takes name as parameter for pg_get_subscription_ddl().
+ */
+Datum
+pg_get_subscription_ddl_by_name(PG_FUNCTION_ARGS)
+{
+	Name		subname = PG_GETARG_NAME(0);
+	Oid			suboid;
+	char	   *ddl_stmt;
+
+	/* Get the OID of the subscription from its name */
+	suboid = get_subscription_oid(NameStr(*subname), false);
+
+	/* Get the CREATE SUBSCRIPTION DDL statement from its OID */
+	ddl_stmt = pg_get_subscription_string(suboid);
+
+	PG_RETURN_TEXT_P(string_to_text(ddl_stmt));
+}
+
+/*
+ * pg_get_subscription_ddl_by_oid
+ *		Get CREATE SUBSCRIPTION statement for a subscription.
+ *
+ * This takes oid as parameter for pg_get_subscription_ddl().
+ */
+Datum
+pg_get_subscription_ddl_by_oid(PG_FUNCTION_ARGS)
+{
+	Oid			suboid = PG_GETARG_OID(0);
+	char	   *ddl_stmt;
+
+	/* Get the CREATE SUBSCRIPTION DDL statement from its OID */
+	ddl_stmt = pg_get_subscription_string(suboid);
+
+	PG_RETURN_TEXT_P(string_to_text(ddl_stmt));
+}
+
+/*
+ * text_array_to_string_list
+ *		Convert text array to list of strings.
+ */
+static List *
+text_array_to_string_list(ArrayType *text_array)
+{
+	List	   *result = NIL;
+	Datum	   *elems;
+	int			nelems;
+
+	deconstruct_array_builtin(text_array, TEXTOID, &elems, NULL, &nelems);
+
+	for (int i = 0; i < nelems; i++)
+		result = lappend(result, makeString(TextDatumGetCString(elems[i])));
+
+	return result;
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index aaadfd8c748..7411bf611ca 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -3993,6 +3993,12 @@
 { oid => '1387', descr => 'constraint description',
   proname => 'pg_get_constraintdef', provolatile => 's', prorettype => 'text',
   proargtypes => 'oid', prosrc => 'pg_get_constraintdef' },
+{ oid => '8001', descr => 'get CREATE statement for subscription',
+  proname => 'pg_get_subscription_ddl', prorettype => 'text',
+  proargtypes => 'name', prosrc => 'pg_get_subscription_ddl_by_name' },
+{ oid => '8002', descr => 'get CREATE statement for subscription',
+  proname => 'pg_get_subscription_ddl', prorettype => 'text',
+  proargtypes => 'oid', prosrc => 'pg_get_subscription_ddl_by_oid' },
 { oid => '1716', descr => 'deparse an encoded expression',
   proname => 'pg_get_expr', provolatile => 's', prorettype => 'text',
   proargtypes => 'pg_node_tree oid', prosrc => 'pg_get_expr' },
diff --git a/src/test/regress/expected/subscription.out b/src/test/regress/expected/subscription.out
index 327d1e7731f..6f6b5f115cc 100644
--- a/src/test/regress/expected/subscription.out
+++ b/src/test/regress/expected/subscription.out
@@ -522,3 +522,98 @@ DROP ROLE regress_subscription_user;
 DROP ROLE regress_subscription_user2;
 DROP ROLE regress_subscription_user3;
 DROP ROLE regress_subscription_user_dummy;
+--
+-- Test pg_get_subscription_ddl() by creating subscriptions with various
+-- configurations and checking the DDL.
+--
+CREATE ROLE regress_createsub_role LOGIN;
+CREATE ROLE regress_readalldata_role LOGIN;
+-- Check the pg_get_subscription_ddl output for a NULL and empty input
+SELECT pg_get_subscription_ddl('');
+ERROR:  subscription "" does not exist
+SELECT pg_get_subscription_ddl(NULL);
+ pg_get_subscription_ddl 
+-------------------------
+ 
+(1 row)
+
+-- Suppress WARNINGS when creating subscription
+SET client_min_messages = 'error';
+-- Create subscription with minimal options
+CREATE SUBSCRIPTION regress_testsub1 CONNECTION 'dbname=db_doesnotexist'
+  PUBLICATION testpub1 WITH (connect=false);
+RESET client_min_messages;
+-- Check that the subscription DDL is correctly created
+SELECT pg_get_subscription_ddl('regress_testsub1');
+                                                                                                                                                                                      pg_get_subscription_ddl                                                                                                                                                                                      
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE SUBSCRIPTION regress_testsub1 CONNECTION 'dbname=db_doesnotexist' PUBLICATION "testpub1" WITH (connect=false, enabled=false, slot_name='regress_testsub1', binary=false, streaming=parallel, synchronous_commit=off, two_phase=false, disable_on_error=false, password_required=true, run_as_owner=false, origin=any, failover=false, retain_dead_tuples=false, max_retention_duration=0);
+(1 row)
+
+-- Create subscription with more options
+SET client_min_messages = 'error';
+CREATE SUBSCRIPTION "regress_TestSub2" CONNECTION 'host=unknown user=dvd password=pass123'
+  PUBLICATION "testpub2", "TestPub3" WITH (connect=false, slot_name='slot1',
+  enabled=off);
+RESET client_min_messages;
+SELECT pg_get_subscription_ddl('regress_TestSub2');
+                                                                                                                                                                                               pg_get_subscription_ddl                                                                                                                                                                                                
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE SUBSCRIPTION "regress_TestSub2" CONNECTION 'host=unknown user=dvd password=pass123' PUBLICATION "testpub2", "TestPub3" WITH (connect=false, enabled=false, slot_name='slot1', binary=false, streaming=parallel, synchronous_commit=off, two_phase=false, disable_on_error=false, password_required=true, run_as_owner=false, origin=any, failover=false, retain_dead_tuples=false, max_retention_duration=0);
+(1 row)
+
+-- Create subscription with all options
+SET client_min_messages = 'error';
+CREATE SUBSCRIPTION regress_testsub3 CONNECTION 'host=unknown user=dvd password=pass12'
+  PUBLICATION testpub4 WITH (connect=false, slot_name=none, enabled=false,
+  create_slot=false, copy_data=false, binary=true, streaming=off,
+  synchronous_commit=local, two_phase=true, disable_on_error=true,
+  password_required=false, run_as_owner=true, origin=none, failover=true,
+  retain_dead_tuples=false, max_retention_duration=100);
+RESET client_min_messages;
+SELECT pg_get_subscription_ddl('regress_testsub3');
+                                                                                                                                                                                               pg_get_subscription_ddl                                                                                                                                                                                               
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE SUBSCRIPTION regress_testsub3 CONNECTION 'host=unknown user=dvd password=pass12' PUBLICATION "testpub4" WITH (connect=false, enabled=false, slot_name=none, create_slot=false, binary=true, streaming=false, synchronous_commit=local, two_phase=true, disable_on_error=true, password_required=false, run_as_owner=true, origin=none, failover=true, retain_dead_tuples=false, max_retention_duration=100);
+(1 row)
+
+-- Non-superusers without pg_create_subscription and/or pg_read_all_data
+-- permissions cannot retrieve the DDL.
+SET SESSION AUTHORIZATION 'regress_createsub_role';
+SELECT pg_get_subscription_ddl('regress_TestSub2');
+ERROR:  permission denied to get the create subscription ddl
+DETAIL:  Only roles with privileges of the "pg_create_subscription" and/or "pg_read_all_data" role may view subscription DDL.
+RESET SESSION AUTHORIZATION;
+SET SESSION AUTHORIZATION 'regress_readalldata_role';
+SELECT pg_get_subscription_ddl('regress_TestSub2');
+ERROR:  permission denied to get the create subscription ddl
+DETAIL:  Only roles with privileges of the "pg_create_subscription" and/or "pg_read_all_data" role may view subscription DDL.
+RESET SESSION AUTHORIZATION;
+-- Administrators can change who can access this function
+GRANT pg_create_subscription TO regress_createsub_role;
+GRANT pg_read_all_data TO regress_readalldata_role;
+SET SESSION AUTHORIZATION 'regress_createsub_role';
+SELECT pg_get_subscription_ddl('regress_TestSub2');
+                                                                                                                                                                                               pg_get_subscription_ddl                                                                                                                                                                                                
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE SUBSCRIPTION "regress_TestSub2" CONNECTION 'host=unknown user=dvd password=pass123' PUBLICATION "testpub2", "TestPub3" WITH (connect=false, enabled=false, slot_name='slot1', binary=false, streaming=parallel, synchronous_commit=off, two_phase=false, disable_on_error=false, password_required=true, run_as_owner=false, origin=any, failover=false, retain_dead_tuples=false, max_retention_duration=0);
+(1 row)
+
+RESET SESSION AUTHORIZATION;
+SET SESSION AUTHORIZATION 'regress_readalldata_role';
+SELECT pg_get_subscription_ddl('regress_TestSub2');
+                                                                                                                                                                                               pg_get_subscription_ddl                                                                                                                                                                                                
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE SUBSCRIPTION "regress_TestSub2" CONNECTION 'host=unknown user=dvd password=pass123' PUBLICATION "testpub2", "TestPub3" WITH (connect=false, enabled=false, slot_name='slot1', binary=false, streaming=parallel, synchronous_commit=off, two_phase=false, disable_on_error=false, password_required=true, run_as_owner=false, origin=any, failover=false, retain_dead_tuples=false, max_retention_duration=0);
+(1 row)
+
+RESET SESSION AUTHORIZATION;
+REVOKE pg_create_subscription FROM regress_createsub_role;
+REVOKE pg_read_all_data FROM regress_readalldata_role;
+ALTER SUBSCRIPTION regress_testsub1 SET (slot_name=NONE);
+DROP SUBSCRIPTION regress_testsub1;
+ALTER SUBSCRIPTION "regress_TestSub2" SET (slot_name=NONE);
+DROP SUBSCRIPTION "regress_TestSub2";
+DROP SUBSCRIPTION regress_testsub3;
+DROP ROLE regress_createsub_role;
+DROP ROLE regress_readalldata_role;
diff --git a/src/test/regress/sql/subscription.sql b/src/test/regress/sql/subscription.sql
index ef0c298d2df..149c0e06cd3 100644
--- a/src/test/regress/sql/subscription.sql
+++ b/src/test/regress/sql/subscription.sql
@@ -370,3 +370,71 @@ DROP ROLE regress_subscription_user;
 DROP ROLE regress_subscription_user2;
 DROP ROLE regress_subscription_user3;
 DROP ROLE regress_subscription_user_dummy;
+
+--
+-- Test pg_get_subscription_ddl() by creating subscriptions with various
+-- configurations and checking the DDL.
+--
+CREATE ROLE regress_createsub_role LOGIN;
+CREATE ROLE regress_readalldata_role LOGIN;
+
+-- Check the pg_get_subscription_ddl output for a NULL and empty input
+SELECT pg_get_subscription_ddl('');
+SELECT pg_get_subscription_ddl(NULL);
+
+-- Suppress WARNINGS when creating subscription
+SET client_min_messages = 'error';
+-- Create subscription with minimal options
+CREATE SUBSCRIPTION regress_testsub1 CONNECTION 'dbname=db_doesnotexist'
+  PUBLICATION testpub1 WITH (connect=false);
+RESET client_min_messages;
+
+-- Check that the subscription DDL is correctly created
+SELECT pg_get_subscription_ddl('regress_testsub1');
+
+-- Create subscription with more options
+SET client_min_messages = 'error';
+CREATE SUBSCRIPTION "regress_TestSub2" CONNECTION 'host=unknown user=dvd password=pass123'
+  PUBLICATION "testpub2", "TestPub3" WITH (connect=false, slot_name='slot1',
+  enabled=off);
+RESET client_min_messages;
+SELECT pg_get_subscription_ddl('regress_TestSub2');
+
+-- Create subscription with all options
+SET client_min_messages = 'error';
+CREATE SUBSCRIPTION regress_testsub3 CONNECTION 'host=unknown user=dvd password=pass12'
+  PUBLICATION testpub4 WITH (connect=false, slot_name=none, enabled=false,
+  create_slot=false, copy_data=false, binary=true, streaming=off,
+  synchronous_commit=local, two_phase=true, disable_on_error=true,
+  password_required=false, run_as_owner=true, origin=none, failover=true,
+  retain_dead_tuples=false, max_retention_duration=100);
+RESET client_min_messages;
+SELECT pg_get_subscription_ddl('regress_testsub3');
+
+-- Non-superusers without pg_create_subscription and/or pg_read_all_data
+-- permissions cannot retrieve the DDL.
+SET SESSION AUTHORIZATION 'regress_createsub_role';
+SELECT pg_get_subscription_ddl('regress_TestSub2');
+RESET SESSION AUTHORIZATION;
+SET SESSION AUTHORIZATION 'regress_readalldata_role';
+SELECT pg_get_subscription_ddl('regress_TestSub2');
+RESET SESSION AUTHORIZATION;
+-- Administrators can change who can access this function
+GRANT pg_create_subscription TO regress_createsub_role;
+GRANT pg_read_all_data TO regress_readalldata_role;
+SET SESSION AUTHORIZATION 'regress_createsub_role';
+SELECT pg_get_subscription_ddl('regress_TestSub2');
+RESET SESSION AUTHORIZATION;
+SET SESSION AUTHORIZATION 'regress_readalldata_role';
+SELECT pg_get_subscription_ddl('regress_TestSub2');
+
+RESET SESSION AUTHORIZATION;
+REVOKE pg_create_subscription FROM regress_createsub_role;
+REVOKE pg_read_all_data FROM regress_readalldata_role;
+ALTER SUBSCRIPTION regress_testsub1 SET (slot_name=NONE);
+DROP SUBSCRIPTION regress_testsub1;
+ALTER SUBSCRIPTION "regress_TestSub2" SET (slot_name=NONE);
+DROP SUBSCRIPTION "regress_TestSub2";
+DROP SUBSCRIPTION regress_testsub3;
+DROP ROLE regress_createsub_role;
+DROP ROLE regress_readalldata_role;
-- 
2.43.0

From d761e1f438c1e76b307de8c063f7f71e5c577de4 Mon Sep 17 00:00:00 2001
From: Vaibhav Dalvi <vaibhav.dalvi@enterprisedb.com>
Date: Tue, 18 Nov 2025 11:43:30 +0000
Subject: [PATCH v8 1/1] Add pg_get_subscription_ddl() function

This new SQL-callable function returns the `CREATE SUBSCRIPTION`
statement for a given subscription name or oid.

Like `pg_dump`, the returned DDL explicitly sets `connect = false`.
This is because the original `CONNECT` option value is not cataloged,
and using `connect = false` ensures the DDL can be successfully executed
even if the remote publisher is unreachable.

This function is restricted to users that have the "pg_read_all_data" and/or
"pg_create_subscription" privilege.  This is a security measure because
subscription connection strings often contain sensitive information, such as
passwords.

Author: Vaibhav Dalvi <vaibhav.dalvi@enterprisedb.com>
Reviewers: Akshay Joshi, Nishant Sharma, Ian Barwick, Chao Li, Peter Smith & Alvaro Herrera
Discussion: https://www.postgresql.org/message-id/CA%2BvB%3DAGG0NoxWW%3D-947RBmba8Pzhj8j7op0Xkv8nLDzVMc2%3D7w%40mail.gmail.com
---
 doc/src/sgml/func/func-info.sgml           |  50 +++++
 src/backend/utils/adt/ruleutils.c          | 239 +++++++++++++++++++++
 src/include/catalog/pg_proc.dat            |   6 +
 src/test/regress/expected/subscription.out |  95 ++++++++
 src/test/regress/sql/subscription.sql      |  68 ++++++
 5 files changed, 458 insertions(+)

diff --git a/doc/src/sgml/func/func-info.sgml b/doc/src/sgml/func/func-info.sgml
index d4508114a48..469c0b02d90 100644
--- a/doc/src/sgml/func/func-info.sgml
+++ b/doc/src/sgml/func/func-info.sgml
@@ -3797,4 +3797,54 @@ acl      | {postgres=arwdDxtm/postgres,foo=r/postgres}
 
   </sect2>
 
+  <sect2 id="functions-get-object-ddl">
+   <title>Object DDL Functions</title>
+
+   <para>
+    The functions shown in <xref linkend="functions-get-object-ddl-table"/>
+    return the DDL statements for various database objects.
+    (This is a decompiled reconstruction, not the original text
+    of the command.)
+   </para>
+
+   <table id="functions-get-object-ddl-table">
+    <title>Object DDL Functions</title>
+    <tgroup cols="1">
+     <thead>
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        Function
+       </para>
+       <para>
+        Description
+       </para></entry>
+      </row>
+     </thead>
+
+     <tbody>
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>pg_get_subscription_ddl</primary>
+        </indexterm>
+        <function>pg_get_subscription_ddl</function> ( <parameter>subscription</parameter> <type>name</type> or <type>oid</type> )
+        <returnvalue>text</returnvalue>
+       </para>
+       <para>
+        Returns the <command>CREATE SUBSCRIPTION</command> command that would
+        create this subscription. The <literal>connect</literal> option set to
+        <literal>false</literal>.
+       </para>
+       <para>
+        This function is restricted to users that have the
+        <literal>pg_read_all_data</literal> and/or
+        <literal>pg_create_subscription</literal> privilege.
+       </para></entry>
+      </row>
+     </tbody>
+    </tgroup>
+   </table>
+
+  </sect2>
+
   </sect1>
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 556ab057e5a..f416872565c 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -35,6 +35,7 @@
 #include "catalog/pg_partitioned_table.h"
 #include "catalog/pg_proc.h"
 #include "catalog/pg_statistic_ext.h"
+#include "catalog/pg_subscription.h"
 #include "catalog/pg_trigger.h"
 #include "catalog/pg_type.h"
 #include "commands/defrem.h"
@@ -57,6 +58,7 @@
 #include "rewrite/rewriteHandler.h"
 #include "rewrite/rewriteManip.h"
 #include "rewrite/rewriteSupport.h"
+#include "utils/acl.h"
 #include "utils/array.h"
 #include "utils/builtins.h"
 #include "utils/fmgroids.h"
@@ -546,6 +548,8 @@ static void get_json_table_nested_columns(TableFunc *tf, JsonTablePlan *plan,
 										  deparse_context *context,
 										  bool showimplicit,
 										  bool needcomma);
+static char *pg_get_subscription_string(const Oid suboid);
+static List *text_array_to_string_list(ArrayType *text_array);
 
 #define only_marker(rte)  ((rte)->inh ? "" : "ONLY ")
 
@@ -13743,3 +13747,238 @@ get_range_partbound_string(List *bound_datums)
 
 	return buf.data;
 }
+
+/*
+ * pg_get_subscription_string
+ *		Build CREATE SUBSCRIPTION statement for a subscription from its OID.
+ *
+ * Helper for pg_get_subscription_ddl_by_name() and
+ * pg_get_subscription_ddl_by_oid().
+ */
+static char *
+pg_get_subscription_string(const Oid suboid)
+{
+	Form_pg_subscription subForm;
+	StringInfoData pubnames,
+				   buf;
+	HeapTuple	tup;
+	char	   *conninfo;
+	List	   *publist;
+	Datum		datum;
+	bool		isnull;
+
+	/*
+	 * To prevent unprivileged users from initiating unauthorized network
+	 * connections, dumping subscription creation is restricted.  A user must
+	 * be specifically authorized (via the appropriate role privilege) to
+	 * create subscriptions and/or to read all data.
+	 */
+	if (!(has_privs_of_role(GetUserId(), ROLE_PG_CREATE_SUBSCRIPTION) ||
+		has_privs_of_role(GetUserId(), ROLE_PG_READ_ALL_DATA)))
+		ereport(ERROR,
+				(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+				 errmsg("permission denied to get the create subscription ddl"),
+				 errdetail("Only roles with privileges of the \"%s\" and/or \"%s\" role may view subscription DDL.",
+						   "pg_create_subscription", "pg_read_all_data")));
+
+	/* Look up the subscription in pg_subscription */
+	tup = SearchSysCache1(SUBSCRIPTIONOID, ObjectIdGetDatum(suboid));
+	if (!HeapTupleIsValid(tup))
+		ereport(ERROR,
+				(errcode(ERRCODE_UNDEFINED_OBJECT),
+				 errmsg("subscription with oid %d does not exist", suboid)));
+
+	/* Get subscription's details from its tuple */
+	subForm = (Form_pg_subscription) GETSTRUCT(tup);
+
+	initStringInfo(&buf);
+
+	/* Build the CREATE SUBSCRIPTION statement */
+	appendStringInfo(&buf, "CREATE SUBSCRIPTION %s ",
+					 quote_identifier(NameStr(subForm->subname)));
+
+	/* Get connection information */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_subconninfo);
+	conninfo = TextDatumGetCString(datum);
+
+	/* Append connection info to the CREATE SUBSCRIPTION statement */
+	appendStringInfo(&buf, "CONNECTION \'%s\'", conninfo);
+
+	/* Build list of quoted publications and append them to query */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_subpublications);
+	publist = text_array_to_string_list(DatumGetArrayTypeP(datum));
+	initStringInfo(&pubnames);
+	GetPublicationsStr(publist, &pubnames, false);
+	appendStringInfo(&buf, " PUBLICATION %s", pubnames.data);
+
+	/*
+	 * Add options using WITH clause.  The 'connect' option value given at the
+	 * time of subscription creation is not available in the catalog.  When
+	 * creating a subscription, the remote host is not reachable or in an
+	 * unclear state, in that case, the subscription can be created using
+	 * 'connect = false' option.  This is what pg_dump uses.
+	 *
+	 * The status or value of the options 'create_slot' and 'copy_data' not
+	 * available in the catalog table.  We can use default values i.e. TRUE
+	 * for both.  This is what pg_dump uses.
+	 */
+	appendStringInfoString(&buf, " WITH (connect=false");
+
+
+	/* Get slotname */
+	datum = SysCacheGetAttr(SUBSCRIPTIONOID, tup,
+							Anum_pg_subscription_subslotname,
+							&isnull);
+	if (!isnull)
+		appendStringInfo(&buf, ", slot_name=%s",
+						 quote_literal_cstr(NameStr(*DatumGetName(datum))));
+	else
+	{
+		appendStringInfoString(&buf, ", slot_name=none");
+		/* Setting slot_name to none must set create_slot to false */
+		appendStringInfoString(&buf, ", create_slot=false");
+	}
+
+	/* Get enabled option */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_subenabled);
+	/* Setting 'slot_name' to none must set 'enabled' to false as well */
+	appendStringInfo(&buf, ", enabled=%s",
+					 (DatumGetBool(datum) && !isnull) ? "true" : "false");
+
+	/* Get binary option */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_subbinary);
+	appendStringInfo(&buf, ", binary=%s",
+					 DatumGetBool(datum) ? "true" : "false");
+
+	/* Get streaming option */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_substream);
+	if (DatumGetChar(datum) == LOGICALREP_STREAM_ON)
+		appendStringInfoString(&buf, ", streaming=on");
+	else if (DatumGetChar(datum) == LOGICALREP_STREAM_OFF)
+		appendStringInfoString(&buf, ", streaming=off");
+	else
+		appendStringInfoString(&buf, ", streaming=parallel");
+
+	/* Get sync commit option */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_subsynccommit);
+	appendStringInfo(&buf, ", synchronous_commit=%s",
+					 TextDatumGetCString(datum));
+
+	/* Get two-phase commit option */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_subtwophasestate);
+	appendStringInfo(&buf, ", two_phase=%s",
+					 DatumGetChar(datum) != LOGICALREP_TWOPHASE_STATE_DISABLED ? "true" : "false");
+
+	/* Get disable on error option */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_subdisableonerr);
+	appendStringInfo(&buf, ", disable_on_error=%s",
+					 DatumGetBool(datum) ? "true" : "false");
+
+	/* Get password required option */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_subpasswordrequired);
+	appendStringInfo(&buf, ", password_required=%s",
+					 DatumGetBool(datum) ? "true" : "false");
+
+	/* Get run as owner option */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_subrunasowner);
+	appendStringInfo(&buf, ", run_as_owner=%s",
+					 DatumGetBool(datum) ? "true" : "false");
+
+	/* Get origin */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_suborigin);
+	appendStringInfo(&buf, ", origin=%s", TextDatumGetCString(datum));
+
+	/* Get failover option */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_subfailover);
+	appendStringInfo(&buf, ", failover=%s",
+					 DatumGetBool(datum) ? "true" : "false");
+
+	/* Get retain dead tuples option */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_subretaindeadtuples);
+	appendStringInfo(&buf, ", retain_dead_tuples=%s",
+					 DatumGetBool(datum) ? "true" : "false");
+
+	/* Get max retention duration */
+	datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+								   Anum_pg_subscription_submaxretention);
+	appendStringInfo(&buf, ", max_retention_duration=%d",
+					 DatumGetInt32(datum));
+
+	appendStringInfoString(&buf, ");");
+
+	ReleaseSysCache(tup);
+
+	return buf.data;
+}
+
+/*
+ * pg_get_subscription_ddl_by_name
+ *		Get CREATE SUBSCRIPTION statement for a subscription.
+ *
+ * This takes name as parameter for pg_get_subscription_ddl().
+ */
+Datum
+pg_get_subscription_ddl_by_name(PG_FUNCTION_ARGS)
+{
+	Name		subname = PG_GETARG_NAME(0);
+	Oid			suboid;
+	char	   *ddl_stmt;
+
+	/* Get the OID of the subscription from its name */
+	suboid = get_subscription_oid(NameStr(*subname), false);
+
+	/* Get the CREATE SUBSCRIPTION DDL statement from its OID */
+	ddl_stmt = pg_get_subscription_string(suboid);
+
+	PG_RETURN_TEXT_P(string_to_text(ddl_stmt));
+}
+
+/*
+ * pg_get_subscription_ddl_by_oid
+ *		Get CREATE SUBSCRIPTION statement for a subscription.
+ *
+ * This takes oid as parameter for pg_get_subscription_ddl().
+ */
+Datum
+pg_get_subscription_ddl_by_oid(PG_FUNCTION_ARGS)
+{
+	Oid			suboid = PG_GETARG_OID(0);
+	char	   *ddl_stmt;
+
+	/* Get the CREATE SUBSCRIPTION DDL statement from its OID */
+	ddl_stmt = pg_get_subscription_string(suboid);
+
+	PG_RETURN_TEXT_P(string_to_text(ddl_stmt));
+}
+
+/*
+ * text_array_to_string_list
+ *		Convert text array to list of strings.
+ */
+static List *
+text_array_to_string_list(ArrayType *text_array)
+{
+	List	   *result = NIL;
+	Datum	   *elems;
+	int			nelems;
+
+	deconstruct_array_builtin(text_array, TEXTOID, &elems, NULL, &nelems);
+
+	for (int i = 0; i < nelems; i++)
+		result = lappend(result, makeString(TextDatumGetCString(elems[i])));
+
+	return result;
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index aaadfd8c748..7411bf611ca 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -3993,6 +3993,12 @@
 { oid => '1387', descr => 'constraint description',
   proname => 'pg_get_constraintdef', provolatile => 's', prorettype => 'text',
   proargtypes => 'oid', prosrc => 'pg_get_constraintdef' },
+{ oid => '8001', descr => 'get CREATE statement for subscription',
+  proname => 'pg_get_subscription_ddl', prorettype => 'text',
+  proargtypes => 'name', prosrc => 'pg_get_subscription_ddl_by_name' },
+{ oid => '8002', descr => 'get CREATE statement for subscription',
+  proname => 'pg_get_subscription_ddl', prorettype => 'text',
+  proargtypes => 'oid', prosrc => 'pg_get_subscription_ddl_by_oid' },
 { oid => '1716', descr => 'deparse an encoded expression',
   proname => 'pg_get_expr', provolatile => 's', prorettype => 'text',
   proargtypes => 'pg_node_tree oid', prosrc => 'pg_get_expr' },
diff --git a/src/test/regress/expected/subscription.out b/src/test/regress/expected/subscription.out
index 327d1e7731f..843481d1134 100644
--- a/src/test/regress/expected/subscription.out
+++ b/src/test/regress/expected/subscription.out
@@ -522,3 +522,98 @@ DROP ROLE regress_subscription_user;
 DROP ROLE regress_subscription_user2;
 DROP ROLE regress_subscription_user3;
 DROP ROLE regress_subscription_user_dummy;
+--
+-- Test pg_get_subscription_ddl() by creating subscriptions with various
+-- configurations and checking the DDL.
+--
+CREATE ROLE regress_createsub_role LOGIN;
+CREATE ROLE regress_readalldata_role LOGIN;
+-- Check the pg_get_subscription_ddl output for a NULL and empty input
+SELECT pg_get_subscription_ddl('');
+ERROR:  subscription "" does not exist
+SELECT pg_get_subscription_ddl(NULL);
+ pg_get_subscription_ddl 
+-------------------------
+ 
+(1 row)
+
+-- Suppress WARNINGS when creating subscription
+SET client_min_messages = 'error';
+-- Create subscription with minimal options
+CREATE SUBSCRIPTION regress_testsub1 CONNECTION 'dbname=db_doesnotexist'
+  PUBLICATION testpub1 WITH (connect=false);
+RESET client_min_messages;
+-- Check that the subscription DDL is correctly created
+SELECT pg_get_subscription_ddl('regress_testsub1');
+                                                                                                                                                                                      pg_get_subscription_ddl                                                                                                                                                                                      
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE SUBSCRIPTION regress_testsub1 CONNECTION 'dbname=db_doesnotexist' PUBLICATION "testpub1" WITH (connect=false, slot_name='regress_testsub1', enabled=false, binary=false, streaming=parallel, synchronous_commit=off, two_phase=false, disable_on_error=false, password_required=true, run_as_owner=false, origin=any, failover=false, retain_dead_tuples=false, max_retention_duration=0);
+(1 row)
+
+-- Create subscription with more options
+SET client_min_messages = 'error';
+CREATE SUBSCRIPTION "regress_TestSub2" CONNECTION 'host=unknown user=dvd password=pass123'
+  PUBLICATION "testpub2", "TestPub3" WITH (connect=false, slot_name='slot1',
+  enabled=off);
+RESET client_min_messages;
+SELECT pg_get_subscription_ddl('regress_TestSub2');
+                                                                                                                                                                                               pg_get_subscription_ddl                                                                                                                                                                                                
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE SUBSCRIPTION "regress_TestSub2" CONNECTION 'host=unknown user=dvd password=pass123' PUBLICATION "testpub2", "TestPub3" WITH (connect=false, slot_name='slot1', enabled=false, binary=false, streaming=parallel, synchronous_commit=off, two_phase=false, disable_on_error=false, password_required=true, run_as_owner=false, origin=any, failover=false, retain_dead_tuples=false, max_retention_duration=0);
+(1 row)
+
+-- Create subscription with all options
+SET client_min_messages = 'error';
+CREATE SUBSCRIPTION regress_testsub3 CONNECTION 'host=unknown user=dvd password=pass12'
+  PUBLICATION testpub4 WITH (connect=false, slot_name=none, enabled=false,
+  create_slot=false, copy_data=false, binary=true, streaming=off,
+  synchronous_commit=local, two_phase=true, disable_on_error=true,
+  password_required=false, run_as_owner=true, origin=none, failover=true,
+  retain_dead_tuples=false, max_retention_duration=100);
+RESET client_min_messages;
+SELECT pg_get_subscription_ddl('regress_testsub3');
+                                                                                                                                                                                              pg_get_subscription_ddl                                                                                                                                                                                              
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE SUBSCRIPTION regress_testsub3 CONNECTION 'host=unknown user=dvd password=pass12' PUBLICATION "testpub4" WITH (connect=false, slot_name=none, create_slot=false, enabled=false, binary=true, streaming=off, synchronous_commit=local, two_phase=true, disable_on_error=true, password_required=false, run_as_owner=true, origin=none, failover=true, retain_dead_tuples=false, max_retention_duration=100);
+(1 row)
+
+-- Non-superusers without pg_create_subscription and/or pg_read_all_data
+-- permissions cannot retrieve the DDL.
+SET SESSION AUTHORIZATION 'regress_createsub_role';
+SELECT pg_get_subscription_ddl('regress_TestSub2');
+ERROR:  permission denied to get the create subscription ddl
+DETAIL:  Only roles with privileges of the "pg_create_subscription" and/or "pg_read_all_data" role may view subscription DDL.
+RESET SESSION AUTHORIZATION;
+SET SESSION AUTHORIZATION 'regress_readalldata_role';
+SELECT pg_get_subscription_ddl('regress_TestSub2');
+ERROR:  permission denied to get the create subscription ddl
+DETAIL:  Only roles with privileges of the "pg_create_subscription" and/or "pg_read_all_data" role may view subscription DDL.
+RESET SESSION AUTHORIZATION;
+-- Administrators can change who can access this function
+GRANT pg_create_subscription TO regress_createsub_role;
+GRANT pg_read_all_data TO regress_readalldata_role;
+SET SESSION AUTHORIZATION 'regress_createsub_role';
+SELECT pg_get_subscription_ddl('regress_TestSub2');
+                                                                                                                                                                                               pg_get_subscription_ddl                                                                                                                                                                                                
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE SUBSCRIPTION "regress_TestSub2" CONNECTION 'host=unknown user=dvd password=pass123' PUBLICATION "testpub2", "TestPub3" WITH (connect=false, slot_name='slot1', enabled=false, binary=false, streaming=parallel, synchronous_commit=off, two_phase=false, disable_on_error=false, password_required=true, run_as_owner=false, origin=any, failover=false, retain_dead_tuples=false, max_retention_duration=0);
+(1 row)
+
+RESET SESSION AUTHORIZATION;
+SET SESSION AUTHORIZATION 'regress_readalldata_role';
+SELECT pg_get_subscription_ddl('regress_TestSub2');
+                                                                                                                                                                                               pg_get_subscription_ddl                                                                                                                                                                                                
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE SUBSCRIPTION "regress_TestSub2" CONNECTION 'host=unknown user=dvd password=pass123' PUBLICATION "testpub2", "TestPub3" WITH (connect=false, slot_name='slot1', enabled=false, binary=false, streaming=parallel, synchronous_commit=off, two_phase=false, disable_on_error=false, password_required=true, run_as_owner=false, origin=any, failover=false, retain_dead_tuples=false, max_retention_duration=0);
+(1 row)
+
+RESET SESSION AUTHORIZATION;
+REVOKE pg_create_subscription FROM regress_createsub_role;
+REVOKE pg_read_all_data FROM regress_readalldata_role;
+ALTER SUBSCRIPTION regress_testsub1 SET (slot_name=NONE);
+DROP SUBSCRIPTION regress_testsub1;
+ALTER SUBSCRIPTION "regress_TestSub2" SET (slot_name=NONE);
+DROP SUBSCRIPTION "regress_TestSub2";
+DROP SUBSCRIPTION regress_testsub3;
+DROP ROLE regress_createsub_role;
+DROP ROLE regress_readalldata_role;
diff --git a/src/test/regress/sql/subscription.sql b/src/test/regress/sql/subscription.sql
index ef0c298d2df..149c0e06cd3 100644
--- a/src/test/regress/sql/subscription.sql
+++ b/src/test/regress/sql/subscription.sql
@@ -370,3 +370,71 @@ DROP ROLE regress_subscription_user;
 DROP ROLE regress_subscription_user2;
 DROP ROLE regress_subscription_user3;
 DROP ROLE regress_subscription_user_dummy;
+
+--
+-- Test pg_get_subscription_ddl() by creating subscriptions with various
+-- configurations and checking the DDL.
+--
+CREATE ROLE regress_createsub_role LOGIN;
+CREATE ROLE regress_readalldata_role LOGIN;
+
+-- Check the pg_get_subscription_ddl output for a NULL and empty input
+SELECT pg_get_subscription_ddl('');
+SELECT pg_get_subscription_ddl(NULL);
+
+-- Suppress WARNINGS when creating subscription
+SET client_min_messages = 'error';
+-- Create subscription with minimal options
+CREATE SUBSCRIPTION regress_testsub1 CONNECTION 'dbname=db_doesnotexist'
+  PUBLICATION testpub1 WITH (connect=false);
+RESET client_min_messages;
+
+-- Check that the subscription DDL is correctly created
+SELECT pg_get_subscription_ddl('regress_testsub1');
+
+-- Create subscription with more options
+SET client_min_messages = 'error';
+CREATE SUBSCRIPTION "regress_TestSub2" CONNECTION 'host=unknown user=dvd password=pass123'
+  PUBLICATION "testpub2", "TestPub3" WITH (connect=false, slot_name='slot1',
+  enabled=off);
+RESET client_min_messages;
+SELECT pg_get_subscription_ddl('regress_TestSub2');
+
+-- Create subscription with all options
+SET client_min_messages = 'error';
+CREATE SUBSCRIPTION regress_testsub3 CONNECTION 'host=unknown user=dvd password=pass12'
+  PUBLICATION testpub4 WITH (connect=false, slot_name=none, enabled=false,
+  create_slot=false, copy_data=false, binary=true, streaming=off,
+  synchronous_commit=local, two_phase=true, disable_on_error=true,
+  password_required=false, run_as_owner=true, origin=none, failover=true,
+  retain_dead_tuples=false, max_retention_duration=100);
+RESET client_min_messages;
+SELECT pg_get_subscription_ddl('regress_testsub3');
+
+-- Non-superusers without pg_create_subscription and/or pg_read_all_data
+-- permissions cannot retrieve the DDL.
+SET SESSION AUTHORIZATION 'regress_createsub_role';
+SELECT pg_get_subscription_ddl('regress_TestSub2');
+RESET SESSION AUTHORIZATION;
+SET SESSION AUTHORIZATION 'regress_readalldata_role';
+SELECT pg_get_subscription_ddl('regress_TestSub2');
+RESET SESSION AUTHORIZATION;
+-- Administrators can change who can access this function
+GRANT pg_create_subscription TO regress_createsub_role;
+GRANT pg_read_all_data TO regress_readalldata_role;
+SET SESSION AUTHORIZATION 'regress_createsub_role';
+SELECT pg_get_subscription_ddl('regress_TestSub2');
+RESET SESSION AUTHORIZATION;
+SET SESSION AUTHORIZATION 'regress_readalldata_role';
+SELECT pg_get_subscription_ddl('regress_TestSub2');
+
+RESET SESSION AUTHORIZATION;
+REVOKE pg_create_subscription FROM regress_createsub_role;
+REVOKE pg_read_all_data FROM regress_readalldata_role;
+ALTER SUBSCRIPTION regress_testsub1 SET (slot_name=NONE);
+DROP SUBSCRIPTION regress_testsub1;
+ALTER SUBSCRIPTION "regress_TestSub2" SET (slot_name=NONE);
+DROP SUBSCRIPTION "regress_TestSub2";
+DROP SUBSCRIPTION regress_testsub3;
+DROP ROLE regress_createsub_role;
+DROP ROLE regress_readalldata_role;
-- 
2.43.0