Re: Why does creating logical replication subscriptions require superuser?

> We successfully created exploits against Aiven and AWS RDS services gaining
> superuser with their ways of subscription creation (and reported
> vulnerabilities, of course). Probably, you have this (not so scary)
> vulnerability too.

Can you share the rough idea of how these exploits work? What parts of the
current architecture allowed that to happen?

I read the thread regarding creating a special role for creating subscriptions,
and I think it helped me understand various aspects of the current architecture
better.

Please correct me if any of these points are incorrect:

Some of the original justifications for requiring superuser to create
subscriptions include:
- Replication inherently adds significant network traffic and extra background
process, and we wouldn't want unprivileged users to be able to add such
drastic load to then database.
- Subjectively, subscription is a "major" operation, so it makes sense to not
allow every user to perform it.
- Running the apply process as a superuser drastically simplifies the number
of possible errors that might arise due to not having sufficient permissions
to write to target tables, and may have simplified the initial
implementation.
- Subscriptions store plaintext passwords, which are sensitive, and we
wouldn't want unprivileged users to see these. Only allowing superusers
to create subscriptions and view the subconninfo column is a way to resolve
this.

Are there any other major reasons that we require superuser? Notably one may
wonder why we didn't check for the REPLICATION attribute, but even replication
users could run into table ownership and access issues.

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?

> 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.
I haven't tested this edge case, but I hope that Postgres doesn't crash if it
subsequently runs into a permission issue; I assume that it will simply stop
replication, which seems appropriate.

One other point:

One message in the thread mentioned somehow tricking Postgres into replicating
system catalog tables:

https://www.postgresql.org/message-id/109201553163096%40myt5-68ad52a76c91.qloud-c.yandex.net

I'm unsure whether this is allowed by default, but it doesn't seem like too
much trouble to run a modified publisher node that does allow it. Then
something like 'UPDATE pg_authid SET rolsuper = TRUE' could result in privilege
escalation on the subscriber side. But, again, if the apply process isn't
running as superuser, then presumably applying the change in the subscriber
would fail, stopping replication, and neutralizing the attack.

Thanks,
Paul