Re: Why does creating logical replication subscriptions require superuser?

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