Re: SET ROLE and reserved roles

Robert,

* Robert Haas (robertmhaas(at)gmail(dot)com) wrote:
> On Fri, Apr 15, 2016 at 11:56 AM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> > Perhaps it would be helpful to return to the initial goal of these
> > default roles.
> >
> > We've identified certain privileges which are currently superuser-only
> > and we would like to be able to be GRANT those to non-superuser roles.
> > Where our GRANT system is able to provide the granularity desired, we
> > have simply removed the superuser checks, set up appropriate default
> > values and, through the "pg_dump dump catalog ACLs" patch, allowed
> > administrators to make the changes to those objects via the
> > 'GRANT privilege ON object TO role' system.
> >
> > For those cases where our GRANT system is unable to provide the
> > granularity desired and it isn't sensible to extend the GRANT system to
> > cover the exact granularity we wish to provide, we needed to come up
> > with an alternative approach. That approach is the use of special
> > default roles, where we can allow exactly the level of granularity
> > desired and give administrators a way to grant those privileges to
> > users.
> >
> > What this means in a nutshell is that we've collectivly decided that
> > we'd rather support:
> >
> > /* uses the 'GRANT role TO role' system */
> > GRANT pg_signal_backend TO test_user;
> >
> > than:
> >
> > /*
> > * uses the 'GRANT privilege ON object TO role' system
> > * seems like cluster-level is actually the right answer here, but
> > * we don't support such an object type currently, so using database
> > * for the example
> > */
> > GRANT SIGNAL BACKEND ON DATABASE my_database TO test_user;
> >
> > The goal being that the result of the two commands is identical (where
> > the second command is only hypothetical at this point, but hopefully the
> > meaning is conveyed).
>
> I quite understand all of that. The problem isn't that I don't
> understand what you did. The problem is that I don't agree with it.

Ok. Your prior comment was "I don't get it." That's why I was
trying to explain the intent and the reasoning behind it. I guess I
misunderstodd what you meant with that comment.

> I don't think that the way you implemented is a good idea, nor do I
> think it reflects the consensus that was reached on list. There was
> agreement to create some special magic roles. There was not agreement
> around the special magic you've sprinkled on those roles that makes
> them work unlike all other roles in the system, and I think that
> special magic is a bad idea.

The concern was raised by Noah about these users being able to own
objects here: 20160221022639(dot)GA486055(at)tornado(dot)leadboat(dot)com(dot) Admittedly,
he merely brought it up as a potential concern without specifying a
preference, but after considering it, I realized that there are issues
with default roles being able to own objects and attempted to address
that issue. I don't think it would have been appropriate to ignore
these issues.

> > However, GRANT'ing a role to a user traditionally also allows the user
> > to 'SET ROLE' to that user, to create objects as that user, and other
> > privileges. This results in two issues, as I see it:
> >
> > 1) The administrator who is looking to grant the specific 'signal
> > backend' privilege to a user is unlikely to intend or desire for that
> > user to also be able to SET ROLE to the role, or for that user to be
> > able to create objects as the default role.
>
> I don't think being able to SET ROLE to that role is something that we
> should be trying to prevent. You're already discovering why. You
> tried to create this new special kind of role that you can't become,
> and there are already various reports of cases that you've missed.
> You will keep finding more for a while, I predict. If that role is
> minimally privileged, who cares if users can become it?

Primairly because objects could be created as that user. I agree that
in well run systems, it's unlikely that will happen, but we can't simply
trust that if we wish to be able to remove or change these roles down
the road.

> > 2) If the default role ends up owning objects then we have much less
> > flexibility in making changes to that role because we must ensure
> > that those objects are preserved across upgrades, etc.
>
> Tom already said his piece on this point, and I think he's right. You
> re-stating the argument doesn't change that. In any case, if we want
> to prevent this, I bet there's some less buggy and invasive way in
> which it could be done than what you've actually chosen.

I re-stated the concern because I don't think it should be missed or
overlooked lightly because it implies requirements on us down the road,
particularly as I had understood from your prior response that it
wasn't clear what the concern was.

I don't mind removing the checks which were added to attempt to prevent
these roles from owning objects, to be clear. It'd certainly simplify
things, today. My concern, and the reason they were added is simply
that it'll complicate things down the road.

Thanks!

Stephen