Re: Additional role attributes && superuser review - Mailing list pgsql-hackers

* Bruce Momjian (bruce@momjian.us) wrote:
> On Wed, Jan  6, 2016 at 12:29:14PM -0500, Robert Haas wrote:
> > The point is that with the GRANT EXECUTE ON FUNCTION proposal, authors
> > of monitoring tools enjoy various really noteworthy advantages.  They
> > can have monitoring roles which have *exactly* the privileges that
> > their tool needs, not whatever set of permissions (larger or smaller)
> > the core project has decide the pg_monitor role should have.  They can
> > have optional features requiring extra permissions and those extra
> > permissions can be granted in precisely those shops where those extra
> > features are in use.  They can deploy a new versions of their
> > monitoring tool that requires an extra privilege on an existing
> > PostgreSQL release without requiring any core modifications, which
> > shaves years of time off the deployment schedule and avoids
> > contentious arguments with the lovable folks who populate this mailing
> > list.  That sounds *terrific* to me compared to the alternative you
> > are proposing.
>
> I assume backup tools would either document the functions they want
> access to via SQL commands, or supply a script.  I assume they would
> create a non-login role (group) with the desired permissions, and then
> have users inherit from that.  They would also need to be able to allow
> upgrades where they would (conditionally?) add the role and then
> add/revoke permissions as needed, e.g. they might not need all
> permissions they needed in a previous release, or they might need new
> ones.
>
> That all seems very straight-forward to me.

I'm not against that idea, though I continue to feel that there are
common sets of privileges which backup tools could leverage.

The other issue that I'm running into, again, while considering how to
move back to ACL-based permissions for these objects is that we can't
grant out the actual permissions which currently exist.  That means we
either need to break backwards compatibility, which would be pretty
ugly, in my view, or come up with new functions and then users will have
to know which functions to use when.

As I don't think we really want to break backwards compatibility or
remove existing functionality, the only approach which is going to make
sense is to add additional functions in some cases.  In particular, we
will need alternate versions of pg_terminate_backend and
pg_cancel_backend.  One thought I had was to make that
'pg_signal_backend', but that sounds like we'd allow any signal sent by
a user with that right, which seems a bit much to me...

Perhaps that's what I'll do though, barring other suggestions.

Thanks!

Stephen