[17] CREATE SUBSCRIPTION ... SERVER

Synopsis:

Publisher:

CREATE TABLE x(i INT);
CREATE TABLE y(i INT);
INSERT INTO x VALUES(1);
INSERT INTO y VALUES(-1);
CREATE PUBLICATION pub1 FOR TABLE x;
CREATE PUBLICATION pub2 FOR TABLE y;

Subscriber:

CREATE SERVER myserver FOR CONNECTION ONLY OPTIONS (
host '...', dbname '...'
);
CREATE USER MAPPING FOR PUBLIC SERVER myserver OPTIONS (
user '...', password '...'
);

CREATE TABLE x(i INT);
CREATE TABLE y(i INT);
CREATE SUBSCRIPTION sub1 SERVER myserver PUBLICATION pub1;
CREATE SUBSCRIPTION sub2 SERVER myserver PUBLICATION pub2;

Motivation:

* Allow managing connections separately from managing the
subscriptions themselves. For instance, if you update an
authentication method or the location of the publisher, updating
the server alone will update all subscriptions at once.
* Enable separating the privileges to create a subscription from the
privileges to create a connection string. (By default
pg_create_subscription has both privileges for compatibility with
v16, but the connection privilege can be revoked from
pg_create_subscription, see below.)
* Enable changing of single connection parameters without pasting
the rest of the connection string as well. E.g. "ALTER SERVER
... OPTIONS (SET ... '...');".
* Benefit from user mappings and ACLs on foreign server object if
you have multiple roles creating subscriptions.

Details:

The attached patch implements "CREATE SUBSCRIPTION ... SERVER myserver"
as an alternative to "CREATE SUBSCRIPTION ... CONNECTION '...'". The
user must be a member of pg_create_subscription and have USAGE
privileges on the server.

The server "myserver" must have been created with the new syntax:

CREATE SERVER myserver FOR CONNECTION ONLY

instead of specifying FOREIGN DATA WRAPPER. In other words, a server
FOR CONNECTION ONLY doesn't have a real FDW, it's a special server just
used for the postgres connection options. To create a server FOR
CONNECTION ONLY, the user must be a member of the new predefined role
pg_create_connection. A server FOR CONNECTION ONLY still uses ACLs and
user mappings the same way as other foreign servers, but cannot be used
to create foreign tables.

The predefined role pg_create_subscription is also a member of the role
pg_create_connection, so that existing members of the
pg_create_subscription role may continue to create subscriptions using
CONNECTION just like in v16 without any additional grant.

Security:

One motivation of this patch is to enable separating the privileges to
create a subscription from the privileges to create a connection
string, because each have their own security implications and may be
done through separate processes. To separate the privileges, simply
revoke pg_create_connection from pg_create_subscription; then you can
grant each one independently as you see fit.

For instance, there may be an administrator that controls what
postgres instances are available, and what connections may be
reasonable between those instances. That admin will need the
pg_create_connection role, and can proactively create all the servers
(using FOR CONNECTION ONLY) and user mappings that may be useful, and
manage and update those as necessary without breaking
subscriptions. Another role may be used to manage the subscriptions
themselves, and they would need to be a member of
pg_create_subscription but do not need the privileges to create raw
connection strings.

Note: the ability to revoke pg_create_connection from
pg_create_subscription avoids some risks in some environments; but
creating a subcription should still be considered a highly privileged
operation whether using SERVER or CONNECTION.

Remaining work:

The code for options handling needs some work. It's similar to
postgres_fdw in behavior, but I didn't spend as much time on it because
I suspect we will want to refactor the various ways connection strings
are handled (in CREATE SUBSCRIPTION ... CONNECTION, postgres_fdw, and
dblink) to make them more consistent.

Also, there are some nuances in handling connection options that I
don't fully understand. postgres_fdw makes a lot of effort: it
overrides client_encoding, it does a
post-connection security check, and allows GSS instead of a password
option for non-superusers. But CREATE SUBSCRIPTION ... CONNECTION makes
little effort, only checking whether the password is specified or not.
I'd like to understand why they are different and what we can unify.

Also, right now dblink has it's own dblink_fdw, and perhaps a server
FOR CONNECTION ONLY should become the preferred method instead.

--
Jeff Davis
PostgreSQL Contributor Team - AWS