Re: Additional role attributes && superuser review

On Thu, Jan 28, 2016 at 11:04 AM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
>> So, this seems like a case where a built-in role would be
>> well-justified. I don't really believe in built-in roles as a way of
>> bundling related permissions; I know you do, but I don't. I'd rather
>> see the individual function permissions granted individually. But
>> here you are talking about a variable level of access to the same
>> function, depending on role. And for that it seems to me that a
>> built-in role has a lot more to recommend it in that case than it does
>> in the other. If you have been granted pg_whack, you can signal any
>> process on the system; otherwise just your own. Those checks are
>> internal to pg_terminate_backend/pg_cancel_backend so GRANT is not a
>> substitute.
>
> If we extend this into the future then it seems to potentially fall
> afoul of Noah's concern that we're going to end up with two different
> ways of saying GRANT EXECUTE X ON Y. Consider the more complicated case
> of pg_stat_activity, where values are shown or hidden based on who the
> current role is. The policy system only supports whole-row, today, but
> the question has already come up, both on the lists and off, of support
> for hiding individual column values for certain rows, exactly as we do
> today for pg_stat_activity. Once we reach that point, we'll have a way
> to GRANT out these rights and a default role which just has them.

Well, I'm not saying that predefined rolls are the *only* way to solve
a problem like this, but I think they're one way and I don't clearly
see that something else is better. However, my precise point is that
we should *not* have predefined rolls that precisely duplicate the
result of GRANT EXECUTE X ON Y. Having predefined rolls that change
the behavior of the system in other ways is a different thing. So I
don't see how this falls afoul of Noah's concern. (If it does,
perhaps he can clarify.)

> Personally, I don't have any particular issue having both, but the
> desire was stated that it would be better to have the regular
> GRANT EXECUTE ON catalog_func() working before we consider having
> default roles for same. That moves the goal posts awful far though, if
> we're to stick with that and consider how we might extend the GRANT
> system in the future.

I don't think it moves the goal posts all that far. Convincing
pg_dump to dump grants on system functions shouldn't be a crazy large
patch.

> What got me thinking along these lines was a question posed by Bruce
> (Bruce, feel free to chime in if I've misunderstood) to me at SCALE
> where we were chatting a bit about this, which was- how could we extend
> GRANT to support the permissions that we actually wish
> pg_terminate_backend() and pg_cancel_backend() to have, instead of using
> a default role? I didn't think too much on it at the time as it strikes
> me as a pretty narrow use-case and requiring quite a bit of complexity
> to get right, but there again, I'd certainly view a system where the
> user is allowed to have pg_start_backup() rights but not
> pg_stop_backup() as being quite a small use-case also, yet that's the
> direction we're largely going in with this discussion.

Well, sure, in that largely artificial example it's not hard to say
ha, ha, silly, but the actual examples we looked at upthread were much
less obviously silly. There was plenty of room for argument about the
precise contours of each predefined role.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company