Re: Additional role attributes && superuser review

On Wed, Dec 24, 2014 at 6:48 PM, Adam Brightwell <
adam(dot)brightwell(at)crunchydatasolutions(dot)com> wrote:

> All,
>
> I want to revive this thread and continue to move these new role
> attributes forward.
>
> In summary, the ultimate goal is to include new role attributes for common
> operations which currently require superuser privileges.
>
> Initially proposed were the following attributes:
>
> * BACKUP - allows role to perform backup operations
> * LOGROTATE - allows role to rotate log files
> * MONITOR - allows role to view pg_stat_* details
> * PROCSIGNAL - allows role to signal backend processes
>
> It seems that PROCSIGNAL and MONITOR were generally well received and
> probably don't warrant much more discussion at this point.
>
> However, based on previous discussions, there seemed to be some
> uncertainty on how to handle BACKUP and LOGROTATE.
>
> Concerns:
>
> * LOGROTATE - only associated with one function/operation.
> * BACKUP - perceived to be too broad of a permission as it it would
> provide the ability to run pg_start/stop_backend and the xlog related
> functions. It is general sentiment is that these should be handled as
> separate privileges.
>
* BACKUP - preferred usage is with pg_dump to giving a user the ability to
> run pg_dump on the whole database without being superuser.
>
> Previous Recommendations:
>
> * LOGROTATE - Use OPERATOR - concern was expressed that this might be too
> general of an attribute for this purpose. Also, concern for privilege
> 'upgrades' as it includes more capabilities in later releases.
> * LOGROTATE - Use LOG_OPERATOR - generally accepted, but concern was raise
> for using extraneous descriptors such as '_OPERATOR' and '_ADMIN', etc.
> * BACKUP - Use WAL_CONTROL for pg_start/stop_backup - no major
> disagreement, though same concern regarding extraneous descriptors.
> * BACKUP - Use XLOG_OPERATOR for xlog operations - no major disagreement,
> though same concern regarding extraneous descriptors.
> * BACKUP - Use BACKUP for granting non-superuser ability to run pg_dump on
> whole database.
>
> Given the above and previous discussions:
>
> I'd like to propose the following new role attributes:
>
> BACKUP - allows role to perform pg_dump* backups of whole database.
>

I'd suggest it's called DUMP if that's what it allows, to keep it separate
from the backup parts.

> WAL - allows role to execute pg_start_backup/pg_stop_backup functions.
>
XLOG - allows role to execute xlog operations.

That seems really bad names, IMHO. Why? Because we use WAL and XLOG
throughout documentation and parameters and code to mean *the same thing*.
And here they'd suddenly mean different things. If we need them as separate
privileges, I think we need much better names. (And a better description -
what is "xlog operations" really?)

And the one under WAL would be very similar to what REPLICATION does today.
Or are you saying that it should specifically *not* allow base backups done
through the replication protocol, only the exclusive ones?

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/