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

