Re: Default Roles (was: Additional role attributes)

Fujii,

* Fujii Masao (masao(dot)fujii(at)gmail(dot)com) wrote:
> On Tue, Jul 14, 2015 at 3:46 AM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> > Possibly, but I'd need to look at them more closely than I have time to
> > right now. Can you provide a use-case? That would certainly help.
>
> I have seen the monitoring system which periodically executes
> the statement like
>
> SELECT * FROM pgstattuple('hoge');
>
> to monitor the relation's physical length, the number of dead
> tuples, etc. Then, for example, if the number of dead tuples is
> increased unexpectedly and the relation becomes bloated, DBA tries
> to find out the cause and execute the maintenance commands
> if necessary to alleviate the situation. The monitoring system calls
> pgstattuple() at off-peak times because pgstattuple() needs to
> scan all the pages in the relation and its performance penalty
> might be big.
[...]
> Currently only superusers can call pgstattuple().

I started looking into this.

If we were starting from a green field, the pg_dump dump catalog ACLs
patch would work just fine for this case. Simply remove the superuser
checks and REVOKE EXECUTE from public in the script and we're done.

Unfortunately, we aren't, and that's where things get complicated. The
usual pg_upgrade case will, quite correctly, dump out the objects
exactly as they exist from the 9.5-or-earlier system and restore them
into the 9.6 system, however, the new .so will be installed and that .so
won't have the superuser checks in it.

The only approach to addressing this which I can think of offhand would
be to have the new .so library check the version of the extension and,
for the 1.3 (pre-9.6) and previous versions, keep the superuser check,
but skip it for 1.4 (9.6) and later versions.

I'm certainly open to other suggestions, of course. Would be great to
remove those superuser() checks and allow non-superusers to be GRANT'd
the right to run those functions, as discussed.

Thanks!

Stephen