Re: Non-superuser subscription owners - Mailing list pgsql-hackers

On Wed, Feb 1, 2023 at 3:37 PM Andres Freund <andres@anarazel.de> wrote:
> The general point is that IMO is that in many setups you should use a
> user with fewer privileges than a superuser.  It doesn't really matter
> whether we have an ad-hoc restriction for system catalogs. More often
> than not being able to modify other tables will give you a lot of
> privileges too.

I don't know what you mean by this. DML doesn't confer privileges. If
code gets executed and runs with the replication user's credentials,
that could lead to privilege escalation, but just moving rows around
doesn't, at least not in the database sense. It might confer
unanticipated real-world benefits, like if you can update your own
salary or something, but in the context of replication you have to
have had the ability to do that on some other node anyway. If that
change wasn't supposed to get replicated to the local node, then why
are we using replication? Or why is that table in the publication? I'm
confused.

> > 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?
>
> Because it implies that the replication happens as a user that's
> privileged enough to change the configuration of replication.

But again, replication is just about inserting, updating, and deleting
rows. To change the replication configuration, you have to be able to
parlay that into the ability to execute code. That's why I think
trigger security is really important. But I'm wondering if there's
some way we can handle that that doesn't require us to make a decision
about arun-as user. For instance, if firing triggers as the table
owner is an acceptable solution, then the only thing that the run-as
user is actually controlling is which tables we're willing to
replicate into in the first place (unless there's some other way that
logical replication can run arbitrary code). The name almost becomes a
misnomer in that case. It's not a run-as user, it's
use-this-user's-permissions-to-see-if-I-should-fail-replication user.

> > 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.
>
> Isn't that largely a duplication of the ACLs on relations etc?

Yeah, maybe.

> > I'm not quite sure what we do here now, but I agree that trigger
> > firing seems like a problem. It might be that we need to worry about
> > the user on the origin server, too. If Alice inserts a row that causes
> > a replicated table owned by Bob to fire a trigger or evaluate a
> > default expression or whatever due the presence of a subscription
> > owned by Charlie, there is a risk that Alice might try to attack
> > either Bob or Charlie, or that Bob might try to attack Charlie.
>
> The attack on Bob exists without logical replication too - a REINDEX or
> such is executed as the owner of the relation and re-evaluates index
> expressions, constraints etc.  Given our security model I don't think we
> can protect the relation owner if they trust somebody to insert rows, so
> I don't really know what we can do to protect Charlie against Bob.

Yikes.

> > > > And if we suppose that that already works and is safe, well then
> > > > what's the case where I do need a run-as user?
> > >
> > > It's not at all safe today, IMO. You need to trust that nothing bad will
> > > be replicated, otherwise the owner of the subscription has to be
> > > considered compromised.
> >
> > What kinds of things are bad to replicate?
>
> I think that's unfortunately going to be specific to a setup.

Can you give an example?

-- 
Robert Haas
EDB: http://www.enterprisedb.com