On Sat, Jan 8, 2022 at 2:38 AM Jeff Davis <pg...@j-davis.com> wrote: > Committed.
I was just noticing that what was committed here didn't actually fix the problem implied by the subject line. That is, non-superuser still can't own subscriptions. To put that another way, there's no way for the superuser to delegate the setup and administration of logical replication to a non-superuser. That's a bummer. Reading the thread, I'm not quite sure why we seemingly did all the preparatory work and then didn't actually fix the problem. It was previously proposed that we introduce a new predefined role pg_create_subscriptions and allow users who have the privileges of that predefined role to create and alter subscriptions. There are a few issues with that which, however, seem fairly solvable to me: 1. Jeff pointed out that if you supply a connection string that is going to try to access local files, you'd better have pg_read_server_files, or else we should not let you use that connection string. I guess that's mostly a function of which parameters you specify, e.g. passfile, sslcert, sslkey, though maybe for host it depends on whether the value starts with a slash. We might need to think a bit here to make sure we get the rules right but it seems like a pretty solvable problem. 2. There was also quite a bit of discussion of what to do if a user who was previously eligible to own a subscription ceases to be eligible, in particular around a superuser who is made into a non-superuser, but the same problem would apply if you had pg_create_subscriptions or pg_read_server_files and then lost it. My vote is to not worry about it too much. Specifically, I think we should certainly check whether the user has permission to create a subscription before letting them do so, but we could handle the case where the user already owns a subscription and tries to modify it by either allowing or denying the operation and I think either of those would be fine. I even think we could do one of those in some cases and the other in other cases and as long as there is some principle to the thing, it's fine. I argue that it's not a normal configuration and therefore it doesn't have to work in a particularly useful way. It shouldn't break the world in some horrendous way, but that's about as good as it needs to be. I'd argue for example that DROP SUBSCRIPTION could just check whether you own the object, and that ALTER SUBSCRIPTION could check whether you own the object and, if you're changing the connection string, also whether you would have privileges to set that new connection string on a new subscription. 3. There was a bit of discussion of maybe wanting to allow users to create subscriptions with some connection strings but not others, perhaps by having some kind of intermediate object that owns the connection string and is owned by a superuser or someone with lots of privileges, and then letting a less-privileged user point a subscription at that object. I agree that might be useful to somebody, but I don't see why it's a hard requirement to get anything at all done here. Right now, a subscription contains a connection string directly. If in the future someone wants to introduce a CREATE REPLICATION DESTINATION command (or whatever) and have a way to point a subscription at a replication destination rather than a connection string directly, cool. Or if someone wants to wire this into CREATE SERVER somehow, also cool. But if you don't care about restricting which IPs somebody can try to access by providing a connection string of their choice, then you would be happy if we just did something simple here and left this problem for another day. I am very curious to know (a) why work on this was abandoned (perhaps the answer is just lack of round tuits, in which case there is no more to be said), and (b) what people think of (1)-(3) above, and (c) whether anyone knows of further problems that need to be considered here. Thanks, -- Robert Haas EDB: http://www.enterprisedb.com