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; CREATE SUBSCRIPTION 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 need to 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers