Re: Additional role attributes && superuser review

On 11/06/2014 03:31 AM, Robert Haas wrote:
> [snip]
>> We haven't reached consensus on this one yet and I didn't want it to fall
>> too far off the radar.
>>
>> Here is what I summarize as the current state of the discussion:
>>
>> 1. Syntax:
>>
>> ALTER ROLE <role> { ADD | DROP } CAPABILITY <capability>

Though a bit late to this thread, I would like to request comments on 
potentially beneficial new  roles ?? and/or capabilities which I have 
recently found needing myself.
The suggested syntax looks intuitive and potentially very flexible.
I'll try to summarize up what I recall from the thread plus my own 
itchs, to try and get others to comment and expand on the matter.

We currently have:    * SUPERUSER    / CREATEUSER    * CREATEDB    * CREATEROLE    * LOGIN    * REPLICATION

(plus INHERITS and ADMIN options, of course)

It has also been suggested to include a    * BACKUP role (capability?) i.e. ability to take an snapshot and 
read all relations, views, triggers and functions (even bypassing RLS) 
and the catalog in order to produce a full, consistent dump of the whole 
cluster.

and I seem to recall something along the lines of    * AUDIT, potentially limited to just engage

I am hereby suggesting the addition of a    * MAINTENANCE role, which would be able to perform VACUUM, ANALYZE, 
REINDEX *CONCURRENTLY* and REFRESH MATERIALIZED VIEW *CONCURRENTLY* ... 
and potentially even ALTER TABLE VALIDATE CONSTRAINT (if we are able to 
produce a non-blocking/fully concurrent version)
    ... which might become very useful for DBAs wishing to use some 
password-less roles for scheduled maintenance routines while at the same 
time reducing the exposure.


While at it, the replication role might as well gain the ability to 
promote/demote a cluster (standby<->active), or shall it be some kind of 
FAILOVER role/capability ?



Thanks in advance.
    / J.L.