public inbox for [email protected]
help / color / mirror / Atom feedFrom: Jeff Davis <[email protected]>
To: Ashutosh Bapat <[email protected]>
Cc: Bharath Rupireddy <[email protected]>
Cc: Joe Conway <[email protected]>
Cc: [email protected]
Subject: Re: [17] CREATE SUBSCRIPTION ... SERVER
Date: Mon, 15 Jan 2024 17:55:44 -0800
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<CAExHW5vv-78ixJs3arsuZ+12A4AyqehJnDPhejp0m4FAKG6JZA@mail.gmail.com>
<[email protected]>
<CAExHW5sAo4Gv3AwuxQNGSQ2Z45B8_MZEb-SG-B8D-hLMdrquAg@mail.gmail.com>
<[email protected]>
<[email protected]>
<CAExHW5unvpDv6yMSmqurHP7Du1PqoJFWVxeK-4YNm5EnoNJiSQ@mail.gmail.com>
<[email protected]>
<CAExHW5sQVJeCgCGMkum_k4Z4VPaK5GkdAjHW-d+F3MngBZ9qBA@mail.gmail.com>
<[email protected]>
<[email protected]>
<[email protected]>
<CALj2ACXDua2Az15Kj3OZFaRm49G8-faemiEEv8t9GNCcsxv8Hw@mail.gmail.com>
<[email protected]>
<CAExHW5v5poohyfNRVQp3yjVE56ej0dWvVMbysSSVtg21aHT3uQ@mail.gmail.com>
<[email protected]>
<CAExHW5vF+cp9efQj=-W+Rhbr9f9ZZqt3XFnP0NB5jKsm=C=5tw@mail.gmail.com>
<[email protected]>
On Fri, 2024-01-12 at 17:17 -0800, Jeff Davis wrote:
> I think 0004 needs a bit more work, so I'm leaving it off for now,
> but
> I'll bring it back in the next patch set.
Here's the next patch set. 0001 - 0003 are mostly the same with some
improved error messages and some code fixes. I am looking to start
committing 0001 - 0003 soon, as they have received some feedback
already and 0004 isn't required for the earlier patches to be useful.
0004 could use more discussion. The purpose is to split the privileges
of pg_create_subscription into two: pg_create_subscription, and
pg_create_connection. By separating the privileges, it's possible to
allow someone to create/manage subscriptions to a predefined set of
foreign servers (on which they have USAGE privileges) without allowing
them to write an arbitrary connection string.
The reasoning behind the separation is that creating a connection
string has different and more nuanced security implications than
creating a subscription (cf. extensive discussion[1] related to the
password_required setting on a subscription).
By default, pg_create_subscription is a member of pg_create_connection,
so there's no change/break of the default behavior. But administrators
who want the privileges to be separated can simply "REVOKE
pg_create_connection FROM pg_create_subscription".
Given that CREATE SUBSCRIPTION ... SERVER works on a server of any FDW,
we would also need to protect against someone making using an
unexpected FDW (with no validation or different validation) to
construct a foreign server with malicious connection settings. To do
so, I added to the grammar "CREATE SERVER ... FOR SUBSCRIPTION" (and a
boolean catalog entry in pg_foreign_server) that can only be set by a
member of pg_create_connection.
There was some resistance[2] to adding more grammar/catalog impact than
necessary, so I'm not sure if others think this is the right approach.
The earlier patches are still worth it without 0004, but I do think the
idea of separating the privileges is useful and it would be nice to
find an agreeable solution to do so. At least with the 0004, the
approach is a bit more direct.
Regards,
Jeff Davis
[1]
https://www.postgresql.org/message-id/9DFC88D3-1300-4DE8-ACBC-4CEF84399A53%40enterprisedb.com
[2]
https://www.postgresql.org/message-id/172273.1693403385%40sss.pgh.pa.us
Attachments:
[text/x-patch] v7-0001-Un-deprecate-postgresql_fdw_validator.patch (8.8K, 2-v7-0001-Un-deprecate-postgresql_fdw_validator.patch)
download | inline diff:
From 07b7baf50df3ecaf527530ea47435edfc588714f Mon Sep 17 00:00:00 2001
From: Jeff Davis <[email protected]>
Date: Thu, 4 Jan 2024 12:15:54 -0800
Subject: [PATCH v7 1/4] Un-deprecate postgresql_fdw_validator().
Extend the walrcv_ API to retrieve the options list from libpq, and
use that for postgresql_fdw_validator().
By using the correct libpq options, it no longer needs to be
deprecated, and can be used by the upcoming pg_connection_fdw.
Discussion: https://postgr.es/m/[email protected]
---
src/backend/foreign/foreign.c | 67 +++++++------------
.../libpqwalreceiver/libpqwalreceiver.c | 48 +++++++++++++
src/include/replication/walreceiver.h | 20 ++++++
3 files changed, 91 insertions(+), 44 deletions(-)
diff --git a/src/backend/foreign/foreign.c b/src/backend/foreign/foreign.c
index 02e1898131..747cc11a60 100644
--- a/src/backend/foreign/foreign.c
+++ b/src/backend/foreign/foreign.c
@@ -23,6 +23,7 @@
#include "funcapi.h"
#include "lib/stringinfo.h"
#include "miscadmin.h"
+#include "replication/walreceiver.h"
#include "utils/builtins.h"
#include "utils/memutils.h"
#include "utils/rel.h"
@@ -549,40 +550,6 @@ pg_options_to_table(PG_FUNCTION_ARGS)
}
-/*
- * Describes the valid options for postgresql FDW, server, and user mapping.
- */
-struct ConnectionOption
-{
- const char *optname;
- Oid optcontext; /* Oid of catalog in which option may appear */
-};
-
-/*
- * Copied from fe-connect.c PQconninfoOptions.
- *
- * The list is small - don't bother with bsearch if it stays so.
- */
-static const struct ConnectionOption libpq_conninfo_options[] = {
- {"authtype", ForeignServerRelationId},
- {"service", ForeignServerRelationId},
- {"user", UserMappingRelationId},
- {"password", UserMappingRelationId},
- {"connect_timeout", ForeignServerRelationId},
- {"dbname", ForeignServerRelationId},
- {"host", ForeignServerRelationId},
- {"hostaddr", ForeignServerRelationId},
- {"port", ForeignServerRelationId},
- {"tty", ForeignServerRelationId},
- {"options", ForeignServerRelationId},
- {"requiressl", ForeignServerRelationId},
- {"sslmode", ForeignServerRelationId},
- {"gsslib", ForeignServerRelationId},
- {"gssdelegation", ForeignServerRelationId},
- {NULL, InvalidOid}
-};
-
-
/*
* Check if the provided option is one of libpq conninfo options.
* context is the Oid of the catalog the option came from, or 0 if we
@@ -593,9 +560,23 @@ is_conninfo_option(const char *option, Oid context)
{
const struct ConnectionOption *opt;
- for (opt = libpq_conninfo_options; opt->optname; opt++)
- if (context == opt->optcontext && strcmp(opt->optname, option) == 0)
- return true;
+ /* skip options that must be overridden */
+ if (strcmp(option, "client_encoding") == 0)
+ return false;
+
+ for (opt = walrcv_conninfo_options(); opt->optname; opt++)
+ {
+ if (strcmp(opt->optname, option) == 0)
+ {
+ if (opt->isdebug)
+ return false;
+
+ if (opt->issecret || strcmp(opt->optname, "user") == 0)
+ return (context == UserMappingRelationId);
+
+ return (context == ForeignServerRelationId);
+ }
+ }
return false;
}
@@ -606,11 +587,6 @@ is_conninfo_option(const char *option, Oid context)
*
* Valid server options are all libpq conninfo options except
* user and password -- these may only appear in USER MAPPING options.
- *
- * Caution: this function is deprecated, and is now meant only for testing
- * purposes, because the list of options it knows about doesn't necessarily
- * square with those known to whichever libpq instance you might be using.
- * Inquire of libpq itself, instead.
*/
Datum
postgresql_fdw_validator(PG_FUNCTION_ARGS)
@@ -620,6 +596,9 @@ postgresql_fdw_validator(PG_FUNCTION_ARGS)
ListCell *cell;
+ /* Load the library providing us libpq calls. */
+ load_file("libpqwalreceiver", false);
+
foreach(cell, options_list)
{
DefElem *def = lfirst(cell);
@@ -636,9 +615,9 @@ postgresql_fdw_validator(PG_FUNCTION_ARGS)
* with a valid option that looks similar, if there is one.
*/
initClosestMatch(&match_state, def->defname, 4);
- for (opt = libpq_conninfo_options; opt->optname; opt++)
+ for (opt = walrcv_conninfo_options(); opt->optname; opt++)
{
- if (catalog == opt->optcontext)
+ if (is_conninfo_option(opt->optname, catalog))
{
has_valid_options = true;
updateClosestMatch(&match_state, opt->optname);
diff --git a/src/backend/replication/libpqwalreceiver/libpqwalreceiver.c b/src/backend/replication/libpqwalreceiver/libpqwalreceiver.c
index 201c36cb22..58f8267fce 100644
--- a/src/backend/replication/libpqwalreceiver/libpqwalreceiver.c
+++ b/src/backend/replication/libpqwalreceiver/libpqwalreceiver.c
@@ -53,6 +53,7 @@ static WalReceiverConn *libpqrcv_connect(const char *conninfo,
const char *appname, char **err);
static void libpqrcv_check_conninfo(const char *conninfo,
bool must_use_password);
+static const struct ConnectionOption *libpqrcv_conninfo_options(void);
static char *libpqrcv_get_conninfo(WalReceiverConn *conn);
static void libpqrcv_get_senderinfo(WalReceiverConn *conn,
char **sender_host, int *sender_port);
@@ -86,6 +87,7 @@ static void libpqrcv_disconnect(WalReceiverConn *conn);
static WalReceiverFunctionsType PQWalReceiverFunctions = {
.walrcv_connect = libpqrcv_connect,
.walrcv_check_conninfo = libpqrcv_check_conninfo,
+ .walrcv_conninfo_options = libpqrcv_conninfo_options,
.walrcv_get_conninfo = libpqrcv_get_conninfo,
.walrcv_get_senderinfo = libpqrcv_get_senderinfo,
.walrcv_identify_system = libpqrcv_identify_system,
@@ -298,6 +300,52 @@ libpqrcv_check_conninfo(const char *conninfo, bool must_use_password)
PQconninfoFree(opts);
}
+static const struct ConnectionOption *
+libpqrcv_conninfo_options(void)
+{
+ static struct ConnectionOption *connection_options = NULL;
+
+ if (connection_options == NULL)
+ {
+ PQconninfoOption *conndefaults = PQconndefaults();
+ PQconninfoOption *lopt;
+ struct ConnectionOption *tmp_options = NULL;
+ struct ConnectionOption *popt;
+ size_t options_size = 0;
+ int num_libpq_opts = 0;
+
+ for (lopt = conndefaults; lopt->keyword; lopt++)
+ num_libpq_opts++;
+
+ /* leave room for all-zero entry at the end */
+ options_size = sizeof(struct ConnectionOption) * (num_libpq_opts + 1);
+ tmp_options = MemoryContextAllocZero(TopMemoryContext, options_size);
+
+ popt = tmp_options;
+ for (lopt = conndefaults; lopt->keyword; lopt++)
+ {
+ if (strchr(lopt->dispchar, '*'))
+ popt->issecret = true;
+ else if (strchr(lopt->dispchar, 'D'))
+ popt->isdebug = true;
+
+ popt->optname = MemoryContextStrdup(TopMemoryContext,
+ lopt->keyword);
+ popt++;
+ }
+
+ /* last entry is all zero */
+ Assert(popt->optname == NULL);
+
+ PQconninfoFree(conndefaults);
+
+ /* if everything succeeded, set static variable */
+ connection_options = tmp_options;
+ }
+
+ return connection_options;
+}
+
/*
* Return a user-displayable conninfo string. Any security-sensitive fields
* are obfuscated.
diff --git a/src/include/replication/walreceiver.h b/src/include/replication/walreceiver.h
index 0899891cdb..541377e095 100644
--- a/src/include/replication/walreceiver.h
+++ b/src/include/replication/walreceiver.h
@@ -223,6 +223,16 @@ typedef struct WalRcvExecResult
TupleDesc tupledesc;
} WalRcvExecResult;
+/*
+ * Describes the valid options for postgresql FDW, server, and user mapping.
+ */
+struct ConnectionOption
+{
+ const char *optname;
+ bool issecret; /* is option for a password? */
+ bool isdebug; /* is option a debug option? */
+};
+
/* WAL receiver - libpqwalreceiver hooks */
/*
@@ -250,6 +260,13 @@ typedef WalReceiverConn *(*walrcv_connect_fn) (const char *conninfo,
typedef void (*walrcv_check_conninfo_fn) (const char *conninfo,
bool must_use_password);
+/*
+ * walrcv_conninfo_options_fn
+ *
+ * Return a pointer to a static array of the available options from libpq.
+ */
+typedef const struct ConnectionOption *(*walrcv_conninfo_options_fn) (void);
+
/*
* walrcv_get_conninfo_fn
*
@@ -389,6 +406,7 @@ typedef struct WalReceiverFunctionsType
{
walrcv_connect_fn walrcv_connect;
walrcv_check_conninfo_fn walrcv_check_conninfo;
+ walrcv_conninfo_options_fn walrcv_conninfo_options;
walrcv_get_conninfo_fn walrcv_get_conninfo;
walrcv_get_senderinfo_fn walrcv_get_senderinfo;
walrcv_identify_system_fn walrcv_identify_system;
@@ -410,6 +428,8 @@ extern PGDLLIMPORT WalReceiverFunctionsType *WalReceiverFunctions;
WalReceiverFunctions->walrcv_connect(conninfo, logical, must_use_password, appname, err)
#define walrcv_check_conninfo(conninfo, must_use_password) \
WalReceiverFunctions->walrcv_check_conninfo(conninfo, must_use_password)
+#define walrcv_conninfo_options() \
+ WalReceiverFunctions->walrcv_conninfo_options()
#define walrcv_get_conninfo(conn) \
WalReceiverFunctions->walrcv_get_conninfo(conn)
#define walrcv_get_senderinfo(conn, sender_host, sender_port) \
--
2.34.1
[text/x-patch] v7-0002-Add-built-in-foreign-data-wrapper-pg_connection_f.patch (125.1K, 3-v7-0002-Add-built-in-foreign-data-wrapper-pg_connection_f.patch)
download | inline diff:
From 9671d034d5e5d73c28b2445492127d1417444c6d Mon Sep 17 00:00:00 2001
From: Jeff Davis <[email protected]>
Date: Tue, 2 Jan 2024 13:33:28 -0800
Subject: [PATCH v7 2/4] Add built-in foreign data wrapper pg_connection_fdw.
In preparation for CREATE SUBSCRIPTION ... SERVER.
Discussion: https://postgr.es/m/[email protected]
---
doc/src/sgml/func.sgml | 19 +
src/backend/catalog/Makefile | 1 +
src/backend/foreign/foreign.c | 145 ++++
src/bin/pg_dump/pg_dump.c | 23 +-
src/include/catalog/meson.build | 1 +
.../catalog/pg_foreign_data_wrapper.dat | 21 +
src/include/catalog/pg_foreign_data_wrapper.h | 4 +-
src/include/catalog/pg_proc.dat | 4 +
src/include/foreign/foreign.h | 2 +
src/test/regress/expected/foreign_data.out | 760 +++++++++---------
src/test/regress/sql/foreign_data.sql | 276 ++++---
11 files changed, 770 insertions(+), 486 deletions(-)
create mode 100644 src/include/catalog/pg_foreign_data_wrapper.dat
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 210c7c0b02..79e1792eae 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -27985,6 +27985,25 @@ postgres=# SELECT '0/0'::pg_lsn + pd.segment_number * ps.setting::int + :offset
</para></entry>
</row>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>pg_conninfo_from_server</primary>
+ </indexterm>
+ <function>pg_conninfo_from_server</function> ( <parameter>servername</parameter> <type>text</type>, <parameter>username</parameter> <type>text</type>, <parameter>append_overrides</parameter> <type>boolean</type> )
+ <returnvalue>text</returnvalue>
+ </para>
+ <para>
+ Returns connection string generated from the foreign server and user
+ mapping associated with the given
+ <replaceable>servername</replaceable> and
+ <replaceable>username</replaceable>. If
+ <replaceable>append_overrides</replaceable> is
+ <literal>true</literal>, it appends override parameters necessary for
+ making outbound connections.
+ </para></entry>
+ </row>
+
<row>
<entry id="pg-logical-emit-message" role="func_table_entry"><para role="func_signature">
<indexterm>
diff --git a/src/backend/catalog/Makefile b/src/backend/catalog/Makefile
index d6b23b4bfd..756a052a70 100644
--- a/src/backend/catalog/Makefile
+++ b/src/backend/catalog/Makefile
@@ -136,6 +136,7 @@ POSTGRES_BKI_DATA = $(addprefix $(top_srcdir)/src/include/catalog/,\
pg_collation.dat \
pg_conversion.dat \
pg_database.dat \
+ pg_foreign_data_wrapper.dat \
pg_language.dat \
pg_namespace.dat \
pg_opclass.dat \
diff --git a/src/backend/foreign/foreign.c b/src/backend/foreign/foreign.c
index 747cc11a60..ead4a2cd2c 100644
--- a/src/backend/foreign/foreign.c
+++ b/src/backend/foreign/foreign.c
@@ -18,18 +18,22 @@
#include "catalog/pg_foreign_server.h"
#include "catalog/pg_foreign_table.h"
#include "catalog/pg_user_mapping.h"
+#include "commands/defrem.h"
#include "foreign/fdwapi.h"
#include "foreign/foreign.h"
#include "funcapi.h"
#include "lib/stringinfo.h"
+#include "mb/pg_wchar.h"
#include "miscadmin.h"
#include "replication/walreceiver.h"
+#include "utils/acl.h"
#include "utils/builtins.h"
#include "utils/memutils.h"
#include "utils/rel.h"
#include "utils/syscache.h"
#include "utils/varlena.h"
+static bool is_conninfo_option(const char *option, Oid context);
/*
* GetForeignDataWrapper - look up the foreign-data wrapper by OID.
@@ -191,6 +195,115 @@ GetForeignServerByName(const char *srvname, bool missing_ok)
}
+/*
+ * Values in connection strings must be enclosed in single quotes. Single
+ * quotes and backslashes must be escaped with backslash. NB: these rules are
+ * different from the rules for escaping a SQL literal.
+ */
+static void
+appendEscapedValue(StringInfo str, const char *val)
+{
+ appendStringInfoChar(str, '\'');
+ for (int i = 0; val[i] != '\0'; i++)
+ {
+ if (val[i] == '\\' || val[i] == '\'')
+ appendStringInfoChar(str, '\\');
+ appendStringInfoChar(str, val[i]);
+ }
+ appendStringInfoChar(str, '\'');
+}
+
+
+/*
+ * Helper for ForeignServerConnectionString() and pg_connection_validator().
+ *
+ * Transform a List of DefElem into a connection string.
+ */
+static char *
+options_to_conninfo(List *options, bool append_overrides)
+{
+ StringInfoData str;
+ ListCell *lc;
+ char *sep = "";
+
+ initStringInfo(&str);
+ foreach(lc, options)
+ {
+ DefElem *d = (DefElem *) lfirst(lc);
+ char *name = d->defname;
+ char *value;
+
+ /* ignore unknown options */
+ if (!is_conninfo_option(name, ForeignServerRelationId) &&
+ !is_conninfo_option(name, UserMappingRelationId))
+ continue;
+
+ value = defGetString(d);
+
+ appendStringInfo(&str, "%s%s = ", sep, name);
+ appendEscapedValue(&str, value);
+ sep = " ";
+ }
+
+ /* override client_encoding */
+ if (append_overrides)
+ {
+ appendStringInfo(&str, "%sclient_encoding = ", sep);
+ appendEscapedValue(&str, GetDatabaseEncodingName());
+ sep = " ";
+ }
+
+ return str.data;
+}
+
+
+/*
+ * Given a user ID and server ID, return a postgres connection string suitable
+ * to pass to libpq.
+ */
+char *
+ForeignServerConnectionString(Oid userid, Oid serverid, bool append_overrides)
+{
+ static MemoryContext tmpcontext = NULL;
+ ForeignServer *server;
+ UserMapping *um;
+ List *options;
+ char *conninfo;
+ MemoryContext oldcontext;
+
+ /* Load the library providing us libpq calls. */
+ load_file("libpqwalreceiver", false);
+
+ /*
+ * Use a temporary context rather than trying to track individual
+ * allocations in GetForeignServer() and GetUserMapping().
+ */
+ if (tmpcontext == NULL)
+ tmpcontext = AllocSetContextCreate(TopMemoryContext,
+ "temp context for building connection string",
+ ALLOCSET_DEFAULT_SIZES);
+
+ oldcontext = MemoryContextSwitchTo(tmpcontext);
+
+ server = GetForeignServer(serverid);
+ um = GetUserMapping(userid, serverid);
+
+ /* user mapping options override server options */
+ options = list_concat(server->options, um->options);
+
+ conninfo = options_to_conninfo(options, append_overrides);
+
+ MemoryContextSwitchTo(oldcontext);
+
+ /* copy only conninfo into the current context */
+ conninfo = pstrdup(conninfo);
+
+ MemoryContextReset(tmpcontext);
+
+ return conninfo;
+}
+
+
/*
* GetUserMapping - look up the user mapping.
*
@@ -580,6 +693,38 @@ is_conninfo_option(const char *option, Oid context)
return false;
}
+/*
+ * pg_conninfo_from_server
+ *
+ * Extract connection string from the given foreign server.
+ */
+Datum
+pg_conninfo_from_server(PG_FUNCTION_ARGS)
+{
+ char *server_name = text_to_cstring(PG_GETARG_TEXT_P(0));
+ char *user_name = text_to_cstring(PG_GETARG_TEXT_P(1));
+ bool append_overrides = PG_GETARG_BOOL(2);
+ Oid serverid = get_foreign_server_oid(server_name, false);
+ Oid userid = get_role_oid_or_public(user_name);
+ AclResult aclresult;
+ char *conninfo;
+
+ /* if the specified userid is not PUBLIC, check SET ROLE privileges */
+ if (userid != ACL_ID_PUBLIC)
+ check_can_set_role(GetUserId(), userid);
+
+ /* ACL check on foreign server */
+ aclresult = object_aclcheck(ForeignServerRelationId, serverid,
+ GetUserId(), ACL_USAGE);
+ if (aclresult != ACLCHECK_OK)
+ aclcheck_error(aclresult, OBJECT_FOREIGN_SERVER, server_name);
+
+ conninfo = ForeignServerConnectionString(userid, serverid,
+ append_overrides);
+
+ PG_RETURN_TEXT_P(cstring_to_text(conninfo));
+}
+
/*
* Validate the generic option given to SERVER or USER MAPPING.
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index bc20a025ce..5b7a18dbb1 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -2067,6 +2067,27 @@ selectDumpableStatisticsObject(StatsExtInfo *sobj, Archive *fout)
sobj->dobj.dump = DUMP_COMPONENT_NONE;
}
+/*
+ * selectDumpableFdw: policy-setting subroutine
+ * Mark foreign data wrapper as to be dumped or not
+ *
+ * Froeign Data Wrappers do not belong to any particular namespace. To
+ * identify built-in foreign data wrappers, we must resort to checking whether
+ * the method's OID is in the range reserved for initdb.
+ */
+static void
+selectDumpableFdw(FdwInfo *fdwinfo, Archive *fout)
+{
+ if (checkExtensionMembership(&fdwinfo->dobj, fout))
+ return; /* extension membership overrides all else */
+
+ if (fdwinfo->dobj.catId.oid <= (Oid) g_last_builtin_oid)
+ fdwinfo->dobj.dump = DUMP_COMPONENT_NONE;
+ else
+ fdwinfo->dobj.dump = fout->dopt->include_everything ?
+ DUMP_COMPONENT_ALL : DUMP_COMPONENT_NONE;
+}
+
/*
* selectDumpableObject: policy-setting subroutine
* Mark a generic dumpable object as to be dumped or not
@@ -9661,7 +9682,7 @@ getForeignDataWrappers(Archive *fout, int *numForeignDataWrappers)
fdwinfo[i].fdwoptions = pg_strdup(PQgetvalue(res, i, i_fdwoptions));
/* Decide whether we want to dump it */
- selectDumpableObject(&(fdwinfo[i].dobj), fout);
+ selectDumpableFdw(&fdwinfo[i], fout);
/* Mark whether FDW has an ACL */
if (!PQgetisnull(res, i, i_fdwacl))
diff --git a/src/include/catalog/meson.build b/src/include/catalog/meson.build
index 2bcbe327cf..c87533c9b2 100644
--- a/src/include/catalog/meson.build
+++ b/src/include/catalog/meson.build
@@ -83,6 +83,7 @@ bki_data = [
'pg_collation.dat',
'pg_conversion.dat',
'pg_database.dat',
+ 'pg_foreign_data_wrapper.dat',
'pg_language.dat',
'pg_namespace.dat',
'pg_opclass.dat',
diff --git a/src/include/catalog/pg_foreign_data_wrapper.dat b/src/include/catalog/pg_foreign_data_wrapper.dat
new file mode 100644
index 0000000000..ef68fe5966
--- /dev/null
+++ b/src/include/catalog/pg_foreign_data_wrapper.dat
@@ -0,0 +1,21 @@
+#----------------------------------------------------------------------
+#
+# pg_foreign_data_wrapper.dat
+# Initial contents of the pg_foreign_data_wrapper system catalog.
+#
+# Portions Copyright (c) 1996-2023, PostgreSQL Global Development Group
+# Portions Copyright (c) 1994, Regents of the University of California
+#
+# src/include/catalog/pg_foreign_data_wrapper.dat
+#
+#----------------------------------------------------------------------
+
+[
+
+{ oid => '6015', oid_symbol => 'PG_CONNECTION_FDW',
+ descr => 'Pseudo FDW for connections to Postgres',
+ fdwname => 'pg_connection_fdw',
+ fdwvalidator => 'postgresql_fdw_validator',
+ fdwacl => '_null_', fdwoptions => '_null_'},
+
+]
diff --git a/src/include/catalog/pg_foreign_data_wrapper.h b/src/include/catalog/pg_foreign_data_wrapper.h
index 5e39e4b42f..a78707fb59 100644
--- a/src/include/catalog/pg_foreign_data_wrapper.h
+++ b/src/include/catalog/pg_foreign_data_wrapper.h
@@ -30,8 +30,8 @@ CATALOG(pg_foreign_data_wrapper,2328,ForeignDataWrapperRelationId)
{
Oid oid; /* oid */
NameData fdwname; /* foreign-data wrapper name */
- Oid fdwowner BKI_LOOKUP(pg_authid); /* FDW owner */
- Oid fdwhandler BKI_LOOKUP_OPT(pg_proc); /* handler function, or 0
+ Oid fdwowner BKI_DEFAULT(POSTGRES) BKI_LOOKUP(pg_authid); /* FDW owner */
+ Oid fdwhandler BKI_DEFAULT(0) BKI_LOOKUP_OPT(pg_proc); /* handler function, or 0
* if none */
Oid fdwvalidator BKI_LOOKUP_OPT(pg_proc); /* option validation
* function, or 0 if
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 58811a6530..322ce001f6 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -7516,6 +7516,10 @@
proname => 'postgresql_fdw_validator', prorettype => 'bool',
proargtypes => '_text oid', prosrc => 'postgresql_fdw_validator' },
+{ oid => '6123', descr => 'extract connection string from the given foreign server',
+ proname => 'pg_conninfo_from_server', prorettype => 'text',
+ proargtypes => 'text text bool', prosrc => 'pg_conninfo_from_server' },
+
{ oid => '2290', descr => 'I/O',
proname => 'record_in', provolatile => 's', prorettype => 'record',
proargtypes => 'cstring oid int4', prosrc => 'record_in' },
diff --git a/src/include/foreign/foreign.h b/src/include/foreign/foreign.h
index 82b8153100..b5b9b97f4d 100644
--- a/src/include/foreign/foreign.h
+++ b/src/include/foreign/foreign.h
@@ -69,6 +69,8 @@ extern ForeignServer *GetForeignServerExtended(Oid serverid,
bits16 flags);
extern ForeignServer *GetForeignServerByName(const char *srvname,
bool missing_ok);
+extern char *ForeignServerConnectionString(Oid userid, Oid serverid,
+ bool append_overrides);
extern UserMapping *GetUserMapping(Oid userid, Oid serverid);
extern ForeignDataWrapper *GetForeignDataWrapper(Oid fdwid);
extern ForeignDataWrapper *GetForeignDataWrapperExtended(Oid fdwid,
diff --git a/src/test/regress/expected/foreign_data.out b/src/test/regress/expected/foreign_data.out
index 1dfe23cc1e..6ae93c41f6 100644
--- a/src/test/regress/expected/foreign_data.out
+++ b/src/test/regress/expected/foreign_data.out
@@ -21,15 +21,15 @@ CREATE ROLE regress_test_role2;
CREATE ROLE regress_test_role_super SUPERUSER;
CREATE ROLE regress_test_indirect;
CREATE ROLE regress_unprivileged_role;
-CREATE FOREIGN DATA WRAPPER dummy;
-COMMENT ON FOREIGN DATA WRAPPER dummy IS 'useless';
-CREATE FOREIGN DATA WRAPPER postgresql VALIDATOR postgresql_fdw_validator;
+CREATE FOREIGN DATA WRAPPER regress_dummy;
+COMMENT ON FOREIGN DATA WRAPPER regress_dummy IS 'useless';
+CREATE FOREIGN DATA WRAPPER regress_postgresql VALIDATOR postgresql_fdw_validator;
-- At this point we should have 2 built-in wrappers and no servers.
-SELECT fdwname, fdwhandler::regproc, fdwvalidator::regproc, fdwoptions FROM pg_foreign_data_wrapper ORDER BY 1, 2, 3;
- fdwname | fdwhandler | fdwvalidator | fdwoptions
-------------+------------+--------------------------+------------
- dummy | - | - |
- postgresql | - | postgresql_fdw_validator |
+SELECT fdwname, fdwhandler::regproc, fdwvalidator::regproc, fdwoptions FROM pg_foreign_data_wrapper WHERE fdwname LIKE 'regress_%' ORDER BY 1, 2, 3;
+ fdwname | fdwhandler | fdwvalidator | fdwoptions
+--------------------+------------+--------------------------+------------
+ regress_dummy | - | - |
+ regress_postgresql | - | postgresql_fdw_validator |
(2 rows)
SELECT srvname, srvoptions FROM pg_foreign_server;
@@ -43,180 +43,180 @@ SELECT * FROM pg_user_mapping;
(0 rows)
-- CREATE FOREIGN DATA WRAPPER
-CREATE FOREIGN DATA WRAPPER foo VALIDATOR bar; -- ERROR
+CREATE FOREIGN DATA WRAPPER regress_foo VALIDATOR bar; -- ERROR
ERROR: function bar(text[], oid) does not exist
-CREATE FOREIGN DATA WRAPPER foo;
-\dew
- List of foreign-data wrappers
- Name | Owner | Handler | Validator
-------------+---------------------------+---------+--------------------------
- dummy | regress_foreign_data_user | - | -
- foo | regress_foreign_data_user | - | -
- postgresql | regress_foreign_data_user | - | postgresql_fdw_validator
+CREATE FOREIGN DATA WRAPPER regress_foo;
+\dew regress_*
+ List of foreign-data wrappers
+ Name | Owner | Handler | Validator
+--------------------+---------------------------+---------+--------------------------
+ regress_dummy | regress_foreign_data_user | - | -
+ regress_foo | regress_foreign_data_user | - | -
+ regress_postgresql | regress_foreign_data_user | - | postgresql_fdw_validator
(3 rows)
-CREATE FOREIGN DATA WRAPPER foo; -- duplicate
-ERROR: foreign-data wrapper "foo" already exists
-DROP FOREIGN DATA WRAPPER foo;
-CREATE FOREIGN DATA WRAPPER foo OPTIONS (testing '1');
-\dew+
- List of foreign-data wrappers
- Name | Owner | Handler | Validator | Access privileges | FDW options | Description
-------------+---------------------------+---------+--------------------------+-------------------+---------------+-------------
- dummy | regress_foreign_data_user | - | - | | | useless
- foo | regress_foreign_data_user | - | - | | (testing '1') |
- postgresql | regress_foreign_data_user | - | postgresql_fdw_validator | | |
+CREATE FOREIGN DATA WRAPPER regress_foo; -- duplicate
+ERROR: foreign-data wrapper "regress_foo" already exists
+DROP FOREIGN DATA WRAPPER regress_foo;
+CREATE FOREIGN DATA WRAPPER regress_foo OPTIONS (testing '1');
+\dew+ regress_*
+ List of foreign-data wrappers
+ Name | Owner | Handler | Validator | Access privileges | FDW options | Description
+--------------------+---------------------------+---------+--------------------------+-------------------+---------------+-------------
+ regress_dummy | regress_foreign_data_user | - | - | | | useless
+ regress_foo | regress_foreign_data_user | - | - | | (testing '1') |
+ regress_postgresql | regress_foreign_data_user | - | postgresql_fdw_validator | | |
(3 rows)
-DROP FOREIGN DATA WRAPPER foo;
-CREATE FOREIGN DATA WRAPPER foo OPTIONS (testing '1', testing '2'); -- ERROR
+DROP FOREIGN DATA WRAPPER regress_foo;
+CREATE FOREIGN DATA WRAPPER regress_foo OPTIONS (testing '1', testing '2'); -- ERROR
ERROR: option "testing" provided more than once
-CREATE FOREIGN DATA WRAPPER foo OPTIONS (testing '1', another '2');
-\dew+
- List of foreign-data wrappers
- Name | Owner | Handler | Validator | Access privileges | FDW options | Description
-------------+---------------------------+---------+--------------------------+-------------------+----------------------------+-------------
- dummy | regress_foreign_data_user | - | - | | | useless
- foo | regress_foreign_data_user | - | - | | (testing '1', another '2') |
- postgresql | regress_foreign_data_user | - | postgresql_fdw_validator | | |
+CREATE FOREIGN DATA WRAPPER regress_foo OPTIONS (testing '1', another '2');
+\dew+ regress_*
+ List of foreign-data wrappers
+ Name | Owner | Handler | Validator | Access privileges | FDW options | Description
+--------------------+---------------------------+---------+--------------------------+-------------------+----------------------------+-------------
+ regress_dummy | regress_foreign_data_user | - | - | | | useless
+ regress_foo | regress_foreign_data_user | - | - | | (testing '1', another '2') |
+ regress_postgresql | regress_foreign_data_user | - | postgresql_fdw_validator | | |
(3 rows)
-DROP FOREIGN DATA WRAPPER foo;
+DROP FOREIGN DATA WRAPPER regress_foo;
SET ROLE regress_test_role;
-CREATE FOREIGN DATA WRAPPER foo; -- ERROR
-ERROR: permission denied to create foreign-data wrapper "foo"
+CREATE FOREIGN DATA WRAPPER regress_foo; -- ERROR
+ERROR: permission denied to create foreign-data wrapper "regress_foo"
HINT: Must be superuser to create a foreign-data wrapper.
RESET ROLE;
-CREATE FOREIGN DATA WRAPPER foo VALIDATOR postgresql_fdw_validator;
-\dew+
- List of foreign-data wrappers
- Name | Owner | Handler | Validator | Access privileges | FDW options | Description
-------------+---------------------------+---------+--------------------------+-------------------+-------------+-------------
- dummy | regress_foreign_data_user | - | - | | | useless
- foo | regress_foreign_data_user | - | postgresql_fdw_validator | | |
- postgresql | regress_foreign_data_user | - | postgresql_fdw_validator | | |
+CREATE FOREIGN DATA WRAPPER regress_foo VALIDATOR postgresql_fdw_validator;
+\dew+ regress_*
+ List of foreign-data wrappers
+ Name | Owner | Handler | Validator | Access privileges | FDW options | Description
+--------------------+---------------------------+---------+--------------------------+-------------------+-------------+-------------
+ regress_dummy | regress_foreign_data_user | - | - | | | useless
+ regress_foo | regress_foreign_data_user | - | postgresql_fdw_validator | | |
+ regress_postgresql | regress_foreign_data_user | - | postgresql_fdw_validator | | |
(3 rows)
-- HANDLER related checks
CREATE FUNCTION invalid_fdw_handler() RETURNS int LANGUAGE SQL AS 'SELECT 1;';
-CREATE FOREIGN DATA WRAPPER test_fdw HANDLER invalid_fdw_handler; -- ERROR
+CREATE FOREIGN DATA WRAPPER regress_test_fdw HANDLER invalid_fdw_handler; -- ERROR
ERROR: function invalid_fdw_handler must return type fdw_handler
-CREATE FOREIGN DATA WRAPPER test_fdw HANDLER test_fdw_handler HANDLER invalid_fdw_handler; -- ERROR
+CREATE FOREIGN DATA WRAPPER regress_test_fdw HANDLER test_fdw_handler HANDLER invalid_fdw_handler; -- ERROR
ERROR: conflicting or redundant options
-LINE 1: ...GN DATA WRAPPER test_fdw HANDLER test_fdw_handler HANDLER in...
+LINE 1: ...WRAPPER regress_test_fdw HANDLER test_fdw_handler HANDLER in...
^
-CREATE FOREIGN DATA WRAPPER test_fdw HANDLER test_fdw_handler;
-DROP FOREIGN DATA WRAPPER test_fdw;
+CREATE FOREIGN DATA WRAPPER regress_test_fdw HANDLER test_fdw_handler;
+DROP FOREIGN DATA WRAPPER regress_test_fdw;
-- ALTER FOREIGN DATA WRAPPER
-ALTER FOREIGN DATA WRAPPER foo OPTIONS (nonexistent 'fdw'); -- ERROR
+ALTER FOREIGN DATA WRAPPER regress_foo OPTIONS (nonexistent 'fdw'); -- ERROR
ERROR: invalid option "nonexistent"
HINT: There are no valid options in this context.
-ALTER FOREIGN DATA WRAPPER foo; -- ERROR
+ALTER FOREIGN DATA WRAPPER regress_foo; -- ERROR
ERROR: syntax error at or near ";"
-LINE 1: ALTER FOREIGN DATA WRAPPER foo;
- ^
-ALTER FOREIGN DATA WRAPPER foo VALIDATOR bar; -- ERROR
+LINE 1: ALTER FOREIGN DATA WRAPPER regress_foo;
+ ^
+ALTER FOREIGN DATA WRAPPER regress_foo VALIDATOR bar; -- ERROR
ERROR: function bar(text[], oid) does not exist
-ALTER FOREIGN DATA WRAPPER foo NO VALIDATOR;
-\dew+
- List of foreign-data wrappers
- Name | Owner | Handler | Validator | Access privileges | FDW options | Description
-------------+---------------------------+---------+--------------------------+-------------------+-------------+-------------
- dummy | regress_foreign_data_user | - | - | | | useless
- foo | regress_foreign_data_user | - | - | | |
- postgresql | regress_foreign_data_user | - | postgresql_fdw_validator | | |
+ALTER FOREIGN DATA WRAPPER regress_foo NO VALIDATOR;
+\dew+ regress_*
+ List of foreign-data wrappers
+ Name | Owner | Handler | Validator | Access privileges | FDW options | Description
+--------------------+---------------------------+---------+--------------------------+-------------------+-------------+-------------
+ regress_dummy | regress_foreign_data_user | - | - | | | useless
+ regress_foo | regress_foreign_data_user | - | - | | |
+ regress_postgresql | regress_foreign_data_user | - | postgresql_fdw_validator | | |
(3 rows)
-ALTER FOREIGN DATA WRAPPER foo OPTIONS (a '1', b '2');
-ALTER FOREIGN DATA WRAPPER foo OPTIONS (SET c '4'); -- ERROR
+ALTER FOREIGN DATA WRAPPER regress_foo OPTIONS (a '1', b '2');
+ALTER FOREIGN DATA WRAPPER regress_foo OPTIONS (SET c '4'); -- ERROR
ERROR: option "c" not found
-ALTER FOREIGN DATA WRAPPER foo OPTIONS (DROP c); -- ERROR
+ALTER FOREIGN DATA WRAPPER regress_foo OPTIONS (DROP c); -- ERROR
ERROR: option "c" not found
-ALTER FOREIGN DATA WRAPPER foo OPTIONS (ADD x '1', DROP x);
-\dew+
- List of foreign-data wrappers
- Name | Owner | Handler | Validator | Access privileges | FDW options | Description
-------------+---------------------------+---------+--------------------------+-------------------+----------------+-------------
- dummy | regress_foreign_data_user | - | - | | | useless
- foo | regress_foreign_data_user | - | - | | (a '1', b '2') |
- postgresql | regress_foreign_data_user | - | postgresql_fdw_validator | | |
+ALTER FOREIGN DATA WRAPPER regress_foo OPTIONS (ADD x '1', DROP x);
+\dew+ regress_*
+ List of foreign-data wrappers
+ Name | Owner | Handler | Validator | Access privileges | FDW options | Description
+--------------------+---------------------------+---------+--------------------------+-------------------+----------------+-------------
+ regress_dummy | regress_foreign_data_user | - | - | | | useless
+ regress_foo | regress_foreign_data_user | - | - | | (a '1', b '2') |
+ regress_postgresql | regress_foreign_data_user | - | postgresql_fdw_validator | | |
(3 rows)
-ALTER FOREIGN DATA WRAPPER foo OPTIONS (DROP a, SET b '3', ADD c '4');
-\dew+
- List of foreign-data wrappers
- Name | Owner | Handler | Validator | Access privileges | FDW options | Description
-------------+---------------------------+---------+--------------------------+-------------------+----------------+-------------
- dummy | regress_foreign_data_user | - | - | | | useless
- foo | regress_foreign_data_user | - | - | | (b '3', c '4') |
- postgresql | regress_foreign_data_user | - | postgresql_fdw_validator | | |
+ALTER FOREIGN DATA WRAPPER regress_foo OPTIONS (DROP a, SET b '3', ADD c '4');
+\dew+ regress_*
+ List of foreign-data wrappers
+ Name | Owner | Handler | Validator | Access privileges | FDW options | Description
+--------------------+---------------------------+---------+--------------------------+-------------------+----------------+-------------
+ regress_dummy | regress_foreign_data_user | - | - | | | useless
+ regress_foo | regress_foreign_data_user | - | - | | (b '3', c '4') |
+ regress_postgresql | regress_foreign_data_user | - | postgresql_fdw_validator | | |
(3 rows)
-ALTER FOREIGN DATA WRAPPER foo OPTIONS (a '2');
-ALTER FOREIGN DATA WRAPPER foo OPTIONS (b '4'); -- ERROR
+ALTER FOREIGN DATA WRAPPER regress_foo OPTIONS (a '2');
+ALTER FOREIGN DATA WRAPPER regress_foo OPTIONS (b '4'); -- ERROR
ERROR: option "b" provided more than once
-\dew+
- List of foreign-data wrappers
- Name | Owner | Handler | Validator | Access privileges | FDW options | Description
-------------+---------------------------+---------+--------------------------+-------------------+-----------------------+-------------
- dummy | regress_foreign_data_user | - | - | | | useless
- foo | regress_foreign_data_user | - | - | | (b '3', c '4', a '2') |
- postgresql | regress_foreign_data_user | - | postgresql_fdw_validator | | |
+\dew+ regress_*
+ List of foreign-data wrappers
+ Name | Owner | Handler | Validator | Access privileges | FDW options | Description
+--------------------+---------------------------+---------+--------------------------+-------------------+-----------------------+-------------
+ regress_dummy | regress_foreign_data_user | - | - | | | useless
+ regress_foo | regress_foreign_data_user | - | - | | (b '3', c '4', a '2') |
+ regress_postgresql | regress_foreign_data_user | - | postgresql_fdw_validator | | |
(3 rows)
SET ROLE regress_test_role;
-ALTER FOREIGN DATA WRAPPER foo OPTIONS (ADD d '5'); -- ERROR
-ERROR: permission denied to alter foreign-data wrapper "foo"
+ALTER FOREIGN DATA WRAPPER regress_foo OPTIONS (ADD d '5'); -- ERROR
+ERROR: permission denied to alter foreign-data wrapper "regress_foo"
HINT: Must be superuser to alter a foreign-data wrapper.
SET ROLE regress_test_role_super;
-ALTER FOREIGN DATA WRAPPER foo OPTIONS (ADD d '5');
-\dew+
- List of foreign-data wrappers
- Name | Owner | Handler | Validator | Access privileges | FDW options | Description
-------------+---------------------------+---------+--------------------------+-------------------+------------------------------+-------------
- dummy | regress_foreign_data_user | - | - | | | useless
- foo | regress_foreign_data_user | - | - | | (b '3', c '4', a '2', d '5') |
- postgresql | regress_foreign_data_user | - | postgresql_fdw_validator | | |
+ALTER FOREIGN DATA WRAPPER regress_foo OPTIONS (ADD d '5');
+\dew+ regress_*
+ List of foreign-data wrappers
+ Name | Owner | Handler | Validator | Access privileges | FDW options | Description
+--------------------+---------------------------+---------+--------------------------+-------------------+------------------------------+-------------
+ regress_dummy | regress_foreign_data_user | - | - | | | useless
+ regress_foo | regress_foreign_data_user | - | - | | (b '3', c '4', a '2', d '5') |
+ regress_postgresql | regress_foreign_data_user | - | postgresql_fdw_validator | | |
(3 rows)
-ALTER FOREIGN DATA WRAPPER foo OWNER TO regress_test_role; -- ERROR
-ERROR: permission denied to change owner of foreign-data wrapper "foo"
+ALTER FOREIGN DATA WRAPPER regress_foo OWNER TO regress_test_role; -- ERROR
+ERROR: permission denied to change owner of foreign-data wrapper "regress_foo"
HINT: The owner of a foreign-data wrapper must be a superuser.
-ALTER FOREIGN DATA WRAPPER foo OWNER TO regress_test_role_super;
+ALTER FOREIGN DATA WRAPPER regress_foo OWNER TO regress_test_role_super;
ALTER ROLE regress_test_role_super NOSUPERUSER;
SET ROLE regress_test_role_super;
-ALTER FOREIGN DATA WRAPPER foo OPTIONS (ADD e '6'); -- ERROR
-ERROR: permission denied to alter foreign-data wrapper "foo"
+ALTER FOREIGN DATA WRAPPER regress_foo OPTIONS (ADD e '6'); -- ERROR
+ERROR: permission denied to alter foreign-data wrapper "regress_foo"
HINT: Must be superuser to alter a foreign-data wrapper.
RESET ROLE;
-\dew+
- List of foreign-data wrappers
- Name | Owner | Handler | Validator | Access privileges | FDW options | Description
-------------+---------------------------+---------+--------------------------+-------------------+------------------------------+-------------
- dummy | regress_foreign_data_user | - | - | | | useless
- foo | regress_test_role_super | - | - | | (b '3', c '4', a '2', d '5') |
- postgresql | regress_foreign_data_user | - | postgresql_fdw_validator | | |
+\dew+ regress_*
+ List of foreign-data wrappers
+ Name | Owner | Handler | Validator | Access privileges | FDW options | Description
+--------------------+---------------------------+---------+--------------------------+-------------------+------------------------------+-------------
+ regress_dummy | regress_foreign_data_user | - | - | | | useless
+ regress_foo | regress_test_role_super | - | - | | (b '3', c '4', a '2', d '5') |
+ regress_postgresql | regress_foreign_data_user | - | postgresql_fdw_validator | | |
(3 rows)
-ALTER FOREIGN DATA WRAPPER foo RENAME TO foo1;
-\dew+
- List of foreign-data wrappers
- Name | Owner | Handler | Validator | Access privileges | FDW options | Description
-------------+---------------------------+---------+--------------------------+-------------------+------------------------------+-------------
- dummy | regress_foreign_data_user | - | - | | | useless
- foo1 | regress_test_role_super | - | - | | (b '3', c '4', a '2', d '5') |
- postgresql | regress_foreign_data_user | - | postgresql_fdw_validator | | |
+ALTER FOREIGN DATA WRAPPER regress_foo RENAME TO regress_foo1;
+\dew+ regress_*
+ List of foreign-data wrappers
+ Name | Owner | Handler | Validator | Access privileges | FDW options | Description
+--------------------+---------------------------+---------+--------------------------+-------------------+------------------------------+-------------
+ regress_dummy | regress_foreign_data_user | - | - | | | useless
+ regress_foo1 | regress_test_role_super | - | - | | (b '3', c '4', a '2', d '5') |
+ regress_postgresql | regress_foreign_data_user | - | postgresql_fdw_validator | | |
(3 rows)
-ALTER FOREIGN DATA WRAPPER foo1 RENAME TO foo;
+ALTER FOREIGN DATA WRAPPER regress_foo1 RENAME TO regress_foo;
-- HANDLER related checks
-ALTER FOREIGN DATA WRAPPER foo HANDLER invalid_fdw_handler; -- ERROR
+ALTER FOREIGN DATA WRAPPER regress_foo HANDLER invalid_fdw_handler; -- ERROR
ERROR: function invalid_fdw_handler must return type fdw_handler
-ALTER FOREIGN DATA WRAPPER foo HANDLER test_fdw_handler HANDLER anything; -- ERROR
+ALTER FOREIGN DATA WRAPPER regress_foo HANDLER test_fdw_handler HANDLER anything; -- ERROR
ERROR: conflicting or redundant options
-LINE 1: ...FOREIGN DATA WRAPPER foo HANDLER test_fdw_handler HANDLER an...
+LINE 1: ...DATA WRAPPER regress_foo HANDLER test_fdw_handler HANDLER an...
^
-ALTER FOREIGN DATA WRAPPER foo HANDLER test_fdw_handler;
+ALTER FOREIGN DATA WRAPPER regress_foo HANDLER test_fdw_handler;
WARNING: changing the foreign-data wrapper handler can change behavior of existing foreign tables
DROP FUNCTION invalid_fdw_handler();
-- DROP FOREIGN DATA WRAPPER
@@ -224,52 +224,52 @@ DROP FOREIGN DATA WRAPPER nonexistent; -- ERROR
ERROR: foreign-data wrapper "nonexistent" does not exist
DROP FOREIGN DATA WRAPPER IF EXISTS nonexistent;
NOTICE: foreign-data wrapper "nonexistent" does not exist, skipping
-\dew+
- List of foreign-data wrappers
- Name | Owner | Handler | Validator | Access privileges | FDW options | Description
-------------+---------------------------+------------------+--------------------------+-------------------+------------------------------+-------------
- dummy | regress_foreign_data_user | - | - | | | useless
- foo | regress_test_role_super | test_fdw_handler | - | | (b '3', c '4', a '2', d '5') |
- postgresql | regress_foreign_data_user | - | postgresql_fdw_validator | | |
+\dew+ regress_*
+ List of foreign-data wrappers
+ Name | Owner | Handler | Validator | Access privileges | FDW options | Description
+--------------------+---------------------------+------------------+--------------------------+-------------------+------------------------------+-------------
+ regress_dummy | regress_foreign_data_user | - | - | | | useless
+ regress_foo | regress_test_role_super | test_fdw_handler | - | | (b '3', c '4', a '2', d '5') |
+ regress_postgresql | regress_foreign_data_user | - | postgresql_fdw_validator | | |
(3 rows)
DROP ROLE regress_test_role_super; -- ERROR
ERROR: role "regress_test_role_super" cannot be dropped because some objects depend on it
-DETAIL: owner of foreign-data wrapper foo
+DETAIL: owner of foreign-data wrapper regress_foo
SET ROLE regress_test_role_super;
-DROP FOREIGN DATA WRAPPER foo;
+DROP FOREIGN DATA WRAPPER regress_foo;
RESET ROLE;
DROP ROLE regress_test_role_super;
-\dew+
- List of foreign-data wrappers
- Name | Owner | Handler | Validator | Access privileges | FDW options | Description
-------------+---------------------------+---------+--------------------------+-------------------+-------------+-------------
- dummy | regress_foreign_data_user | - | - | | | useless
- postgresql | regress_foreign_data_user | - | postgresql_fdw_validator | | |
+\dew+ regress_*
+ List of foreign-data wrappers
+ Name | Owner | Handler | Validator | Access privileges | FDW options | Description
+--------------------+---------------------------+---------+--------------------------+-------------------+-------------+-------------
+ regress_dummy | regress_foreign_data_user | - | - | | | useless
+ regress_postgresql | regress_foreign_data_user | - | postgresql_fdw_validator | | |
(2 rows)
-CREATE FOREIGN DATA WRAPPER foo;
-CREATE SERVER s1 FOREIGN DATA WRAPPER foo;
+CREATE FOREIGN DATA WRAPPER regress_foo;
+CREATE SERVER s1 FOREIGN DATA WRAPPER regress_foo;
COMMENT ON SERVER s1 IS 'foreign server';
CREATE USER MAPPING FOR current_user SERVER s1;
CREATE USER MAPPING FOR current_user SERVER s1; -- ERROR
ERROR: user mapping for "regress_foreign_data_user" already exists for server "s1"
CREATE USER MAPPING IF NOT EXISTS FOR current_user SERVER s1; -- NOTICE
NOTICE: user mapping for "regress_foreign_data_user" already exists for server "s1", skipping
-\dew+
- List of foreign-data wrappers
- Name | Owner | Handler | Validator | Access privileges | FDW options | Description
-------------+---------------------------+---------+--------------------------+-------------------+-------------+-------------
- dummy | regress_foreign_data_user | - | - | | | useless
- foo | regress_foreign_data_user | - | - | | |
- postgresql | regress_foreign_data_user | - | postgresql_fdw_validator | | |
+\dew+ regress_*
+ List of foreign-data wrappers
+ Name | Owner | Handler | Validator | Access privileges | FDW options | Description
+--------------------+---------------------------+---------+--------------------------+-------------------+-------------+-------------
+ regress_dummy | regress_foreign_data_user | - | - | | | useless
+ regress_foo | regress_foreign_data_user | - | - | | |
+ regress_postgresql | regress_foreign_data_user | - | postgresql_fdw_validator | | |
(3 rows)
\des+
List of foreign servers
Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW options | Description
------+---------------------------+----------------------+-------------------+------+---------+-------------+----------------
- s1 | regress_foreign_data_user | foo | | | | | foreign server
+ s1 | regress_foreign_data_user | regress_foo | | | | | foreign server
(1 row)
\deu+
@@ -279,25 +279,25 @@ NOTICE: user mapping for "regress_foreign_data_user" already exists for server
s1 | regress_foreign_data_user |
(1 row)
-DROP FOREIGN DATA WRAPPER foo; -- ERROR
-ERROR: cannot drop foreign-data wrapper foo because other objects depend on it
-DETAIL: server s1 depends on foreign-data wrapper foo
+DROP FOREIGN DATA WRAPPER regress_foo; -- ERROR
+ERROR: cannot drop foreign-data wrapper regress_foo because other objects depend on it
+DETAIL: server s1 depends on foreign-data wrapper regress_foo
user mapping for regress_foreign_data_user on server s1 depends on server s1
HINT: Use DROP ... CASCADE to drop the dependent objects too.
SET ROLE regress_test_role;
-DROP FOREIGN DATA WRAPPER foo CASCADE; -- ERROR
-ERROR: must be owner of foreign-data wrapper foo
+DROP FOREIGN DATA WRAPPER regress_foo CASCADE; -- ERROR
+ERROR: must be owner of foreign-data wrapper regress_foo
RESET ROLE;
-DROP FOREIGN DATA WRAPPER foo CASCADE;
+DROP FOREIGN DATA WRAPPER regress_foo CASCADE;
NOTICE: drop cascades to 2 other objects
DETAIL: drop cascades to server s1
drop cascades to user mapping for regress_foreign_data_user on server s1
-\dew+
- List of foreign-data wrappers
- Name | Owner | Handler | Validator | Access privileges | FDW options | Description
-------------+---------------------------+---------+--------------------------+-------------------+-------------+-------------
- dummy | regress_foreign_data_user | - | - | | | useless
- postgresql | regress_foreign_data_user | - | postgresql_fdw_validator | | |
+\dew+ regress_*
+ List of foreign-data wrappers
+ Name | Owner | Handler | Validator | Access privileges | FDW options | Description
+--------------------+---------------------------+---------+--------------------------+-------------------+-------------+-------------
+ regress_dummy | regress_foreign_data_user | - | - | | | useless
+ regress_postgresql | regress_foreign_data_user | - | postgresql_fdw_validator | | |
(2 rows)
\des+
@@ -313,87 +313,125 @@ drop cascades to user mapping for regress_foreign_data_user on server s1
(0 rows)
-- exercise CREATE SERVER
-CREATE SERVER s1 FOREIGN DATA WRAPPER foo; -- ERROR
-ERROR: foreign-data wrapper "foo" does not exist
-CREATE FOREIGN DATA WRAPPER foo OPTIONS ("test wrapper" 'true');
-CREATE SERVER s1 FOREIGN DATA WRAPPER foo;
-CREATE SERVER s1 FOREIGN DATA WRAPPER foo; -- ERROR
+CREATE SERVER s1 FOREIGN DATA WRAPPER regress_foo; -- ERROR
+ERROR: foreign-data wrapper "regress_foo" does not exist
+CREATE FOREIGN DATA WRAPPER regress_foo OPTIONS ("test wrapper" 'true');
+CREATE SERVER s1 FOREIGN DATA WRAPPER regress_foo;
+CREATE SERVER s1 FOREIGN DATA WRAPPER regress_foo; -- ERROR
ERROR: server "s1" already exists
-CREATE SERVER IF NOT EXISTS s1 FOREIGN DATA WRAPPER foo; -- No ERROR, just NOTICE
+CREATE SERVER IF NOT EXISTS s1 FOREIGN DATA WRAPPER regress_foo; -- No ERROR, just NOTICE
NOTICE: server "s1" already exists, skipping
-CREATE SERVER s2 FOREIGN DATA WRAPPER foo OPTIONS (host 'a', dbname 'b');
-CREATE SERVER s3 TYPE 'oracle' FOREIGN DATA WRAPPER foo;
-CREATE SERVER s4 TYPE 'oracle' FOREIGN DATA WRAPPER foo OPTIONS (host 'a', dbname 'b');
-CREATE SERVER s5 VERSION '15.0' FOREIGN DATA WRAPPER foo;
-CREATE SERVER s6 VERSION '16.0' FOREIGN DATA WRAPPER foo OPTIONS (host 'a', dbname 'b');
-CREATE SERVER s7 TYPE 'oracle' VERSION '17.0' FOREIGN DATA WRAPPER foo OPTIONS (host 'a', dbname 'b');
-CREATE SERVER s8 FOREIGN DATA WRAPPER postgresql OPTIONS (foo '1'); -- ERROR
+CREATE SERVER s2 FOREIGN DATA WRAPPER regress_foo OPTIONS (host 'a', dbname 'b');
+CREATE SERVER s3 TYPE 'oracle' FOREIGN DATA WRAPPER regress_foo;
+CREATE SERVER s4 TYPE 'oracle' FOREIGN DATA WRAPPER regress_foo OPTIONS (host 'a', dbname 'b');
+CREATE SERVER s5 VERSION '15.0' FOREIGN DATA WRAPPER regress_foo;
+CREATE SERVER s6 VERSION '16.0' FOREIGN DATA WRAPPER regress_foo OPTIONS (host 'a', dbname 'b');
+CREATE SERVER s7 TYPE 'oracle' VERSION '17.0' FOREIGN DATA WRAPPER regress_foo OPTIONS (host 'a', dbname 'b');
+CREATE SERVER s8 FOREIGN DATA WRAPPER regress_postgresql OPTIONS (foo '1'); -- ERROR
ERROR: invalid option "foo"
-CREATE SERVER s8 FOREIGN DATA WRAPPER postgresql OPTIONS (host 'localhost', dbname 's8db');
+CREATE SERVER s8 FOREIGN DATA WRAPPER regress_postgresql OPTIONS (host 'localhost', dbname 's8db');
\des+
List of foreign servers
Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW options | Description
------+---------------------------+----------------------+-------------------+--------+---------+-----------------------------------+-------------
- s1 | regress_foreign_data_user | foo | | | | |
- s2 | regress_foreign_data_user | foo | | | | (host 'a', dbname 'b') |
- s3 | regress_foreign_data_user | foo | | oracle | | |
- s4 | regress_foreign_data_user | foo | | oracle | | (host 'a', dbname 'b') |
- s5 | regress_foreign_data_user | foo | | | 15.0 | |
- s6 | regress_foreign_data_user | foo | | | 16.0 | (host 'a', dbname 'b') |
- s7 | regress_foreign_data_user | foo | | oracle | 17.0 | (host 'a', dbname 'b') |
- s8 | regress_foreign_data_user | postgresql | | | | (host 'localhost', dbname 's8db') |
+ s1 | regress_foreign_data_user | regress_foo | | | | |
+ s2 | regress_foreign_data_user | regress_foo | | | | (host 'a', dbname 'b') |
+ s3 | regress_foreign_data_user | regress_foo | | oracle | | |
+ s4 | regress_foreign_data_user | regress_foo | | oracle | | (host 'a', dbname 'b') |
+ s5 | regress_foreign_data_user | regress_foo | | | 15.0 | |
+ s6 | regress_foreign_data_user | regress_foo | | | 16.0 | (host 'a', dbname 'b') |
+ s7 | regress_foreign_data_user | regress_foo | | oracle | 17.0 | (host 'a', dbname 'b') |
+ s8 | regress_foreign_data_user | regress_postgresql | | | | (host 'localhost', dbname 's8db') |
(8 rows)
SET ROLE regress_test_role;
-CREATE SERVER t1 FOREIGN DATA WRAPPER foo; -- ERROR: no usage on FDW
-ERROR: permission denied for foreign-data wrapper foo
+CREATE SERVER t1 FOREIGN DATA WRAPPER regress_foo; -- ERROR: no usage on FDW
+ERROR: permission denied for foreign-data wrapper regress_foo
RESET ROLE;
-GRANT USAGE ON FOREIGN DATA WRAPPER foo TO regress_test_role;
+GRANT USAGE ON FOREIGN DATA WRAPPER regress_foo TO regress_test_role;
SET ROLE regress_test_role;
-CREATE SERVER t1 FOREIGN DATA WRAPPER foo;
+CREATE SERVER t1 FOREIGN DATA WRAPPER regress_foo;
RESET ROLE;
\des+
List of foreign servers
Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW options | Description
------+---------------------------+----------------------+-------------------+--------+---------+-----------------------------------+-------------
- s1 | regress_foreign_data_user | foo | | | | |
- s2 | regress_foreign_data_user | foo | | | | (host 'a', dbname 'b') |
- s3 | regress_foreign_data_user | foo | | oracle | | |
- s4 | regress_foreign_data_user | foo | | oracle | | (host 'a', dbname 'b') |
- s5 | regress_foreign_data_user | foo | | | 15.0 | |
- s6 | regress_foreign_data_user | foo | | | 16.0 | (host 'a', dbname 'b') |
- s7 | regress_foreign_data_user | foo | | oracle | 17.0 | (host 'a', dbname 'b') |
- s8 | regress_foreign_data_user | postgresql | | | | (host 'localhost', dbname 's8db') |
- t1 | regress_test_role | foo | | | | |
+ s1 | regress_foreign_data_user | regress_foo | | | | |
+ s2 | regress_foreign_data_user | regress_foo | | | | (host 'a', dbname 'b') |
+ s3 | regress_foreign_data_user | regress_foo | | oracle | | |
+ s4 | regress_foreign_data_user | regress_foo | | oracle | | (host 'a', dbname 'b') |
+ s5 | regress_foreign_data_user | regress_foo | | | 15.0 | |
+ s6 | regress_foreign_data_user | regress_foo | | | 16.0 | (host 'a', dbname 'b') |
+ s7 | regress_foreign_data_user | regress_foo | | oracle | 17.0 | (host 'a', dbname 'b') |
+ s8 | regress_foreign_data_user | regress_postgresql | | | | (host 'localhost', dbname 's8db') |
+ t1 | regress_test_role | regress_foo | | | | |
(9 rows)
-REVOKE USAGE ON FOREIGN DATA WRAPPER foo FROM regress_test_role;
-GRANT USAGE ON FOREIGN DATA WRAPPER foo TO regress_test_indirect;
+REVOKE USAGE ON FOREIGN DATA WRAPPER regress_foo FROM regress_test_role;
+GRANT USAGE ON FOREIGN DATA WRAPPER regress_foo TO regress_test_indirect;
SET ROLE regress_test_role;
-CREATE SERVER t2 FOREIGN DATA WRAPPER foo; -- ERROR
-ERROR: permission denied for foreign-data wrapper foo
+CREATE SERVER t2 FOREIGN DATA WRAPPER regress_foo; -- ERROR
+ERROR: permission denied for foreign-data wrapper regress_foo
RESET ROLE;
GRANT regress_test_indirect TO regress_test_role;
SET ROLE regress_test_role;
-CREATE SERVER t2 FOREIGN DATA WRAPPER foo;
+CREATE SERVER t2 FOREIGN DATA WRAPPER regress_foo;
\des+
List of foreign servers
Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW options | Description
------+---------------------------+----------------------+-------------------+--------+---------+-----------------------------------+-------------
- s1 | regress_foreign_data_user | foo | | | | |
- s2 | regress_foreign_data_user | foo | | | | (host 'a', dbname 'b') |
- s3 | regress_foreign_data_user | foo | | oracle | | |
- s4 | regress_foreign_data_user | foo | | oracle | | (host 'a', dbname 'b') |
- s5 | regress_foreign_data_user | foo | | | 15.0 | |
- s6 | regress_foreign_data_user | foo | | | 16.0 | (host 'a', dbname 'b') |
- s7 | regress_foreign_data_user | foo | | oracle | 17.0 | (host 'a', dbname 'b') |
- s8 | regress_foreign_data_user | postgresql | | | | (host 'localhost', dbname 's8db') |
- t1 | regress_test_role | foo | | | | |
- t2 | regress_test_role | foo | | | | |
+ s1 | regress_foreign_data_user | regress_foo | | | | |
+ s2 | regress_foreign_data_user | regress_foo | | | | (host 'a', dbname 'b') |
+ s3 | regress_foreign_data_user | regress_foo | | oracle | | |
+ s4 | regress_foreign_data_user | regress_foo | | oracle | | (host 'a', dbname 'b') |
+ s5 | regress_foreign_data_user | regress_foo | | | 15.0 | |
+ s6 | regress_foreign_data_user | regress_foo | | | 16.0 | (host 'a', dbname 'b') |
+ s7 | regress_foreign_data_user | regress_foo | | oracle | 17.0 | (host 'a', dbname 'b') |
+ s8 | regress_foreign_data_user | regress_postgresql | | | | (host 'localhost', dbname 's8db') |
+ t1 | regress_test_role | regress_foo | | | | |
+ t2 | regress_test_role | regress_foo | | | | |
(10 rows)
RESET ROLE;
REVOKE regress_test_indirect FROM regress_test_role;
+--
+-- test pg_connection_fdw
+--
+\set VERBOSITY terse
+CREATE SERVER connection_server FOREIGN DATA WRAPPER pg_connection_fdw
+ OPTIONS (host 'thehost', client_encoding 'LATIN1'); -- fail
+ERROR: invalid option "client_encoding"
+CREATE SERVER connection_server FOREIGN DATA WRAPPER pg_connection_fdw
+ OPTIONS (host 'thehost', nonsense 'asdf'); -- fail
+ERROR: invalid option "nonsense"
+CREATE SERVER connection_server FOREIGN DATA WRAPPER pg_connection_fdw
+ OPTIONS (host 'thehost', password 'secret'); -- fail
+ERROR: invalid option "password"
+\set VERBOSITY default
+CREATE SERVER connection_server FOREIGN DATA WRAPPER pg_connection_fdw
+ OPTIONS (host 'thehost', port '5432');
+CREATE USER MAPPING FOR regress_test_role SERVER connection_server
+ OPTIONS (user 'role', password 'secret', host 'otherhost'); -- fail
+ERROR: invalid option "host"
+CREATE USER MAPPING FOR regress_test_role SERVER connection_server
+ OPTIONS (user 'role', password 'secret');
+CREATE USER MAPPING FOR PUBLIC SERVER connection_server
+ OPTIONS (user 'publicuser', password $pwd$'\"$# secret'$pwd$);
+SELECT pg_conninfo_from_server('connection_server', 'regress_test_role', false);
+ pg_conninfo_from_server
+------------------------------------------------------------------
+ host = 'thehost' port = '5432' user = 'role' password = 'secret'
+(1 row)
+
+SELECT pg_conninfo_from_server('connection_server', 'regress_test_role2', false);
+ pg_conninfo_from_server
+----------------------------------------------------------------------------------
+ host = 'thehost' port = '5432' user = 'publicuser' password = '\'\\"$# secret\''
+(1 row)
+
+DROP USER MAPPING FOR regress_test_role SERVER connection_server;
+DROP USER MAPPING FOR PUBLIC SERVER connection_server;
+DROP SERVER connection_server;
-- ALTER SERVER
ALTER SERVER s0; -- ERROR
ERROR: syntax error at or near ";"
@@ -410,18 +448,18 @@ GRANT USAGE ON FOREIGN SERVER s6 TO regress_test_role2 WITH GRANT OPTION;
List of foreign servers
Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW options | Description
------+---------------------------+----------------------+-------------------------------------------------------+--------+---------+-----------------------------------+-------------
- s1 | regress_foreign_data_user | foo | regress_foreign_data_user=U/regress_foreign_data_user+| | 1.0 | (servername 's1') |
+ s1 | regress_foreign_data_user | regress_foo | regress_foreign_data_user=U/regress_foreign_data_user+| | 1.0 | (servername 's1') |
| | | regress_test_role=U/regress_foreign_data_user | | | |
- s2 | regress_foreign_data_user | foo | | | 1.1 | (host 'a', dbname 'b') |
- s3 | regress_foreign_data_user | foo | | oracle | | ("tns name" 'orcl', port '1521') |
- s4 | regress_foreign_data_user | foo | | oracle | | (host 'a', dbname 'b') |
- s5 | regress_foreign_data_user | foo | | | 15.0 | |
- s6 | regress_foreign_data_user | foo | regress_foreign_data_user=U/regress_foreign_data_user+| | 16.0 | (host 'a', dbname 'b') |
+ s2 | regress_foreign_data_user | regress_foo | | | 1.1 | (host 'a', dbname 'b') |
+ s3 | regress_foreign_data_user | regress_foo | | oracle | | ("tns name" 'orcl', port '1521') |
+ s4 | regress_foreign_data_user | regress_foo | | oracle | | (host 'a', dbname 'b') |
+ s5 | regress_foreign_data_user | regress_foo | | | 15.0 | |
+ s6 | regress_foreign_data_user | regress_foo | regress_foreign_data_user=U/regress_foreign_data_user+| | 16.0 | (host 'a', dbname 'b') |
| | | regress_test_role2=U*/regress_foreign_data_user | | | |
- s7 | regress_foreign_data_user | foo | | oracle | 17.0 | (host 'a', dbname 'b') |
- s8 | regress_foreign_data_user | postgresql | | | | (host 'localhost', dbname 's8db') |
- t1 | regress_test_role | foo | | | | |
- t2 | regress_test_role | foo | | | | |
+ s7 | regress_foreign_data_user | regress_foo | | oracle | 17.0 | (host 'a', dbname 'b') |
+ s8 | regress_foreign_data_user | regress_postgresql | | | | (host 'localhost', dbname 's8db') |
+ t1 | regress_test_role | regress_foo | | | | |
+ t2 | regress_test_role | regress_foo | | | | |
(10 rows)
SET ROLE regress_test_role;
@@ -435,7 +473,7 @@ GRANT regress_test_role2 TO regress_test_role;
SET ROLE regress_test_role;
ALTER SERVER s1 VERSION '1.1';
ALTER SERVER s1 OWNER TO regress_test_role2; -- ERROR
-ERROR: permission denied for foreign-data wrapper foo
+ERROR: permission denied for foreign-data wrapper regress_foo
RESET ROLE;
ALTER SERVER s8 OPTIONS (foo '1'); -- ERROR option validation
ERROR: invalid option "foo"
@@ -448,29 +486,29 @@ GRANT regress_test_indirect TO regress_test_role;
SET ROLE regress_test_role;
ALTER SERVER s1 OWNER TO regress_test_indirect;
RESET ROLE;
-GRANT USAGE ON FOREIGN DATA WRAPPER foo TO regress_test_indirect;
+GRANT USAGE ON FOREIGN DATA WRAPPER regress_foo TO regress_test_indirect;
SET ROLE regress_test_role;
ALTER SERVER s1 OWNER TO regress_test_indirect;
RESET ROLE;
DROP ROLE regress_test_indirect; -- ERROR
ERROR: role "regress_test_indirect" cannot be dropped because some objects depend on it
-DETAIL: privileges for foreign-data wrapper foo
+DETAIL: privileges for foreign-data wrapper regress_foo
owner of server s1
\des+
List of foreign servers
Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW options | Description
------+---------------------------+----------------------+-------------------------------------------------------+--------+---------+--------------------------------------+-------------
- s1 | regress_test_indirect | foo | regress_test_indirect=U/regress_test_indirect | | 1.1 | (servername 's1') |
- s2 | regress_foreign_data_user | foo | | | 1.1 | (host 'a', dbname 'b') |
- s3 | regress_foreign_data_user | foo | | oracle | | ("tns name" 'orcl', port '1521') |
- s4 | regress_foreign_data_user | foo | | oracle | | (host 'a', dbname 'b') |
- s5 | regress_foreign_data_user | foo | | | 15.0 | |
- s6 | regress_foreign_data_user | foo | regress_foreign_data_user=U/regress_foreign_data_user+| | 16.0 | (host 'a', dbname 'b') |
+ s1 | regress_test_indirect | regress_foo | regress_test_indirect=U/regress_test_indirect | | 1.1 | (servername 's1') |
+ s2 | regress_foreign_data_user | regress_foo | | | 1.1 | (host 'a', dbname 'b') |
+ s3 | regress_foreign_data_user | regress_foo | | oracle | | ("tns name" 'orcl', port '1521') |
+ s4 | regress_foreign_data_user | regress_foo | | oracle | | (host 'a', dbname 'b') |
+ s5 | regress_foreign_data_user | regress_foo | | | 15.0 | |
+ s6 | regress_foreign_data_user | regress_foo | regress_foreign_data_user=U/regress_foreign_data_user+| | 16.0 | (host 'a', dbname 'b') |
| | | regress_test_role2=U*/regress_foreign_data_user | | | |
- s7 | regress_foreign_data_user | foo | | oracle | 17.0 | (host 'a', dbname 'b') |
- s8 | regress_foreign_data_user | postgresql | | | | (dbname 'db1', connect_timeout '30') |
- t1 | regress_test_role | foo | | | | |
- t2 | regress_test_role | foo | | | | |
+ s7 | regress_foreign_data_user | regress_foo | | oracle | 17.0 | (host 'a', dbname 'b') |
+ s8 | regress_foreign_data_user | regress_postgresql | | | | (dbname 'db1', connect_timeout '30') |
+ t1 | regress_test_role | regress_foo | | | | |
+ t2 | regress_test_role | regress_foo | | | | |
(10 rows)
ALTER SERVER s8 RENAME to s8new;
@@ -478,17 +516,17 @@ ALTER SERVER s8 RENAME to s8new;
List of foreign servers
Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW options | Description
-------+---------------------------+----------------------+-------------------------------------------------------+--------+---------+--------------------------------------+-------------
- s1 | regress_test_indirect | foo | regress_test_indirect=U/regress_test_indirect | | 1.1 | (servername 's1') |
- s2 | regress_foreign_data_user | foo | | | 1.1 | (host 'a', dbname 'b') |
- s3 | regress_foreign_data_user | foo | | oracle | | ("tns name" 'orcl', port '1521') |
- s4 | regress_foreign_data_user | foo | | oracle | | (host 'a', dbname 'b') |
- s5 | regress_foreign_data_user | foo | | | 15.0 | |
- s6 | regress_foreign_data_user | foo | regress_foreign_data_user=U/regress_foreign_data_user+| | 16.0 | (host 'a', dbname 'b') |
+ s1 | regress_test_indirect | regress_foo | regress_test_indirect=U/regress_test_indirect | | 1.1 | (servername 's1') |
+ s2 | regress_foreign_data_user | regress_foo | | | 1.1 | (host 'a', dbname 'b') |
+ s3 | regress_foreign_data_user | regress_foo | | oracle | | ("tns name" 'orcl', port '1521') |
+ s4 | regress_foreign_data_user | regress_foo | | oracle | | (host 'a', dbname 'b') |
+ s5 | regress_foreign_data_user | regress_foo | | | 15.0 | |
+ s6 | regress_foreign_data_user | regress_foo | regress_foreign_data_user=U/regress_foreign_data_user+| | 16.0 | (host 'a', dbname 'b') |
| | | regress_test_role2=U*/regress_foreign_data_user | | | |
- s7 | regress_foreign_data_user | foo | | oracle | 17.0 | (host 'a', dbname 'b') |
- s8new | regress_foreign_data_user | postgresql | | | | (dbname 'db1', connect_timeout '30') |
- t1 | regress_test_role | foo | | | | |
- t2 | regress_test_role | foo | | | | |
+ s7 | regress_foreign_data_user | regress_foo | | oracle | 17.0 | (host 'a', dbname 'b') |
+ s8new | regress_foreign_data_user | regress_postgresql | | | | (dbname 'db1', connect_timeout '30') |
+ t1 | regress_test_role | regress_foo | | | | |
+ t2 | regress_test_role | regress_foo | | | | |
(10 rows)
ALTER SERVER s8new RENAME to s8;
@@ -501,16 +539,16 @@ NOTICE: server "nonexistent" does not exist, skipping
List of foreign servers
Name | Owner | Foreign-data wrapper
------+---------------------------+----------------------
- s1 | regress_test_indirect | foo
- s2 | regress_foreign_data_user | foo
- s3 | regress_foreign_data_user | foo
- s4 | regress_foreign_data_user | foo
- s5 | regress_foreign_data_user | foo
- s6 | regress_foreign_data_user | foo
- s7 | regress_foreign_data_user | foo
- s8 | regress_foreign_data_user | postgresql
- t1 | regress_test_role | foo
- t2 | regress_test_role | foo
+ s1 | regress_test_indirect | regress_foo
+ s2 | regress_foreign_data_user | regress_foo
+ s3 | regress_foreign_data_user | regress_foo
+ s4 | regress_foreign_data_user | regress_foo
+ s5 | regress_foreign_data_user | regress_foo
+ s6 | regress_foreign_data_user | regress_foo
+ s7 | regress_foreign_data_user | regress_foo
+ s8 | regress_foreign_data_user | regress_postgresql
+ t1 | regress_test_role | regress_foo
+ t2 | regress_test_role | regress_foo
(10 rows)
SET ROLE regress_test_role;
@@ -522,15 +560,15 @@ RESET ROLE;
List of foreign servers
Name | Owner | Foreign-data wrapper
------+---------------------------+----------------------
- s2 | regress_foreign_data_user | foo
- s3 | regress_foreign_data_user | foo
- s4 | regress_foreign_data_user | foo
- s5 | regress_foreign_data_user | foo
- s6 | regress_foreign_data_user | foo
- s7 | regress_foreign_data_user | foo
- s8 | regress_foreign_data_user | postgresql
- t1 | regress_test_role | foo
- t2 | regress_test_role | foo
+ s2 | regress_foreign_data_user | regress_foo
+ s3 | regress_foreign_data_user | regress_foo
+ s4 | regress_foreign_data_user | regress_foo
+ s5 | regress_foreign_data_user | regress_foo
+ s6 | regress_foreign_data_user | regress_foo
+ s7 | regress_foreign_data_user | regress_foo
+ s8 | regress_foreign_data_user | regress_postgresql
+ t1 | regress_test_role | regress_foo
+ t2 | regress_test_role | regress_foo
(9 rows)
ALTER SERVER s2 OWNER TO regress_test_role;
@@ -541,14 +579,14 @@ RESET ROLE;
List of foreign servers
Name | Owner | Foreign-data wrapper
------+---------------------------+----------------------
- s3 | regress_foreign_data_user | foo
- s4 | regress_foreign_data_user | foo
- s5 | regress_foreign_data_user | foo
- s6 | regress_foreign_data_user | foo
- s7 | regress_foreign_data_user | foo
- s8 | regress_foreign_data_user | postgresql
- t1 | regress_test_role | foo
- t2 | regress_test_role | foo
+ s3 | regress_foreign_data_user | regress_foo
+ s4 | regress_foreign_data_user | regress_foo
+ s5 | regress_foreign_data_user | regress_foo
+ s6 | regress_foreign_data_user | regress_foo
+ s7 | regress_foreign_data_user | regress_foo
+ s8 | regress_foreign_data_user | regress_postgresql
+ t1 | regress_test_role | regress_foo
+ t2 | regress_test_role | regress_foo
(8 rows)
CREATE USER MAPPING FOR current_user SERVER s3;
@@ -569,13 +607,13 @@ NOTICE: drop cascades to user mapping for regress_foreign_data_user on server s
List of foreign servers
Name | Owner | Foreign-data wrapper
------+---------------------------+----------------------
- s4 | regress_foreign_data_user | foo
- s5 | regress_foreign_data_user | foo
- s6 | regress_foreign_data_user | foo
- s7 | regress_foreign_data_user | foo
- s8 | regress_foreign_data_user | postgresql
- t1 | regress_test_role | foo
- t2 | regress_test_role | foo
+ s4 | regress_foreign_data_user | regress_foo
+ s5 | regress_foreign_data_user | regress_foo
+ s6 | regress_foreign_data_user | regress_foo
+ s7 | regress_foreign_data_user | regress_foo
+ s8 | regress_foreign_data_user | regress_postgresql
+ t1 | regress_test_role | regress_foo
+ t2 | regress_test_role | regress_foo
(7 rows)
\deu
@@ -690,7 +728,7 @@ DROP SERVER s7;
-- CREATE FOREIGN TABLE
CREATE SCHEMA foreign_schema;
-CREATE SERVER s0 FOREIGN DATA WRAPPER dummy;
+CREATE SERVER s0 FOREIGN DATA WRAPPER regress_dummy;
CREATE FOREIGN TABLE ft1 (); -- ERROR
ERROR: syntax error at or near ";"
LINE 1: CREATE FOREIGN TABLE ft1 ();
@@ -758,9 +796,9 @@ CREATE INDEX id_ft1_c2 ON ft1 (c2); -- ERROR
ERROR: cannot create index on relation "ft1"
DETAIL: This operation is not supported for foreign tables.
SELECT * FROM ft1; -- ERROR
-ERROR: foreign-data wrapper "dummy" has no handler
+ERROR: foreign-data wrapper "regress_dummy" has no handler
EXPLAIN SELECT * FROM ft1; -- ERROR
-ERROR: foreign-data wrapper "dummy" has no handler
+ERROR: foreign-data wrapper "regress_dummy" has no handler
CREATE TABLE lt1 (a INT) PARTITION BY RANGE (a);
CREATE FOREIGN TABLE ft_part1
PARTITION OF lt1 FOR VALUES FROM (0) TO (1000) SERVER s0;
@@ -966,30 +1004,30 @@ NOTICE: relation "doesnt_exist_ft1" does not exist, skipping
ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 RENAME TO foreign_table_1;
NOTICE: relation "doesnt_exist_ft1" does not exist, skipping
-- Information schema
-SELECT * FROM information_schema.foreign_data_wrappers ORDER BY 1, 2;
+SELECT * FROM information_schema.foreign_data_wrappers WHERE authorization_identifier = 'regress_foreign_data_user' ORDER BY 1, 2;
foreign_data_wrapper_catalog | foreign_data_wrapper_name | authorization_identifier | library_name | foreign_data_wrapper_language
------------------------------+---------------------------+---------------------------+--------------+-------------------------------
- regression | dummy | regress_foreign_data_user | | c
- regression | foo | regress_foreign_data_user | | c
- regression | postgresql | regress_foreign_data_user | | c
+ regression | regress_dummy | regress_foreign_data_user | | c
+ regression | regress_foo | regress_foreign_data_user | | c
+ regression | regress_postgresql | regress_foreign_data_user | | c
(3 rows)
SELECT * FROM information_schema.foreign_data_wrapper_options ORDER BY 1, 2, 3;
foreign_data_wrapper_catalog | foreign_data_wrapper_name | option_name | option_value
------------------------------+---------------------------+--------------+--------------
- regression | foo | test wrapper | true
+ regression | regress_foo | test wrapper | true
(1 row)
SELECT * FROM information_schema.foreign_servers ORDER BY 1, 2;
foreign_server_catalog | foreign_server_name | foreign_data_wrapper_catalog | foreign_data_wrapper_name | foreign_server_type | foreign_server_version | authorization_identifier
------------------------+---------------------+------------------------------+---------------------------+---------------------+------------------------+---------------------------
- regression | s0 | regression | dummy | | | regress_foreign_data_user
- regression | s4 | regression | foo | oracle | | regress_foreign_data_user
- regression | s5 | regression | foo | | 15.0 | regress_test_role
- regression | s6 | regression | foo | | 16.0 | regress_test_indirect
- regression | s8 | regression | postgresql | | | regress_foreign_data_user
- regression | t1 | regression | foo | | | regress_test_indirect
- regression | t2 | regression | foo | | | regress_test_role
+ regression | s0 | regression | regress_dummy | | | regress_foreign_data_user
+ regression | s4 | regression | regress_foo | oracle | | regress_foreign_data_user
+ regression | s5 | regression | regress_foo | | 15.0 | regress_test_role
+ regression | s6 | regression | regress_foo | | 16.0 | regress_test_indirect
+ regression | s8 | regression | regress_postgresql | | | regress_foreign_data_user
+ regression | t1 | regression | regress_foo | | | regress_test_indirect
+ regression | t2 | regression | regress_foo | | | regress_test_role
(7 rows)
SELECT * FROM information_schema.foreign_server_options ORDER BY 1, 2, 3;
@@ -1028,20 +1066,20 @@ SELECT * FROM information_schema.user_mapping_options ORDER BY lower(authorizati
regress_test_role | regression | t1 | username | bob
(7 rows)
-SELECT * FROM information_schema.usage_privileges WHERE object_type LIKE 'FOREIGN%' AND object_name IN ('s6', 'foo') ORDER BY 1, 2, 3, 4, 5;
+SELECT * FROM information_schema.usage_privileges WHERE object_type LIKE 'FOREIGN%' AND object_name IN ('s6', 'regress_foo') ORDER BY 1, 2, 3, 4, 5;
grantor | grantee | object_catalog | object_schema | object_name | object_type | privilege_type | is_grantable
---------------------------+---------------------------+----------------+---------------+-------------+----------------------+----------------+--------------
- regress_foreign_data_user | regress_foreign_data_user | regression | | foo | FOREIGN DATA WRAPPER | USAGE | YES
- regress_foreign_data_user | regress_test_indirect | regression | | foo | FOREIGN DATA WRAPPER | USAGE | NO
+ regress_foreign_data_user | regress_foreign_data_user | regression | | regress_foo | FOREIGN DATA WRAPPER | USAGE | YES
+ regress_foreign_data_user | regress_test_indirect | regression | | regress_foo | FOREIGN DATA WRAPPER | USAGE | NO
regress_test_indirect | regress_test_indirect | regression | | s6 | FOREIGN SERVER | USAGE | YES
regress_test_indirect | regress_test_role2 | regression | | s6 | FOREIGN SERVER | USAGE | YES
(4 rows)
-SELECT * FROM information_schema.role_usage_grants WHERE object_type LIKE 'FOREIGN%' AND object_name IN ('s6', 'foo') ORDER BY 1, 2, 3, 4, 5;
+SELECT * FROM information_schema.role_usage_grants WHERE object_type LIKE 'FOREIGN%' AND object_name IN ('s6', 'regress_foo') ORDER BY 1, 2, 3, 4, 5;
grantor | grantee | object_catalog | object_schema | object_name | object_type | privilege_type | is_grantable
---------------------------+---------------------------+----------------+---------------+-------------+----------------------+----------------+--------------
- regress_foreign_data_user | regress_foreign_data_user | regression | | foo | FOREIGN DATA WRAPPER | USAGE | YES
- regress_foreign_data_user | regress_test_indirect | regression | | foo | FOREIGN DATA WRAPPER | USAGE | NO
+ regress_foreign_data_user | regress_foreign_data_user | regression | | regress_foo | FOREIGN DATA WRAPPER | USAGE | YES
+ regress_foreign_data_user | regress_test_indirect | regression | | regress_foo | FOREIGN DATA WRAPPER | USAGE | NO
regress_test_indirect | regress_test_indirect | regression | | s6 | FOREIGN SERVER | USAGE | YES
regress_test_indirect | regress_test_role2 | regression | | s6 | FOREIGN SERVER | USAGE | YES
(4 rows)
@@ -1071,18 +1109,18 @@ SELECT * FROM information_schema.user_mapping_options ORDER BY 1, 2, 3, 4;
regress_test_role | regression | t1 | username | bob
(5 rows)
-SELECT * FROM information_schema.usage_privileges WHERE object_type LIKE 'FOREIGN%' AND object_name IN ('s6', 'foo') ORDER BY 1, 2, 3, 4, 5;
+SELECT * FROM information_schema.usage_privileges WHERE object_type LIKE 'FOREIGN%' AND object_name IN ('s6', 'regress_foo') ORDER BY 1, 2, 3, 4, 5;
grantor | grantee | object_catalog | object_schema | object_name | object_type | privilege_type | is_grantable
---------------------------+-----------------------+----------------+---------------+-------------+----------------------+----------------+--------------
- regress_foreign_data_user | regress_test_indirect | regression | | foo | FOREIGN DATA WRAPPER | USAGE | NO
+ regress_foreign_data_user | regress_test_indirect | regression | | regress_foo | FOREIGN DATA WRAPPER | USAGE | NO
regress_test_indirect | regress_test_indirect | regression | | s6 | FOREIGN SERVER | USAGE | YES
regress_test_indirect | regress_test_role2 | regression | | s6 | FOREIGN SERVER | USAGE | YES
(3 rows)
-SELECT * FROM information_schema.role_usage_grants WHERE object_type LIKE 'FOREIGN%' AND object_name IN ('s6', 'foo') ORDER BY 1, 2, 3, 4, 5;
+SELECT * FROM information_schema.role_usage_grants WHERE object_type LIKE 'FOREIGN%' AND object_name IN ('s6', 'regress_foo') ORDER BY 1, 2, 3, 4, 5;
grantor | grantee | object_catalog | object_schema | object_name | object_type | privilege_type | is_grantable
---------------------------+-----------------------+----------------+---------------+-------------+----------------------+----------------+--------------
- regress_foreign_data_user | regress_test_indirect | regression | | foo | FOREIGN DATA WRAPPER | USAGE | NO
+ regress_foreign_data_user | regress_test_indirect | regression | | regress_foo | FOREIGN DATA WRAPPER | USAGE | NO
regress_test_indirect | regress_test_indirect | regression | | s6 | FOREIGN SERVER | USAGE | YES
regress_test_indirect | regress_test_role2 | regression | | s6 | FOREIGN SERVER | USAGE | YES
(3 rows)
@@ -1098,13 +1136,13 @@ SELECT * FROM information_schema.user_mapping_options ORDER BY 1, 2, 3, 4;
RESET ROLE;
-- has_foreign_data_wrapper_privilege
SELECT has_foreign_data_wrapper_privilege('regress_test_role',
- (SELECT oid FROM pg_foreign_data_wrapper WHERE fdwname='foo'), 'USAGE');
+ (SELECT oid FROM pg_foreign_data_wrapper WHERE fdwname='regress_foo'), 'USAGE');
has_foreign_data_wrapper_privilege
------------------------------------
t
(1 row)
-SELECT has_foreign_data_wrapper_privilege('regress_test_role', 'foo', 'USAGE');
+SELECT has_foreign_data_wrapper_privilege('regress_test_role', 'regress_foo', 'USAGE');
has_foreign_data_wrapper_privilege
------------------------------------
t
@@ -1112,34 +1150,34 @@ SELECT has_foreign_data_wrapper_privilege('regress_test_role', 'foo', 'USAGE');
SELECT has_foreign_data_wrapper_privilege(
(SELECT oid FROM pg_roles WHERE rolname='regress_test_role'),
- (SELECT oid FROM pg_foreign_data_wrapper WHERE fdwname='foo'), 'USAGE');
+ (SELECT oid FROM pg_foreign_data_wrapper WHERE fdwname='regress_foo'), 'USAGE');
has_foreign_data_wrapper_privilege
------------------------------------
t
(1 row)
SELECT has_foreign_data_wrapper_privilege(
- (SELECT oid FROM pg_foreign_data_wrapper WHERE fdwname='foo'), 'USAGE');
+ (SELECT oid FROM pg_foreign_data_wrapper WHERE fdwname='regress_foo'), 'USAGE');
has_foreign_data_wrapper_privilege
------------------------------------
t
(1 row)
SELECT has_foreign_data_wrapper_privilege(
- (SELECT oid FROM pg_roles WHERE rolname='regress_test_role'), 'foo', 'USAGE');
+ (SELECT oid FROM pg_roles WHERE rolname='regress_test_role'), 'regress_foo', 'USAGE');
has_foreign_data_wrapper_privilege
------------------------------------
t
(1 row)
-SELECT has_foreign_data_wrapper_privilege('foo', 'USAGE');
+SELECT has_foreign_data_wrapper_privilege('regress_foo', 'USAGE');
has_foreign_data_wrapper_privilege
------------------------------------
t
(1 row)
-GRANT USAGE ON FOREIGN DATA WRAPPER foo TO regress_test_role;
-SELECT has_foreign_data_wrapper_privilege('regress_test_role', 'foo', 'USAGE');
+GRANT USAGE ON FOREIGN DATA WRAPPER regress_foo TO regress_test_role;
+SELECT has_foreign_data_wrapper_privilege('regress_test_role', 'regress_foo', 'USAGE');
has_foreign_data_wrapper_privilege
------------------------------------
t
@@ -1199,25 +1237,25 @@ GRANT USAGE ON FOREIGN SERVER s4 TO regress_test_role;
DROP USER MAPPING FOR public SERVER s4;
ALTER SERVER s6 OPTIONS (DROP host, DROP dbname);
ALTER USER MAPPING FOR regress_test_role SERVER s6 OPTIONS (DROP username);
-ALTER FOREIGN DATA WRAPPER foo VALIDATOR postgresql_fdw_validator;
+ALTER FOREIGN DATA WRAPPER regress_foo VALIDATOR postgresql_fdw_validator;
WARNING: changing the foreign-data wrapper validator can cause the options for dependent objects to become invalid
-- Privileges
SET ROLE regress_unprivileged_role;
-CREATE FOREIGN DATA WRAPPER foobar; -- ERROR
-ERROR: permission denied to create foreign-data wrapper "foobar"
+CREATE FOREIGN DATA WRAPPER regress_foobar; -- ERROR
+ERROR: permission denied to create foreign-data wrapper "regress_foobar"
HINT: Must be superuser to create a foreign-data wrapper.
-ALTER FOREIGN DATA WRAPPER foo OPTIONS (gotcha 'true'); -- ERROR
-ERROR: permission denied to alter foreign-data wrapper "foo"
+ALTER FOREIGN DATA WRAPPER regress_foo OPTIONS (gotcha 'true'); -- ERROR
+ERROR: permission denied to alter foreign-data wrapper "regress_foo"
HINT: Must be superuser to alter a foreign-data wrapper.
-ALTER FOREIGN DATA WRAPPER foo OWNER TO regress_unprivileged_role; -- ERROR
-ERROR: permission denied to change owner of foreign-data wrapper "foo"
+ALTER FOREIGN DATA WRAPPER regress_foo OWNER TO regress_unprivileged_role; -- ERROR
+ERROR: permission denied to change owner of foreign-data wrapper "regress_foo"
HINT: Must be superuser to change owner of a foreign-data wrapper.
-DROP FOREIGN DATA WRAPPER foo; -- ERROR
-ERROR: must be owner of foreign-data wrapper foo
-GRANT USAGE ON FOREIGN DATA WRAPPER foo TO regress_test_role; -- ERROR
-ERROR: permission denied for foreign-data wrapper foo
-CREATE SERVER s9 FOREIGN DATA WRAPPER foo; -- ERROR
-ERROR: permission denied for foreign-data wrapper foo
+DROP FOREIGN DATA WRAPPER regress_foo; -- ERROR
+ERROR: must be owner of foreign-data wrapper regress_foo
+GRANT USAGE ON FOREIGN DATA WRAPPER regress_foo TO regress_test_role; -- ERROR
+ERROR: permission denied for foreign-data wrapper regress_foo
+CREATE SERVER s9 FOREIGN DATA WRAPPER regress_foo; -- ERROR
+ERROR: permission denied for foreign-data wrapper regress_foo
ALTER SERVER s4 VERSION '0.5'; -- ERROR
ERROR: must be owner of foreign server s4
ALTER SERVER s4 OWNER TO regress_unprivileged_role; -- ERROR
@@ -1233,21 +1271,21 @@ ERROR: must be owner of foreign server s6
DROP USER MAPPING FOR regress_test_role SERVER s6; -- ERROR
ERROR: must be owner of foreign server s6
RESET ROLE;
-GRANT USAGE ON FOREIGN DATA WRAPPER postgresql TO regress_unprivileged_role;
-GRANT USAGE ON FOREIGN DATA WRAPPER foo TO regress_unprivileged_role WITH GRANT OPTION;
+GRANT USAGE ON FOREIGN DATA WRAPPER regress_postgresql TO regress_unprivileged_role;
+GRANT USAGE ON FOREIGN DATA WRAPPER regress_foo TO regress_unprivileged_role WITH GRANT OPTION;
SET ROLE regress_unprivileged_role;
-CREATE FOREIGN DATA WRAPPER foobar; -- ERROR
-ERROR: permission denied to create foreign-data wrapper "foobar"
+CREATE FOREIGN DATA WRAPPER regress_foobar; -- ERROR
+ERROR: permission denied to create foreign-data wrapper "regress_foobar"
HINT: Must be superuser to create a foreign-data wrapper.
-ALTER FOREIGN DATA WRAPPER foo OPTIONS (gotcha 'true'); -- ERROR
-ERROR: permission denied to alter foreign-data wrapper "foo"
+ALTER FOREIGN DATA WRAPPER regress_foo OPTIONS (gotcha 'true'); -- ERROR
+ERROR: permission denied to alter foreign-data wrapper "regress_foo"
HINT: Must be superuser to alter a foreign-data wrapper.
-DROP FOREIGN DATA WRAPPER foo; -- ERROR
-ERROR: must be owner of foreign-data wrapper foo
-GRANT USAGE ON FOREIGN DATA WRAPPER postgresql TO regress_test_role; -- WARNING
-WARNING: no privileges were granted for "postgresql"
-GRANT USAGE ON FOREIGN DATA WRAPPER foo TO regress_test_role;
-CREATE SERVER s9 FOREIGN DATA WRAPPER postgresql;
+DROP FOREIGN DATA WRAPPER regress_foo; -- ERROR
+ERROR: must be owner of foreign-data wrapper regress_foo
+GRANT USAGE ON FOREIGN DATA WRAPPER regress_postgresql TO regress_test_role; -- WARNING
+WARNING: no privileges were granted for "regress_postgresql"
+GRANT USAGE ON FOREIGN DATA WRAPPER regress_foo TO regress_test_role;
+CREATE SERVER s9 FOREIGN DATA WRAPPER regress_postgresql;
ALTER SERVER s6 VERSION '0.5'; -- ERROR
ERROR: must be owner of foreign server s6
DROP SERVER s6; -- ERROR
@@ -1263,15 +1301,15 @@ ERROR: must be owner of foreign server s6
DROP USER MAPPING FOR regress_test_role SERVER s6; -- ERROR
ERROR: must be owner of foreign server s6
RESET ROLE;
-REVOKE USAGE ON FOREIGN DATA WRAPPER foo FROM regress_unprivileged_role; -- ERROR
+REVOKE USAGE ON FOREIGN DATA WRAPPER regress_foo FROM regress_unprivileged_role; -- ERROR
ERROR: dependent privileges exist
HINT: Use CASCADE to revoke them too.
-REVOKE USAGE ON FOREIGN DATA WRAPPER foo FROM regress_unprivileged_role CASCADE;
+REVOKE USAGE ON FOREIGN DATA WRAPPER regress_foo FROM regress_unprivileged_role CASCADE;
SET ROLE regress_unprivileged_role;
-GRANT USAGE ON FOREIGN DATA WRAPPER foo TO regress_test_role; -- ERROR
-ERROR: permission denied for foreign-data wrapper foo
-CREATE SERVER s10 FOREIGN DATA WRAPPER foo; -- ERROR
-ERROR: permission denied for foreign-data wrapper foo
+GRANT USAGE ON FOREIGN DATA WRAPPER regress_foo TO regress_test_role; -- ERROR
+ERROR: permission denied for foreign-data wrapper regress_foo
+CREATE SERVER s10 FOREIGN DATA WRAPPER regress_foo; -- ERROR
+ERROR: permission denied for foreign-data wrapper regress_foo
ALTER SERVER s9 VERSION '1.1';
GRANT USAGE ON FOREIGN SERVER s9 TO regress_test_role;
CREATE USER MAPPING FOR current_user SERVER s9;
@@ -1280,7 +1318,7 @@ NOTICE: drop cascades to 2 other objects
DETAIL: drop cascades to user mapping for public on server s9
drop cascades to user mapping for regress_unprivileged_role on server s9
RESET ROLE;
-CREATE SERVER s9 FOREIGN DATA WRAPPER foo;
+CREATE SERVER s9 FOREIGN DATA WRAPPER regress_foo;
GRANT USAGE ON FOREIGN SERVER s9 TO regress_unprivileged_role;
SET ROLE regress_unprivileged_role;
ALTER SERVER s9 VERSION '1.2'; -- ERROR
@@ -1292,7 +1330,7 @@ DROP SERVER s9 CASCADE; -- ERROR
ERROR: must be owner of foreign server s9
-- Check visibility of user mapping data
SET ROLE regress_test_role;
-CREATE SERVER s10 FOREIGN DATA WRAPPER foo;
+CREATE SERVER s10 FOREIGN DATA WRAPPER regress_foo;
CREATE USER MAPPING FOR public SERVER s10 OPTIONS (user 'secret');
CREATE USER MAPPING FOR regress_unprivileged_role SERVER s10 OPTIONS (user 'secret');
-- owner of server can see some option fields
@@ -1890,14 +1928,14 @@ DROP TABLE fd_pt1 CASCADE;
NOTICE: drop cascades to foreign table ft2
-- IMPORT FOREIGN SCHEMA
IMPORT FOREIGN SCHEMA s1 FROM SERVER s9 INTO public; -- ERROR
-ERROR: foreign-data wrapper "foo" has no handler
+ERROR: foreign-data wrapper "regress_foo" has no handler
IMPORT FOREIGN SCHEMA s1 LIMIT TO (t1) FROM SERVER s9 INTO public; --ERROR
-ERROR: foreign-data wrapper "foo" has no handler
+ERROR: foreign-data wrapper "regress_foo" has no handler
IMPORT FOREIGN SCHEMA s1 EXCEPT (t1) FROM SERVER s9 INTO public; -- ERROR
-ERROR: foreign-data wrapper "foo" has no handler
+ERROR: foreign-data wrapper "regress_foo" has no handler
IMPORT FOREIGN SCHEMA s1 EXCEPT (t1, t2) FROM SERVER s9 INTO public
OPTIONS (option1 'value1', option2 'value2'); -- ERROR
-ERROR: foreign-data wrapper "foo" has no handler
+ERROR: foreign-data wrapper "regress_foo" has no handler
-- DROP FOREIGN TABLE
DROP FOREIGN TABLE no_table; -- ERROR
ERROR: foreign table "no_table" does not exist
@@ -2155,13 +2193,13 @@ DROP TABLE temp_parted;
DROP SCHEMA foreign_schema CASCADE;
DROP ROLE regress_test_role; -- ERROR
ERROR: role "regress_test_role" cannot be dropped because some objects depend on it
-DETAIL: privileges for foreign-data wrapper foo
+DETAIL: privileges for foreign-data wrapper regress_foo
privileges for server s4
owner of user mapping for regress_test_role on server s6
DROP SERVER t1 CASCADE;
NOTICE: drop cascades to user mapping for public on server t1
DROP USER MAPPING FOR regress_test_role SERVER s6;
-DROP FOREIGN DATA WRAPPER foo CASCADE;
+DROP FOREIGN DATA WRAPPER regress_foo CASCADE;
NOTICE: drop cascades to 5 other objects
DETAIL: drop cascades to server s4
drop cascades to user mapping for regress_foreign_data_user on server s4
@@ -2176,17 +2214,17 @@ DROP ROLE regress_test_indirect;
DROP ROLE regress_test_role;
DROP ROLE regress_unprivileged_role; -- ERROR
ERROR: role "regress_unprivileged_role" cannot be dropped because some objects depend on it
-DETAIL: privileges for foreign-data wrapper postgresql
-REVOKE ALL ON FOREIGN DATA WRAPPER postgresql FROM regress_unprivileged_role;
+DETAIL: privileges for foreign-data wrapper regress_postgresql
+REVOKE ALL ON FOREIGN DATA WRAPPER regress_postgresql FROM regress_unprivileged_role;
DROP ROLE regress_unprivileged_role;
DROP ROLE regress_test_role2;
-DROP FOREIGN DATA WRAPPER postgresql CASCADE;
-DROP FOREIGN DATA WRAPPER dummy CASCADE;
+DROP FOREIGN DATA WRAPPER regress_postgresql CASCADE;
+DROP FOREIGN DATA WRAPPER regress_dummy CASCADE;
NOTICE: drop cascades to server s0
\c
DROP ROLE regress_foreign_data_user;
-- At this point we should have no wrappers, no servers, and no mappings.
-SELECT fdwname, fdwhandler, fdwvalidator, fdwoptions FROM pg_foreign_data_wrapper;
+SELECT fdwname, fdwhandler, fdwvalidator, fdwoptions FROM pg_foreign_data_wrapper WHERE fdwname LIKE 'regress_%';
fdwname | fdwhandler | fdwvalidator | fdwoptions
---------+------------+--------------+------------
(0 rows)
diff --git a/src/test/regress/sql/foreign_data.sql b/src/test/regress/sql/foreign_data.sql
index eefb860adc..b125dff9fb 100644
--- a/src/test/regress/sql/foreign_data.sql
+++ b/src/test/regress/sql/foreign_data.sql
@@ -31,155 +31,187 @@ CREATE ROLE regress_test_role_super SUPERUSER;
CREATE ROLE regress_test_indirect;
CREATE ROLE regress_unprivileged_role;
-CREATE FOREIGN DATA WRAPPER dummy;
-COMMENT ON FOREIGN DATA WRAPPER dummy IS 'useless';
-CREATE FOREIGN DATA WRAPPER postgresql VALIDATOR postgresql_fdw_validator;
+CREATE FOREIGN DATA WRAPPER regress_dummy;
+COMMENT ON FOREIGN DATA WRAPPER regress_dummy IS 'useless';
+CREATE FOREIGN DATA WRAPPER regress_postgresql VALIDATOR postgresql_fdw_validator;
-- At this point we should have 2 built-in wrappers and no servers.
-SELECT fdwname, fdwhandler::regproc, fdwvalidator::regproc, fdwoptions FROM pg_foreign_data_wrapper ORDER BY 1, 2, 3;
+SELECT fdwname, fdwhandler::regproc, fdwvalidator::regproc, fdwoptions FROM pg_foreign_data_wrapper WHERE fdwname LIKE 'regress_%' ORDER BY 1, 2, 3;
SELECT srvname, srvoptions FROM pg_foreign_server;
SELECT * FROM pg_user_mapping;
-- CREATE FOREIGN DATA WRAPPER
-CREATE FOREIGN DATA WRAPPER foo VALIDATOR bar; -- ERROR
-CREATE FOREIGN DATA WRAPPER foo;
-\dew
+CREATE FOREIGN DATA WRAPPER regress_foo VALIDATOR bar; -- ERROR
+CREATE FOREIGN DATA WRAPPER regress_foo;
+\dew regress_*
-CREATE FOREIGN DATA WRAPPER foo; -- duplicate
-DROP FOREIGN DATA WRAPPER foo;
-CREATE FOREIGN DATA WRAPPER foo OPTIONS (testing '1');
-\dew+
+CREATE FOREIGN DATA WRAPPER regress_foo; -- duplicate
+DROP FOREIGN DATA WRAPPER regress_foo;
+CREATE FOREIGN DATA WRAPPER regress_foo OPTIONS (testing '1');
+\dew+ regress_*
-DROP FOREIGN DATA WRAPPER foo;
-CREATE FOREIGN DATA WRAPPER foo OPTIONS (testing '1', testing '2'); -- ERROR
-CREATE FOREIGN DATA WRAPPER foo OPTIONS (testing '1', another '2');
-\dew+
+DROP FOREIGN DATA WRAPPER regress_foo;
+CREATE FOREIGN DATA WRAPPER regress_foo OPTIONS (testing '1', testing '2'); -- ERROR
+CREATE FOREIGN DATA WRAPPER regress_foo OPTIONS (testing '1', another '2');
+\dew+ regress_*
-DROP FOREIGN DATA WRAPPER foo;
+DROP FOREIGN DATA WRAPPER regress_foo;
SET ROLE regress_test_role;
-CREATE FOREIGN DATA WRAPPER foo; -- ERROR
+CREATE FOREIGN DATA WRAPPER regress_foo; -- ERROR
RESET ROLE;
-CREATE FOREIGN DATA WRAPPER foo VALIDATOR postgresql_fdw_validator;
-\dew+
+CREATE FOREIGN DATA WRAPPER regress_foo VALIDATOR postgresql_fdw_validator;
+\dew+ regress_*
-- HANDLER related checks
CREATE FUNCTION invalid_fdw_handler() RETURNS int LANGUAGE SQL AS 'SELECT 1;';
-CREATE FOREIGN DATA WRAPPER test_fdw HANDLER invalid_fdw_handler; -- ERROR
-CREATE FOREIGN DATA WRAPPER test_fdw HANDLER test_fdw_handler HANDLER invalid_fdw_handler; -- ERROR
-CREATE FOREIGN DATA WRAPPER test_fdw HANDLER test_fdw_handler;
-DROP FOREIGN DATA WRAPPER test_fdw;
+CREATE FOREIGN DATA WRAPPER regress_test_fdw HANDLER invalid_fdw_handler; -- ERROR
+CREATE FOREIGN DATA WRAPPER regress_test_fdw HANDLER test_fdw_handler HANDLER invalid_fdw_handler; -- ERROR
+CREATE FOREIGN DATA WRAPPER regress_test_fdw HANDLER test_fdw_handler;
+DROP FOREIGN DATA WRAPPER regress_test_fdw;
-- ALTER FOREIGN DATA WRAPPER
-ALTER FOREIGN DATA WRAPPER foo OPTIONS (nonexistent 'fdw'); -- ERROR
+ALTER FOREIGN DATA WRAPPER regress_foo OPTIONS (nonexistent 'fdw'); -- ERROR
-ALTER FOREIGN DATA WRAPPER foo; -- ERROR
-ALTER FOREIGN DATA WRAPPER foo VALIDATOR bar; -- ERROR
-ALTER FOREIGN DATA WRAPPER foo NO VALIDATOR;
-\dew+
+ALTER FOREIGN DATA WRAPPER regress_foo; -- ERROR
+ALTER FOREIGN DATA WRAPPER regress_foo VALIDATOR bar; -- ERROR
+ALTER FOREIGN DATA WRAPPER regress_foo NO VALIDATOR;
+\dew+ regress_*
-ALTER FOREIGN DATA WRAPPER foo OPTIONS (a '1', b '2');
-ALTER FOREIGN DATA WRAPPER foo OPTIONS (SET c '4'); -- ERROR
-ALTER FOREIGN DATA WRAPPER foo OPTIONS (DROP c); -- ERROR
-ALTER FOREIGN DATA WRAPPER foo OPTIONS (ADD x '1', DROP x);
-\dew+
+ALTER FOREIGN DATA WRAPPER regress_foo OPTIONS (a '1', b '2');
+ALTER FOREIGN DATA WRAPPER regress_foo OPTIONS (SET c '4'); -- ERROR
+ALTER FOREIGN DATA WRAPPER regress_foo OPTIONS (DROP c); -- ERROR
+ALTER FOREIGN DATA WRAPPER regress_foo OPTIONS (ADD x '1', DROP x);
+\dew+ regress_*
-ALTER FOREIGN DATA WRAPPER foo OPTIONS (DROP a, SET b '3', ADD c '4');
-\dew+
+ALTER FOREIGN DATA WRAPPER regress_foo OPTIONS (DROP a, SET b '3', ADD c '4');
+\dew+ regress_*
-ALTER FOREIGN DATA WRAPPER foo OPTIONS (a '2');
-ALTER FOREIGN DATA WRAPPER foo OPTIONS (b '4'); -- ERROR
-\dew+
+ALTER FOREIGN DATA WRAPPER regress_foo OPTIONS (a '2');
+ALTER FOREIGN DATA WRAPPER regress_foo OPTIONS (b '4'); -- ERROR
+\dew+ regress_*
SET ROLE regress_test_role;
-ALTER FOREIGN DATA WRAPPER foo OPTIONS (ADD d '5'); -- ERROR
+ALTER FOREIGN DATA WRAPPER regress_foo OPTIONS (ADD d '5'); -- ERROR
SET ROLE regress_test_role_super;
-ALTER FOREIGN DATA WRAPPER foo OPTIONS (ADD d '5');
-\dew+
+ALTER FOREIGN DATA WRAPPER regress_foo OPTIONS (ADD d '5');
+\dew+ regress_*
-ALTER FOREIGN DATA WRAPPER foo OWNER TO regress_test_role; -- ERROR
-ALTER FOREIGN DATA WRAPPER foo OWNER TO regress_test_role_super;
+ALTER FOREIGN DATA WRAPPER regress_foo OWNER TO regress_test_role; -- ERROR
+ALTER FOREIGN DATA WRAPPER regress_foo OWNER TO regress_test_role_super;
ALTER ROLE regress_test_role_super NOSUPERUSER;
SET ROLE regress_test_role_super;
-ALTER FOREIGN DATA WRAPPER foo OPTIONS (ADD e '6'); -- ERROR
+ALTER FOREIGN DATA WRAPPER regress_foo OPTIONS (ADD e '6'); -- ERROR
RESET ROLE;
-\dew+
+\dew+ regress_*
-ALTER FOREIGN DATA WRAPPER foo RENAME TO foo1;
-\dew+
-ALTER FOREIGN DATA WRAPPER foo1 RENAME TO foo;
+ALTER FOREIGN DATA WRAPPER regress_foo RENAME TO regress_foo1;
+\dew+ regress_*
+ALTER FOREIGN DATA WRAPPER regress_foo1 RENAME TO regress_foo;
-- HANDLER related checks
-ALTER FOREIGN DATA WRAPPER foo HANDLER invalid_fdw_handler; -- ERROR
-ALTER FOREIGN DATA WRAPPER foo HANDLER test_fdw_handler HANDLER anything; -- ERROR
-ALTER FOREIGN DATA WRAPPER foo HANDLER test_fdw_handler;
+ALTER FOREIGN DATA WRAPPER regress_foo HANDLER invalid_fdw_handler; -- ERROR
+ALTER FOREIGN DATA WRAPPER regress_foo HANDLER test_fdw_handler HANDLER anything; -- ERROR
+ALTER FOREIGN DATA WRAPPER regress_foo HANDLER test_fdw_handler;
DROP FUNCTION invalid_fdw_handler();
-- DROP FOREIGN DATA WRAPPER
DROP FOREIGN DATA WRAPPER nonexistent; -- ERROR
DROP FOREIGN DATA WRAPPER IF EXISTS nonexistent;
-\dew+
+\dew+ regress_*
DROP ROLE regress_test_role_super; -- ERROR
SET ROLE regress_test_role_super;
-DROP FOREIGN DATA WRAPPER foo;
+DROP FOREIGN DATA WRAPPER regress_foo;
RESET ROLE;
DROP ROLE regress_test_role_super;
-\dew+
+\dew+ regress_*
-CREATE FOREIGN DATA WRAPPER foo;
-CREATE SERVER s1 FOREIGN DATA WRAPPER foo;
+CREATE FOREIGN DATA WRAPPER regress_foo;
+CREATE SERVER s1 FOREIGN DATA WRAPPER regress_foo;
COMMENT ON SERVER s1 IS 'foreign server';
CREATE USER MAPPING FOR current_user SERVER s1;
CREATE USER MAPPING FOR current_user SERVER s1; -- ERROR
CREATE USER MAPPING IF NOT EXISTS FOR current_user SERVER s1; -- NOTICE
-\dew+
+\dew+ regress_*
\des+
\deu+
-DROP FOREIGN DATA WRAPPER foo; -- ERROR
+DROP FOREIGN DATA WRAPPER regress_foo; -- ERROR
SET ROLE regress_test_role;
-DROP FOREIGN DATA WRAPPER foo CASCADE; -- ERROR
+DROP FOREIGN DATA WRAPPER regress_foo CASCADE; -- ERROR
RESET ROLE;
-DROP FOREIGN DATA WRAPPER foo CASCADE;
-\dew+
+DROP FOREIGN DATA WRAPPER regress_foo CASCADE;
+\dew+ regress_*
\des+
\deu+
-- exercise CREATE SERVER
-CREATE SERVER s1 FOREIGN DATA WRAPPER foo; -- ERROR
-CREATE FOREIGN DATA WRAPPER foo OPTIONS ("test wrapper" 'true');
-CREATE SERVER s1 FOREIGN DATA WRAPPER foo;
-CREATE SERVER s1 FOREIGN DATA WRAPPER foo; -- ERROR
-CREATE SERVER IF NOT EXISTS s1 FOREIGN DATA WRAPPER foo; -- No ERROR, just NOTICE
-CREATE SERVER s2 FOREIGN DATA WRAPPER foo OPTIONS (host 'a', dbname 'b');
-CREATE SERVER s3 TYPE 'oracle' FOREIGN DATA WRAPPER foo;
-CREATE SERVER s4 TYPE 'oracle' FOREIGN DATA WRAPPER foo OPTIONS (host 'a', dbname 'b');
-CREATE SERVER s5 VERSION '15.0' FOREIGN DATA WRAPPER foo;
-CREATE SERVER s6 VERSION '16.0' FOREIGN DATA WRAPPER foo OPTIONS (host 'a', dbname 'b');
-CREATE SERVER s7 TYPE 'oracle' VERSION '17.0' FOREIGN DATA WRAPPER foo OPTIONS (host 'a', dbname 'b');
-CREATE SERVER s8 FOREIGN DATA WRAPPER postgresql OPTIONS (foo '1'); -- ERROR
-CREATE SERVER s8 FOREIGN DATA WRAPPER postgresql OPTIONS (host 'localhost', dbname 's8db');
+CREATE SERVER s1 FOREIGN DATA WRAPPER regress_foo; -- ERROR
+CREATE FOREIGN DATA WRAPPER regress_foo OPTIONS ("test wrapper" 'true');
+CREATE SERVER s1 FOREIGN DATA WRAPPER regress_foo;
+CREATE SERVER s1 FOREIGN DATA WRAPPER regress_foo; -- ERROR
+CREATE SERVER IF NOT EXISTS s1 FOREIGN DATA WRAPPER regress_foo; -- No ERROR, just NOTICE
+CREATE SERVER s2 FOREIGN DATA WRAPPER regress_foo OPTIONS (host 'a', dbname 'b');
+CREATE SERVER s3 TYPE 'oracle' FOREIGN DATA WRAPPER regress_foo;
+CREATE SERVER s4 TYPE 'oracle' FOREIGN DATA WRAPPER regress_foo OPTIONS (host 'a', dbname 'b');
+CREATE SERVER s5 VERSION '15.0' FOREIGN DATA WRAPPER regress_foo;
+CREATE SERVER s6 VERSION '16.0' FOREIGN DATA WRAPPER regress_foo OPTIONS (host 'a', dbname 'b');
+CREATE SERVER s7 TYPE 'oracle' VERSION '17.0' FOREIGN DATA WRAPPER regress_foo OPTIONS (host 'a', dbname 'b');
+CREATE SERVER s8 FOREIGN DATA WRAPPER regress_postgresql OPTIONS (foo '1'); -- ERROR
+CREATE SERVER s8 FOREIGN DATA WRAPPER regress_postgresql OPTIONS (host 'localhost', dbname 's8db');
\des+
SET ROLE regress_test_role;
-CREATE SERVER t1 FOREIGN DATA WRAPPER foo; -- ERROR: no usage on FDW
+CREATE SERVER t1 FOREIGN DATA WRAPPER regress_foo; -- ERROR: no usage on FDW
RESET ROLE;
-GRANT USAGE ON FOREIGN DATA WRAPPER foo TO regress_test_role;
+GRANT USAGE ON FOREIGN DATA WRAPPER regress_foo TO regress_test_role;
SET ROLE regress_test_role;
-CREATE SERVER t1 FOREIGN DATA WRAPPER foo;
+CREATE SERVER t1 FOREIGN DATA WRAPPER regress_foo;
RESET ROLE;
\des+
-REVOKE USAGE ON FOREIGN DATA WRAPPER foo FROM regress_test_role;
-GRANT USAGE ON FOREIGN DATA WRAPPER foo TO regress_test_indirect;
+REVOKE USAGE ON FOREIGN DATA WRAPPER regress_foo FROM regress_test_role;
+GRANT USAGE ON FOREIGN DATA WRAPPER regress_foo TO regress_test_indirect;
SET ROLE regress_test_role;
-CREATE SERVER t2 FOREIGN DATA WRAPPER foo; -- ERROR
+CREATE SERVER t2 FOREIGN DATA WRAPPER regress_foo; -- ERROR
RESET ROLE;
GRANT regress_test_indirect TO regress_test_role;
SET ROLE regress_test_role;
-CREATE SERVER t2 FOREIGN DATA WRAPPER foo;
+CREATE SERVER t2 FOREIGN DATA WRAPPER regress_foo;
\des+
RESET ROLE;
REVOKE regress_test_indirect FROM regress_test_role;
+--
+-- test pg_connection_fdw
+--
+
+\set VERBOSITY terse
+CREATE SERVER connection_server FOREIGN DATA WRAPPER pg_connection_fdw
+ OPTIONS (host 'thehost', client_encoding 'LATIN1'); -- fail
+CREATE SERVER connection_server FOREIGN DATA WRAPPER pg_connection_fdw
+ OPTIONS (host 'thehost', nonsense 'asdf'); -- fail
+CREATE SERVER connection_server FOREIGN DATA WRAPPER pg_connection_fdw
+ OPTIONS (host 'thehost', password 'secret'); -- fail
+\set VERBOSITY default
+
+CREATE SERVER connection_server FOREIGN DATA WRAPPER pg_connection_fdw
+ OPTIONS (host 'thehost', port '5432');
+
+CREATE USER MAPPING FOR regress_test_role SERVER connection_server
+ OPTIONS (user 'role', password 'secret', host 'otherhost'); -- fail
+
+CREATE USER MAPPING FOR regress_test_role SERVER connection_server
+ OPTIONS (user 'role', password 'secret');
+CREATE USER MAPPING FOR PUBLIC SERVER connection_server
+ OPTIONS (user 'publicuser', password $pwd$'\"$# secret'$pwd$);
+
+SELECT pg_conninfo_from_server('connection_server', 'regress_test_role', false);
+
+SELECT pg_conninfo_from_server('connection_server', 'regress_test_role2', false);
+
+DROP USER MAPPING FOR regress_test_role SERVER connection_server;
+DROP USER MAPPING FOR PUBLIC SERVER connection_server;
+DROP SERVER connection_server;
+
-- ALTER SERVER
ALTER SERVER s0; -- ERROR
ALTER SERVER s0 OPTIONS (a '1'); -- ERROR
@@ -208,7 +240,7 @@ GRANT regress_test_indirect TO regress_test_role;
SET ROLE regress_test_role;
ALTER SERVER s1 OWNER TO regress_test_indirect;
RESET ROLE;
-GRANT USAGE ON FOREIGN DATA WRAPPER foo TO regress_test_indirect;
+GRANT USAGE ON FOREIGN DATA WRAPPER regress_foo TO regress_test_indirect;
SET ROLE regress_test_role;
ALTER SERVER s1 OWNER TO regress_test_indirect;
RESET ROLE;
@@ -293,7 +325,7 @@ DROP SERVER s7;
-- CREATE FOREIGN TABLE
CREATE SCHEMA foreign_schema;
-CREATE SERVER s0 FOREIGN DATA WRAPPER dummy;
+CREATE SERVER s0 FOREIGN DATA WRAPPER regress_dummy;
CREATE FOREIGN TABLE ft1 (); -- ERROR
CREATE FOREIGN TABLE ft1 () SERVER no_server; -- ERROR
CREATE FOREIGN TABLE ft1 (
@@ -453,20 +485,20 @@ ALTER FOREIGN TABLE IF EXISTS doesnt_exist_ft1 RENAME TO foreign_table_1;
-- Information schema
-SELECT * FROM information_schema.foreign_data_wrappers ORDER BY 1, 2;
+SELECT * FROM information_schema.foreign_data_wrappers WHERE authorization_identifier = 'regress_foreign_data_user' ORDER BY 1, 2;
SELECT * FROM information_schema.foreign_data_wrapper_options ORDER BY 1, 2, 3;
SELECT * FROM information_schema.foreign_servers ORDER BY 1, 2;
SELECT * FROM information_schema.foreign_server_options ORDER BY 1, 2, 3;
SELECT * FROM information_schema.user_mappings ORDER BY lower(authorization_identifier), 2, 3;
SELECT * FROM information_schema.user_mapping_options ORDER BY lower(authorization_identifier), 2, 3, 4;
-SELECT * FROM information_schema.usage_privileges WHERE object_type LIKE 'FOREIGN%' AND object_name IN ('s6', 'foo') ORDER BY 1, 2, 3, 4, 5;
-SELECT * FROM information_schema.role_usage_grants WHERE object_type LIKE 'FOREIGN%' AND object_name IN ('s6', 'foo') ORDER BY 1, 2, 3, 4, 5;
+SELECT * FROM information_schema.usage_privileges WHERE object_type LIKE 'FOREIGN%' AND object_name IN ('s6', 'regress_foo') ORDER BY 1, 2, 3, 4, 5;
+SELECT * FROM information_schema.role_usage_grants WHERE object_type LIKE 'FOREIGN%' AND object_name IN ('s6', 'regress_foo') ORDER BY 1, 2, 3, 4, 5;
SELECT * FROM information_schema.foreign_tables ORDER BY 1, 2, 3;
SELECT * FROM information_schema.foreign_table_options ORDER BY 1, 2, 3, 4;
SET ROLE regress_test_role;
SELECT * FROM information_schema.user_mapping_options ORDER BY 1, 2, 3, 4;
-SELECT * FROM information_schema.usage_privileges WHERE object_type LIKE 'FOREIGN%' AND object_name IN ('s6', 'foo') ORDER BY 1, 2, 3, 4, 5;
-SELECT * FROM information_schema.role_usage_grants WHERE object_type LIKE 'FOREIGN%' AND object_name IN ('s6', 'foo') ORDER BY 1, 2, 3, 4, 5;
+SELECT * FROM information_schema.usage_privileges WHERE object_type LIKE 'FOREIGN%' AND object_name IN ('s6', 'regress_foo') ORDER BY 1, 2, 3, 4, 5;
+SELECT * FROM information_schema.role_usage_grants WHERE object_type LIKE 'FOREIGN%' AND object_name IN ('s6', 'regress_foo') ORDER BY 1, 2, 3, 4, 5;
DROP USER MAPPING FOR current_user SERVER t1;
SET ROLE regress_test_role2;
SELECT * FROM information_schema.user_mapping_options ORDER BY 1, 2, 3, 4;
@@ -475,18 +507,18 @@ RESET ROLE;
-- has_foreign_data_wrapper_privilege
SELECT has_foreign_data_wrapper_privilege('regress_test_role',
- (SELECT oid FROM pg_foreign_data_wrapper WHERE fdwname='foo'), 'USAGE');
-SELECT has_foreign_data_wrapper_privilege('regress_test_role', 'foo', 'USAGE');
+ (SELECT oid FROM pg_foreign_data_wrapper WHERE fdwname='regress_foo'), 'USAGE');
+SELECT has_foreign_data_wrapper_privilege('regress_test_role', 'regress_foo', 'USAGE');
SELECT has_foreign_data_wrapper_privilege(
(SELECT oid FROM pg_roles WHERE rolname='regress_test_role'),
- (SELECT oid FROM pg_foreign_data_wrapper WHERE fdwname='foo'), 'USAGE');
+ (SELECT oid FROM pg_foreign_data_wrapper WHERE fdwname='regress_foo'), 'USAGE');
SELECT has_foreign_data_wrapper_privilege(
- (SELECT oid FROM pg_foreign_data_wrapper WHERE fdwname='foo'), 'USAGE');
+ (SELECT oid FROM pg_foreign_data_wrapper WHERE fdwname='regress_foo'), 'USAGE');
SELECT has_foreign_data_wrapper_privilege(
- (SELECT oid FROM pg_roles WHERE rolname='regress_test_role'), 'foo', 'USAGE');
-SELECT has_foreign_data_wrapper_privilege('foo', 'USAGE');
-GRANT USAGE ON FOREIGN DATA WRAPPER foo TO regress_test_role;
-SELECT has_foreign_data_wrapper_privilege('regress_test_role', 'foo', 'USAGE');
+ (SELECT oid FROM pg_roles WHERE rolname='regress_test_role'), 'regress_foo', 'USAGE');
+SELECT has_foreign_data_wrapper_privilege('regress_foo', 'USAGE');
+GRANT USAGE ON FOREIGN DATA WRAPPER regress_foo TO regress_test_role;
+SELECT has_foreign_data_wrapper_privilege('regress_test_role', 'regress_foo', 'USAGE');
-- has_server_privilege
SELECT has_server_privilege('regress_test_role',
@@ -508,16 +540,16 @@ GRANT USAGE ON FOREIGN SERVER s4 TO regress_test_role;
DROP USER MAPPING FOR public SERVER s4;
ALTER SERVER s6 OPTIONS (DROP host, DROP dbname);
ALTER USER MAPPING FOR regress_test_role SERVER s6 OPTIONS (DROP username);
-ALTER FOREIGN DATA WRAPPER foo VALIDATOR postgresql_fdw_validator;
+ALTER FOREIGN DATA WRAPPER regress_foo VALIDATOR postgresql_fdw_validator;
-- Privileges
SET ROLE regress_unprivileged_role;
-CREATE FOREIGN DATA WRAPPER foobar; -- ERROR
-ALTER FOREIGN DATA WRAPPER foo OPTIONS (gotcha 'true'); -- ERROR
-ALTER FOREIGN DATA WRAPPER foo OWNER TO regress_unprivileged_role; -- ERROR
-DROP FOREIGN DATA WRAPPER foo; -- ERROR
-GRANT USAGE ON FOREIGN DATA WRAPPER foo TO regress_test_role; -- ERROR
-CREATE SERVER s9 FOREIGN DATA WRAPPER foo; -- ERROR
+CREATE FOREIGN DATA WRAPPER regress_foobar; -- ERROR
+ALTER FOREIGN DATA WRAPPER regress_foo OPTIONS (gotcha 'true'); -- ERROR
+ALTER FOREIGN DATA WRAPPER regress_foo OWNER TO regress_unprivileged_role; -- ERROR
+DROP FOREIGN DATA WRAPPER regress_foo; -- ERROR
+GRANT USAGE ON FOREIGN DATA WRAPPER regress_foo TO regress_test_role; -- ERROR
+CREATE SERVER s9 FOREIGN DATA WRAPPER regress_foo; -- ERROR
ALTER SERVER s4 VERSION '0.5'; -- ERROR
ALTER SERVER s4 OWNER TO regress_unprivileged_role; -- ERROR
DROP SERVER s4; -- ERROR
@@ -527,15 +559,15 @@ ALTER USER MAPPING FOR regress_test_role SERVER s6 OPTIONS (gotcha 'true'); -- E
DROP USER MAPPING FOR regress_test_role SERVER s6; -- ERROR
RESET ROLE;
-GRANT USAGE ON FOREIGN DATA WRAPPER postgresql TO regress_unprivileged_role;
-GRANT USAGE ON FOREIGN DATA WRAPPER foo TO regress_unprivileged_role WITH GRANT OPTION;
+GRANT USAGE ON FOREIGN DATA WRAPPER regress_postgresql TO regress_unprivileged_role;
+GRANT USAGE ON FOREIGN DATA WRAPPER regress_foo TO regress_unprivileged_role WITH GRANT OPTION;
SET ROLE regress_unprivileged_role;
-CREATE FOREIGN DATA WRAPPER foobar; -- ERROR
-ALTER FOREIGN DATA WRAPPER foo OPTIONS (gotcha 'true'); -- ERROR
-DROP FOREIGN DATA WRAPPER foo; -- ERROR
-GRANT USAGE ON FOREIGN DATA WRAPPER postgresql TO regress_test_role; -- WARNING
-GRANT USAGE ON FOREIGN DATA WRAPPER foo TO regress_test_role;
-CREATE SERVER s9 FOREIGN DATA WRAPPER postgresql;
+CREATE FOREIGN DATA WRAPPER regress_foobar; -- ERROR
+ALTER FOREIGN DATA WRAPPER regress_foo OPTIONS (gotcha 'true'); -- ERROR
+DROP FOREIGN DATA WRAPPER regress_foo; -- ERROR
+GRANT USAGE ON FOREIGN DATA WRAPPER regress_postgresql TO regress_test_role; -- WARNING
+GRANT USAGE ON FOREIGN DATA WRAPPER regress_foo TO regress_test_role;
+CREATE SERVER s9 FOREIGN DATA WRAPPER regress_postgresql;
ALTER SERVER s6 VERSION '0.5'; -- ERROR
DROP SERVER s6; -- ERROR
GRANT USAGE ON FOREIGN SERVER s6 TO regress_test_role; -- ERROR
@@ -546,17 +578,17 @@ ALTER USER MAPPING FOR regress_test_role SERVER s6 OPTIONS (gotcha 'true'); -- E
DROP USER MAPPING FOR regress_test_role SERVER s6; -- ERROR
RESET ROLE;
-REVOKE USAGE ON FOREIGN DATA WRAPPER foo FROM regress_unprivileged_role; -- ERROR
-REVOKE USAGE ON FOREIGN DATA WRAPPER foo FROM regress_unprivileged_role CASCADE;
+REVOKE USAGE ON FOREIGN DATA WRAPPER regress_foo FROM regress_unprivileged_role; -- ERROR
+REVOKE USAGE ON FOREIGN DATA WRAPPER regress_foo FROM regress_unprivileged_role CASCADE;
SET ROLE regress_unprivileged_role;
-GRANT USAGE ON FOREIGN DATA WRAPPER foo TO regress_test_role; -- ERROR
-CREATE SERVER s10 FOREIGN DATA WRAPPER foo; -- ERROR
+GRANT USAGE ON FOREIGN DATA WRAPPER regress_foo TO regress_test_role; -- ERROR
+CREATE SERVER s10 FOREIGN DATA WRAPPER regress_foo; -- ERROR
ALTER SERVER s9 VERSION '1.1';
GRANT USAGE ON FOREIGN SERVER s9 TO regress_test_role;
CREATE USER MAPPING FOR current_user SERVER s9;
DROP SERVER s9 CASCADE;
RESET ROLE;
-CREATE SERVER s9 FOREIGN DATA WRAPPER foo;
+CREATE SERVER s9 FOREIGN DATA WRAPPER regress_foo;
GRANT USAGE ON FOREIGN SERVER s9 TO regress_unprivileged_role;
SET ROLE regress_unprivileged_role;
ALTER SERVER s9 VERSION '1.2'; -- ERROR
@@ -566,7 +598,7 @@ DROP SERVER s9 CASCADE; -- ERROR
-- Check visibility of user mapping data
SET ROLE regress_test_role;
-CREATE SERVER s10 FOREIGN DATA WRAPPER foo;
+CREATE SERVER s10 FOREIGN DATA WRAPPER regress_foo;
CREATE USER MAPPING FOR public SERVER s10 OPTIONS (user 'secret');
CREATE USER MAPPING FOR regress_unprivileged_role SERVER s10 OPTIONS (user 'secret');
-- owner of server can see some option fields
@@ -847,20 +879,20 @@ DROP SCHEMA foreign_schema CASCADE;
DROP ROLE regress_test_role; -- ERROR
DROP SERVER t1 CASCADE;
DROP USER MAPPING FOR regress_test_role SERVER s6;
-DROP FOREIGN DATA WRAPPER foo CASCADE;
+DROP FOREIGN DATA WRAPPER regress_foo CASCADE;
DROP SERVER s8 CASCADE;
DROP ROLE regress_test_indirect;
DROP ROLE regress_test_role;
DROP ROLE regress_unprivileged_role; -- ERROR
-REVOKE ALL ON FOREIGN DATA WRAPPER postgresql FROM regress_unprivileged_role;
+REVOKE ALL ON FOREIGN DATA WRAPPER regress_postgresql FROM regress_unprivileged_role;
DROP ROLE regress_unprivileged_role;
DROP ROLE regress_test_role2;
-DROP FOREIGN DATA WRAPPER postgresql CASCADE;
-DROP FOREIGN DATA WRAPPER dummy CASCADE;
+DROP FOREIGN DATA WRAPPER regress_postgresql CASCADE;
+DROP FOREIGN DATA WRAPPER regress_dummy CASCADE;
\c
DROP ROLE regress_foreign_data_user;
-- At this point we should have no wrappers, no servers, and no mappings.
-SELECT fdwname, fdwhandler, fdwvalidator, fdwoptions FROM pg_foreign_data_wrapper;
+SELECT fdwname, fdwhandler, fdwvalidator, fdwoptions FROM pg_foreign_data_wrapper WHERE fdwname LIKE 'regress_%';
SELECT srvname, srvoptions FROM pg_foreign_server;
SELECT * FROM pg_user_mapping;
--
2.34.1
[text/x-patch] v7-0003-CREATE-SUSBCRIPTION-.-SERVER.patch (51.2K, 4-v7-0003-CREATE-SUSBCRIPTION-.-SERVER.patch)
download | inline diff:
From 1fc2121b5ae96152df23a32fa9612f3a45851890 Mon Sep 17 00:00:00 2001
From: Jeff Davis <[email protected]>
Date: Tue, 2 Jan 2024 13:42:48 -0800
Subject: [PATCH v7 3/4] CREATE SUSBCRIPTION ... SERVER.
---
contrib/postgres_fdw/Makefile | 2 +
.../postgres_fdw/expected/postgres_fdw.out | 14 ++
contrib/postgres_fdw/meson.build | 5 +
contrib/postgres_fdw/sql/postgres_fdw.sql | 8 +
contrib/postgres_fdw/t/010_subscription.pl | 68 ++++++
doc/src/sgml/ref/alter_subscription.sgml | 18 +-
doc/src/sgml/ref/create_subscription.sgml | 11 +-
src/backend/catalog/pg_subscription.c | 39 +++-
src/backend/commands/subscriptioncmds.c | 197 ++++++++++++++++--
src/backend/foreign/foreign.c | 25 +++
src/backend/parser/gram.y | 20 ++
src/backend/replication/logical/worker.c | 16 +-
src/bin/pg_dump/pg_dump.c | 27 ++-
src/bin/pg_dump/pg_dump.h | 1 +
src/bin/psql/tab-complete.c | 2 +-
src/include/catalog/pg_subscription.h | 7 +-
src/include/foreign/foreign.h | 1 +
src/include/nodes/parsenodes.h | 3 +
src/test/regress/expected/foreign_data.out | 14 ++
src/test/regress/expected/subscription.out | 52 +++++
src/test/regress/sql/foreign_data.sql | 17 ++
src/test/regress/sql/subscription.sql | 56 +++++
src/test/subscription/t/001_rep_changes.pl | 57 +++++
23 files changed, 627 insertions(+), 33 deletions(-)
create mode 100644 contrib/postgres_fdw/t/010_subscription.pl
diff --git a/contrib/postgres_fdw/Makefile b/contrib/postgres_fdw/Makefile
index c1b0cad453..c3498ea6b4 100644
--- a/contrib/postgres_fdw/Makefile
+++ b/contrib/postgres_fdw/Makefile
@@ -18,6 +18,8 @@ DATA = postgres_fdw--1.0.sql postgres_fdw--1.0--1.1.sql
REGRESS = postgres_fdw
+TAP_TESTS = 1
+
ifdef USE_PGXS
PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index d83f6ae8cb..0aa751e099 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -256,6 +256,20 @@ SELECT c3, c4 FROM ft1 ORDER BY c3, c1 LIMIT 1; -- should work again
ANALYZE ft1;
ALTER FOREIGN TABLE ft2 OPTIONS (use_remote_estimate 'true');
-- ===================================================================
+-- test subscription
+-- ===================================================================
+CREATE SUBSCRIPTION regress_pgfdw_subscription SERVER testserver1
+ PUBLICATION pub1 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 refresh the subscription.
+SELECT pg_conninfo_from_server('testserver1', CURRENT_USER, false);
+ pg_conninfo_from_server
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ service = 'value' connect_timeout = 'value' dbname = 'value' host = 'value' hostaddr = 'value' port = 'value' application_name = 'value' keepalives = 'value' keepalives_idle = 'value' keepalives_interval = 'value' tcp_user_timeout = 'value' sslcompression = 'value' sslmode = 'value' sslcert = 'value' sslkey = 'value' sslrootcert = 'value' sslcrl = 'value' krbsrvname = 'value' gsslib = 'value' gssdelegation = 'value' sslpassword = 'dummy' sslkey = 'value' sslcert = 'value'
+(1 row)
+
+DROP SUBSCRIPTION regress_pgfdw_subscription;
+-- ===================================================================
-- test error case for create publication on foreign table
-- ===================================================================
CREATE PUBLICATION testpub_ftbl FOR TABLE ft1; -- should fail
diff --git a/contrib/postgres_fdw/meson.build b/contrib/postgres_fdw/meson.build
index 2b86d8a6ee..cf7071dbf8 100644
--- a/contrib/postgres_fdw/meson.build
+++ b/contrib/postgres_fdw/meson.build
@@ -39,4 +39,9 @@ tests += {
],
'regress_args': ['--dlpath', meson.build_root() / 'src/test/regress'],
},
+ 'tap': {
+ 'tests': [
+ 't/010_subscription.pl',
+ ],
+ },
}
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index 90c8fa4b70..3b2716b82e 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -248,6 +248,14 @@ SELECT c3, c4 FROM ft1 ORDER BY c3, c1 LIMIT 1; -- should work again
ANALYZE ft1;
ALTER FOREIGN TABLE ft2 OPTIONS (use_remote_estimate 'true');
+-- ===================================================================
+-- test subscription
+-- ===================================================================
+CREATE SUBSCRIPTION regress_pgfdw_subscription SERVER testserver1
+ PUBLICATION pub1 WITH (slot_name = NONE, connect = false);
+SELECT pg_conninfo_from_server('testserver1', CURRENT_USER, false);
+DROP SUBSCRIPTION regress_pgfdw_subscription;
+
-- ===================================================================
-- test error case for create publication on foreign table
-- ===================================================================
diff --git a/contrib/postgres_fdw/t/010_subscription.pl b/contrib/postgres_fdw/t/010_subscription.pl
new file mode 100644
index 0000000000..daa0b9edd2
--- /dev/null
+++ b/contrib/postgres_fdw/t/010_subscription.pl
@@ -0,0 +1,68 @@
+
+# Copyright (c) 2021-2024, PostgreSQL Global Development Group
+
+# Basic logical replication test
+use strict;
+use warnings FATAL => 'all';
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+use Test::More;
+
+# Initialize publisher node
+my $node_publisher = PostgreSQL::Test::Cluster->new('publisher');
+$node_publisher->init(allows_streaming => 'logical');
+$node_publisher->start;
+
+# Create subscriber node
+my $node_subscriber = PostgreSQL::Test::Cluster->new('subscriber');
+$node_subscriber->init;
+$node_subscriber->start;
+
+# Create some preexisting content on publisher
+$node_publisher->safe_psql('postgres',
+ "CREATE TABLE tab_ins AS SELECT generate_series(1,1002) AS a");
+
+# Replicate the changes without columns
+$node_publisher->safe_psql('postgres', "CREATE TABLE tab_no_col()");
+$node_publisher->safe_psql('postgres',
+ "INSERT INTO tab_no_col default VALUES");
+
+# Setup structure on subscriber
+$node_subscriber->safe_psql('postgres', "CREATE EXTENSION postgres_fdw");
+$node_subscriber->safe_psql('postgres', "CREATE TABLE tab_ins (a int)");
+
+# Setup logical replication
+my $publisher_connstr = $node_publisher->connstr . ' dbname=postgres';
+$node_publisher->safe_psql('postgres', "CREATE PUBLICATION tap_pub FOR TABLE tab_ins");
+
+my $publisher_host = $node_publisher->host;
+my $publisher_port = $node_publisher->port;
+$node_subscriber->safe_psql('postgres',
+ "CREATE SERVER tap_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '$publisher_host', port '$publisher_port', dbname 'postgres')"
+);
+
+$node_subscriber->safe_psql('postgres',
+ "CREATE USER MAPPING FOR PUBLIC SERVER tap_server"
+);
+
+$node_subscriber->safe_psql('postgres',
+ "CREATE SUBSCRIPTION tap_sub SERVER tap_server PUBLICATION tap_pub WITH (password_required=false)"
+);
+
+# Wait for initial table sync to finish
+$node_subscriber->wait_for_subscription_sync($node_publisher, 'tap_sub');
+
+my $result =
+ $node_subscriber->safe_psql('postgres', "SELECT count(*) FROM tab_ins");
+is($result, qq(1002), 'check initial data was copied to subscriber');
+
+$node_publisher->safe_psql('postgres',
+ "INSERT INTO tab_ins SELECT generate_series(1,50)");
+
+$node_publisher->wait_for_catchup('tap_sub');
+
+$result =
+ $node_subscriber->safe_psql('postgres', "SELECT count(*) FROM tab_ins");
+is($result, qq(1052), 'check initial data was copied to subscriber');
+
+done_testing();
diff --git a/doc/src/sgml/ref/alter_subscription.sgml b/doc/src/sgml/ref/alter_subscription.sgml
index 6d36ff0dc9..6d219145a9 100644
--- a/doc/src/sgml/ref/alter_subscription.sgml
+++ b/doc/src/sgml/ref/alter_subscription.sgml
@@ -21,6 +21,7 @@ PostgreSQL documentation
<refsynopsisdiv>
<synopsis>
+ALTER SUBSCRIPTION <replaceable class="parameter">name</replaceable> SERVER <replaceable>servername</replaceable>
ALTER SUBSCRIPTION <replaceable class="parameter">name</replaceable> CONNECTION '<replaceable>conninfo</replaceable>'
ALTER SUBSCRIPTION <replaceable class="parameter">name</replaceable> SET PUBLICATION <replaceable class="parameter">publication_name</replaceable> [, ...] [ WITH ( <replaceable class="parameter">publication_option</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] ) ]
ALTER SUBSCRIPTION <replaceable class="parameter">name</replaceable> ADD PUBLICATION <replaceable class="parameter">publication_name</replaceable> [, ...] [ WITH ( <replaceable class="parameter">publication_option</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] ) ]
@@ -94,13 +95,24 @@ ALTER SUBSCRIPTION <replaceable class="parameter">name</replaceable> RENAME TO <
</listitem>
</varlistentry>
+ <varlistentry id="sql-altersubscription-params-server">
+ <term><literal>SERVER <replaceable class="parameter">servername</replaceable></literal></term>
+ <listitem>
+ <para>
+ This clause replaces the foreign server or connection string originally
+ set by <xref linkend="sql-createsubscription"/> with the foreign server
+ <replaceable>servername</replaceable>.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry id="sql-altersubscription-params-connection">
<term><literal>CONNECTION '<replaceable class="parameter">conninfo</replaceable>'</literal></term>
<listitem>
<para>
- This clause replaces the connection string originally set by
- <xref linkend="sql-createsubscription"/>. See there for more
- information.
+ This clause replaces the foreign server or connection string originally
+ set by <xref linkend="sql-createsubscription"/> with the connection
+ string <replaceable>conninfo</replaceable>.
</para>
</listitem>
</varlistentry>
diff --git a/doc/src/sgml/ref/create_subscription.sgml b/doc/src/sgml/ref/create_subscription.sgml
index c7ace922f9..24538baf98 100644
--- a/doc/src/sgml/ref/create_subscription.sgml
+++ b/doc/src/sgml/ref/create_subscription.sgml
@@ -22,7 +22,7 @@ PostgreSQL documentation
<refsynopsisdiv>
<synopsis>
CREATE SUBSCRIPTION <replaceable class="parameter">subscription_name</replaceable>
- CONNECTION '<replaceable class="parameter">conninfo</replaceable>'
+ { SERVER <replaceable class="parameter">servername</replaceable> | CONNECTION '<replaceable class="parameter">conninfo</replaceable>' }
PUBLICATION <replaceable class="parameter">publication_name</replaceable> [, ...]
[ WITH ( <replaceable class="parameter">subscription_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] ) ]
</synopsis>
@@ -77,6 +77,15 @@ CREATE SUBSCRIPTION <replaceable class="parameter">subscription_name</replaceabl
</listitem>
</varlistentry>
+ <varlistentry id="sql-createsubscription-params-server">
+ <term><literal>SERVER <replaceable class="parameter">servername</replaceable></literal></term>
+ <listitem>
+ <para>
+ A foreign server to use for the connection.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry id="sql-createsubscription-params-connection">
<term><literal>CONNECTION '<replaceable class="parameter">conninfo</replaceable>'</literal></term>
<listitem>
diff --git a/src/backend/catalog/pg_subscription.c b/src/backend/catalog/pg_subscription.c
index c516c25ac7..5a2eaa803d 100644
--- a/src/backend/catalog/pg_subscription.c
+++ b/src/backend/catalog/pg_subscription.c
@@ -20,12 +20,15 @@
#include "access/tableam.h"
#include "access/xact.h"
#include "catalog/indexing.h"
+#include "catalog/pg_foreign_server.h"
#include "catalog/pg_subscription.h"
#include "catalog/pg_subscription_rel.h"
#include "catalog/pg_type.h"
+#include "foreign/foreign.h"
#include "miscadmin.h"
#include "nodes/makefuncs.h"
#include "storage/lmgr.h"
+#include "utils/acl.h"
#include "utils/array.h"
#include "utils/builtins.h"
#include "utils/fmgroids.h"
@@ -40,7 +43,7 @@ static List *textarray_to_stringlist(ArrayType *textarray);
* Fetch the subscription from the syscache.
*/
Subscription *
-GetSubscription(Oid subid, bool missing_ok)
+GetSubscription(Oid subid, bool missing_ok, bool aclcheck)
{
HeapTuple tup;
Subscription *sub;
@@ -75,10 +78,36 @@ GetSubscription(Oid subid, bool missing_ok)
sub->runasowner = subform->subrunasowner;
/* Get conninfo */
- datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID,
- tup,
- Anum_pg_subscription_subconninfo);
- sub->conninfo = TextDatumGetCString(datum);
+ if (OidIsValid(subform->subserver))
+ {
+ AclResult aclresult;
+
+ /* recheck ACL if requested */
+ if (aclcheck)
+ {
+ aclresult = object_aclcheck(ForeignServerRelationId,
+ subform->subserver,
+ subform->subowner, ACL_USAGE);
+
+ if (aclresult != ACLCHECK_OK)
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("subscription owner \"%s\" does not have permission on foreign server \"%s\"",
+ GetUserNameFromId(subform->subowner, false),
+ ForeignServerName(subform->subserver))));
+ }
+
+ sub->conninfo = ForeignServerConnectionString(subform->subowner,
+ subform->subserver,
+ true);
+ }
+ else
+ {
+ datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID,
+ tup,
+ Anum_pg_subscription_subconninfo);
+ sub->conninfo = TextDatumGetCString(datum);
+ }
/* Get slotname */
datum = SysCacheGetAttr(SUBSCRIPTIONOID,
diff --git a/src/backend/commands/subscriptioncmds.c b/src/backend/commands/subscriptioncmds.c
index 75e6cd8ae3..bec665fd29 100644
--- a/src/backend/commands/subscriptioncmds.c
+++ b/src/backend/commands/subscriptioncmds.c
@@ -25,14 +25,17 @@
#include "catalog/objectaddress.h"
#include "catalog/pg_authid_d.h"
#include "catalog/pg_database_d.h"
+#include "catalog/pg_foreign_server.h"
#include "catalog/pg_subscription.h"
#include "catalog/pg_subscription_rel.h"
#include "catalog/pg_type.h"
+#include "catalog/pg_user_mapping.h"
#include "commands/dbcommands.h"
#include "commands/defrem.h"
#include "commands/event_trigger.h"
#include "commands/subscriptioncmds.h"
#include "executor/executor.h"
+#include "foreign/foreign.h"
#include "miscadmin.h"
#include "nodes/makefuncs.h"
#include "pgstat.h"
@@ -574,6 +577,8 @@ CreateSubscription(ParseState *pstate, CreateSubscriptionStmt *stmt,
Datum values[Natts_pg_subscription];
Oid owner = GetUserId();
HeapTuple tup;
+ Oid serverid;
+ Oid umid;
char *conninfo;
char originname[NAMEDATALEN];
List *publications;
@@ -666,15 +671,42 @@ CreateSubscription(ParseState *pstate, CreateSubscriptionStmt *stmt,
if (opts.synchronous_commit == NULL)
opts.synchronous_commit = "off";
- conninfo = stmt->conninfo;
- publications = stmt->publication;
-
/* Load the library providing us libpq calls. */
load_file("libpqwalreceiver", false);
+ if (stmt->servername)
+ {
+ ForeignServer *server;
+ UserMapping *um;
+
+ Assert(!stmt->conninfo);
+ conninfo = NULL;
+
+ server = GetForeignServerByName(stmt->servername, false);
+ aclresult = object_aclcheck(ForeignServerRelationId, server->serverid, owner, ACL_USAGE);
+ if (aclresult != ACLCHECK_OK)
+ aclcheck_error(aclresult, OBJECT_FOREIGN_SERVER, server->servername);
+
+ um = GetUserMapping(owner, server->serverid);
+
+ serverid = server->serverid;
+ umid = um->umid;
+ conninfo = ForeignServerConnectionString(owner, serverid, true);
+ }
+ else
+ {
+ Assert(stmt->conninfo);
+
+ serverid = InvalidOid;
+ umid = InvalidOid;
+ conninfo = stmt->conninfo;
+ }
+
/* Check the connection info string. */
walrcv_check_conninfo(conninfo, opts.passwordrequired && !superuser());
+ publications = stmt->publication;
+
/* Everything ok, form a new tuple. */
memset(values, 0, sizeof(values));
memset(nulls, false, sizeof(nulls));
@@ -697,8 +729,12 @@ CreateSubscription(ParseState *pstate, CreateSubscriptionStmt *stmt,
values[Anum_pg_subscription_subdisableonerr - 1] = BoolGetDatum(opts.disableonerr);
values[Anum_pg_subscription_subpasswordrequired - 1] = BoolGetDatum(opts.passwordrequired);
values[Anum_pg_subscription_subrunasowner - 1] = BoolGetDatum(opts.runasowner);
- values[Anum_pg_subscription_subconninfo - 1] =
- CStringGetTextDatum(conninfo);
+ values[Anum_pg_subscription_subserver - 1] = serverid;
+ if (!OidIsValid(serverid))
+ values[Anum_pg_subscription_subconninfo - 1] =
+ CStringGetTextDatum(conninfo);
+ else
+ nulls[Anum_pg_subscription_subconninfo - 1] = true;
if (opts.slot_name)
values[Anum_pg_subscription_subslotname - 1] =
DirectFunctionCall1(namein, CStringGetDatum(opts.slot_name));
@@ -719,6 +755,20 @@ CreateSubscription(ParseState *pstate, CreateSubscriptionStmt *stmt,
recordDependencyOnOwner(SubscriptionRelationId, subid, owner);
+ ObjectAddressSet(myself, SubscriptionRelationId, subid);
+
+ if (stmt->servername)
+ {
+ ObjectAddress referenced;
+ Assert(OidIsValid(serverid) && OidIsValid(umid));
+
+ ObjectAddressSet(referenced, ForeignServerRelationId, serverid);
+ recordDependencyOn(&myself, &referenced, DEPENDENCY_NORMAL);
+
+ ObjectAddressSet(referenced, UserMappingRelationId, umid);
+ recordDependencyOn(&myself, &referenced, DEPENDENCY_NORMAL);
+ }
+
ReplicationOriginNameForLogicalRep(subid, InvalidOid, originname, sizeof(originname));
replorigin_create(originname);
@@ -835,8 +885,6 @@ CreateSubscription(ParseState *pstate, CreateSubscriptionStmt *stmt,
if (opts.enabled)
ApplyLauncherWakeupAtCommit();
- ObjectAddressSet(myself, SubscriptionRelationId, subid);
-
InvokeObjectPostCreateHook(SubscriptionRelationId, subid, 0);
return myself;
@@ -1104,7 +1152,14 @@ AlterSubscription(ParseState *pstate, AlterSubscriptionStmt *stmt,
aclcheck_error(ACLCHECK_NOT_OWNER, OBJECT_SUBSCRIPTION,
stmt->subname);
- sub = GetSubscription(subid, false);
+ /*
+ * Skip ACL checks on the subscription's foreign server, if any. If
+ * changing the server (or replacing it with a raw connection), then the
+ * old one will be removed anyway. If changing something unrelated,
+ * there's no need to do an additional ACL check here; that will be done
+ * by the subscription worker anyway.
+ */
+ sub = GetSubscription(subid, false, false);
/*
* Don't allow non-superuser modification of a subscription with
@@ -1124,6 +1179,8 @@ AlterSubscription(ParseState *pstate, AlterSubscriptionStmt *stmt,
memset(nulls, false, sizeof(nulls));
memset(replaces, false, sizeof(replaces));
+ ObjectAddressSet(myself, SubscriptionRelationId, subid);
+
switch (stmt->kind)
{
case ALTER_SUBSCRIPTION_OPTIONS:
@@ -1244,7 +1301,92 @@ AlterSubscription(ParseState *pstate, AlterSubscriptionStmt *stmt,
break;
}
+ case ALTER_SUBSCRIPTION_SERVER:
+ {
+ ForeignServer *new_server;
+ UserMapping *new_um;
+ ObjectAddress referenced;
+ AclResult aclresult;
+ char *conninfo;
+
+ /*
+ * Remove what was there before, either another foreign server
+ * or a connection string.
+ */
+ if (form->subserver)
+ {
+ UserMapping *old_um = GetUserMapping(form->subowner, form->subserver);
+
+ deleteDependencyRecordsForSpecific(SubscriptionRelationId, form->oid,
+ DEPENDENCY_NORMAL,
+ ForeignServerRelationId, form->subserver);
+ deleteDependencyRecordsForSpecific(SubscriptionRelationId, form->oid,
+ DEPENDENCY_NORMAL,
+ UserMappingRelationId, old_um->umid);
+ }
+ else
+ {
+ nulls[Anum_pg_subscription_subconninfo - 1] = true;
+ replaces[Anum_pg_subscription_subconninfo - 1] = true;
+ }
+
+ /*
+ * Find the new server and user mapping. Check ACL of server
+ * based on current user ID, but find the user mapping based
+ * on the subscription owner.
+ */
+ new_server = GetForeignServerByName(stmt->servername, false);
+ aclresult = object_aclcheck(ForeignServerRelationId,
+ new_server->serverid,
+ form->subowner, ACL_USAGE);
+ if (aclresult != ACLCHECK_OK)
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("subscription owner \"%s\" does not have permission on foreign server \"%s\"",
+ GetUserNameFromId(form->subowner, false),
+ ForeignServerName(new_server->serverid))));
+
+ new_um = GetUserMapping(form->subowner, new_server->serverid);
+
+ conninfo = ForeignServerConnectionString(form->subowner,
+ new_server->serverid,
+ true);
+
+ /* Load the library providing us libpq calls. */
+ load_file("libpqwalreceiver", false);
+ /* Check the connection info string. */
+ walrcv_check_conninfo(conninfo,
+ sub->passwordrequired && !sub->ownersuperuser);
+
+ values[Anum_pg_subscription_subserver - 1] = new_server->serverid;
+ replaces[Anum_pg_subscription_subserver - 1] = true;
+
+ ObjectAddressSet(referenced, ForeignServerRelationId, new_server->serverid);
+ recordDependencyOn(&myself, &referenced, DEPENDENCY_NORMAL);
+
+ ObjectAddressSet(referenced, UserMappingRelationId, new_um->umid);
+ recordDependencyOn(&myself, &referenced, DEPENDENCY_NORMAL);
+ update_tuple = true;
+ }
+ break;
+
case ALTER_SUBSCRIPTION_CONNECTION:
+ /* remove reference to foreign server and dependencies, if present */
+ if (form->subserver)
+ {
+ UserMapping *old_um = GetUserMapping(form->subowner, form->subserver);
+
+ deleteDependencyRecordsForSpecific(SubscriptionRelationId, form->oid,
+ DEPENDENCY_NORMAL,
+ ForeignServerRelationId, form->subserver);
+ deleteDependencyRecordsForSpecific(SubscriptionRelationId, form->oid,
+ DEPENDENCY_NORMAL,
+ UserMappingRelationId, old_um->umid);
+
+ values[Anum_pg_subscription_subserver - 1] = InvalidOid;
+ replaces[Anum_pg_subscription_subserver - 1] = true;
+ }
+
/* Load the library providing us libpq calls. */
load_file("libpqwalreceiver", false);
/* Check the connection info string. */
@@ -1455,8 +1597,6 @@ AlterSubscription(ParseState *pstate, AlterSubscriptionStmt *stmt,
table_close(rel, RowExclusiveLock);
- ObjectAddressSet(myself, SubscriptionRelationId, subid);
-
InvokeObjectPostAlterHook(SubscriptionRelationId, subid, 0);
/* Wake up related replication workers to handle this change quickly. */
@@ -1541,9 +1681,28 @@ DropSubscription(DropSubscriptionStmt *stmt, bool isTopLevel)
subname = pstrdup(NameStr(*DatumGetName(datum)));
/* Get conninfo */
- datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
- Anum_pg_subscription_subconninfo);
- conninfo = TextDatumGetCString(datum);
+ if (OidIsValid(form->subserver))
+ {
+ AclResult aclresult;
+
+ aclresult = object_aclcheck(ForeignServerRelationId, form->subserver,
+ form->subowner, ACL_USAGE);
+ if (aclresult != ACLCHECK_OK)
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("subscription owner \"%s\" does not have permission on foreign server \"%s\"",
+ GetUserNameFromId(form->subowner, false),
+ ForeignServerName(form->subserver))));
+
+ conninfo = ForeignServerConnectionString(form->subowner,
+ form->subserver, true);
+ }
+ else
+ {
+ datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID, tup,
+ Anum_pg_subscription_subconninfo);
+ conninfo = TextDatumGetCString(datum);
+ }
/* Get slotname */
datum = SysCacheGetAttr(SUBSCRIPTIONOID, tup,
@@ -1644,6 +1803,7 @@ DropSubscription(DropSubscriptionStmt *stmt, bool isTopLevel)
}
/* Clean up dependencies */
+ deleteDependencyRecordsFor(SubscriptionRelationId, subid, false);
deleteSharedDependencyRecordsFor(SubscriptionRelationId, subid, 0);
/* Remove any associated relation synchronization states. */
@@ -1853,6 +2013,17 @@ AlterSubscriptionOwner_internal(Relation rel, HeapTuple tup, Oid newOwnerId)
aclcheck_error(aclresult, OBJECT_DATABASE,
get_database_name(MyDatabaseId));
+ if (form->subserver)
+ {
+ UserMapping *old_um = GetUserMapping(form->subowner, form->subserver);
+ UserMapping *new_um = GetUserMapping(newOwnerId, form->subserver);
+
+ if (changeDependencyFor(SubscriptionRelationId, form->oid,
+ UserMappingRelationId, old_um->umid, new_um->umid) != 1)
+ elog(ERROR, "could not change user mapping dependency for subscription %u",
+ form->oid);
+ }
+
form->subowner = newOwnerId;
CatalogTupleUpdate(rel, &tup->t_self, tup);
diff --git a/src/backend/foreign/foreign.c b/src/backend/foreign/foreign.c
index ead4a2cd2c..ba2dea88a9 100644
--- a/src/backend/foreign/foreign.c
+++ b/src/backend/foreign/foreign.c
@@ -180,6 +180,31 @@ GetForeignServerExtended(Oid serverid, bits16 flags)
}
+/*
+ * ForeignServerName - get name of foreign server.
+ */
+char *
+ForeignServerName(Oid serverid)
+{
+ Form_pg_foreign_server serverform;
+ char *servername;
+ HeapTuple tp;
+
+ tp = SearchSysCache1(FOREIGNSERVEROID, ObjectIdGetDatum(serverid));
+
+ if (!HeapTupleIsValid(tp))
+ elog(ERROR, "cache lookup failed for foreign server %u", serverid);
+
+ serverform = (Form_pg_foreign_server) GETSTRUCT(tp);
+
+ servername = pstrdup(NameStr(serverform->srvname));
+
+ ReleaseSysCache(tp);
+
+ return servername;
+}
+
+
/*
* GetForeignServerByName - look up the foreign server definition by name.
*/
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 3460fea56b..c27e0b8b5d 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -10654,6 +10654,16 @@ CreateSubscriptionStmt:
n->options = $8;
$$ = (Node *) n;
}
+ | CREATE SUBSCRIPTION name SERVER name PUBLICATION name_list opt_definition
+ {
+ CreateSubscriptionStmt *n =
+ makeNode(CreateSubscriptionStmt);
+ n->subname = $3;
+ n->servername = $5;
+ n->publication = $7;
+ n->options = $8;
+ $$ = (Node *) n;
+ }
;
/*****************************************************************************
@@ -10683,6 +10693,16 @@ AlterSubscriptionStmt:
n->conninfo = $5;
$$ = (Node *) n;
}
+ | ALTER SUBSCRIPTION name SERVER name
+ {
+ AlterSubscriptionStmt *n =
+ makeNode(AlterSubscriptionStmt);
+
+ n->kind = ALTER_SUBSCRIPTION_SERVER;
+ n->subname = $3;
+ n->servername = $5;
+ $$ = (Node *) n;
+ }
| ALTER SUBSCRIPTION name REFRESH PUBLICATION opt_definition
{
AlterSubscriptionStmt *n =
diff --git a/src/backend/replication/logical/worker.c b/src/backend/replication/logical/worker.c
index 911835c5cb..73cc7fc2c6 100644
--- a/src/backend/replication/logical/worker.c
+++ b/src/backend/replication/logical/worker.c
@@ -3897,7 +3897,7 @@ maybe_reread_subscription(void)
/* Ensure allocations in permanent context. */
oldctx = MemoryContextSwitchTo(ApplyContext);
- newsub = GetSubscription(MyLogicalRepWorker->subid, true);
+ newsub = GetSubscription(MyLogicalRepWorker->subid, true, true);
/*
* Exit if the subscription was removed. This normally should not happen
@@ -4003,7 +4003,9 @@ maybe_reread_subscription(void)
}
/*
- * Callback from subscription syscache invalidation.
+ * Callback from subscription syscache invalidation. Also needed for server or
+ * user mapping invalidation, which can change the connection information for
+ * subscriptions that connect using a server object.
*/
static void
subscription_change_cb(Datum arg, int cacheid, uint32 hashvalue)
@@ -4602,7 +4604,7 @@ InitializeLogRepWorker(void)
StartTransactionCommand();
oldctx = MemoryContextSwitchTo(ApplyContext);
- MySubscription = GetSubscription(MyLogicalRepWorker->subid, true);
+ MySubscription = GetSubscription(MyLogicalRepWorker->subid, true, true);
if (!MySubscription)
{
ereport(LOG,
@@ -4639,6 +4641,14 @@ InitializeLogRepWorker(void)
CacheRegisterSyscacheCallback(SUBSCRIPTIONOID,
subscription_change_cb,
(Datum) 0);
+ /* Keep us informed about subscription changes. */
+ CacheRegisterSyscacheCallback(FOREIGNSERVEROID,
+ subscription_change_cb,
+ (Datum) 0);
+ /* Keep us informed about subscription changes. */
+ CacheRegisterSyscacheCallback(USERMAPPINGOID,
+ subscription_change_cb,
+ (Datum) 0);
CacheRegisterSyscacheCallback(AUTHOID,
subscription_change_cb,
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 5b7a18dbb1..36d06fb02b 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -4655,6 +4655,7 @@ getSubscriptions(Archive *fout)
int i_subdisableonerr;
int i_subpasswordrequired;
int i_subrunasowner;
+ int i_subservername;
int i_subconninfo;
int i_subslotname;
int i_subsynccommit;
@@ -4726,10 +4727,12 @@ getSubscriptions(Archive *fout)
LOGICALREP_ORIGIN_ANY);
if (dopt->binary_upgrade && fout->remoteVersion >= 170000)
- appendPQExpBufferStr(query, " o.remote_lsn AS suboriginremotelsn,\n"
+ appendPQExpBufferStr(query, " fs.srvname AS subservername,\n"
+ " o.remote_lsn AS suboriginremotelsn,\n"
" s.subenabled\n");
else
- appendPQExpBufferStr(query, " NULL AS suboriginremotelsn,\n"
+ appendPQExpBufferStr(query, " NULL AS subservername,\n"
+ " NULL AS suboriginremotelsn,\n"
" false AS subenabled\n");
appendPQExpBufferStr(query,
@@ -4737,6 +4740,8 @@ getSubscriptions(Archive *fout)
if (dopt->binary_upgrade && fout->remoteVersion >= 170000)
appendPQExpBufferStr(query,
+ "LEFT JOIN pg_catalog.pg_foreign_server fs \n"
+ " ON fs.oid = s.subserver \n"
"LEFT JOIN pg_catalog.pg_replication_origin_status o \n"
" ON o.external_id = 'pg_' || s.oid::text \n");
@@ -4762,6 +4767,7 @@ getSubscriptions(Archive *fout)
i_subdisableonerr = PQfnumber(res, "subdisableonerr");
i_subpasswordrequired = PQfnumber(res, "subpasswordrequired");
i_subrunasowner = PQfnumber(res, "subrunasowner");
+ i_subservername = PQfnumber(res, "subservername");
i_subconninfo = PQfnumber(res, "subconninfo");
i_subslotname = PQfnumber(res, "subslotname");
i_subsynccommit = PQfnumber(res, "subsynccommit");
@@ -4781,7 +4787,10 @@ getSubscriptions(Archive *fout)
AssignDumpId(&subinfo[i].dobj);
subinfo[i].dobj.name = pg_strdup(PQgetvalue(res, i, i_subname));
subinfo[i].rolname = getRoleName(PQgetvalue(res, i, i_subowner));
-
+ if (PQgetisnull(res, i, i_subservername))
+ subinfo[i].subservername = NULL;
+ else
+ subinfo[i].subservername = pg_strdup(PQgetvalue(res, i, i_subservername));
subinfo[i].subbinary =
pg_strdup(PQgetvalue(res, i, i_subbinary));
subinfo[i].substream =
@@ -5007,9 +5016,17 @@ dumpSubscription(Archive *fout, const SubscriptionInfo *subinfo)
appendPQExpBuffer(delq, "DROP SUBSCRIPTION %s;\n",
qsubname);
- appendPQExpBuffer(query, "CREATE SUBSCRIPTION %s CONNECTION ",
+ appendPQExpBuffer(query, "CREATE SUBSCRIPTION %s ",
qsubname);
- appendStringLiteralAH(query, subinfo->subconninfo, fout);
+ if (subinfo->subservername)
+ {
+ appendPQExpBuffer(query, "SERVER %s", fmtId(subinfo->subservername));
+ }
+ else
+ {
+ appendPQExpBuffer(query, "CONNECTION ");
+ appendStringLiteralAH(query, subinfo->subconninfo, fout);
+ }
/* Build list of quoted publications and append them to query. */
if (!parsePGArray(subinfo->subpublications, &pubnames, &npubnames))
diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h
index f0772d2157..849950e470 100644
--- a/src/bin/pg_dump/pg_dump.h
+++ b/src/bin/pg_dump/pg_dump.h
@@ -659,6 +659,7 @@ typedef struct _SubscriptionInfo
char *subdisableonerr;
char *subpasswordrequired;
char *subrunasowner;
+ char *subservername;
char *subconninfo;
char *subslotname;
char *subsynccommit;
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 09914165e4..995f4f7f22 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -3322,7 +3322,7 @@ psql_completion(const char *text, int start, int end)
/* CREATE SUBSCRIPTION */
else if (Matches("CREATE", "SUBSCRIPTION", MatchAny))
- COMPLETE_WITH("CONNECTION");
+ COMPLETE_WITH("SERVER", "CONNECTION");
else if (Matches("CREATE", "SUBSCRIPTION", MatchAny, "CONNECTION", MatchAny))
COMPLETE_WITH("PUBLICATION");
else if (Matches("CREATE", "SUBSCRIPTION", MatchAny, "CONNECTION",
diff --git a/src/include/catalog/pg_subscription.h b/src/include/catalog/pg_subscription.h
index ca32625585..e2321cd3a4 100644
--- a/src/include/catalog/pg_subscription.h
+++ b/src/include/catalog/pg_subscription.h
@@ -93,9 +93,11 @@ CATALOG(pg_subscription,6100,SubscriptionRelationId) BKI_SHARED_RELATION BKI_ROW
bool subrunasowner; /* True if replication should execute as the
* subscription owner */
+ Oid subserver; /* Set if connecting with server */
+
#ifdef CATALOG_VARLEN /* variable-length fields start here */
/* Connection string to the publisher */
- text subconninfo BKI_FORCE_NOT_NULL;
+ text subconninfo; /* Set if connecting with connection string */
/* Slot name on publisher */
NameData subslotname BKI_FORCE_NULL;
@@ -162,7 +164,8 @@ typedef struct Subscription
*/
#define LOGICALREP_STREAM_PARALLEL 'p'
-extern Subscription *GetSubscription(Oid subid, bool missing_ok);
+extern Subscription *GetSubscription(Oid subid, bool missing_ok,
+ bool aclcheck);
extern void FreeSubscription(Subscription *sub);
extern void DisableSubscription(Oid subid);
diff --git a/src/include/foreign/foreign.h b/src/include/foreign/foreign.h
index b5b9b97f4d..a2f04ce9af 100644
--- a/src/include/foreign/foreign.h
+++ b/src/include/foreign/foreign.h
@@ -65,6 +65,7 @@ typedef struct ForeignTable
extern ForeignServer *GetForeignServer(Oid serverid);
+extern char *ForeignServerName(Oid serverid);
extern ForeignServer *GetForeignServerExtended(Oid serverid,
bits16 flags);
extern ForeignServer *GetForeignServerByName(const char *srvname,
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index b3181f34ae..6d6b242cec 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -4041,6 +4041,7 @@ typedef struct CreateSubscriptionStmt
{
NodeTag type;
char *subname; /* Name of the subscription */
+ char *servername; /* Server name of publisher */
char *conninfo; /* Connection string to publisher */
List *publication; /* One or more publication to subscribe to */
List *options; /* List of DefElem nodes */
@@ -4049,6 +4050,7 @@ typedef struct CreateSubscriptionStmt
typedef enum AlterSubscriptionType
{
ALTER_SUBSCRIPTION_OPTIONS,
+ ALTER_SUBSCRIPTION_SERVER,
ALTER_SUBSCRIPTION_CONNECTION,
ALTER_SUBSCRIPTION_SET_PUBLICATION,
ALTER_SUBSCRIPTION_ADD_PUBLICATION,
@@ -4063,6 +4065,7 @@ typedef struct AlterSubscriptionStmt
NodeTag type;
AlterSubscriptionType kind; /* ALTER_SUBSCRIPTION_OPTIONS, etc */
char *subname; /* Name of the subscription */
+ char *servername; /* Server name of publisher */
char *conninfo; /* Connection string to publisher */
List *publication; /* One or more publication to subscribe to */
List *options; /* List of DefElem nodes */
diff --git a/src/test/regress/expected/foreign_data.out b/src/test/regress/expected/foreign_data.out
index 6ae93c41f6..83d02af343 100644
--- a/src/test/regress/expected/foreign_data.out
+++ b/src/test/regress/expected/foreign_data.out
@@ -432,6 +432,20 @@ SELECT pg_conninfo_from_server('connection_server', 'regress_test_role2', false)
DROP USER MAPPING FOR regress_test_role SERVER connection_server;
DROP USER MAPPING FOR PUBLIC SERVER connection_server;
DROP SERVER connection_server;
+SET ROLE regress_test_role;
+CREATE SERVER t3 FOREIGN DATA WRAPPER pg_connection_fdw; -- ERROR: no permissions on FDW
+ERROR: permission denied for foreign-data wrapper pg_connection_fdw
+RESET ROLE;
+GRANT USAGE ON FOREIGN DATA WRAPPER pg_connection_fdw TO regress_test_role;
+SET ROLE regress_test_role;
+CREATE SERVER t3 FOREIGN DATA WRAPPER pg_connection_fdw;
+IMPORT FOREIGN SCHEMA foo FROM SERVER t3 INTO bar; -- fails
+ERROR: schema "bar" does not exist
+CREATE USER MAPPING FOR PUBLIC SERVER t3 OPTIONS (user 'x', password 'secret');
+DROP USER MAPPING FOR PUBLIC SERVER t3;
+DROP SERVER t3;
+RESET ROLE;
+REVOKE USAGE ON FOREIGN DATA WRAPPER pg_connection_fdw FROM regress_test_role;
-- ALTER SERVER
ALTER SERVER s0; -- ERROR
ERROR: syntax error at or near ";"
diff --git a/src/test/regress/expected/subscription.out b/src/test/regress/expected/subscription.out
index b15eddbff3..11edae46b1 100644
--- a/src/test/regress/expected/subscription.out
+++ b/src/test/regress/expected/subscription.out
@@ -144,6 +144,58 @@ ERROR: could not connect to the publisher: invalid port number: "-1"
ALTER SUBSCRIPTION regress_testsub CONNECTION 'foobar';
ERROR: invalid connection string syntax: missing "=" after "foobar" in connection info string
+RESET SESSION AUTHORIZATION;
+GRANT CREATE ON DATABASE REGRESSION TO regress_subscription_user3;
+SET SESSION AUTHORIZATION regress_subscription_user3;
+CREATE SUBSCRIPTION regress_testsub6 CONNECTION 'dbname=regress_doesnotexist password=regress_fakepassword' 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 refresh the subscription.
+DROP SUBSCRIPTION regress_testsub6;
+-- test using a server object instead of connection string
+RESET SESSION AUTHORIZATION;
+CREATE SERVER regress_testserver1 FOREIGN DATA WRAPPER pg_connection_fdw;
+CREATE SERVER regress_testserver2 FOREIGN DATA WRAPPER pg_connection_fdw;
+CREATE USER MAPPING FOR regress_subscription_user3 SERVER regress_testserver1
+ OPTIONS (password 'secret');
+CREATE USER MAPPING FOR regress_subscription_user3 SERVER regress_testserver2
+ OPTIONS (password 'secret');
+GRANT USAGE ON FOREIGN SERVER regress_testserver2 TO regress_subscription_user3;
+SET SESSION AUTHORIZATION regress_subscription_user3;
+CREATE SUBSCRIPTION regress_testsub6 SERVER regress_testserver1 PUBLICATION testpub
+ WITH (slot_name = NONE, connect = false); -- fails
+ERROR: permission denied for foreign server regress_testserver1
+CREATE SUBSCRIPTION regress_testsub6 SERVER regress_testserver2 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 refresh the subscription.
+RESET SESSION AUTHORIZATION;
+ALTER SUBSCRIPTION regress_testsub6 SERVER regress_testserver1; -- fails
+ERROR: subscription owner "regress_subscription_user3" does not have permission on foreign server "regress_testserver1"
+GRANT USAGE ON FOREIGN SERVER regress_testserver1 TO regress_subscription_user3;
+ALTER SUBSCRIPTION regress_testsub6 SERVER regress_testserver1;
+DROP USER MAPPING FOR regress_subscription_user3 SERVER regress_testserver2;
+DROP SERVER regress_testserver2;
+-- test an FDW with no validator
+CREATE FOREIGN DATA WRAPPER regress_fdw;
+CREATE SERVER regress_testserver3 FOREIGN DATA WRAPPER regress_fdw
+ OPTIONS (abc 'xyz');
+CREATE USER MAPPING FOR regress_subscription_user3 SERVER regress_testserver3
+ OPTIONS (password 'secret');
+GRANT USAGE ON FOREIGN SERVER regress_testserver3 TO regress_subscription_user3;
+SET SESSION AUTHORIZATION regress_subscription_user3;
+ALTER SUBSCRIPTION regress_testsub6 SERVER regress_testserver3;
+ALTER SUBSCRIPTION regress_testsub6 SERVER regress_testserver1;
+RESET SESSION AUTHORIZATION;
+DROP USER MAPPING FOR regress_subscription_user3 SERVER regress_testserver3;
+DROP SERVER regress_testserver3;
+DROP FOREIGN DATA WRAPPER regress_fdw;
+ALTER SUBSCRIPTION regress_testsub6 OWNER TO regress_subscription_user; -- fail, no user mapping
+ERROR: user mapping not found for user "regress_subscription_user", server "regress_testserver1"
+DROP SUBSCRIPTION regress_testsub6;
+DROP USER MAPPING FOR regress_subscription_user3 SERVER regress_testserver1;
+DROP SERVER regress_testserver1;
+REVOKE CREATE ON DATABASE regression FROM regress_subscription_user3;
+SET SESSION AUTHORIZATION regress_subscription_user;
\dRs+
List of subscriptions
Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Synchronous commit | Conninfo | Skip LSN
diff --git a/src/test/regress/sql/foreign_data.sql b/src/test/regress/sql/foreign_data.sql
index b125dff9fb..163ec62e78 100644
--- a/src/test/regress/sql/foreign_data.sql
+++ b/src/test/regress/sql/foreign_data.sql
@@ -212,6 +212,23 @@ DROP USER MAPPING FOR regress_test_role SERVER connection_server;
DROP USER MAPPING FOR PUBLIC SERVER connection_server;
DROP SERVER connection_server;
+SET ROLE regress_test_role;
+CREATE SERVER t3 FOREIGN DATA WRAPPER pg_connection_fdw; -- ERROR: no permissions on FDW
+RESET ROLE;
+GRANT USAGE ON FOREIGN DATA WRAPPER pg_connection_fdw TO regress_test_role;
+SET ROLE regress_test_role;
+
+CREATE SERVER t3 FOREIGN DATA WRAPPER pg_connection_fdw;
+
+IMPORT FOREIGN SCHEMA foo FROM SERVER t3 INTO bar; -- fails
+
+CREATE USER MAPPING FOR PUBLIC SERVER t3 OPTIONS (user 'x', password 'secret');
+DROP USER MAPPING FOR PUBLIC SERVER t3;
+DROP SERVER t3;
+
+RESET ROLE;
+REVOKE USAGE ON FOREIGN DATA WRAPPER pg_connection_fdw FROM regress_test_role;
+
-- ALTER SERVER
ALTER SERVER s0; -- ERROR
ALTER SERVER s0 OPTIONS (a '1'); -- ERROR
diff --git a/src/test/regress/sql/subscription.sql b/src/test/regress/sql/subscription.sql
index 444e563ff3..bf8421fb50 100644
--- a/src/test/regress/sql/subscription.sql
+++ b/src/test/regress/sql/subscription.sql
@@ -88,6 +88,62 @@ CREATE SUBSCRIPTION regress_testsub5 CONNECTION 'port=-1' PUBLICATION testpub;
-- fail - invalid connection string during ALTER
ALTER SUBSCRIPTION regress_testsub CONNECTION 'foobar';
+RESET SESSION AUTHORIZATION;
+GRANT CREATE ON DATABASE REGRESSION TO regress_subscription_user3;
+SET SESSION AUTHORIZATION regress_subscription_user3;
+
+CREATE SUBSCRIPTION regress_testsub6 CONNECTION 'dbname=regress_doesnotexist password=regress_fakepassword' PUBLICATION testpub WITH (slot_name = NONE, connect = false);
+DROP SUBSCRIPTION regress_testsub6;
+
+-- test using a server object instead of connection string
+
+RESET SESSION AUTHORIZATION;
+CREATE SERVER regress_testserver1 FOREIGN DATA WRAPPER pg_connection_fdw;
+CREATE SERVER regress_testserver2 FOREIGN DATA WRAPPER pg_connection_fdw;
+CREATE USER MAPPING FOR regress_subscription_user3 SERVER regress_testserver1
+ OPTIONS (password 'secret');
+CREATE USER MAPPING FOR regress_subscription_user3 SERVER regress_testserver2
+ OPTIONS (password 'secret');
+GRANT USAGE ON FOREIGN SERVER regress_testserver2 TO regress_subscription_user3;
+
+SET SESSION AUTHORIZATION regress_subscription_user3;
+CREATE SUBSCRIPTION regress_testsub6 SERVER regress_testserver1 PUBLICATION testpub
+ WITH (slot_name = NONE, connect = false); -- fails
+CREATE SUBSCRIPTION regress_testsub6 SERVER regress_testserver2 PUBLICATION testpub
+ WITH (slot_name = NONE, connect = false);
+RESET SESSION AUTHORIZATION;
+
+ALTER SUBSCRIPTION regress_testsub6 SERVER regress_testserver1; -- fails
+GRANT USAGE ON FOREIGN SERVER regress_testserver1 TO regress_subscription_user3;
+ALTER SUBSCRIPTION regress_testsub6 SERVER regress_testserver1;
+DROP USER MAPPING FOR regress_subscription_user3 SERVER regress_testserver2;
+DROP SERVER regress_testserver2;
+
+-- test an FDW with no validator
+CREATE FOREIGN DATA WRAPPER regress_fdw;
+CREATE SERVER regress_testserver3 FOREIGN DATA WRAPPER regress_fdw
+ OPTIONS (abc 'xyz');
+CREATE USER MAPPING FOR regress_subscription_user3 SERVER regress_testserver3
+ OPTIONS (password 'secret');
+GRANT USAGE ON FOREIGN SERVER regress_testserver3 TO regress_subscription_user3;
+
+SET SESSION AUTHORIZATION regress_subscription_user3;
+ALTER SUBSCRIPTION regress_testsub6 SERVER regress_testserver3;
+ALTER SUBSCRIPTION regress_testsub6 SERVER regress_testserver1;
+
+RESET SESSION AUTHORIZATION;
+DROP USER MAPPING FOR regress_subscription_user3 SERVER regress_testserver3;
+DROP SERVER regress_testserver3;
+DROP FOREIGN DATA WRAPPER regress_fdw;
+
+ALTER SUBSCRIPTION regress_testsub6 OWNER TO regress_subscription_user; -- fail, no user mapping
+DROP SUBSCRIPTION regress_testsub6;
+
+DROP USER MAPPING FOR regress_subscription_user3 SERVER regress_testserver1;
+DROP SERVER regress_testserver1;
+REVOKE CREATE ON DATABASE regression FROM regress_subscription_user3;
+SET SESSION AUTHORIZATION regress_subscription_user;
+
\dRs+
ALTER SUBSCRIPTION regress_testsub SET PUBLICATION testpub2, testpub3 WITH (refresh = false);
diff --git a/src/test/subscription/t/001_rep_changes.pl b/src/test/subscription/t/001_rep_changes.pl
index 9ccebd890a..4cbf2dceaa 100644
--- a/src/test/subscription/t/001_rep_changes.pl
+++ b/src/test/subscription/t/001_rep_changes.pl
@@ -27,6 +27,8 @@ $node_publisher->safe_psql('postgres',
"CREATE TABLE tab_notrep AS SELECT generate_series(1,10) AS a");
$node_publisher->safe_psql('postgres',
"CREATE TABLE tab_ins AS SELECT generate_series(1,1002) AS a");
+$node_publisher->safe_psql('postgres',
+ "CREATE TABLE tab_ins2 AS SELECT generate_series(1,1002) AS a");
$node_publisher->safe_psql('postgres',
"CREATE TABLE tab_full AS SELECT generate_series(1,10) AS a");
$node_publisher->safe_psql('postgres', "CREATE TABLE tab_full2 (x text)");
@@ -65,6 +67,7 @@ $node_publisher->safe_psql('postgres',
# Setup structure on subscriber
$node_subscriber->safe_psql('postgres', "CREATE TABLE tab_notrep (a int)");
$node_subscriber->safe_psql('postgres', "CREATE TABLE tab_ins (a int)");
+$node_subscriber->safe_psql('postgres', "CREATE TABLE tab_ins2 (a int)");
$node_subscriber->safe_psql('postgres', "CREATE TABLE tab_full (a int)");
$node_subscriber->safe_psql('postgres', "CREATE TABLE tab_full2 (x text)");
$node_subscriber->safe_psql('postgres',
@@ -110,6 +113,22 @@ $node_subscriber->safe_psql('postgres',
"CREATE SUBSCRIPTION tap_sub CONNECTION '$publisher_connstr' PUBLICATION tap_pub, tap_pub_ins_only"
);
+my $publisher_host = $node_publisher->host;
+my $publisher_port = $node_publisher->port;
+$node_subscriber->safe_psql('postgres',
+ "CREATE SERVER tap_sub2_server FOREIGN DATA WRAPPER pg_connection_fdw OPTIONS (host '$publisher_host', port '$publisher_port', dbname 'postgres')"
+);
+
+$node_subscriber->safe_psql('postgres',
+ "CREATE USER MAPPING FOR PUBLIC SERVER tap_sub2_server"
+);
+
+$node_publisher->safe_psql('postgres', "CREATE PUBLICATION tap_simple_pub FOR TABLE tab_ins2");
+
+$node_subscriber->safe_psql('postgres',
+ "CREATE SUBSCRIPTION tap_sub2 SERVER tap_sub2_server PUBLICATION tap_simple_pub WITH (password_required=false)"
+);
+
# Wait for initial table sync to finish
$node_subscriber->wait_for_subscription_sync($node_publisher, 'tap_sub');
@@ -121,11 +140,22 @@ $result =
$node_subscriber->safe_psql('postgres', "SELECT count(*) FROM tab_ins");
is($result, qq(1002), 'check initial data was copied to subscriber');
+$result =
+ $node_subscriber->safe_psql('postgres', "SELECT count(*) FROM tab_ins2");
+is($result, qq(1002), 'check initial data was copied to subscriber');
+
+$node_subscriber->safe_psql('postgres', "ALTER SUBSCRIPTION tap_sub2 CONNECTION '$publisher_connstr'");
+
$node_publisher->safe_psql('postgres',
"INSERT INTO tab_ins SELECT generate_series(1,50)");
$node_publisher->safe_psql('postgres', "DELETE FROM tab_ins WHERE a > 20");
$node_publisher->safe_psql('postgres', "UPDATE tab_ins SET a = -a");
+$node_publisher->safe_psql('postgres',
+ "INSERT INTO tab_ins2 SELECT generate_series(1,50)");
+
+$node_subscriber->safe_psql('postgres', "ALTER SUBSCRIPTION tap_sub2 SERVER tap_sub2_server");
+
$node_publisher->safe_psql('postgres',
"INSERT INTO tab_rep SELECT generate_series(1,50)");
$node_publisher->safe_psql('postgres', "DELETE FROM tab_rep WHERE a > 20");
@@ -158,6 +188,10 @@ $result = $node_subscriber->safe_psql('postgres',
"SELECT count(*), min(a), max(a) FROM tab_ins");
is($result, qq(1052|1|1002), 'check replicated inserts on subscriber');
+$result = $node_subscriber->safe_psql('postgres',
+ "SELECT count(*), min(a), max(a) FROM tab_ins2");
+is($result, qq(1052|1|1002), 'check replicated inserts on subscriber');
+
$result = $node_subscriber->safe_psql('postgres',
"SELECT count(*), min(a), max(a) FROM tab_rep");
is($result, qq(20|-20|-1), 'check replicated changes on subscriber');
@@ -449,10 +483,27 @@ $node_publisher->poll_query_until('postgres',
or die
"Timed out while waiting for apply to restart after changing PUBLICATION";
+# test that changes to a foreign server subscription cause the worker
+# to restart
+$oldpid = $node_publisher->safe_psql('postgres',
+ "SELECT pid FROM pg_stat_replication WHERE application_name = 'tap_sub2' AND state = 'streaming';"
+);
+$node_subscriber->safe_psql('postgres',
+ "ALTER SERVER tap_sub2_server OPTIONS (sslmode 'disable')"
+);
+$node_publisher->poll_query_until('postgres',
+ "SELECT pid != $oldpid FROM pg_stat_replication WHERE application_name = 'tap_sub2' AND state = 'streaming';"
+ )
+ or die
+ "Timed out while waiting for apply to restart after changing PUBLICATION";
+
$node_publisher->safe_psql('postgres',
"INSERT INTO tab_ins SELECT generate_series(1001,1100)");
$node_publisher->safe_psql('postgres', "DELETE FROM tab_rep");
+$node_publisher->safe_psql('postgres',
+ "INSERT INTO tab_ins2 SELECT generate_series(1001,1100)");
+
# Restart the publisher and check the state of the subscriber which
# should be in a streaming state after catching up.
$node_publisher->stop('fast');
@@ -465,6 +516,11 @@ $result = $node_subscriber->safe_psql('postgres',
is($result, qq(1152|1|1100),
'check replicated inserts after subscription publication change');
+$result = $node_subscriber->safe_psql('postgres',
+ "SELECT count(*), min(a), max(a) FROM tab_ins2");
+is($result, qq(1152|1|1100),
+ 'check replicated inserts after subscription publication change');
+
$result = $node_subscriber->safe_psql('postgres',
"SELECT count(*), min(a), max(a) FROM tab_rep");
is($result, qq(20|-20|-1),
@@ -533,6 +589,7 @@ $node_publisher->poll_query_until('postgres',
# check all the cleanup
$node_subscriber->safe_psql('postgres', "DROP SUBSCRIPTION tap_sub_renamed");
+$node_subscriber->safe_psql('postgres', "DROP SUBSCRIPTION tap_sub2");
$result = $node_subscriber->safe_psql('postgres',
"SELECT count(*) FROM pg_subscription");
--
2.34.1
[text/x-patch] v7-0004-Introduce-pg_create_connection-predefined-role.patch (26.3K, 5-v7-0004-Introduce-pg_create_connection-predefined-role.patch)
download | inline diff:
From e72601fcf4e71ef358bbadf9d87741cd5aa63df9 Mon Sep 17 00:00:00 2001
From: Jeff Davis <[email protected]>
Date: Tue, 2 Jan 2024 13:13:54 -0800
Subject: [PATCH v7 4/4] Introduce pg_create_connection predefined role.
In addition to pg_create_subscription, membership in this role is
necessary to create a subscription with a connection string. The
pg_create_subscription role is a member of pg_create_connection, so by
default pg_create_subscription has the same capability as before.
An administrator may revoke pg_create_connection from
pg_create_subscription, which will enable the privileges to be
separated. That will be useful in an upcoming change to introduce
CREATE SUBSCRIPTION ... SERVER, which will not use a raw connection
string, and therefore not require membership in the
pg_create_connection role.
---
.../postgres_fdw/expected/postgres_fdw.out | 2 +-
contrib/postgres_fdw/sql/postgres_fdw.sql | 2 +-
contrib/postgres_fdw/t/010_subscription.pl | 2 +-
doc/src/sgml/ref/alter_server.sgml | 14 ++++++++
doc/src/sgml/ref/create_server.sgml | 14 ++++++++
doc/src/sgml/user-manag.sgml | 12 +++++--
src/backend/catalog/system_functions.sql | 2 ++
src/backend/commands/foreigncmds.c | 27 ++++++++++++++++
src/backend/commands/subscriptioncmds.c | 25 +++++++++++++--
src/backend/foreign/foreign.c | 1 +
src/backend/parser/gram.y | 30 ++++++++++++++---
src/include/catalog/pg_authid.dat | 5 +++
src/include/catalog/pg_foreign_server.h | 1 +
src/include/foreign/foreign.h | 1 +
src/include/nodes/parsenodes.h | 3 ++
src/test/regress/expected/subscription.out | 31 ++++++++++++++++--
src/test/regress/sql/subscription.sql | 32 +++++++++++++++++--
src/test/subscription/t/001_rep_changes.pl | 2 +-
18 files changed, 189 insertions(+), 17 deletions(-)
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index 0aa751e099..dce87919af 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -2,7 +2,7 @@
-- create FDW objects
-- ===================================================================
CREATE EXTENSION postgres_fdw;
-CREATE SERVER testserver1 FOREIGN DATA WRAPPER postgres_fdw;
+CREATE SERVER testserver1 FOREIGN DATA WRAPPER postgres_fdw FOR SUBSCRIPTION;
DO $d$
BEGIN
EXECUTE $$CREATE SERVER loopback FOREIGN DATA WRAPPER postgres_fdw
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index 3b2716b82e..a34aca2956 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -4,7 +4,7 @@
CREATE EXTENSION postgres_fdw;
-CREATE SERVER testserver1 FOREIGN DATA WRAPPER postgres_fdw;
+CREATE SERVER testserver1 FOREIGN DATA WRAPPER postgres_fdw FOR SUBSCRIPTION;
DO $d$
BEGIN
EXECUTE $$CREATE SERVER loopback FOREIGN DATA WRAPPER postgres_fdw
diff --git a/contrib/postgres_fdw/t/010_subscription.pl b/contrib/postgres_fdw/t/010_subscription.pl
index daa0b9edd2..d1d80d0679 100644
--- a/contrib/postgres_fdw/t/010_subscription.pl
+++ b/contrib/postgres_fdw/t/010_subscription.pl
@@ -38,7 +38,7 @@ $node_publisher->safe_psql('postgres', "CREATE PUBLICATION tap_pub FOR TABLE tab
my $publisher_host = $node_publisher->host;
my $publisher_port = $node_publisher->port;
$node_subscriber->safe_psql('postgres',
- "CREATE SERVER tap_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '$publisher_host', port '$publisher_port', dbname 'postgres')"
+ "CREATE SERVER tap_server FOREIGN DATA WRAPPER postgres_fdw FOR SUBSCRIPTION OPTIONS (host '$publisher_host', port '$publisher_port', dbname 'postgres')"
);
$node_subscriber->safe_psql('postgres',
diff --git a/doc/src/sgml/ref/alter_server.sgml b/doc/src/sgml/ref/alter_server.sgml
index 467bf85589..1a4227e548 100644
--- a/doc/src/sgml/ref/alter_server.sgml
+++ b/doc/src/sgml/ref/alter_server.sgml
@@ -22,6 +22,7 @@ PostgreSQL documentation
<refsynopsisdiv>
<synopsis>
ALTER SERVER <replaceable class="parameter">name</replaceable> [ VERSION '<replaceable class="parameter">new_version</replaceable>' ]
+ [ { FOR | NO } SUBSCRIPTION ]
[ OPTIONS ( [ ADD | SET | DROP ] <replaceable class="parameter">option</replaceable> ['<replaceable class="parameter">value</replaceable>'] [, ... ] ) ]
ALTER SERVER <replaceable class="parameter">name</replaceable> OWNER TO { <replaceable>new_owner</replaceable> | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
ALTER SERVER <replaceable class="parameter">name</replaceable> RENAME TO <replaceable>new_name</replaceable>
@@ -70,6 +71,19 @@ ALTER SERVER <replaceable class="parameter">name</replaceable> RENAME TO <replac
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>{ FOR | NO } SUBSCRIPTION</literal></term>
+ <listitem>
+ <para>
+ This clause specifies whether the foreign server may be used for a
+ subscription (see <xref linkend="sql-createsubscription"/>). The default
+ is <literal>NO SUBSCRIPTION</literal>. Only members of the role
+ <literal>pg_create_connection</literal> may specify <literal>FOR
+ SUBSCRIPTION</literal>.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><literal>OPTIONS ( [ ADD | SET | DROP ] <replaceable class="parameter">option</replaceable> ['<replaceable class="parameter">value</replaceable>'] [, ... ] )</literal></term>
<listitem>
diff --git a/doc/src/sgml/ref/create_server.sgml b/doc/src/sgml/ref/create_server.sgml
index 05f4019453..913cebabf2 100644
--- a/doc/src/sgml/ref/create_server.sgml
+++ b/doc/src/sgml/ref/create_server.sgml
@@ -23,6 +23,7 @@ PostgreSQL documentation
<synopsis>
CREATE SERVER [ IF NOT EXISTS ] <replaceable class="parameter">server_name</replaceable> [ TYPE '<replaceable class="parameter">server_type</replaceable>' ] [ VERSION '<replaceable class="parameter">server_version</replaceable>' ]
FOREIGN DATA WRAPPER <replaceable class="parameter">fdw_name</replaceable>
+ [ { FOR | NO } SUBSCRIPTION ]
[ OPTIONS ( <replaceable class="parameter">option</replaceable> '<replaceable class="parameter">value</replaceable>' [, ... ] ) ]
</synopsis>
</refsynopsisdiv>
@@ -104,6 +105,19 @@ CREATE SERVER [ IF NOT EXISTS ] <replaceable class="parameter">server_name</repl
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>{ FOR | NO } SUBSCRIPTION</literal></term>
+ <listitem>
+ <para>
+ This clause specifies whether the foreign server may be used for a
+ subscription (see <xref linkend="sql-createsubscription"/>). The default
+ is <literal>NO SUBSCRIPTION</literal>. Only members of the role
+ <literal>pg_create_connection</literal> may specify <literal>FOR
+ SUBSCRIPTION</literal>.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><literal>OPTIONS ( <replaceable class="parameter">option</replaceable> '<replaceable class="parameter">value</replaceable>' [, ... ] )</literal></term>
<listitem>
diff --git a/doc/src/sgml/user-manag.sgml b/doc/src/sgml/user-manag.sgml
index 92a299d2d3..4f4c20ba3c 100644
--- a/doc/src/sgml/user-manag.sgml
+++ b/doc/src/sgml/user-manag.sgml
@@ -687,11 +687,19 @@ DROP ROLE doomed_role;
<entry>Allow use of connection slots reserved via
<xref linkend="guc-reserved-connections"/>.</entry>
</row>
+ <row>
+ <entry>pg_create_connection</entry>
+ <entry>Allow users to specify a connection string directly in <link
+ linkend="sql-createsubscription"><command>CREATE
+ SUBSCRIPTION</command></link>.</entry>
+ </row>
<row>
<entry>pg_create_subscription</entry>
<entry>Allow users with <literal>CREATE</literal> permission on the
- database to issue
- <link linkend="sql-createsubscription"><command>CREATE SUBSCRIPTION</command></link>.</entry>
+ database to issue <link
+ linkend="sql-createsubscription"><command>CREATE
+ SUBSCRIPTION</command></link>. This role is a member of
+ <literal>pg_create_connection</literal>.</entry>
</row>
</tbody>
</tgroup>
diff --git a/src/backend/catalog/system_functions.sql b/src/backend/catalog/system_functions.sql
index f315fecf18..73512688de 100644
--- a/src/backend/catalog/system_functions.sql
+++ b/src/backend/catalog/system_functions.sql
@@ -781,3 +781,5 @@ GRANT pg_read_all_settings TO pg_monitor;
GRANT pg_read_all_stats TO pg_monitor;
GRANT pg_stat_scan_tables TO pg_monitor;
+
+GRANT pg_create_connection TO pg_create_subscription;
diff --git a/src/backend/commands/foreigncmds.c b/src/backend/commands/foreigncmds.c
index cf61bbac1f..2f83555370 100644
--- a/src/backend/commands/foreigncmds.c
+++ b/src/backend/commands/foreigncmds.c
@@ -21,6 +21,7 @@
#include "catalog/dependency.h"
#include "catalog/indexing.h"
#include "catalog/objectaccess.h"
+#include "catalog/pg_authid_d.h"
#include "catalog/pg_foreign_data_wrapper.h"
#include "catalog/pg_foreign_server.h"
#include "catalog/pg_foreign_table.h"
@@ -923,6 +924,18 @@ CreateForeignServer(CreateForeignServerStmt *stmt)
else
nulls[Anum_pg_foreign_server_srvversion - 1] = true;
+ if (stmt->forsubscription)
+ {
+ if (!has_privs_of_role(ownerId, ROLE_PG_CREATE_CONNECTION))
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("permission denied to create server for subscription"),
+ errdetail("Only roles with privileges of the \"%s\" role may create foreign servers with FOR SUBSCRIPTION specified.",
+ "pg_create_subscription")));
+
+ values[Anum_pg_foreign_server_srvforsubscription - 1] = true;
+ }
+
/* Start with a blank acl */
nulls[Anum_pg_foreign_server_srvacl - 1] = true;
@@ -1020,6 +1033,20 @@ AlterForeignServer(AlterForeignServerStmt *stmt)
repl_repl[Anum_pg_foreign_server_srvversion - 1] = true;
}
+ if ((srvForm->srvforsubscription || stmt->forsubscription) &&
+ !has_privs_of_role(srvForm->srvowner, ROLE_PG_CREATE_CONNECTION))
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("permission denied to alter server for subscription"),
+ errdetail("Only roles with privileges of the \"%s\" role may alter foreign servers with FOR SUBSCRIPTION specified.",
+ "pg_create_connection")));
+
+ if (stmt->has_forsubscription)
+ {
+ repl_val[Anum_pg_foreign_server_srvforsubscription - 1] = stmt->forsubscription;
+ repl_repl[Anum_pg_foreign_server_srvforsubscription - 1] = true;
+ }
+
if (stmt->options)
{
ForeignDataWrapper *fdw = GetForeignDataWrapper(srvForm->srvfdw);
diff --git a/src/backend/commands/subscriptioncmds.c b/src/backend/commands/subscriptioncmds.c
index bec665fd29..084928a212 100644
--- a/src/backend/commands/subscriptioncmds.c
+++ b/src/backend/commands/subscriptioncmds.c
@@ -609,9 +609,9 @@ CreateSubscription(ParseState *pstate, CreateSubscriptionStmt *stmt,
PreventInTransactionBlock(isTopLevel, "CREATE SUBSCRIPTION ... WITH (create_slot = true)");
/*
- * We don't want to allow unprivileged users to be able to trigger
- * attempts to access arbitrary network destinations, so require the user
- * to have been specifically authorized to create subscriptions.
+ * We don't want to allow unprivileged users to utilize the resources that
+ * a subscription requires (such as a background worker), so require the
+ * user to have been specifically authorized to create subscriptions.
*/
if (!has_privs_of_role(owner, ROLE_PG_CREATE_SUBSCRIPTION))
ereport(ERROR,
@@ -687,6 +687,12 @@ CreateSubscription(ParseState *pstate, CreateSubscriptionStmt *stmt,
if (aclresult != ACLCHECK_OK)
aclcheck_error(aclresult, OBJECT_FOREIGN_SERVER, server->servername);
+ if (!server->forsubscription)
+ ereport(ERROR,
+ (errmsg("foreign server \"%s\" not usable for subscription",
+ server->servername),
+ errhint("Specify FOR SUBSCRIPTION when creating the foreign server.")));
+
um = GetUserMapping(owner, server->serverid);
serverid = server->serverid;
@@ -697,6 +703,19 @@ CreateSubscription(ParseState *pstate, CreateSubscriptionStmt *stmt,
{
Assert(stmt->conninfo);
+ /*
+ * We don't want to allow unprivileged users to be able to trigger
+ * attempts to access arbitrary network destinations, so require the user
+ * to have been specifically authorized to create connections.
+ */
+ if (!has_privs_of_role(owner, ROLE_PG_CREATE_CONNECTION))
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("permission denied to create subscription with a connection string"),
+ errdetail("Only roles with privileges of the \"%s\" role may create subscriptions with CONNECTION specified.",
+ "pg_create_connection"),
+ errhint("Create a subscription to a foreign server by specifying SERVER instead.")));
+
serverid = InvalidOid;
umid = InvalidOid;
conninfo = stmt->conninfo;
diff --git a/src/backend/foreign/foreign.c b/src/backend/foreign/foreign.c
index ba2dea88a9..fc01a4f9c9 100644
--- a/src/backend/foreign/foreign.c
+++ b/src/backend/foreign/foreign.c
@@ -149,6 +149,7 @@ GetForeignServerExtended(Oid serverid, bits16 flags)
server->servername = pstrdup(NameStr(serverform->srvname));
server->owner = serverform->srvowner;
server->fdwid = serverform->srvfdw;
+ server->forsubscription = serverform->srvforsubscription;
/* Extract server type */
datum = SysCacheGetAttr(FOREIGNSERVEROID,
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index c27e0b8b5d..3abcebd8b3 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -366,6 +366,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
%type <str> opt_type
%type <str> foreign_server_version opt_foreign_server_version
+%type <boolean> for_subscription opt_for_subscription
%type <str> opt_in_database
%type <str> parameter_name
@@ -5397,7 +5398,7 @@ generic_option_arg:
*****************************************************************************/
CreateForeignServerStmt: CREATE SERVER name opt_type opt_foreign_server_version
- FOREIGN DATA_P WRAPPER name create_generic_options
+ FOREIGN DATA_P WRAPPER name opt_for_subscription create_generic_options
{
CreateForeignServerStmt *n = makeNode(CreateForeignServerStmt);
@@ -5405,12 +5406,13 @@ CreateForeignServerStmt: CREATE SERVER name opt_type opt_foreign_server_version
n->servertype = $4;
n->version = $5;
n->fdwname = $9;
- n->options = $10;
+ n->forsubscription = $10;
+ n->options = $11;
n->if_not_exists = false;
$$ = (Node *) n;
}
| CREATE SERVER IF_P NOT EXISTS name opt_type opt_foreign_server_version
- FOREIGN DATA_P WRAPPER name create_generic_options
+ FOREIGN DATA_P WRAPPER name opt_for_subscription create_generic_options
{
CreateForeignServerStmt *n = makeNode(CreateForeignServerStmt);
@@ -5418,7 +5420,8 @@ CreateForeignServerStmt: CREATE SERVER name opt_type opt_foreign_server_version
n->servertype = $7;
n->version = $8;
n->fdwname = $12;
- n->options = $13;
+ n->forsubscription = $13;
+ n->options = $14;
n->if_not_exists = true;
$$ = (Node *) n;
}
@@ -5440,6 +5443,16 @@ opt_foreign_server_version:
| /*EMPTY*/ { $$ = NULL; }
;
+for_subscription:
+ FOR SUBSCRIPTION { $$ = true; }
+ | NO SUBSCRIPTION { $$ = false; }
+ ;
+
+opt_for_subscription:
+ for_subscription { $$ = $1; }
+ | /*EMPTY*/ { $$ = false; }
+ ;
+
/*****************************************************************************
*
* QUERY :
@@ -5457,6 +5470,15 @@ AlterForeignServerStmt: ALTER SERVER name foreign_server_version alter_generic_o
n->has_version = true;
$$ = (Node *) n;
}
+ | ALTER SERVER name for_subscription
+ {
+ AlterForeignServerStmt *n = makeNode(AlterForeignServerStmt);
+
+ n->servername = $3;
+ n->forsubscription = $4;
+ n->has_forsubscription = true;
+ $$ = (Node *) n;
+ }
| ALTER SERVER name foreign_server_version
{
AlterForeignServerStmt *n = makeNode(AlterForeignServerStmt);
diff --git a/src/include/catalog/pg_authid.dat b/src/include/catalog/pg_authid.dat
index 82a2ec2862..dcfad7a0c0 100644
--- a/src/include/catalog/pg_authid.dat
+++ b/src/include/catalog/pg_authid.dat
@@ -94,5 +94,10 @@
rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f',
rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1',
rolpassword => '_null_', rolvaliduntil => '_null_' },
+{ oid => '6122', oid_symbol => 'ROLE_PG_CREATE_CONNECTION',
+ rolname => 'pg_create_connection', rolsuper => 'f', rolinherit => 't',
+ rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f',
+ rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1',
+ rolpassword => '_null_', rolvaliduntil => '_null_' },
]
diff --git a/src/include/catalog/pg_foreign_server.h b/src/include/catalog/pg_foreign_server.h
index 995f140bf3..fa1e8fad56 100644
--- a/src/include/catalog/pg_foreign_server.h
+++ b/src/include/catalog/pg_foreign_server.h
@@ -31,6 +31,7 @@ CATALOG(pg_foreign_server,1417,ForeignServerRelationId)
NameData srvname; /* foreign server name */
Oid srvowner BKI_LOOKUP(pg_authid); /* server owner */
Oid srvfdw BKI_LOOKUP(pg_foreign_data_wrapper); /* server FDW */
+ bool srvforsubscription BKI_DEFAULT(f); /* usable for subscription */
#ifdef CATALOG_VARLEN /* variable-length fields start here */
text srvtype;
diff --git a/src/include/foreign/foreign.h b/src/include/foreign/foreign.h
index a2f04ce9af..e1d93c26ba 100644
--- a/src/include/foreign/foreign.h
+++ b/src/include/foreign/foreign.h
@@ -36,6 +36,7 @@ typedef struct ForeignServer
Oid serverid; /* server Oid */
Oid fdwid; /* foreign-data wrapper */
Oid owner; /* server owner user Oid */
+ bool forsubscription; /* usable for a subscription */
char *servername; /* name of the server */
char *servertype; /* server type, optional */
char *serverversion; /* server version, optional */
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 6d6b242cec..00547bbd88 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -2726,6 +2726,7 @@ typedef struct CreateForeignServerStmt
char *version; /* optional server version */
char *fdwname; /* FDW name */
bool if_not_exists; /* just do nothing if it already exists? */
+ bool forsubscription; /* usable for subscription */
List *options; /* generic options to server */
} CreateForeignServerStmt;
@@ -2734,8 +2735,10 @@ typedef struct AlterForeignServerStmt
NodeTag type;
char *servername; /* server name */
char *version; /* optional server version */
+ bool forsubscription; /* usable for subscription */
List *options; /* generic options to server */
bool has_version; /* version specified */
+ bool has_forsubscription; /* [FOR|NO] SUBSCRIPTION specified */
} AlterForeignServerStmt;
/* ----------------------
diff --git a/src/test/regress/expected/subscription.out b/src/test/regress/expected/subscription.out
index 11edae46b1..64e35eaa39 100644
--- a/src/test/regress/expected/subscription.out
+++ b/src/test/regress/expected/subscription.out
@@ -153,17 +153,42 @@ HINT: To initiate replication, you must manually create the replication slot, e
DROP SUBSCRIPTION regress_testsub6;
-- test using a server object instead of connection string
RESET SESSION AUTHORIZATION;
-CREATE SERVER regress_testserver1 FOREIGN DATA WRAPPER pg_connection_fdw;
+CREATE ROLE regress_connection_role;
+CREATE SERVER regress_testserver1 FOREIGN DATA WRAPPER pg_connection_fdw
+ FOR SUBSCRIPTION;
CREATE SERVER regress_testserver2 FOREIGN DATA WRAPPER pg_connection_fdw;
+ALTER SERVER regress_testserver1 OWNER TO regress_connection_role;
+ALTER SERVER regress_testserver2 OWNER TO regress_connection_role;
CREATE USER MAPPING FOR regress_subscription_user3 SERVER regress_testserver1
OPTIONS (password 'secret');
CREATE USER MAPPING FOR regress_subscription_user3 SERVER regress_testserver2
OPTIONS (password 'secret');
GRANT USAGE ON FOREIGN SERVER regress_testserver2 TO regress_subscription_user3;
+-- temporarily revoke pg_create_connection from pg_create_subscription
+-- to test that CREATE SUBSCRIPTION ... CONNECTION fails
+REVOKE pg_create_connection FROM pg_create_subscription;
SET SESSION AUTHORIZATION regress_subscription_user3;
+-- fail - not a member of pg_create_connection, cannot use CONNECTION
+CREATE SUBSCRIPTION regress_testsub6 CONNECTION 'dbname=regress_doesnotexist password=regress_fakepassword' PUBLICATION testpub WITH (slot_name = NONE, connect = false);
+ERROR: permission denied to create subscription with a connection string
+DETAIL: Only roles with privileges of the "pg_create_connection" role may create subscriptions with CONNECTION specified.
+HINT: Create a subscription to a foreign server by specifying SERVER instead.
CREATE SUBSCRIPTION regress_testsub6 SERVER regress_testserver1 PUBLICATION testpub
- WITH (slot_name = NONE, connect = false); -- fails
+ WITH (slot_name = NONE, connect = false); -- fail - no USAGE
ERROR: permission denied for foreign server regress_testserver1
+CREATE SUBSCRIPTION regress_testsub6 SERVER regress_testserver2 PUBLICATION testpub
+ WITH (slot_name = NONE, connect = false); -- fail - not FOR SUBSCRIPTION
+ERROR: foreign server "regress_testserver2" not usable for subscription
+HINT: Specify FOR SUBSCRIPTION when creating the foreign server.
+SET SESSION AUTHORIZATION regress_connection_role;
+ALTER SERVER regress_testserver2 FOR SUBSCRIPTION; -- fails - need pg_create_connection
+ERROR: permission denied to alter server for subscription
+DETAIL: Only roles with privileges of the "pg_create_connection" role may alter foreign servers with FOR SUBSCRIPTION specified.
+RESET SESSION AUTHORIZATION;
+GRANT pg_create_connection TO regress_connection_role;
+SET SESSION AUTHORIZATION regress_connection_role;
+ALTER SERVER regress_testserver2 FOR SUBSCRIPTION;
+SET SESSION AUTHORIZATION regress_subscription_user3;
CREATE SUBSCRIPTION regress_testsub6 SERVER regress_testserver2 PUBLICATION testpub
WITH (slot_name = NONE, connect = false);
WARNING: subscription was created, but is not connected
@@ -195,6 +220,8 @@ DROP SUBSCRIPTION regress_testsub6;
DROP USER MAPPING FOR regress_subscription_user3 SERVER regress_testserver1;
DROP SERVER regress_testserver1;
REVOKE CREATE ON DATABASE regression FROM regress_subscription_user3;
+-- re-grant pg_create_connection to pg_create_subscription
+GRANT pg_create_connection TO pg_create_subscription;
SET SESSION AUTHORIZATION regress_subscription_user;
\dRs+
List of subscriptions
diff --git a/src/test/regress/sql/subscription.sql b/src/test/regress/sql/subscription.sql
index bf8421fb50..0439b5a2fe 100644
--- a/src/test/regress/sql/subscription.sql
+++ b/src/test/regress/sql/subscription.sql
@@ -98,17 +98,41 @@ DROP SUBSCRIPTION regress_testsub6;
-- test using a server object instead of connection string
RESET SESSION AUTHORIZATION;
-CREATE SERVER regress_testserver1 FOREIGN DATA WRAPPER pg_connection_fdw;
+CREATE ROLE regress_connection_role;
+CREATE SERVER regress_testserver1 FOREIGN DATA WRAPPER pg_connection_fdw
+ FOR SUBSCRIPTION;
CREATE SERVER regress_testserver2 FOREIGN DATA WRAPPER pg_connection_fdw;
+ALTER SERVER regress_testserver1 OWNER TO regress_connection_role;
+ALTER SERVER regress_testserver2 OWNER TO regress_connection_role;
CREATE USER MAPPING FOR regress_subscription_user3 SERVER regress_testserver1
OPTIONS (password 'secret');
CREATE USER MAPPING FOR regress_subscription_user3 SERVER regress_testserver2
OPTIONS (password 'secret');
GRANT USAGE ON FOREIGN SERVER regress_testserver2 TO regress_subscription_user3;
+-- temporarily revoke pg_create_connection from pg_create_subscription
+-- to test that CREATE SUBSCRIPTION ... CONNECTION fails
+REVOKE pg_create_connection FROM pg_create_subscription;
+
SET SESSION AUTHORIZATION regress_subscription_user3;
+
+-- fail - not a member of pg_create_connection, cannot use CONNECTION
+CREATE SUBSCRIPTION regress_testsub6 CONNECTION 'dbname=regress_doesnotexist password=regress_fakepassword' PUBLICATION testpub WITH (slot_name = NONE, connect = false);
+
CREATE SUBSCRIPTION regress_testsub6 SERVER regress_testserver1 PUBLICATION testpub
- WITH (slot_name = NONE, connect = false); -- fails
+ WITH (slot_name = NONE, connect = false); -- fail - no USAGE
+CREATE SUBSCRIPTION regress_testsub6 SERVER regress_testserver2 PUBLICATION testpub
+ WITH (slot_name = NONE, connect = false); -- fail - not FOR SUBSCRIPTION
+
+SET SESSION AUTHORIZATION regress_connection_role;
+ALTER SERVER regress_testserver2 FOR SUBSCRIPTION; -- fails - need pg_create_connection
+RESET SESSION AUTHORIZATION;
+GRANT pg_create_connection TO regress_connection_role;
+SET SESSION AUTHORIZATION regress_connection_role;
+ALTER SERVER regress_testserver2 FOR SUBSCRIPTION;
+
+SET SESSION AUTHORIZATION regress_subscription_user3;
+
CREATE SUBSCRIPTION regress_testsub6 SERVER regress_testserver2 PUBLICATION testpub
WITH (slot_name = NONE, connect = false);
RESET SESSION AUTHORIZATION;
@@ -142,6 +166,10 @@ DROP SUBSCRIPTION regress_testsub6;
DROP USER MAPPING FOR regress_subscription_user3 SERVER regress_testserver1;
DROP SERVER regress_testserver1;
REVOKE CREATE ON DATABASE regression FROM regress_subscription_user3;
+
+-- re-grant pg_create_connection to pg_create_subscription
+GRANT pg_create_connection TO pg_create_subscription;
+
SET SESSION AUTHORIZATION regress_subscription_user;
\dRs+
diff --git a/src/test/subscription/t/001_rep_changes.pl b/src/test/subscription/t/001_rep_changes.pl
index 4cbf2dceaa..91a7f9695b 100644
--- a/src/test/subscription/t/001_rep_changes.pl
+++ b/src/test/subscription/t/001_rep_changes.pl
@@ -116,7 +116,7 @@ $node_subscriber->safe_psql('postgres',
my $publisher_host = $node_publisher->host;
my $publisher_port = $node_publisher->port;
$node_subscriber->safe_psql('postgres',
- "CREATE SERVER tap_sub2_server FOREIGN DATA WRAPPER pg_connection_fdw OPTIONS (host '$publisher_host', port '$publisher_port', dbname 'postgres')"
+ "CREATE SERVER tap_sub2_server FOREIGN DATA WRAPPER pg_connection_fdw FOR SUBSCRIPTION OPTIONS (host '$publisher_host', port '$publisher_port', dbname 'postgres')"
);
$node_subscriber->safe_psql('postgres',
--
2.34.1
view thread (45+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: [17] CREATE SUBSCRIPTION ... SERVER
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox