Re: Non-superuser subscription owners

On Wed, Jan 18, 2023 at 3:26 PM Mark Dilger
<mark.dilger@enterprisedb.com> wrote:
> Prior to the patch, if a superuser created a subscription, then later was demoted to non-superuser, the subscription apply workers still applied the changes with superuser force.  So creating a superuser Alice, letting Alice create a subscription, then revoking superuser from Alice didn't accomplish anything interesting.  But after the patch, it does.  The superuser can now create non-superuser subscriptions.  (I'm not sure this ability is well advertised.)  But the problem of non-superuser roles creating non-superuser subscriptions is not solved.

Ah, OK, thanks for the clarification!

> There are different ways of solving (1), and Jeff and I discussed them in Dec 2021.  My recollection was that idea (3) was the cleanest.  Other ideas might be simpler than (3), or they may just appear simpler but in truth turn into a can of worms.  I don't know, since I never went as far as trying to implement either approach.
>
> Idea (2) seems to contemplate non-superuser subscription owners as a theoretical thing, but it's quite real already.  Again, see 027_nosuperuser.pl.

I think the solution to the problem of a connection string trying to
access local files is to just look at the connection string, decide
whether it does that, and if yes, require the owner to have
pg_read_server_files as well as pg_create_subscription. (3) is about
creating some more sophisticated and powerful solution to that
problem, but that seems like a nice-to-have, not something essential,
and a lot more complicated to implement.

I guess what I listed as (2) is not relevant since I didn't understand
correctly what the current state of things is.

Unless I'm missing something, it seems like this could be a quite small patch.

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