Re: Monitoring roles patch

On Tue, Mar 28, 2017 at 12:04 PM, Mark Dilger <hornschnorter(at)gmail(dot)com> wrote:
>
>> On Mar 28, 2017, at 8:34 AM, Dave Page <dpage(at)pgadmin(dot)org> wrote:
>>
>> On Tue, Mar 28, 2017 at 11:31 AM, Peter Eisentraut
>> <peter(dot)eisentraut(at)2ndquadrant(dot)com> wrote:
>>> This patch touches the pg_buffercache and pg_freespacemap extensions,
>>> but there appear to be some files missing.
>>
>> Are you looking at an old version? There was one where I forgot to add
>> some files, but that was fixed within an hour or so in a new version.
>>
>> Right now I'm waiting for discussion to conclude before updating the
>> patch again.
>
> There does not seem to be a new patch since Robert made his "modest proposal",
> so I guess I just have to ask questions about how this would work.

There hasn't been one yet.

> I don't see any precedent in the code for having a hardcoded role, other than
> superuser, and allowing privileges based on a hardcoded test for membership
> in that role. I'm struggling to think of all the security implications of that.

This would be the first.

> If I have even one table in my database which is security sensitive, such that
> I cannot allow users to see the size of the table, nor whether the table has
> unvacuumed rows (owing to the fact that would give away that it has been
> changed since the last vacuum time), then I can't use pg_real_all_stats for
> anything, right? And I would need to exercise some due diligence to make
> certain it does not get granted to anybody?

Right.

> What happens if I execute:
>
> REVOKE ALL ON TABLE mysecuretable FROM pg_read_all_stats?
>
> Does it work?

Yes, for the documented use of GRANT/REVOKE on a table.

> Does it silently fail? Does it raise an exception?

No and no.

> Does
> pg_read_all_stats still have access to stats for mysecuretable?

Yes, because the ACL on the table controls reading/writing the data in
the table. It doesn't have any bearing on any kind of table metadata.
A user who has no privileges on a table can already look at (for
example) pg_stat_all_tables and see the sort of info you're talking
about. This patch would just allow members of a specific role get the
on-disk size as well, *if* you choose to use it.

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company