Re: Default Roles (was: Additional role attributes)

On Tue, Jul 14, 2015 at 3:46 AM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> Fujii,
>
> * Fujii Masao (masao(dot)fujii(at)gmail(dot)com) wrote:
>> he documents of the functions which the corresponding default roles
>> are added by this patch need to be updated. For example, the description
>> of pg_xlog_replay_pause() says "Pauses recovery immediately (restricted
>> to superusers).". I think that the description needs to mention
>> the corresponding default role "pg_replay". Otherwise, it's difficult for
>> users to see which default role is related to the function they want to use.
>> Or probably we can add the table explaining all the relationships between
>> default roles and corresponding operations. And it's useful.
>
> Certainly, totally agree that we need to make it clear in the function
> descriptions also.
>
>> Why do we allow users to change the attributes of default roles?
>> For example, ALTER ROLE default_role or GRANT ... TO default_role.
>> Those changes are not dumped by pg_dumpall. So if users change
>> the attributes for some reasons but they disappear via pg_dumpall,
>> maybe the system goes into unexpected state.
>
> Good point. I'm fine with simply disallowing that completely; does
> anyone want to argue that we should allow superusers to ALTER or GRANT
> to these roles? I have a hard time seeing the need for that and it
> could make things quite ugly.
>
>> I think that it's better to allow the roles with pg_monitor to
>> execute pgstattuple functions. They are usually used for monitoring.
>> Thought?
>
> 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.

> Also, we are mostly focused on things which are currently superuser-only
> capabilities, if you don't need to be superuser today then the
> monitoring system could be granted access using the normal mechanisms.

Currently only superusers can call pgstattuple().

Regards,

--
Fujii Masao