Re: Monitoring roles patch

> On Mar 28, 2017, at 1:17 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Mark Dilger <hornschnorter(at)gmail(dot)com> writes:
>> I don't see anything wrong with adding roles in pg_authid.h with a #define'd
>> Oid. That's actually pretty helpful for anyone writing code against the database,
>> as they don't have to look up the Oid of the role.
>
>> But why not then grant privileges to that role in information_schema.sql?
>
> To the extent that the desired privileges can be represented at the SQL
> level, I agree that that's a better solution than hard-wiring checks in C
> code. The problem comes in with cases where that's not fine-grained
> enough. Consider a policy like "anybody can select from pg_stat_activity,
> but unless you have privilege X, the query column should read as nulls
> except for sessions belonging to you". That behavior can't realistically
> be represented as a separate SQL privilege. Right now we have "privilege
> X" for this purpose hard-coded as "is superuser", but it would be much
> better if it were associated with a grantable role.

Many thanks for all the explanation. I now understand better what the
patch is trying to do, and have (with some experimentation) seen flaws
in what I was saying upthread. I find the notion of a role not being a
group of privileges but instead actually being a privilege confusing, and
it made it hard to think about the security implications of the proposal.
I'm accustomed to the idea of being able to revoke a privilege from a
role, and that doesn't work if the "role" is in some sense a privilege, not
a role. I might find it all easier to think about if we named these things
privileges and not roles, like pg_read_stats_privilege instead of
pg_read_stats_role, and then we could grant pg_read_stats_privilege
to roles. But I recall you were saying upthread that you did not want to
have privilege bits for each of these types of things. I hope this feature
is worth the confusion it causes.

mark