Re: Why does creating logical replication subscriptions require superuser?

On Sat, Jan 23, 2021 at 3:46 AM Paul Martinez <paulmtz(at)google(dot)com> wrote:
>
>
> Unless I'm mistaken, the apply worker process runs as the user that created
> the subscription. Thus, it is the requirement that only superusers can create
> subscriptions that leads to two warnings in the Security documentation:
>
> https://www.postgresql.org/docs/current/logical-replication-security.html
>
> > The subscription apply process will run in the local database with the
> > privileges of a superuser.
>
> This is a direct consequence of requiring superuser to create subscriptions
> and running the apply process as the creator. If the subscription weren't
> created by a superuser, then the apply process wouldn't run as superuser
> either, correct?
>

Yes, this is correct. We use the owner of the subscription in the
apply process to connect to the local database.

> > A user able to modify the schema of subscriber-side tables can execute
> > arbitrary code as a superuser. Limit ownership and TRIGGER privilege on such
> > tables to roles that superusers trust.
>
> I believe a theoretical exploit here would involve the unprivileged user
> creating a trigger with a function defined using SECURITY INVOKER and attaching
> it to a table that is a subscription target. Since the apply process is running
> as superuser, this means that the trigger is invoked as superuser, leading to
> the privilege escalation. More accurately, a user able to modify the schema
> of subscriber-side tables could execute arbitrary code as the _creator of the
> subscription_, correct?
>
> So it seems privilege escalation to _superuser_ can be prevented by simply
> making the owner of the subscription not a superuser. But this can already
> happen now by simply altering the user after the subscription has been created.
>

We can't change the owner of the subscription to a non-superuser. See
the below example:
postgres=# Alter Subscription mysub Owner to test;
ERROR: permission denied to change owner of subscription "mysub"
HINT: The owner of a subscription must be a superuser.

In the above example, the 'test' is a non-superuser.

--
With Regards,
Amit Kapila.