Re: running logical replication as the subscription owner - Mailing list pgsql-hackers

On Fri, Mar 24, 2023 at 12:58 PM Mark Dilger
<mark.dilger@enterprisedb.com> wrote:
> I also think the subscription owner should be a low-privileged user, owing to the risk of the publisher injecting
maliciouscontent into the publication.  I think you are focused on all the bad actors on the subscription-side database
andwhat they can do to each other.  That's also valid, but I get the impression that you're losing sight of the risk
posedby malicious publishers.  Or maybe you aren't, and can explain? 

You have a point.

As things stand today, if you think somebody's going to send you
changes to tables other than the ones you intend to replicate, you
could handle that by making sure that the user that owns the
subscription only has permission to write to the tables that are
expected to receive replicated data. It's a bit awkward to set up
because you have to initially make the subscription owner a superuser
and then later remove the superuser bit, so I think this is another
argument for the pg_create_subscription patch posted elsewhere, but if
you're a superuser yourself, you can do it. However, with this patch,
that wouldn't work any more, because the permissions checks don't
happen until after we've switched to the target role. You could
alternatively set up a user to own the subscription who has the
ability to SET ROLE to some users and not others, but that only lets
you restrict replication based on which user owns the tables, rather
than which specific tables are getting data replicated into them. That
actually wouldn't work today, and with the patch it would start
working, so basically the effect of the patch on the problem that you
mention would be to remove the ability to filter by specific table and
add the ability to filter by owning role.

I don't know how bad that sounds to you, and if it does sound bad, I
don't immediately see how to mitigate it. As I said to Jeff, if you
can replicate into a table that has a casually-written SECURITY
INVOKER trigger on it, you can probably hack into the table owner's
account. So I think that if we allow user A to replicate into user B's
table with fewer privileges than A-can-set-role-to-B, we're building a
privilege-escalation attack into the system. But if we do require
A-can-set-role-to-B, then things change as described above.

I suppose in theory we could check both A-can-set-role-to-B and
A-can-modify-this-table-as-A, but that feels pretty unprincipled. I
can't particularly see why A should need permission to perform an
action that is actually going to be performed as B; what makes sense
is to check that A can become B, and that B has permission to perform
the action, which is the state that this patch would create. I guess
there are other things we could do, too, like add replication
restriction or filtering capabilities specifically to address this
problem, or devise some other kind of new kind of permission system
around this, but I don't have a specific idea what that would look
like.

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