Re: role self-revocation

On Sun, Mar 6, 2022 at 9:53 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Robert Haas <robertmhaas@gmail.com> writes:
> > ... Suppose the superuser grants "admin" to both "joe" and "sally".
> > Now "joe" can SET ROLE to "admin" and revoke it from "sally", and the
> > superuser has no tool to prevent this.
>
> Really?
>
> regression=# grant admin to joe;
> GRANT ROLE
> regression=# grant admin to sally;
> GRANT ROLE
> regression=# \c - joe
> You are now connected to database "regression" as user "joe".
> regression=> revoke admin from sally;
> ERROR:  must have admin option on role "admin"
> regression=> set role admin;
> SET
> regression=> revoke admin from sally;
> ERROR:  must have admin option on role "admin"
>
> I think there is an issue here around exactly what the admin option
> means, but if it doesn't grant you the ability to remove grants
> made by other people, it's pretty hard to see what it's for.
>
>
Precisely.

The current system, with the session_user exception, basically guides a
superuser to define two kinds of roles.

Groups: No login, permission grants
Users: Login, inherits permissions from groups, can manage group membership
if given WITH ADMIN OPTION.

The original example using only users is not all that compelling to me.
IMO, DBAs should not be setting up their system that way.

Two questions remain:

1. Are we willing to get rid of the session_user exception?

2. Do we want to track who the grantor is for role membership grants and
institute a requirement that non-superusers can only revoke the grants that
they personally made?

I'm personally in favor of getting rid of the session_user exception, which
nicely prevents the problem at the beginning of this thread and further
encourages the DBA to define groups and roles with a greater
separation-of-concerns design.  WITH ADMIN OPTION is sufficient.

I think tracking grantor information for role membership would allow for
greater auditing capabilities and a better degree of control in the
permissions system.

In short, I am in favor of both options.  The grantor tracking seems to be
headed for acceptance.

So, do we really want to treat every single login role as a potential group
by keeping the session_user exception?

David J.