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: Fri, 12 Jan 2024 17:17:26 -0800
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAExHW5vF+cp9efQj=-W+Rhbr9f9ZZqt3XFnP0NB5jKsm=C=5tw@mail.gmail.com>
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>
On Fri, 2024-01-05 at 16:11 +0530, Ashutosh Bapat wrote:
> I don't think we need to add a test for every FDW. E.g. adding a test
> in file_fdw would be pointless. But postgres_fdw is special. The test
> could further create a foreign table ftab_foo on subscriber
> referencing foo on publisher and then compare the data from foo and
> ftab_foo to make sure that the replication is happening. This will
> serve as a good starting point for replicated tables setup in a
> sharded cluster.
>
Attached updated patch set with added TAP test for postgres_fdw, which
uses a postgres_fdw server as the source for subscription connection
information.
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.
Regards,
Jeff Davis
Attachments:
[text/x-patch] v6-0001-Fix-postgresql_fdw_validator-to-use-full-libpq-op.patch (8.5K, 2-v6-0001-Fix-postgresql_fdw_validator-to-use-full-libpq-op.patch)
download | inline diff:
From dbe3d132165ff3379572f11186d8d7b38cead675 Mon Sep 17 00:00:00 2001
From: Jeff Davis <[email protected]>
Date: Thu, 4 Jan 2024 12:15:54 -0800
Subject: [PATCH v6 1/4] Fix postgresql_fdw_validator to use full libpq options
list.
Extend the walrcv_ API to retrieve the options list from libpq, and
use that for postgresql_fdw_validator(). Un-deprecate it.
---
src/backend/foreign/foreign.c | 67 +++++++------------
.../libpqwalreceiver/libpqwalreceiver.c | 47 +++++++++++++
src/include/replication/walreceiver.h | 20 ++++++
3 files changed, 90 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 ead30f87c9..ff7d071903 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,
@@ -293,6 +295,51 @@ 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;
+ struct ConnectionOption *popt;
+ PQconninfoOption *conndefaults;
+ PQconninfoOption *lopt;
+ int num_libpq_opts = 0;
+
+ if (connection_options)
+ return connection_options;
+
+ conndefaults = PQconndefaults();
+ for (lopt = conndefaults; lopt->keyword; lopt++)
+ num_libpq_opts++;
+
+ connection_options = MemoryContextAlloc(
+ TopMemoryContext,
+ sizeof(struct ConnectionOption) * (num_libpq_opts + 1));
+
+ popt = connection_options;
+ for (lopt = conndefaults; lopt->keyword; lopt++)
+ {
+ popt->issecret = false;
+ popt->isdebug = false;
+
+ if (strchr(lopt->dispchar, '*'))
+ popt->issecret = true;
+ else if (strchr(lopt->dispchar, 'D'))
+ popt->isdebug = true;
+
+ popt->optname = MemoryContextStrdup(TopMemoryContext,
+ lopt->keyword);
+ popt++;
+ }
+
+ popt->optname = NULL;
+ popt->issecret = false;
+ popt->isdebug = false;
+
+ PQconninfoFree(conndefaults);
+
+ 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] v6-0002-Add-built-in-foreign-data-wrapper-pg_connection_f.patch (124.9K, 3-v6-0002-Add-built-in-foreign-data-wrapper-pg_connection_f.patch)
download | inline diff:
From 462757fb573b1982e6ae4633e8496a44999152c3 Mon Sep 17 00:00:00 2001
From: Jeff Davis <[email protected]>
Date: Tue, 2 Jan 2024 13:33:28 -0800
Subject: [PATCH v6 2/4] Add built-in foreign data wrapper pg_connection_fdw.
In preparation for CREATE SUBSCRIPTION ... SERVER.
---
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 0f7d409e60..d58b499966 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 options for the 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 connections from the server.
+ </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..fd1b644d72 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.
+ */
+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;
+ bool first = true;
+
+ 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 = ",
+ first ? "" : " ", name);
+ appendEscapedValue(&str, value);
+ first = false;
+ }
+
+ /* override client_encoding */
+ if (append_overrides)
+ {
+ appendStringInfo(&str, "%sclient_encoding = ",
+ first ? "" : " ");
+ appendEscapedValue(&str, GetDatabaseEncodingName());
+ }
+
+ 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);
+
+ /* copy only conninfo into the current context */
+ MemoryContextSwitchTo(oldcontext);
+
+ 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 22d1e6cf92..7b9c79005e 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
@@ -9732,7 +9753,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..d51e83ff92 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 'secret2');
+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 = 'secret2'
+(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..43461de7f9 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 'secret2');
+
+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] v6-0003-CREATE-SUSBCRIPTION-.-SERVER.patch (47.2K, 4-v6-0003-CREATE-SUSBCRIPTION-.-SERVER.patch)
download | inline diff:
From 4663f28197b2fda75bb3f99c8447853cd1af159f Mon Sep 17 00:00:00 2001
From: Jeff Davis <[email protected]>
Date: Tue, 2 Jan 2024 13:42:48 -0800
Subject: [PATCH v6 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 | 31 ++-
src/backend/commands/subscriptioncmds.c | 182 ++++++++++++++++--
src/backend/foreign/foreign.c | 25 +++
src/backend/parser/gram.y | 20 ++
src/backend/replication/logical/worker.c | 12 +-
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 | 4 +-
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 | 40 ++++
src/test/regress/sql/foreign_data.sql | 17 ++
src/test/regress/sql/subscription.sql | 45 +++++
src/test/subscription/t/001_rep_changes.pl | 57 ++++++
23 files changed, 579 insertions(+), 28 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..b3cbc170d5 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"
@@ -75,10 +78,30 @@ 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 */
+ aclresult = object_aclcheck(ForeignServerRelationId,
+ subform->subserver,
+ subform->subowner, ACL_USAGE);
+
+ if (aclresult != ACLCHECK_OK)
+ aclcheck_error(aclresult, OBJECT_FOREIGN_SERVER,
+ 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..60287c73e7 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;
@@ -1124,6 +1172,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 +1294,89 @@ 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)
+ aclcheck_error(aclresult, OBJECT_FOREIGN_SERVER,
+ new_server->servername);
+
+ 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 +1587,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 +1671,25 @@ 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)
+ aclcheck_error(aclresult, OBJECT_FOREIGN_SERVER,
+ 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 +1790,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 +2000,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 fd1b644d72..d5d78c347e 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 6b88096e8e..0a024ab637 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -10648,6 +10648,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;
+ }
;
/*****************************************************************************
@@ -10677,6 +10687,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..4cb94a1ee8 100644
--- a/src/backend/replication/logical/worker.c
+++ b/src/backend/replication/logical/worker.c
@@ -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)
@@ -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 7b9c79005e..6456416ad3 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 9a34347cfc..d3aaa26861 100644
--- a/src/bin/pg_dump/pg_dump.h
+++ b/src/bin/pg_dump/pg_dump.h
@@ -669,6 +669,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..74e904b11d 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;
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 d51e83ff92..6685c134c6 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..7b866a6fe6 100644
--- a/src/test/regress/expected/subscription.out
+++ b/src/test/regress/expected/subscription.out
@@ -144,6 +144,46 @@ 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_testserver FOREIGN DATA WRAPPER pg_connection_fdw;
+CREATE USER MAPPING FOR regress_subscription_user3 SERVER regress_testserver
+ OPTIONS (password 'secret');
+GRANT USAGE ON FOREIGN SERVER regress_testserver TO regress_subscription_user3;
+SET SESSION AUTHORIZATION regress_subscription_user3;
+CREATE SUBSCRIPTION regress_testsub6 SERVER regress_testserver 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;
+-- test an FDW with no validator
+CREATE FOREIGN DATA WRAPPER regress_fdw;
+CREATE SERVER regress_testserver2 FOREIGN DATA WRAPPER regress_fdw
+ OPTIONS (abc 'xyz');
+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;
+ALTER SUBSCRIPTION regress_testsub6 SERVER regress_testserver2;
+ALTER SUBSCRIPTION regress_testsub6 SERVER regress_testserver;
+RESET SESSION AUTHORIZATION;
+DROP USER MAPPING FOR regress_subscription_user3 SERVER regress_testserver2;
+DROP SERVER regress_testserver2;
+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_testserver"
+DROP SUBSCRIPTION regress_testsub6;
+DROP USER MAPPING FOR regress_subscription_user3 SERVER regress_testserver;
+DROP SERVER regress_testserver;
+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 43461de7f9..337acafc2d 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..95c826030b 100644
--- a/src/test/regress/sql/subscription.sql
+++ b/src/test/regress/sql/subscription.sql
@@ -88,6 +88,51 @@ 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_testserver FOREIGN DATA WRAPPER pg_connection_fdw;
+CREATE USER MAPPING FOR regress_subscription_user3 SERVER regress_testserver
+ OPTIONS (password 'secret');
+GRANT USAGE ON FOREIGN SERVER regress_testserver TO regress_subscription_user3;
+
+SET SESSION AUTHORIZATION regress_subscription_user3;
+CREATE SUBSCRIPTION regress_testsub6 SERVER regress_testserver PUBLICATION testpub
+ WITH (slot_name = NONE, connect = false);
+RESET SESSION AUTHORIZATION;
+
+-- test an FDW with no validator
+CREATE FOREIGN DATA WRAPPER regress_fdw;
+CREATE SERVER regress_testserver2 FOREIGN DATA WRAPPER regress_fdw
+ OPTIONS (abc 'xyz');
+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;
+ALTER SUBSCRIPTION regress_testsub6 SERVER regress_testserver2;
+ALTER SUBSCRIPTION regress_testsub6 SERVER regress_testserver;
+
+RESET SESSION AUTHORIZATION;
+DROP USER MAPPING FOR regress_subscription_user3 SERVER regress_testserver2;
+DROP SERVER regress_testserver2;
+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_testserver;
+DROP SERVER regress_testserver;
+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
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