Обсуждение: logical replication connection information management
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
Peter Eisentraut wrote: > 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). I was with you until you mentioned pg_constraint, because as I understand it we're not entirely happy with that one catalog. We've talked about splitting it up into two catalogs for table and domain constraints (I don't think we've considered assertions). Doing this would cause us to drop the NOT NULL from pg_foreign_server.srvfdw. However, while this sounds bad I think it's not *too* bad: it's not as heavily used as the corresponding pg_constraint columns would be. In other words, this raised some red flags with me initially but I think it's okay. > 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. We'll need to have a better answer to this sooner rather than later. Perhaps a new predefined grantable privilege? -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On 12/10/16 19:59, Peter Eisentraut wrote: > > 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. Yuck. > > 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). > I think these two latter options sound better, I kinda wonder if it should not be CREATE PUBLICATION SERVER though as the server represents publication not subscription, but either way this is all reasonable I think. > 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. > Yes, superuser in the beginning, especially for subscriptions as it will be bit harder to do proper checks without loss of performance. The good part is that if we do superuser initially we can always add some GRANT or ROLE later to lift the limitation so we don't have to build the whole role model right from start. -- Petr Jelinek http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services