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

Robert, all,

* Stephen Frost (sfrost@snowman.net) wrote:
> * Stephen Frost (sfrost@snowman.net) wrote:
> > * Robert Haas (robertmhaas@gmail.com) wrote:
> > > The tricky part of this seems to me to be the pg_dump changes.  The
> > > new catalog flag seems a little sketchy to me; wouldn't it be better
> > > to make the dump flag into an enum, DUMP_EVERYTHING, DUMP_ACL,
> > > DUMP_NONE?
> >
> > I agree that the pg_dump changes are a very big part of this change.
> > I'll look at using an enum and see if that would work.
>
> I've implemented this approach and there are things which I like about
> it and things which I don't.  I'd love to hear your thoughts.  As
> mentioned previously, this patch does not break the pg_stat_activity or
> pg_stat_replication view APIs.  Similairly, the functions which directly
> feed those views return the same results they did previously, there are
> just additional functions now which provide everything, and view on top
> of those, for users who are GRANT'd access to them.

Here is the latest revision of this patch.

The big change here is the addition of default roles.  This approach has
been brought up a few times and Magnus recently mentioned it to me
again.  Having the default roles greatly reduced the impact of this
change on the test_deparse regression test, which was quite nice.

Updates are included for pg_upgrade and pg_dumpall to handle roles which
start with "pg_" specially as we are now claiming those as "System
defined" roles (similar to how we claim schemas starting with pg_ are
system defined, etc).  These new default roles are in line with the
previously discussed role attributes, but have the advantage that they
act just like normal roles and work inside of the normal permissions
system.  They are:

pg_backup - Start and stop backups, switch xlogs, create restore points.
pg_monitor - View privileged system info (user activity, replica lag)
pg_replay - Control XLOG replay on replica (pause, resume)
pg_replication - Create, destroy, work with replication slots

pg_admin - All of the above, plus rotate logfiles and signal backends

Feedback on all of this would be great.  One interesting idea is that,
with these defined default roles, we could rip out the majority of the
changes to pg_dump and declare that users have to use only the roles we
provide to manage access to those functions (or risk any changes they
make to the ACLs of system objects disappearing across upgrades or
pg_dump/restore's, which is what happens today anyway).  I'm a bit on
the fence about it myself; it'd certainly reduce the risk of this change
but it would also limit users to only being able to operate at the
pre-defined levels we've set, but then again, the same was going to be
true with the role attributes-based approach and I don't recall any
complaints during that discussion.

Thoughts?  Feedback on this would be most welcome; it's been a long time
incubating and I'd really like to get this capability in and close it
out of the current commitfest.  I'm certainly of the opinion that it
will be a welcome step forward for quite a few of our users as the
discussion about how to create non-superuser roles for certain
operations (a monitor role, in particular, but also backup and replay)
has come up quite a bit, both on the lists and directly from clients.

    Thanks!

        Stephen