Thread: Why does creating logical replication subscriptions require superuser?

Hey, all,

I'm working with native logical replication, and I don't fully understand
why logical replication subscribers need to be superusers, nor do I fully
understand the implication of some of the comments made on this page:

https://www.postgresql.org/docs/current/logical-replication-security.html

> A user able to modify the schema of subscriber-side tables can execute
> arbitrary code as a superuser.

Does "execute arbitrary code" here really mean executing arbitrary code on the
machine that is running Postgres, or simply running arbitrary SQL in the
replicating database? Would it only be able to modify data in the database
containing the subscription, or could it modify other databases in the same
cluster? Is there any "blast-radius" to the capabilities gained by such a user?

According to the commit message that added this text, the callout of this
point was added as result of CVE-2020-14349, but the details there don't
really help me understand what the concern is here, nor do I have a deep
understanding of various features that might combine to create a vulnerability.

I'm not sure what arbitrary code could be executed, but my rough guess, based
on some of the other text on that page, is that custom triggers, written in
an arbitrary language (e.g., Python), would run arbitrary code and that is
the concern. Is that correct? And, if so, assuming that Python triggers were
the only way to execute arbitrary code, then simply building Postgres without
Python support would prevent users that can modify the schema from executing
code as superuser. What is the full set of features that could lead to running
arbitrary code in this scenario? Is it just all the different languages that
can be used to write triggers?

Essentially, I'm wondering what a loose proof-of-concept privilege escalation
vulnerability would look like if a non-superuser can modify the schema of
subscriber-side tables.

On a related note, what happens if a superuser creates a subscription, and then
is demoted to a regular user? My understanding is that the worker that applies
the logical changes to the database connects as the user that created the
subscription, so would that prevent potential vulnerabilities in any way?


Thanks,
Paul

Hi!

> 21 янв. 2021 г., в 21:20, Paul Martinez <paulmtz@google.com> написал(а):
>
> Hey, all,
>
> I'm working with native logical replication, and I don't fully understand
> why logical replication subscribers need to be superusers, nor do I fully
> understand the implication of some of the comments made on this page:
>
> https://www.postgresql.org/docs/current/logical-replication-security.html
>
>> A user able to modify the schema of subscriber-side tables can execute
>> arbitrary code as a superuser.
>
> Does "execute arbitrary code" here really mean executing arbitrary code on the
> machine that is running Postgres, or simply running arbitrary SQL in the
> replicating database? Would it only be able to modify data in the database
> containing the subscription, or could it modify other databases in the same
> cluster? Is there any "blast-radius" to the capabilities gained by such a user?
I suspect it means what it states. Replication is running under superuser and e.g. one can add system catalog to
subscription.
Or exploit this fact other way. Having superuser you can just COPY FROM PROGRAM anything.

> According to the commit message that added this text, the callout of this
> point was added as result of CVE-2020-14349, but the details there don't
> really help me understand what the concern is here, nor do I have a deep
> understanding of various features that might combine to create a vulnerability.
>
> I'm not sure what arbitrary code could be executed, but my rough guess, based
> on some of the other text on that page, is that custom triggers, written in
> an arbitrary language (e.g., Python), would run arbitrary code and that is
> the concern. Is that correct? And, if so, assuming that Python triggers were
> the only way to execute arbitrary code, then simply building Postgres without
> Python support would prevent users that can modify the schema from executing
> code as superuser. What is the full set of features that could lead to running
> arbitrary code in this scenario? Is it just all the different languages that
> can be used to write triggers?
We cannot build PostgreSQL without SQL.

> Essentially, I'm wondering what a loose proof-of-concept privilege escalation
> vulnerability would look like if a non-superuser can modify the schema of
> subscriber-side tables.

> On a related note, what happens if a superuser creates a subscription, and then
> is demoted to a regular user? My understanding is that the worker that applies
> the logical changes to the database connects as the user that created the
> subscription, so would that prevent potential vulnerabilities in any way?

Subscription operations must run with privileges of user that created it. All other ways are error prone and leave
subscriptionsonly in superuser's arsenal. 

Thanks!

Best regards, Andrey Borodin.

Andrey Borodin schrieb am 22.01.2021 um 08:32:

> Replication is running under superuser and e.g. one can add system catalog to subscription.
> Or exploit this fact other way. Having superuser you can just COPY FROM PROGRAM anything.

It was my understanding that the replication process itself runs with the user specified
when creating the subscription - which is no necessarily a superuser. Only a user that
is part of the "replication" role.

The replication user also needs to be granted SELECT privileges on all tables of the publication,
so it's quite easy to control what the replication user has access to.
Plus the publication also limits what the replication can see.

I second the idea that not requiring a superuser to create a subscription would make things
a lot easier. We worked around that by creating a security definer function that runs
the CREATE SUBSCRIPTION command.

Thomas

[offlist]

> 22 янв. 2021 г., в 13:16, Thomas Kellerer <shammat@gmx.net> написал(а):
>
> Andrey Borodin schrieb am 22.01.2021 um 08:32:
>
>> Replication is running under superuser and e.g. one can add system catalog to subscription.
>> Or exploit this fact other way. Having superuser you can just COPY FROM PROGRAM anything.
>
> It was my understanding that the replication process itself runs with the user specified
> when creating the subscription - which is no necessarily a superuser. Only a user that
> is part of the "replication" role.
>
> The replication user also needs to be granted SELECT privileges on all tables of the publication,
> so it's quite easy to control what the replication user has access to.
> Plus the publication also limits what the replication can see.
>
> I second the idea that not requiring a superuser to create a subscription would make things
> a lot easier. We worked around that by creating a security definer function that runs
> the CREATE SUBSCRIPTION command.

Hi! Yes, at Yandex.Cloud we want it too
https://www.postgresql.org/message-id/flat/1269681541151271%40myt5-68ad52a76c91.qloud-c.yandex.net
And we run PG with patches that create special role for replication that allows you to create subscriptions for tables
youown. 
We successfully created exploits against Aiven and AWS RDS services gaining superuser with their ways of subscription
creation(and reported vulnerabilities, of cause). Probably, you have this (not so scary) vulnerability too. 

Best regards, Andrey Borodin.

> 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

On Sat, Jan 23, 2021 at 3:46 AM Paul Martinez <paulmtz@google.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.

On Fri, Jan 22, 2021 at 02:08:02PM -0800, Paul Martinez wrote:
> 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.

I think you're referring to these messages:

  https://postgr.es/m/CA+TgmoahEoM2zZO71yv4883HFarXcBcOs3if6fEdRcRs8Fs=zA@mail.gmail.com
  https://postgr.es/m/CA+TgmobqXGe_7dcX1_Dv8+kaf3NEoe5Sy4NXGB9AyfM5YDjsGQ@mail.gmail.com

A permanent background process bypasses authentication, so mechanisms like
pg_hba.conf and expiration of the auth SSL certificate don't stop access.
Like that thread discussed, this justifies some privilege enforcement.
(Autovacuum also bypasses authentication, but those are less predictable.)

Since we already let users drive the database to out-of-memory, I wouldn't
worry about load.  In other words, the quantity of network traffic and number
of background processes don't matter, just the act of allowing them at all.

> - 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.

I think you're referring to this:

https://postgr.es/m/CA+TgmoYe1x21zLyCqOVL-KDd9DJSVZ4v8NNmfDscjRwV9Qfgkg@mail.gmail.com wrote:
> It seems more likely that there is a person whose job it is to set up
> replication but who doesn't normally interact with the table data
> itself.  In that kind of case, you just want to give the person
> permission to create subscriptions, without needing to also give them
> lots of privileges on individual tables (and maybe having whatever
> they are trying to do fail if you miss a table someplace).

Exposure to permission checks is a chief benefit of doing anything as a
non-superuser, so I disagree with this.  (I've bcc'd the author of that
message, in case he wants to comment.)  We could add a pg_write_any_table
special role.  DBAs should be more cautious granting pg_write_any_table than
granting subscription privilege.  (For this use case, grant both.)

> - 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.

pg_user_mapping.umoptions has the same security considerations; one should be
able to protect it and subconninfo roughly the same way.

> Are there any other major reasons that we require superuser?

As another prerequisite for non-superuser-owned subscriptions, the connection
to the publisher must enforce the equivalent of dblink_security_check().

> 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.

REPLICATION represents the authority to read all bytes of the data directory.
Compared to the implications of starting a subscriber, REPLICATION carries a
lot of power.  I would not reuse REPLICATION here.

> 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.

This is a special case of the need for ordinary ACL checks in the subscriber.
Treating system catalogs differently would be insufficient and unnecessary.

Thanks,
nm