On Wed, Feb 1, 2023 at 1:09 PM Mark Dilger <mark.dil...@enterprisedb.com> wrote: > > On Feb 1, 2023, at 6:43 AM, Robert Haas <robertmh...@gmail.com> wrote: > > The thing I'm > > struggling to understand is: if you only want to replicate into tables > > that Alice can write, why not just make Alice own the subscription? > > For a run-as user to make sense, you need a scenario where we want the > > replication to target only tables that Alice can touch, but we also > > don't want Alice herself to be able to touch the subscription, so you > > make Alice the run-as user and yourself the owner, or something like > > that. But I'm not sure what that scenario is exactly. > > This "run-as" idea came about because we didn't want arbitrary roles to be > able to change the subscription's connection string. A competing idea was to > have a server object rather than a string, with roles like Alice being able > to use the server object if they have been granted usage privilege, and not > otherwise. So the "run-as" and "server" ideas were somewhat competing.
As far as not changing the connection string goes, a few more ideas have entered the fray: the current patch uses a password_required property that is modelled on postgres_fdw, and I've elsewhere proposed a reverse pg_hba.conf. IMHO, for the use cases that I can imagine, the reverse pg_hba.conf idea feels better than all competitors, because it's the only idea that lets you define a class of acceptable connection strings. Jeff's idea of a separate connection object is fine if you have a specific, short list of connection strings and you want to allow those and disallow everything else, and there may be cases where people want that, and that's fine, but my guess is that it's overly restrictive in a lot of environments. The password_required property has the virtue of being compatible with what we do in other places right now, and of preventing wraparound-to-superuser attacks effectively, but it's totally unconfigurable and that sucks. The runas user idea gives you some control over who is allowed to set the connection string, but it doesn't help you delegate that to a non-superuser, because the idea there is that you want the non-superuser to be able to set connection strings that are OK with the actual superuser but not others. I think part of my confusion here is that I thought that the point of the runas user was to defend against logical replication itself changing the connection string, and I don't see how it would do that. It's just moving rows around. If the point is that somebody who can log in as the runas user might change the connection string to something we don't like, that makes somewhat more sense. I think I had in my head that you wouldn't use someone's actual login role to run logical replication, but rather some role specifically set up for that purpose. In that scenario, nobody's running SQL commands as the runas user, so even if they also own the subscription, there's no way for it to get modified. > > Mark was postulating a scenario where the publisher and subscriber > > don't trust each other. I was thinking a little bit more about that. I > > still maintain that the current system is poorly set up to make that > > work, but suppose we wanted to do better. We could add filtering on > > the subscriber side, like you list schemas or specific relations that > > you are or are not willing to replicate into. Then you could, for > > example, connect your subscription to a certain remote publication, > > but with the restriction that you're only willing to replicate into > > the "headquarters" schema. Then we'll replicate whatever tables they > > send us, but if the dorks at headquarters mess up the publications on > > their end (intentionally or otherwise) and add some tables from the > > "locally_controlled_stuff" schema, we'll refuse to replicate that into > > our eponymous schema. > > That example is good, though I don't see how "filters" are better than > roles+privileges. Care to elaborate? I'm not sure that they are. Are we assuming that the user who is creating subscriptions is also powerful enough to create roles and give them just the required amount of privilege? If so, it seems like they might as well just do it that way. And maybe we should assume that, because in most cases, a dedication replication role makes more sense to me than running replication under some role that you're also using for other things. On the other hand, I bet a lot of people today are just running replication as a superuser, in which case maybe this could be useful? This whole idea was mostly just me spitballing to see what other people thought. I'm not wild about the complexity involved for what you get out of it, so if we don't need it, that's more than fine with me. -- Robert Haas EDB: http://www.enterprisedb.com
