logical replication connection information management - Mailing list pgsql-hackers

I want to discuss the connection information management aspect of the
logical replication patch set that is currently being proposed
(https://commitfest.postgresql.org/10/701/).

To review, the user-visible interfaces center around
  -- on sending end  CREATE PUBLICATION mypub FOR TABLE tbl1, tbl2, ...;
  -- on receiving end  CREATE SUBSCRIPTION mysub PUBLICATION mypub CONNECTION 'host= dbname=
...'

Both of these map pretty directly into system catalogs pg_publication
and pg_subscription.

The concern is about storing the connection information.  Right now,
this is just a string that is stored and passed to libpqwalreceiver.
But this string can contain passwords, so it needs to be protected.
Currently, pg_subscription has read permissions removed.  This creates
various annoyances.

An idea was to use the facilities we already have for foreign data
access for storing replication connection information.  It already has
considered and solved these problems.  So it might look like this:
   CREATE SERVER node1 OPTIONS (host '...', dbname '...');   CREATE USER MAPPING FOR CURRENT_USER SERVER node1;
CREATESUBSCRIPTION mysub PUBLICATION mypub SERVER node1;
 

This would have a number of advantages:

- Secret information such as passwords is all stored in one place that is already secured.

- Remote connection information is stored all in one place.

- Subscriptions pointing to the same remote host are logically connected.

- It's easier to change connection information for all subscriptions pointing to a host or to change the password of a
user.

- Access control can use existing facilities.  We would not need a new concept for who can create subscriptions and not
needto use superuser or some semi-superuser status.  To allow the use of a server, grant USAGE on the server.
 

So functionality-wise, this looks pretty good, but there is some
awkwardness in how to wire this into the existing facilities, since a
server, also known as a foreign server, is currently tied to a foreign
data wrapper.  I have currently implemented this by creating a fake
built-in foreign data wrapper called "subscription", so the actual
syntax is
   CREATE SERVER node1 WRAPPER subscription OPTIONS (host '...', dbname
'...');

which isn't terrible, but still a bit weird.

An idea is to make the foreign server concept more general and allow
it to exist independently of a foreign data wrapper.  Then create more
specific syntax like
   CREATE SERVER node1 FOR SUBSCRIPTION OPTIONS ( ... );

or
   CREATE SUBSCRIPTION SERVER ...

This would work a bit like pg_constraint, which can be tied to a table
or a type or even nothing (for the hypothetical assertions feature).

We'd need a separate mechanism for controlling which user has the right
to create such subscription servers, but it might be acceptable at the
beginning to just require superuserness.

Thoughts on that?

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services