Re: role self-revocation

On Sun, Mar 6, 2022 at 11:01 PM David G. Johnston
<david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
> 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"".

This doesn't seem to me to be making a constructive argument. I showed
an example with certain names demonstrating a certain behavior that I
find problematic. You don't have to think it's problematic, and you
can show other examples that demonstrate things you want to show. But
please don't tell me that when I literally cut and paste the output
from my terminal into an email window, what I'm showing is somehow
counterfactual. The behavior as it exists today is surely a fact, and
an easily demonstrable one at that. It's not a "fact'" in quotes, and
it doesn't "get it wrong". It is the actual behavior and the example
with the names I picked demonstrates precisely what I want to
demonstrate. When you say that I should have chosen a different
example or used different identifier names or talked about it in
different way, *that* is an opinion. I believe that you are wholly
entitled to that opinion, even if (as in this case) I disagree, but I
believe that it is not right at all to make it sound as if I don't
have the right to pick the examples I care about, or as if terminal
output is not a factual representation of how things work today.

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

It clearly can in some limited cases, because I showed an example
demonstrating *exactly that thing*.

> 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.

I agree with this, but that's not my concern, because that's a
different use case from the one that I complained about. Since the
session user exception only applies to login roles, the problem that
I'm talking about only occurs when a login role is granted to some
other role.

> 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.

I disagree with this on principle, and I also think that's not how it
works today. On the general principle, I do not see a compelling
reason why we should have two systems for maintaining groups of users,
one of which is used for additive things and one of which is used for
subtractive things. That is a lot of extra machinery for little gain,
especially given how close we are to having it sorted out so that the
same mechanism can serve both purposes. It presently appears to me
that if we either remove the session user exception OR do the
grantor-tracking thing discussed earlier, we can get to a place where
the same facility can be used for either purpose. That would, I think,
be a significant step forward over the status quo. In terms of how
things work today, see Joshua Brindle's email about the use of groups
in pg_hba.conf. That is an excellent example of how removing oneself
from a group could enable one to bypass security restrictions intended
by the DBA.

--
Robert Haas
EDB: http://www.enterprisedb.com