Re: Predefined role pg_maintenance for VACUUM, ANALYZE, CHECKPOINT.

Greetings,

* Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
> Stephen Frost <sfrost(at)snowman(dot)net> writes:
> > Independent of other things, getting to the point where everything can
> > be done in the database without the need for superuser is absolutely a
> > good goal to be striving for, not something to be avoiding.
> > I don't think that makes superuser become 'dummy', but perhaps the
> > only explicit superuser check we end up needing is "superuser is a
> > member of all roles". That would be a very cool end state.
>
> I'm not entirely following how that's going to work. It implies that
> there is some allegedly-not-superuser role that has the ability to
> become superuser -- either within SQL or by breaking out to the OS --
> because certainly a superuser can do those things.

I don't think it implies that at all. Either that, or I'm not following
what you're saying here. We have predefined roles today which aren't
superusers and yet they're able to break out to the OS. Of course they
can become superusers if they put the effort in. None of that gets away
from the more general idea that we could get to a point where all of a
superuser's capabilities come from roles which the superuser is
automatically a member of such that we need have only one explicit
superuser() check.

> I don't think we're serving any good purpose by giving people the
> impression that roles with such permissions are somehow not
> superuser-equivalent. Certainly, the providers who don't want to
> give users superuser are just going to need a longer list of roles
> they won't give access to (and they probably won't be pleased about
> having to vet every predefined role carefully).

I agree that we need to be clear through the documentation about which
predefined roles are able to "break outside the box" and become
superuser, but that's independent from the question of if we will get to
a point where every capability the superuser has can also be given
through membership in some predefined role or not.

That providers have to figure out what makes sense for them in terms of
what they'll allow their users to do or not do doesn't seem entirely
relevant here- different providers are by definition different and some
might be fine with given out certain rights that others don't want to
give out. That's actually kind of the point of breaking out all of
these capabilities into more fine-grained ways of granting capabilities
out.

We already have roles today which are ones that can break outside the
box and get to the OS and are able to do things that a superuser can do,
or become a superuser themselves, and that's been generally a positive
thing. We also have roles which are able to do things that only
superusers used to be able to do but which are not able to become
superusers themselves and aren't able to break out of the box. I don't
see any reason why we can't continue with this and eventually eliminate
the explicit superuser() checks except from the one where a superuser is
a member of all roles. Sure, some of those roles give capabilities
which can be used to become superuser, while others don't, but I hardly
see that as an argument against the general idea that each is able to be
independently GRANT'd.

If nothing else, if we could eventually get to the point where there's
only one such explicit check then maybe we'd stop creating *new* places
where capabilities are locked behind a superuser check. I did an audit
once upon a time of all superuser checks and rather than that number
going down, as I had hoped at the time, it's been going up instead
across new releases. I view that as unfortunate.

Thanks,

Stephen