[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
Attachments:
v1-0001-CREATE-SUBSCRIPTION-.-SERVER.patchtext/x-patch; charset=UTF-8; name=v1-0001-CREATE-SUBSCRIPTION-.-SERVER.patchDownload+1139-120
Hi Jeff,
On Wed, Aug 30, 2023 at 2:12 PM Jeff Davis <pgsql@j-davis.com> wrote:
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.
Are you suggesting that SERVERs created with FDW can not be used as
publishers? I think there's value in knowing that the publisher which
contains a replica of a table is the same as the foreign server which
is referenced by another foreign table. We can push down a join
between a replicated table and foreign table down to the foreign
server. A basic need for sharding with replicated tables. Of course
there's a lot work that we have to do in order to actually achieve
such a push down but by restricting this feature to only CONNECTION
ONLY, we are restricting the possibility of such a push down.
--
Best Wishes,
Ashutosh Bapat
Jeff Davis <pgsql@j-davis.com> writes:
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.
This seems like it requires a whole lot of new mechanism (parser
and catalog infrastructure) that could be done far more easily
in other ways. In particular, how about inventing a built-in
dummy FDW to serve the purpose? That could have some use for
other testing as well.
regards, tom lane
On Wed, 2023-08-30 at 19:11 +0530, Ashutosh Bapat wrote:
Are you suggesting that SERVERs created with FDW can not be used as
publishers?
Correct. Without that, how would the subscription know that the FDW
contains valid postgres connection information? I suppose it could
create a connection string out of the options itself and do another
round of validation, is that what you had in mind?
We can push down a join
between a replicated table and foreign table down to the foreign
server.
Interesting idea.
Regards,
Jeff Davis
On Wed, 2023-08-30 at 09:49 -0400, Tom Lane wrote:
This seems like it requires a whole lot of new mechanism (parser
and catalog infrastructure) that could be done far more easily
in other ways. In particular, how about inventing a built-in
dummy FDW to serve the purpose?
That was my initial approach, but it was getting a bit messy.
FDWs don't have a schema, so we can't put it in pg_catalog, and names
beginning with "pg_" aren't restricted now. Should I retroactively
restrict FDW names that begin with "pg_"? Or just use special cases in
pg_dump and elsewhere? Also I didn't see a great place to document it.
Admittedly, I didn't complete the dummy-FDW approach, so perhaps it
works out better overall. I can give it a try.
Regards,
Jeff Davis
On Wed, Aug 30, 2023 at 9:00 PM Jeff Davis <pgsql@j-davis.com> wrote:
On Wed, 2023-08-30 at 19:11 +0530, Ashutosh Bapat wrote:
Are you suggesting that SERVERs created with FDW can not be used as
publishers?Correct. Without that, how would the subscription know that the FDW
contains valid postgres connection information? I suppose it could
create a connection string out of the options itself and do another
round of validation, is that what you had in mind?
The server's FDW has to be postgres_fdw. So we have to handle the
awkward dependency between core and postgres_fdw (an extension). The
connection string should be created from options itself. A special
user mapping for replication may be used. That's how I see it at a
high level.
--
Best Wishes,
Ashutosh Bapat
On Wed, Aug 30, 2023 at 1:19 PM Jeff Davis <pgsql@j-davis.com> wrote:
On Wed, 2023-08-30 at 09:49 -0400, Tom Lane wrote:
This seems like it requires a whole lot of new mechanism (parser
and catalog infrastructure) that could be done far more easily
in other ways. In particular, how about inventing a built-in
dummy FDW to serve the purpose?That was my initial approach, but it was getting a bit messy.
FDWs don't have a schema, so we can't put it in pg_catalog, and names
beginning with "pg_" aren't restricted now. Should I retroactively
restrict FDW names that begin with "pg_"? Or just use special cases in
pg_dump and elsewhere? Also I didn't see a great place to document it.Admittedly, I didn't complete the dummy-FDW approach, so perhaps it
works out better overall. I can give it a try.
What I feel is kind of weird about this syntax is that it seems like
it's entangled with the FDW mechanism but doesn't really overlap with
it. You could have something that is completely separate (CREATE
SUBSCRIPTION CONNECTION) or something that truly does have some
overlap (no new syntax and a dummy fdw, as Tom proposes, or somehow
knowing that postgres_fdw is special, as Ashutosh proposes). But this
seems like sort of an odd middle ground.
I also think that the decision to make pg_create_connection a member
of pg_create_subscription by default, but encouraging users to think
about revoking it, is kind of strange. I don't think we really want to
encourage users to tinker with predefined roles in this kind of way. I
think there are better ways of achieving the goals here.
--
Robert Haas
EDB: http://www.enterprisedb.com
On Wed, 2023-08-30 at 09:09 -0700, Jeff Davis wrote:
Admittedly, I didn't complete the dummy-FDW approach, so perhaps it
works out better overall. I can give it a try.
We need to hide the dummy FDW from pg_dump. And we need to hide it from
psql's \dew, because that's used in tests and prints the owner's name,
and the bootstrap superuser doesn't have a consistent name. But I
didn't find a good way to hide it because it doesn't have a schema.
The best I could come up with is special-casing by the name, but that
seems like a pretty bad hack. For other built-in objects, psql is
willing to print them out if you just specify something like "\dT
pg_catalog.*", but that wouldn't work here. We could maybe do something
based on the "pg_" prefix, but we'd have to retroactively restrict FDWs
with that prefix, which sounds like a bad idea.
Suggestions?
Regards,
Jeff Davis
On Thu, 2023-08-31 at 10:59 +0530, Ashutosh Bapat wrote:
The server's FDW has to be postgres_fdw. So we have to handle the
awkward dependency between core and postgres_fdw (an extension).
That sounds more than just "awkward". I can't think of any precedent
for that and it seems to violate the idea of an "extension" entirely.
Can you explain more concretely how we might resolve that?
Regards,
Jeff Davis
On Thu, 2023-08-31 at 08:37 -0400, Robert Haas wrote:
What I feel is kind of weird about this syntax is that it seems like
it's entangled with the FDW mechanism but doesn't really overlap with
it.
I like the fact that it works with user mappings and benefits from the
other thinking that's gone into that system. I would call that a
"feature" not an "entanglement".
You could have something that is completely separate (CREATE
SUBSCRIPTION CONNECTION)
I thought about that but it would be a new object type with a new
catalog and I didn't really see an upside. It would open up questions
about permissions, raw string vs individual options, whether we need
user mappings or not, etc., and those have all been worked out already
with foreign servers.
or something that truly does have some
overlap (no new syntax and a dummy fdw, as Tom proposes, or somehow
knowing that postgres_fdw is special, as Ashutosh proposes).
I ran into a (perhaps very minor?) challenge[1] with the dummy FDW:
/messages/by-id/c47e8ba923bf0a13671f7d8230a81d465c21fb04.camel@j-davis.com
suggestions welcome there, of course.
Regarding core code depending on postgres_fdw: how would that work?
Would that be acceptable?
But this
seems like sort of an odd middle ground.
I assume here that you're talking about the CREATE SERVER ... FOR
CONNECTION ONLY syntax. I don't think it's odd. We have lots of objects
that are a lot like another object but treated differently for various
reasons. A foreign table is an obvious example.
I also think that the decision to make pg_create_connection a member
of pg_create_subscription by default, but encouraging users to think
about revoking it, is kind of strange. I don't think we really want
to
encourage users to tinker with predefined roles in this kind of way.
I
think there are better ways of achieving the goals here.
Such as?
Regards,
Jeff Davis
On 8/31/23 12:52, Jeff Davis wrote:
On Thu, 2023-08-31 at 10:59 +0530, Ashutosh Bapat wrote:
The server's FDW has to be postgres_fdw. So we have to handle the
awkward dependency between core and postgres_fdw (an extension).That sounds more than just "awkward". I can't think of any precedent
for that and it seems to violate the idea of an "extension" entirely.Can you explain more concretely how we might resolve that?
Maybe move postgres_fdw to be a first class built in feature instead of
an extension?
--
Joe Conway
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com
On Fri, Sep 1, 2023 at 2:47 AM Joe Conway <mail@joeconway.com> wrote:
On 8/31/23 12:52, Jeff Davis wrote:
On Thu, 2023-08-31 at 10:59 +0530, Ashutosh Bapat wrote:
The server's FDW has to be postgres_fdw. So we have to handle the
awkward dependency between core and postgres_fdw (an extension).That sounds more than just "awkward". I can't think of any precedent
for that and it seems to violate the idea of an "extension" entirely.Can you explain more concretely how we might resolve that?
Maybe move postgres_fdw to be a first class built in feature instead of
an extension?
Yes, that's one way.
Thinking larger, how about we allow any FDW to be used here. We might
as well, allow extensions to start logical receivers which accept
changes from non-PostgreSQL databases. So we don't have to make an
exception for postgres_fdw. But I think there's some value in bringing
together these two subsystems which deal with foreign data logically
(as in logical vs physical view of data).
--
Best Wishes,
Ashutosh Bapat
On Fri, 2023-09-01 at 12:28 +0530, Ashutosh Bapat wrote:
Thinking larger, how about we allow any FDW to be used here.
That's a possibility, but I think that means the subscription would
need to constantly re-check the parameters rather than relying on the
FDW's validator.
Otherwise it might be the wrong kind of FDW, and the user might be able
to circumvent the password_required protection. It might not even be a
postgres-related FDW at all, which would be a bit strange.
If it's constantly re-checking the parameters then it raises the
possibility that some "ALTER SERVER" or "ALTER USER MAPPING" succeeds
but then subscriptions to that foreign server start failing, which
would not be ideal. But I could be fine with that.
But I think there's some value in bringing
together these two subsystems which deal with foreign data logically
(as in logical vs physical view of data).
I still don't understand how a core dependency on an extension would
work.
Regards,
Jeff Davis
On Thu, 2023-08-31 at 17:17 -0400, Joe Conway wrote:
Maybe move postgres_fdw to be a first class built in feature instead
of
an extension?
That could make sense, but we still have to solve the problem of how to
present a built-in FDW.
FDWs don't have a schema, so it can't be inside pg_catalog. So we'd
need some special logic somewhere to make pg_dump and psql \dew work as
expected, and I'm not quite sure what to do there.
Regards,
Jeff Davis
On Fri, Sep 1, 2023 at 4:04 PM Jeff Davis <pgsql@j-davis.com> wrote:
On Thu, 2023-08-31 at 17:17 -0400, Joe Conway wrote:
Maybe move postgres_fdw to be a first class built in feature instead
of
an extension?That could make sense, but we still have to solve the problem of how to
present a built-in FDW.FDWs don't have a schema, so it can't be inside pg_catalog. So we'd
need some special logic somewhere to make pg_dump and psql \dew work as
expected, and I'm not quite sure what to do there.
I'm worried that an approach based on postgres_fdw would have security
problems. I think that we don't want postgres_fdw installed in every
PostgreSQL cluster for security reasons. And I think that the set of
people who should be permitted to manage connection strings for
logical replication subscriptions could be different from the set of
people who are entitled to use postgres_fdw.
--
Robert Haas
EDB: http://www.enterprisedb.com
On Sat, Sep 2, 2023 at 12:24 AM Jeff Davis <pgsql@j-davis.com> wrote:
On Fri, 2023-09-01 at 12:28 +0530, Ashutosh Bapat wrote:
Thinking larger, how about we allow any FDW to be used here.
That's a possibility, but I think that means the subscription would
need to constantly re-check the parameters rather than relying on the
FDW's validator.Otherwise it might be the wrong kind of FDW, and the user might be able
to circumvent the password_required protection. It might not even be a
postgres-related FDW at all, which would be a bit strange.If it's constantly re-checking the parameters then it raises the
possibility that some "ALTER SERVER" or "ALTER USER MAPPING" succeeds
but then subscriptions to that foreign server start failing, which
would not be ideal. But I could be fine with that.
Why do we need to re-check parameters constantly? We will need to
restart subscriptions which are using the user mapping of FDW when
user mapping or server options change. If that mechanism isn't there,
we will need to build it. But that's doable.
I didn't understand your worry about circumventing password_required protection.
But I think there's some value in bringing
together these two subsystems which deal with foreign data logically
(as in logical vs physical view of data).I still don't understand how a core dependency on an extension would
work.
We don't need to if we allow any FDW (even if non-postgreSQL) to be
specified there. For non-postgresql FDW the receiver will need to
construct the appropriate command and use appropriate protocol to get
the changes and apply locally. The server at the other end may not
even have logical replication capability. The extension or "input
plugin" (as against output plugin) would decide whether it can deal
with the foreign server specific logical replication protocol. We add
another callback to FDW which can return whether the given foreign
server supports logical replication or not. If the users
misconfigured, their subscriptions will throw errors.
But with this change we open a built-in way to "replicate in" as we
have today to "replicate out".
--
Best Wishes,
Ashutosh Bapat
On Sat, Sep 2, 2023 at 1:41 AM Robert Haas <robertmhaas@gmail.com> wrote:
On Fri, Sep 1, 2023 at 4:04 PM Jeff Davis <pgsql@j-davis.com> wrote:
On Thu, 2023-08-31 at 17:17 -0400, Joe Conway wrote:
Maybe move postgres_fdw to be a first class built in feature instead
of
an extension?That could make sense, but we still have to solve the problem of how to
present a built-in FDW.FDWs don't have a schema, so it can't be inside pg_catalog. So we'd
need some special logic somewhere to make pg_dump and psql \dew work as
expected, and I'm not quite sure what to do there.I'm worried that an approach based on postgres_fdw would have security
problems. I think that we don't want postgres_fdw installed in every
PostgreSQL cluster for security reasons. And I think that the set of
people who should be permitted to manage connection strings for
logical replication subscriptions could be different from the set of
people who are entitled to use postgres_fdw.
If postgres_fdw was the only way to specify a connection to be used
with subscriptions, what you are saying makes sense. But it's not. We
will continue to support current mechanism which doesn't require
postgres_fdw to be installed on every PostgreSQL cluster.
What security problems do you foresee if postgres_fdw is used in
addition to the current mechanism?
--
Best Wishes,
Ashutosh Bapat
On Mon, 2023-09-04 at 18:01 +0530, Ashutosh Bapat wrote:
Why do we need to re-check parameters constantly? We will need to
restart subscriptions which are using the user mapping of FDW when
user mapping or server options change.
"Constantly" was an exaggeration, but the point is that it's a separate
validation step after the ALTER SERVER or ALTER USER MAPPING has
already happened, so the subscription would start failing.
Perhaps this is OK, but it's not the ideal user experience. Ideally,
the user would get some indication from the ALTER SERVER or ALTER USER
MAPPING that it's about to break a subscription that depends on it.
I didn't understand your worry about circumventing password_required
protection.
If the subscription doesn't do its own validation, and if the FDW
doesn't ensure that the password is set, then it could end up creating
a creating a connection string without supplying the password.
We don't need to if we allow any FDW (even if non-postgreSQL) to be
specified there.
OK, so we could have a built-in FDW called pg_connection that would do
the right kinds of validation; and then also allow other FDWs but the
subscription would have to do its own validation.
Regards,
Jeff Davis
On Tue, 2023-09-05 at 12:08 -0700, Jeff Davis wrote:
OK, so we could have a built-in FDW called pg_connection that would
do
the right kinds of validation; and then also allow other FDWs but the
subscription would have to do its own validation.
While working on this, I found a minor bug and there's another
discussion happening here:
/messages/by-id/e5892973ae2a80a1a3e0266806640dae3c428100.camel@j-davis.com
It looks like that's going in the direction of checking for the
presence of a password in the connection string at connection time.
Ashutosh, that's compatible with your suggestion that CREATE
SUBSCRIPTION ... SERVER works for any FDW that supplies the right
information, because we need to validate it at connection time anyway.
I'll wait to see how that discussion gets resolved, and then I'll post
the next version.
Regards,
Jeff Davis
On Tue, 2023-09-05 at 12:08 -0700, Jeff Davis wrote:
OK, so we could have a built-in FDW called pg_connection that would
do
the right kinds of validation; and then also allow other FDWs but the
subscription would have to do its own validation.
Attached a rough rebased version implementing the above with a
pg_connection_fdw foreign data wrapper built in.
Regards,
Jeff Davis
