Re: role self-revocation

On Sun, Mar 6, 2022 at 8:19 AM Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> The choice of names in my example wasn't accidental. If the granted
> role is a login role, then the superuser's intention was to vest the
> privileges of that role in some other role, and it is surely not right
> for that role to be able to decide that it doesn't want it's
> privileges to be so granted. That's why I chose the name "peon".

>> rhaas [as peon] => revoke peon from boss; -- i don't like being bossed
around!

Well, the peon is not getting bossed around, the boss is getting peoned
around and the peon has decided that they like boss too much and don't need
to do that anymore.

When you grant a group "to" a role you place the role under the group - and
inheritance flows downward.

In the original thread Stephen wrote:

"This is because we allow 'self administration' of roles, meaning that
they can decide what other roles they are a member of.:

The example, which you moved here, then attempts to demonstrate this "fact"
but gets it wrong. Boss became a member of peon so if you want to
demonstrate self-administration of a role's membership in a different group
you have to login as boss, not peon. Doing that, and then revoking peon
from boss, yields "ERROR: must have admin option on role "peon"".

So no, without "WITH ADMIN OPTION" a role cannot decide what other roles
they are a member of.

I don't necessarily have an issue changing self-administration but if the
motivating concern is that all these new pg_* roles we are creating are
something a normal user can opt-out of/revoke that simply isn't the case
today, unless they are added to the pg_* role WITH ADMIN OPTION.

That all said, permissions SHOULD BE strictly additive. If boss doesn't
want to be a member of pg_read_all_files allowing them to revoke themself
from that role seems like it should be acceptable. If there is fear in
allowing someone to revoke (not add) themselves as a member of a different
role that suggests we have a design issue in another feature of the
system. Today, they neither grant nor revoke, and the self-revocation
doesn't seem that important to add.

David J.