Re: Non-superuser subscription owners

On Wed, Feb 1, 2023 at 1:09 PM Mark Dilger <mark.dilger@enterprisedb.com> wrote:
> > On Feb 1, 2023, at 6:43 AM, Robert Haas <robertmhaas@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