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.