Re: Non-superuser subscription owners - Mailing list pgsql-hackers

On Wed, 2021-11-17 at 10:25 -0800, Mark Dilger wrote:
> We may eventually allow non-superusers to create subscriptions, but
> there are lots of details to work out.

I am setting aside the idea of subscriptions created by non-superusers.

My comments were about your idea for "low-power users" that can still
do things with subscriptions. And for that, GRANT seems like a better
fit than ownership.

With v2-0001, there are several things that seem weird to me:

  * Why can there be only one low-power user per subscription?
  * Why is RENAME a separate capability from CREATE/DROP?
  * What if you want to make the privileges more fine-grained, or make
changes in the future? Ownership is a single bit, so it requires that
everyone agree. Maybe some people want RENAME to be a part of that, and
others don't.

GRANT seems to provide better answers here.

> Since we're trying to make subscriptions into things that non-
> superusers can use, we have to deal with some things in those
> functions.

I understand the use case where a superuser isn't required anywhere in
the process, and some special users can create and own subscriptions. I
also understand that's not what these patches are trying to accomplish
(though v2-0003 seems like a good step in that direction).

I don't understand the use case as well where a non-superuser can
merely "use" a subscription. I'm sure such use cases exist and I'm fine
to go along with that idea, but I'd like to understand why ownership
(partial ownership?) is the right way to do this and GRANT is the wrong
way.

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

That violates my expectations of what "ownership" means.

> I think this is pretty analogous to how security definer functions
> work.

The analogy to SECURITY DEFINER functions seems to support my
suggestion for GRANT at least as much as your modified definition of
ownership.

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

I don't necessarily see a problem there, but I could be missing
something.

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

By introducing and documenting a way to get non-superusers to own a
subscription, it makes it more likely that people will do it, and
harder for us to change. That means the standard should be "this is
what we really want", rather than just "more sane than before".

Regards,
    Jeff Davis