Re: Additional role attributes && superuser review

* Simon Riggs (simon@2ndQuadrant.com) wrote:
> On 15 October 2014 06:22, Stephen Frost <sfrost@snowman.net> wrote:
> >   BACKUP:
> >     pg_start_backup()
> >     pg_stop_backup()
> >     pg_switch_xlog()
> >     pg_create_restore_point()
>
> Yes, but its more complex. As Jim says, you need to include pg_dump,
> plus you need to include the streaming utilities, e.g. pg_basebackup.

I'd rather have more, simpler, role attributes than one 'catch-all'.

Once I understand what "include pg_dump" and "include pg_basebackup"
mean, I'd be happy to work on adding those.

> >   LOGROTATE:
> >     pg_rotate_logfile()
>
> Do we need one just for that?

It didn't seem to "belong" to any others and it's currently limited to
superuser but useful for non-superusers, so I would argue 'yes'.  Now,
another option (actually, for many of these...) would be to trust in our
authorization system (GRANT EXECUTE) and remove the explicit superuser
check inside the functions, revoke EXECUTE from public, and tell users
to GRANT EXECUTE to the roles which should be allowed.

> >   MONITOR:
> >     View detailed information regarding other processes.
> >     pg_stat_get_wal_senders()
> >     pg_stat_get_activity()
>
> +1
>
> >   Connect using 'reserved' slots
> >     This is another one which we might add as another role attribute.
>
> RESERVED?

Seems reasonable, but do we need another GUC for how many connections
are reserved for 'RESERVED' roles?  Or are we happy to allow those with
the RESERVED role attribute to contend for the same slots as superusers?

For my 2c- I'm happy to continue to have just one 'pool' of reserved
connections and just allow roles with RESERVED to connect using those
slots also.

> Perhaps we need a few others also - BASHFUL, HAPPY, GRUMPY etc

Hah. :)

There was a suggestion raised (by Robert, I believe) that we store these
additional role attributes as a bitmask instead of individual columns.
I'm fine with either way, but it'd be a backwards-incompatible change
for anyone who looks at pg_authid.  This would be across a major version
change, of course, so we are certainly within rights to do so, but I'm
also not sure how much we need to worry about a few bytes per pg_authid
row; we still have other issues if we want to try and support millions
of roles, starting with the inability to partition catalogs..
Thanks!
    Stephen