public inbox for [email protected]
help / color / mirror / Atom feedFrom: Jeff Davis <[email protected]>
To: Bharath Rupireddy <[email protected]>
Cc: Ashutosh Bapat <[email protected]>
Cc: Joe Conway <[email protected]>
Cc: [email protected]
Subject: Re: [17] CREATE SUBSCRIPTION ... SERVER
Date: Thu, 04 Jan 2024 16:56:11 -0800
Message-ID: <[email protected]> (raw)
In-Reply-To: <CALj2ACXDua2Az15Kj3OZFaRm49G8-faemiEEv8t9GNCcsxv8Hw@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>
On Tue, 2024-01-02 at 15:14 +0530, Bharath Rupireddy wrote:
> Can the pg_create_connection predefined role related code be put into
> a separate 0001 patch? I think this can go in a separate commit.
Done (see below for details).
> 2. Can one use {FDW, user_mapping, foreign_server} combo other than
> the built-in pg_connection_fdw?
Yes, you can use any FDW for which you have USAGE privileges, passes
the validations, and provides enough of the expected fields to form a
connection string.
There was some discussion on this point already. Initially, I
implemented it with more catalog and grammar support, which improved
error checking, but others objected that the grammar wasn't worth it
and that it was too inflexible. See:
https://www.postgresql.org/message-id/172273.1693403385%40sss.pgh.pa.us
https://www.postgresql.org/message-id/CAExHW5unvpDv6yMSmqurHP7Du1PqoJFWVxeK-4YNm5EnoNJiSQ%40mail.gma...
> If yes, why to allow say oracle_fdw
> foreign server and user mapping with logical replication? Isn't this
> a
> security concern?
A user would need USAGE privileges on that other FDW and also must be a
member of pg_create_subscription.
In v16, a user with such privileges would already be able to create
such connection by specifying the raw connection string, so that's not
a new risk with my proposal.
> 3. I'd like to understand how the permission model works with this
> feature amidst various users a) subscription owner b) table owner c)
> FDW owner d) user mapping owner e) foreign server owner f) superuser
> g) user with which logical replication bg workers (table sync,
> {parallel} apply workers) are started up and running.
(a) The subscription owner is only relevant if the subscription is
created with run_as_owner=true, in which case the logical worker
applies the changes with the privileges of the subscription owner. [No
change.]
(b) The table owner is only relevant if the subscription is created
with run_as_owner=false (default), in which case the logical worker
applies the changes with the privileges of the table owner. [No
change.]
(c) The FDW owner is irrelevant, though the creator of a foreign server
must have USAGE privileges on it. [No change.]
(d) User mappings do not have owners. [No change.]
(e) The foreign server owner is irrelevant, but USAGE privileges on the
foreign server are needed to create a subscription to it. [New
behavior.]
(f) Not sure what you mean here, but superusers can do anything. [No
change.]
(g) The actual user the process runs as is still the subscription
owner. If run_as_owner=false, the actions are performed as the table
owner; if run_as_owner=true, the actions are performed as the
subscription owner. [No change.]
There are only two actual changes to the model:
1. Users with USAGE privileges on a foreign server can create
subscriptions using that foreign server instead of a connection string
(they still need to be a member of pg_create_subscription).
2. I created a conceptual separation of privileges between
pg_create_subscription and pg_create_connection; though by default
pg_create_subscription has exactly the same capabilities as before.
There is no behavior change unless the administrator revokes
pg_create_connection from pg_create_subscription.
I'd like to also add the capability for subscriptions to a server to
use a passwordless connection as long as the server is trusted somehow.
The password_required subscription option is already fairly complex, so
we'd need to come up with a sensible way for those options to interact.
> What if foreign server owner doesn't have permissions on the table
> being applied by logical replication bg workers?
The owner of the foreign server is irrelevant -- only the USAGE
privileges on that foreign server matter, and only when it comes to
creating subscriptions.
> What if foreign server owner is changed with ALTER SERVER ... OWNER
> TO
> when logical replication is in-progress?
That should have no effect as long as the USAGE priv is still present.
Note that if the owner of the *subscription* changes, it may find a
different user mapping.
> What if the owner of {FDW, user_mapping, foreign_server} is
> different
> from a subscription owner with USAGE privilege granted? Can the
> subscription still use the foreign server?
Yes.
> 4. How does the invalidation of {FDW, user_mapping, foreign_server}
> affect associated subscription and vice-versa?
If the user mapping or foreign server change, it causes the apply
worker to re-build the connection string from those objects and restart
if something important changed.
If the FDW changes I don't think that matters.
> 5. What if the password is changed in user mapping with ALTER USER
> MAPPING? Will it refresh the subscription so that all the logical
> replication workers get restarted with new connection info?
Yes. Notice the subscription_change_cb.
That's actually one of the nice features -- if your connection info
changes, update it in one place to affect all subscriptions to that
server.
> 6. How does this feature fit if a subscription is created with
> run_as_owner? Will it check if the table owner has permissions to use
> {FDW, user_mapping, foreign_server} comob?
See above.
> Can we emit an informative error message and hint using
> initClosestMatch, updateClosestMatch, getClosestMatch similar to
> other
> FDWs elsewhere in the code?
Done.
> 8.
> + errmsg("password is required"),
> + errdetail("Non-superusers must provide a
> password in the connection string.")));
>
> The error message and detail look generic, can it be improved to
> include something about pg_connection_fdw?
I believe this is addressed after some refactoring -- the FDW itself
doesn't try to validate that a password exists, because we can't rely
on that anyway (someone can use an FDW with no validation or different
validation). Instead, the subscription does this validation.
Note that there is an unrelated hole in the way the subscription does
the validation of password_required, which will be addressed separately
as a part of this other thread:
https://www.postgresql.org/message-id/e5892973ae2a80a1a3e0266806640dae3c428100.camel%40j-davis.com
> 9.
> +{ oid => '6015', oid_symbol => 'PG_CONNECTION_FDW',
> + descr => 'Pseudo FDW for connections to Postgres',
> + fdwname => 'pg_connection_fdw', fdwowner => 'POSTGRES',
>
> What if the database cluster is initialized with an owner different
> than 'POSTGRES' at the time of initdb? Will the fdwowner be correct
> in
> that case?
Thank you, I changed it to use the conventional BKI_DEFAULT(POSTGRES)
instead. (The previous way worked, but was not consistent with existing
patterns.)
> 10.
> +# src/include/catalog/pg_foreign_data_wrapper.dat
> +{ oid => '6015', oid_symbol => 'PG_CONNECTION_FDW',
>
> Do we want to REVOKE USAGE ON FOREIGN DATA WRAPPER pg_connection_fdw
> FROM PUBLIC
The FDW doesn't have USAGE privileges by default so we don't need to
revoke them.
> and REVOKE EXECUTE ON its handler functions?
It has no handler function.
I don't see a reason to restrict privileges on
postgresql_fdw_validator(); it seems useful for testing/debugging.
> 11. How about splitting patches in the following manner for better
> manageability (all of which can go as separate commits) of this
> feature?
> 0001 for pg_create_connection predefined role per comment #1.
> 0002 for introducing in-built FDW pg_connection_fdw.
> 0003 utilizing in-built FDW for logical replication to provide CREATE
> SUBSCRIPTION ... SERVER.
Good suggestion, though I split it a bit differently:
0001: fix postgresql_fdw_validator to use libpq options via walrcv
method. This is appropriate for looser validation that doesn't try to
check for password_required or that a password is set -- that's left up
to the subscription.
0002: built-in pg_connection_fdw, also includes code for validation and
transforming into a connection string. This creates a lot of test diffs
in foreign_data.out because I need to exclude the built in FDW (it's
owned by the bootstrap supseruser which is not a stable username). It
would be nice if there was a way to use a negative-matching regex in a
psql \dew+ command -- something like "(?!pg_)*" -- but I couldn't find
a way to do that because "(?...)" seems to not work in psql. Let me
know if you know a trick to do so.
0003: CREATE SUBSCRIPTION... SERVER.
0004: Add pg_create_connection role.
Regards,
Jeff Davis
Attachments:
[text/x-patch] v5-0001-Fix-postgresql_fdw_validator-to-use-full-libpq-op.patch (8.5K, 2-v5-0001-Fix-postgresql_fdw_validator-to-use-full-libpq-op.patch)
download | inline diff:
From 5cd100aca49c23357b7fda02187cd59dc6df4da9 Mon Sep 17 00:00:00 2001
From: Jeff Davis <[email protected]>
Date: Thu, 4 Jan 2024 12:15:54 -0800
Subject: [PATCH v5 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 c9748539aa..2c4c440126 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,
@@ -284,6 +286,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] v5-0002-Add-built-in-foreign-data-wrapper-pg_connection_f.patch (124.9K, 3-v5-0002-Add-built-in-foreign-data-wrapper-pg_connection_f.patch)
download | inline diff:
From 7f4426da0e840a515a207d00d36fed8837792702 Mon Sep 17 00:00:00 2001
From: Jeff Davis <[email protected]>
Date: Tue, 2 Jan 2024 13:33:28 -0800
Subject: [PATCH v5 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 cec21e42c0..83a573f8d1 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -27877,6 +27877,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 7979392776..b251775dbb 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] v5-0003-CREATE-SUSBCRIPTION-.-SERVER.patch (42.1K, 4-v5-0003-CREATE-SUSBCRIPTION-.-SERVER.patch)
download | inline diff:
From 805f7ab23fabc11c7dc5367412f24a88a8056cf1 Mon Sep 17 00:00:00 2001
From: Jeff Davis <[email protected]>
Date: Tue, 2 Jan 2024 13:42:48 -0800
Subject: [PATCH v5 3/4] CREATE SUSBCRIPTION ... SERVER.
---
.../postgres_fdw/expected/postgres_fdw.out | 8 +
contrib/postgres_fdw/sql/postgres_fdw.sql | 7 +
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 ++++++
20 files changed, 497 insertions(+), 28 deletions(-)
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index d83f6ae8cb..5ce83bcb91 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -256,6 +256,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);
+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_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/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index 90c8fa4b70..81dac4981c 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -248,6 +248,13 @@ 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);
+DROP SUBSCRIPTION regress_pgfdw_subscription;
+
-- ===================================================================
-- test error case for create publication on foreign table
-- ===================================================================
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 f1c20b3a46..5f2c50a31e 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
[text/x-patch] v5-0004-Introduce-pg_create_connection-predefined-role.patch (8.8K, 5-v5-0004-Introduce-pg_create_connection-predefined-role.patch)
download | inline diff:
From 30a8482ac5a0981a6ebefe6f4a24d2e3b2a01d13 Mon Sep 17 00:00:00 2001
From: Jeff Davis <[email protected]>
Date: Tue, 2 Jan 2024 13:13:54 -0800
Subject: [PATCH v5 4/4] Introduce pg_create_connection predefined role.
In addition to pg_create_subscription, membership in this role is
necessary to create a subscription with a connection string. The
pg_create_subscription role is a member of pg_create_connection, so by
default pg_create_subscription has the same capability as before.
An administrator may revoke pg_create_connection from
pg_create_subscription, which will enable the privileges to be
separated. That will be useful in an upcoming change to introduce
CREATE SUBSCRIPTION ... SERVER, which will not use a raw connection
string, and therefore not require membership in the
pg_create_connection role.
---
doc/src/sgml/user-manag.sgml | 12 ++++++++++--
src/backend/catalog/system_functions.sql | 2 ++
src/backend/commands/subscriptioncmds.c | 19 ++++++++++++++++---
src/include/catalog/pg_authid.dat | 5 +++++
src/test/regress/expected/subscription.out | 11 +++++++++++
src/test/regress/sql/subscription.sql | 14 ++++++++++++++
6 files changed, 58 insertions(+), 5 deletions(-)
diff --git a/doc/src/sgml/user-manag.sgml b/doc/src/sgml/user-manag.sgml
index 92a299d2d3..4f4c20ba3c 100644
--- a/doc/src/sgml/user-manag.sgml
+++ b/doc/src/sgml/user-manag.sgml
@@ -687,11 +687,19 @@ DROP ROLE doomed_role;
<entry>Allow use of connection slots reserved via
<xref linkend="guc-reserved-connections"/>.</entry>
</row>
+ <row>
+ <entry>pg_create_connection</entry>
+ <entry>Allow users to specify a connection string directly in <link
+ linkend="sql-createsubscription"><command>CREATE
+ SUBSCRIPTION</command></link>.</entry>
+ </row>
<row>
<entry>pg_create_subscription</entry>
<entry>Allow users with <literal>CREATE</literal> permission on the
- database to issue
- <link linkend="sql-createsubscription"><command>CREATE SUBSCRIPTION</command></link>.</entry>
+ database to issue <link
+ linkend="sql-createsubscription"><command>CREATE
+ SUBSCRIPTION</command></link>. This role is a member of
+ <literal>pg_create_connection</literal>.</entry>
</row>
</tbody>
</tgroup>
diff --git a/src/backend/catalog/system_functions.sql b/src/backend/catalog/system_functions.sql
index f315fecf18..73512688de 100644
--- a/src/backend/catalog/system_functions.sql
+++ b/src/backend/catalog/system_functions.sql
@@ -781,3 +781,5 @@ GRANT pg_read_all_settings TO pg_monitor;
GRANT pg_read_all_stats TO pg_monitor;
GRANT pg_stat_scan_tables TO pg_monitor;
+
+GRANT pg_create_connection TO pg_create_subscription;
diff --git a/src/backend/commands/subscriptioncmds.c b/src/backend/commands/subscriptioncmds.c
index 60287c73e7..03555d5159 100644
--- a/src/backend/commands/subscriptioncmds.c
+++ b/src/backend/commands/subscriptioncmds.c
@@ -609,9 +609,9 @@ CreateSubscription(ParseState *pstate, CreateSubscriptionStmt *stmt,
PreventInTransactionBlock(isTopLevel, "CREATE SUBSCRIPTION ... WITH (create_slot = true)");
/*
- * We don't want to allow unprivileged users to be able to trigger
- * attempts to access arbitrary network destinations, so require the user
- * to have been specifically authorized to create subscriptions.
+ * We don't want to allow unprivileged users to utilize the resources that
+ * a subscription requires (such as a background worker), so require the
+ * user to have been specifically authorized to create subscriptions.
*/
if (!has_privs_of_role(owner, ROLE_PG_CREATE_SUBSCRIPTION))
ereport(ERROR,
@@ -697,6 +697,19 @@ CreateSubscription(ParseState *pstate, CreateSubscriptionStmt *stmt,
{
Assert(stmt->conninfo);
+ /*
+ * We don't want to allow unprivileged users to be able to trigger
+ * attempts to access arbitrary network destinations, so require the user
+ * to have been specifically authorized to create connections.
+ */
+ if (!has_privs_of_role(owner, ROLE_PG_CREATE_CONNECTION))
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("permission denied to create subscription with a connection string"),
+ errdetail("Only roles with privileges of the \"%s\" role may create subscriptions with CONNECTION specified.",
+ "pg_create_connection"),
+ errhint("Create a subscription to a foreign server by specifying SERVER instead.")));
+
serverid = InvalidOid;
umid = InvalidOid;
conninfo = stmt->conninfo;
diff --git a/src/include/catalog/pg_authid.dat b/src/include/catalog/pg_authid.dat
index 82a2ec2862..dcfad7a0c0 100644
--- a/src/include/catalog/pg_authid.dat
+++ b/src/include/catalog/pg_authid.dat
@@ -94,5 +94,10 @@
rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f',
rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1',
rolpassword => '_null_', rolvaliduntil => '_null_' },
+{ oid => '6122', oid_symbol => 'ROLE_PG_CREATE_CONNECTION',
+ rolname => 'pg_create_connection', rolsuper => 'f', rolinherit => 't',
+ rolcreaterole => 'f', rolcreatedb => 'f', rolcanlogin => 'f',
+ rolreplication => 'f', rolbypassrls => 'f', rolconnlimit => '-1',
+ rolpassword => '_null_', rolvaliduntil => '_null_' },
]
diff --git a/src/test/regress/expected/subscription.out b/src/test/regress/expected/subscription.out
index 7b866a6fe6..2737901751 100644
--- a/src/test/regress/expected/subscription.out
+++ b/src/test/regress/expected/subscription.out
@@ -157,7 +157,16 @@ 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;
+-- temporarily revoke pg_create_connection from pg_create_subscription
+-- to test that CREATE SUBSCRIPTION ... CONNECTION fails
+REVOKE pg_create_connection FROM pg_create_subscription;
SET SESSION AUTHORIZATION regress_subscription_user3;
+-- fail - not a member of pg_create_connection, cannot use CONNECTION
+CREATE SUBSCRIPTION regress_testsub6 CONNECTION 'dbname=regress_doesnotexist password=regress_fakepassword' PUBLICATION testpub WITH (slot_name = NONE, connect = false);
+ERROR: permission denied to create subscription with a connection string
+DETAIL: Only roles with privileges of the "pg_create_connection" role may create subscriptions with CONNECTION specified.
+HINT: Create a subscription to a foreign server by specifying SERVER instead.
+-- succeed - subscription to foreign server
CREATE SUBSCRIPTION regress_testsub6 SERVER regress_testserver PUBLICATION testpub
WITH (slot_name = NONE, connect = false);
WARNING: subscription was created, but is not connected
@@ -183,6 +192,8 @@ 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;
+-- re-grant pg_create_connection to pg_create_subscription
+GRANT pg_create_connection TO pg_create_subscription;
SET SESSION AUTHORIZATION regress_subscription_user;
\dRs+
List of subscriptions
diff --git a/src/test/regress/sql/subscription.sql b/src/test/regress/sql/subscription.sql
index 95c826030b..b041a6d542 100644
--- a/src/test/regress/sql/subscription.sql
+++ b/src/test/regress/sql/subscription.sql
@@ -103,9 +103,19 @@ CREATE USER MAPPING FOR regress_subscription_user3 SERVER regress_testserver
OPTIONS (password 'secret');
GRANT USAGE ON FOREIGN SERVER regress_testserver TO regress_subscription_user3;
+-- temporarily revoke pg_create_connection from pg_create_subscription
+-- to test that CREATE SUBSCRIPTION ... CONNECTION fails
+REVOKE pg_create_connection FROM pg_create_subscription;
+
SET SESSION AUTHORIZATION regress_subscription_user3;
+
+-- fail - not a member of pg_create_connection, cannot use CONNECTION
+CREATE SUBSCRIPTION regress_testsub6 CONNECTION 'dbname=regress_doesnotexist password=regress_fakepassword' PUBLICATION testpub WITH (slot_name = NONE, connect = false);
+
+-- succeed - subscription to foreign server
CREATE SUBSCRIPTION regress_testsub6 SERVER regress_testserver PUBLICATION testpub
WITH (slot_name = NONE, connect = false);
+
RESET SESSION AUTHORIZATION;
-- test an FDW with no validator
@@ -131,6 +141,10 @@ 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;
+
+-- re-grant pg_create_connection to pg_create_subscription
+GRANT pg_create_connection TO pg_create_subscription;
+
SET SESSION AUTHORIZATION regress_subscription_user;
\dRs+
--
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