Re: Monitoring roles patch - Mailing list pgsql-hackers

Robert,

* Robert Haas (robertmhaas@gmail.com) wrote:
> On Fri, Mar 24, 2017 at 8:30 AM, Stephen Frost <sfrost@snowman.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.

I wasn't very clear with my thinking as to why I felt it was akin to
those other commands, please let me explain it a bit more clearly.

Consider if all the needs of a monitoring role existed in a 'monitor'
schema, and ignore the fact that pg_upgrade ignores default privileges
on upgrade and instead think of this from the perspective of developing
an application in PG.

In such a case, users could create a 'monitor' role, GRANT ALL to that
user on the functions and tables and views in the 'monitor' schema, and
set up DEFAULT PRIVs for the monitor role to have access to such future
functions/tables/etc as might be created in the future in that schema.

There's no way for users to do that today with those pieces of the
system which are associated with monitoring.  We are proposing given a
way for users to do that, with this proposed default role.

> 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.

I don't believe the assertion that when we added GRANT ON ALL TABLES
that we reduced flexibility of the system.  I do agree that it's a
convenience function- a valuable enough one that we added it.  It is
certainly possible to have exceptions to GRANT ON ALL TABLES- you simply
remove those privileges which were GRANT'd, in the same transaction.
You are correct that it wouldn't be possible to remove pg_monitor's
rights and users would instead need to GRANT just those privileges they
wish their monitor user to have, but that is the exception to the rule.

As it relates to tools, I might be wrong, but it certainly seems like
Dave and I, from two independent consulting organizations, are on the
same page when it comes to what this pg_monitor role should have access
to, with, frankly, very little discussion needed.

The point about least-privilege is certainly one I understand, but I
don't believe it's really properly applied here.  While a given
monitoring tool might not use function X today and therefore from a
strict least-privilege standpoint it shouldn't have access to it, but
what is the risk of GRANT'ing that access to the monitor user?  Is the
expectation that a future version of that tool will need access to that
function?  If the risk is low and the expectation is that the tool will
need access in the future, then GRANT'ing the access is entirely
reasonable.

Consider that an accountant might not need access to the
employee/manager relationships today because they are processing only
invoices.  Tomorrow, however, they will need access because they are
reviewing Purchase Requests.  Does it make sense to only GRANT the
access they will actually use at the start of each day?

These are all trade-offs to consider between the risk of granting access
and not.  There will undoubtably be environments where the tool will
actually support using function X, but the user doesn't feel comfortable
with giving the monitoring user even that access and, therefore, tools
will need to at least fail gracefully in such cases, but those are the
exceptions, and will be happy to carefully craft their privileges based
on their own determination of the trade-offs.  For the vast majority of
users, allowing the monitor user access to basically everything, as long
as it can't become super-user or directly view/modify the data, will
cover the attack vectors they are most concerned with, just like the
GRANT ON ALL and ALTER DEFAULT PRIVS satisfy very similar use-cases for
a lot of users.

> > 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.

The one-size-fits-all solution actually works perfectly because it's
"all the privileges needed to monitor PG for version X".  We're not
asking for some set of privileges which is tailored to Dave's monitoring
solution, or which is tailored to Stephen's monitoring solution, or to
check_postgres, or to some other specific monitoring tool.  In the
general sense, it isn't actually very difficult to define what
capabilities PG has to assist monitoring of the system, nor to define
what privileges monitoring tools should have on those capabilities.

Maybe that will become an issue some day, but I tend to doubt it-
certainly the other systems to which PG is regularly compared have
solved this, even in cases where their monitoring capabilities are far
beyond what we support today.

Thanks!

Stephen