Re: Monitoring roles patch

On Mon, Mar 27, 2017 at 3:51 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> On 25 March 2017 at 16:30, Dave Page <dpage(at)pgadmin(dot)org> wrote:
>
>> I believe this and other reasons we've described are exactly why other DBMS' do what we're proposing.
>
> It would help review if you could show some links and give a
> commentary on what you think others do, what they get right and what
> they get wrong, so we can be sure we are providing something people
> actually want and/or expect. POLA needed. I don't want to be reading
> various blogs about what those numpties on the Postgres project did in
> v10. Thanks

Most other DBMSs seem to provide either capabilities (or privileges,
whatever they may be called by the vendor) that can be assigned to
roles, or pre-defined roles with capabilities, or some combination of
the two.

SQL Server provides a number of server and database level roles that
are pre-configured for specific tasks, with set of capabilities. See
https://msdn.microsoft.com/en-us/library/ms189612.aspx for example.

DB2 appears to provide capabilities that can be assigned to roles. See
https://www.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.admin.sec.doc/doc/c0050531.html

Oracle has something of a mix or roles and capabilities, eg. the DBA
role and SYSOPER privileges, e.g.
https://docs.oracle.com/cd/B28359_01/server.111/b28310/dba005.htm#ADMIN11040

What is being proposed here is a similar system, but focussing on
pre-defined roles. These make it easy to grant privileges for specific
purposes en-masse, without requiring the user to use them, i.e.
they're free to ignore them if they wish. As they are roles, they also
have the freedom to extend or restrict them in cases where privileges
are acquired through GRANT.

I believe this offers both the greatest flexibility and the most
straightforward and easy to use interface for the end user - the
ability to customise is maximised, whilst the default roles will be
both safe to use and should work out of the box for the majority of
monitoring scenarios.

The most important thing is that we'll be able to stop users having to
grant superuser privileges to their monitoring roles.

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company