Re: Non-superuser subscription owners

> On Jan 18, 2023, at 11:38 AM, Robert Haas <robertmhaas@gmail.com> wrote:
>
> I was just noticing that what was committed here didn't actually fix
> the problem implied by the subject line. That is, non-superuser still
> can't own subscriptions.

Not so.  They can.  See src/test/subscription/027_nosuperuser.pl

> To put that another way, there's no way for
> the superuser to delegate the setup and administration of logical
> replication to a non-superuser.

True.

> That's a bummer.

Also true.

> Reading the thread, I'm not quite sure why we seemingly did all the
> preparatory work and then didn't actually fix the problem.

Prior to the patch, if a superuser created a subscription, then later was demoted to non-superuser, the subscription
applyworkers 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.
Thesuperuser can now create non-superuser subscriptions.  (I'm not sure this ability is well advertised.)  But the
problemof non-superuser roles creating non-superuser subscriptions is not solved. 

From a security perspective, the bit that was solved may be the more important part; from a usability perspective,
perhapsnot. 

> It was
> previously proposed that we introduce a new predefined role
> pg_create_subscriptions and allow users who have the privileges of
> that predefined role to create and alter subscriptions. There are a
> few issues with that which, however, seem fairly solvable to me:
>
> 1. Jeff pointed out that if you supply a connection string that is
> going to try to access local files, you'd better have
> pg_read_server_files, or else we should not let you use that
> connection string. I guess that's mostly a function of which
> parameters you specify, e.g. passfile, sslcert, sslkey, though maybe
> for host it depends on whether the value starts with a slash. We might
> need to think a bit here to make sure we get the rules right but it
> seems like a pretty solvable problem.
>
> 2. There was also quite a bit of discussion of what to do if a user
> who was previously eligible to own a subscription ceases to be
> eligible, in particular around a superuser who is made into a
> non-superuser, but the same problem would apply if you had
> pg_create_subscriptions or pg_read_server_files and then lost it. My
> vote is to not worry about it too much. Specifically, I think we
> should certainly check whether the user has permission to create a
> subscription before letting them do so, but we could handle the case
> where the user already owns a subscription and tries to modify it by
> either allowing or denying the operation and I think either of those
> would be fine. I even think we could do one of those in some cases and
> the other in other cases and as long as there is some principle to the
> thing, it's fine. I argue that it's not a normal configuration and
> therefore it doesn't have to work in a particularly useful way. It
> shouldn't break the world in some horrendous way, but that's about as
> good as it needs to be. I'd argue for example that DROP SUBSCRIPTION
> could just check whether you own the object, and that ALTER
> SUBSCRIPTION could check whether you own the object and, if you're
> changing the connection string, also whether you would have privileges
> to set that new connection string on a new subscription.
>
> 3. There was a bit of discussion of maybe wanting to allow users to
> create subscriptions with some connection strings but not others,
> perhaps by having some kind of intermediate object that owns the
> connection string and is owned by a superuser or someone with lots of
> privileges, and then letting a less-privileged user point a
> subscription at that object. I agree that might be useful to somebody,
> but I don't see why it's a hard requirement to get anything at all
> done here. Right now, a subscription contains a connection string
> directly. If in the future someone wants to introduce a CREATE
> REPLICATION DESTINATION command (or whatever) and have a way to point
> a subscription at a replication destination rather than a connection
> string directly, cool. Or if someone wants to wire this into CREATE
> SERVER somehow, also cool. But if you don't care about restricting
> which IPs somebody can try to access by providing a connection string
> of their choice, then you would be happy if we just did something
> simple here and left this problem for another day.
>
> I am very curious to know (a) why work on this was abandoned (perhaps
> the answer is just lack of round tuits, in which case there is no more
> to be said)

Mostly, it was a lack of round-tuits.  After the patch was committed, I quickly switched my focus elsewhere.

> , and (b) what people think of (1)-(3) above

There are different ways of solving (1), and Jeff and I discussed them in Dec 2021.  My recollection was that idea (3)
wasthe cleanest.  Other ideas might be simpler than (3), or they may just appear simpler but in truth turn into a can
ofworms.  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. 

—
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company