Re: Non-superuser subscription owners

> On Nov 17, 2021, at 9:33 AM, Jeff Davis <pgsql(at)j-davis(dot)com> wrote:
>
> I am still trying to understand this use case. It doesn't feel like
> "ownership" to me, it feels more like some kind of delegation.
>
> Is GRANT a better fit here? That would allow more than one user to
> REFRESH, or ENABLE/DISABLE the same subscription. It wouldn't allow
> RENAME, but I don't see why we'd separate privileges for
> CREATE/DROP/RENAME anyway.

We may eventually allow non-superusers to create subscriptions, but there are lots of details to work out. Should there be limits on how many subscriptions they can create? Should there be limits to the number of simultaneously open connections they can create out to other database servers (publishers)? Should they need to be granted USAGE on a database publisher in order to use the connection string for that publisher in a subscription they create? Should they need to be granted USAGE on a publication in order to replicate it? Yes, there may be restrictions on the publisher side, too, but the user model on subscriber and publisher might differ, and the connection string used might not match the subscription owner, so some restriction on the subscriber side may be needed.

The implementation of [CREATE | ALTER] SUBSCRIPTION was designed at a time when only superusers could execute them, and as far as I can infer from the design, no effort to constrain the effects of those commands was made. Since we're trying to make subscriptions into things that non-superusers can use, we have to deal with some things in those functions. For example, ALTER SUBSCRIPTION can change the database connection parameter, or the publication subscribed, or whether synchronous_commit is used. I don't see that a subscription owner should necessarily be allowed to mess with that, at least not without some other privilege checks beyond mere ownership.

I think this is pretty analogous to how security definer functions work. You might call those "delegation" also, but the basic idea is that the function will run under the privileges of the function's owner, who might be quite privileged if you want the function to do highly secure things for you, but who could also intentionally be limited in privilege. It wouldn't make much sense to say the owner of a security definer function can arbitrarily escalate their privileges to do things like open connections to other database servers, or have the transactions in which they run have a different setting of synchronous_commit. Yet with subscriptions, if the subscription owner can run all forms of ALTER SUBSCRIPTION, that's what they can do.

I took a conservative position in the design of the patch to avoid giving away too much. I suspect that we'll come back to these design decisions and relax them at some point, but the exact way in which we relax them is not obvious. We could just agree to remove them (as you seem to propose), or we might agree to create predefined roles and say that the subscription owner can change certain aspects of the subscription if and only if they are members of one or more of those roles, or we may create new grantable privileges. Each of those debates may be long and hard fought, so I don't want to invite that as part of this thread, or this patch will almost surely miss the cutoff for v15.

> This would not address the weirdness of the existing code where a
> superuser loses their superuser privileges but still owns a
> subscription. But perhaps we can solve that a different way, like just
> performing a check when someone loses their superuser privileges that
> they don't own any subscriptions.

I gave that a slight amount of thought during the design of this patch, but didn't think we could refuse to revoke superuser on such a basis, and didn't see what we should do with the subscription other than have it continue to be owned by the recently-non-superuser. If you have a better idea, we can discuss it, but to some degree I think that is also orthogonal to the purpose of this patch. The only sense in which this patch depends on that issue is that this patch proposes that non-superuser subscription owners are already an issue, and therefore that this patch isn't creating a new issue, but rather making more sane something that already can happen.

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