Hi Hackers, Thank you Chao Li, Peter Smith and Alvaro for the review. I have incorporated all your review comments except below ones:
7 > > ``` > + /* Append connection info to the CREATE SUBSCRIPTION statement */ > + appendStringInfo(&buf, "CONNECTION \'%s\'", conninfo); > ``` > A connection string contains a db access credential, and > ROLE_PG_READ_ALL_DATA (not a high privilege) can view the DDL, is there a > concern of leaking the secret? Should we redact the password in connection > string? > > If the user installs a password in the conninfo, I think they are being dumb about it, and it's not this function's job to educate them on that. Restricting the function to users that have the pg_read_all_data and/or pg_create_subscription privilege (which applies to superusers, but also if the DBA grants that to other users, it'd work for those also) is a better idea. > 1. Question - is it deliberate to *always* return DLL with every > > > possible option assigned, even if those are just the option default > > values? e.g. For something like the CREATE PUBLICATION command the > > string returned could be only half the size if it accounts for > > default. > Yeah, I was asking myself the same. I think we definitely want options > to be printed when there are GUCs that can affect the outcome (e.g., > something that is considered default in this server but not on a > differently- configured one would give different results). But for > those that are just hardcoded defaults, omitting them would make sense. > > In future, the default value of any of the parameters may change so this function would create the wrong ddl. Also, having lengthy DDL doesn't create any problem and provides values for all the options. > 2. I was also wondering if it was really necessary to have so many > > > appendStringInfoString() calls to reconstruct the command. > There are a couple of these patches that have an auxiliary > pretty-printing helper function to add newline-tabs instead of > individual spaces. I think that wouldn't work as nicely if you tried to > condense the printing in the way you suggest. On the other hand, if you > have a long format string, it's harder to visually match each specifier > to its corresponding argument. If this was performance-critical code I > would agree to use denser code and avoid function calls, but for this > usage I don't think we care much. > > +1. Please find a revised patch. Thanks, Vaibhav Dalvi EnterpriseDB On Thu, Nov 13, 2025 at 1:50 PM Álvaro Herrera <[email protected]> wrote: > On 2025-Nov-13, Peter Smith wrote: > > > 1. Question - is it deliberate to *always* return DLL with every > > possible option assigned, even if those are just the option default > > values? e.g. For something like the CREATE PUBLICATION command the > > string returned could be only half the size if it accounts for > > default. > > Yeah, I was asking myself the same. I think we definitely want options > to be printed when there are GUCs that can affect the outcome (e.g., > something that is considered default in this server but not on a > differently- configured one would give different results). But for > those that are just hardcoded defaults, omitting them would make sense. > > > 2. I was also wondering if it was really necessary to have so many > > appendStringInfoString() calls to reconstruct the command. > > There are a couple of these patches that have an auxiliary > pretty-printing helper function to add newline-tabs instead of > individual spaces. I think that wouldn't work as nicely if you tried to > condense the printing in the way you suggest. On the other hand, if you > have a long format string, it's harder to visually match each specifier > to its corresponding argument. If this was performance-critical code I > would agree to use denser code and avoid function calls, but for this > usage I don't think we care much. > > -- > Álvaro Herrera Breisgau, Deutschland — > https://www.EnterpriseDB.com/ > Al principio era UNIX, y UNIX habló y dijo: "Hello world\n". > No dijo "Hello New Jersey\n", ni "Hello USA\n". >
From 4d33dd0d5e6228457d4512f126e496888c3528c3 Mon Sep 17 00:00:00 2001 From: Vaibhav Dalvi <[email protected]> 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 <[email protected]> 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
