Re: Monitoring roles patch

On Fri, Mar 24, 2017 at 8:30 AM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
>> But why not do it with GRANTs in the first place then?
>
> This is akin to asking why do we need GRANT ALL and ALTER DEFAULT PRIVs.

Not really. ALTER DEFAULT PRIVILEGES affects what happens for future
objects, which isn't necessary here at all. The monitoring tool only
needs to be granted the minimum set of privileges that it currently
needs, not future privileges which it or other monitoring tools may
need in future versions. GRANT ALL is closer, but GRANT .. ON ALL
TABLES IN SCHEMA really is just a convenience function. You would
still have the capability to do the exact same thing without that
function; it would just be more work. And the same is true here. I
understand why you and Dave want to make this a single command: that's
easier. But, like GRANT ON ALL TABLES, it's also less flexible. If
you want to grant on all tables except one, you can't use that magic
syntax any more, and so here. pg_monitor will either target one
particular monitoring solution (hey, maybe it'll be the one my
employer produces!) or the judgement of one particular person
(whatever Stephen Frost thinks it SHOULD do in a given release,
independent of what tools on the ground do) and those aren't good
definitions.

> If we make the users run all the statements individually then they'll
> also have to get an updated script for the next version of PG too
> because we will have added things that the tools will want access to.

That's possible, but it really depends on the tool, not on core
PostgreSQL. The tool should be the one providing the script, because
the tool is what knows its own permissions requirements. Users who
care about security won't want to grant every privilege given by a
pg_monitor role to a tool that only needs a subset of those
privileges.

> Further, they'll have to make sure to install all the contrib modules
> they want to use before running the GRANT script which is provided, or
> deal with GRANT'ing the rights out after installing some extension.

That doesn't sound very hard.

> With the approach that Dave and I are advocating, we can avoid all of
> that. Contrib modules can bake-in GRANTs to the appropriate roles,
> upgrades can be handled smoothly even when we add new capabilities which
> are appropriate, users have a simple and straight-forward way to set up
> good monitoring, and tool authors will know what permissions are
> available and can finally have a better answer than "well, just make the
> monior user superuser if you want to avoid all these complexities."

They can have that anyway. They just have to run a script provided by
the tool rather than one provided by the core project as a
one-size-fits-all solution for every tool.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company