Re: REVOKE FROM warning on grantor

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Sat, Mar 16, 2024 at 8:17 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Even taking the position that this is an unspecified point that we
>> could implement how we like, I don't think there's a sufficient
>> argument for changing behavior that's stood for a couple of decades.

> I got curious about the behavior of other database systems.

Yeah, I was mildly curious about that too; it'd be unlikely to sway
my bottom-line opinion, but it would be interesting to check.

> https://dev.mysql.com/doc/refman/8.0/en/revoke.html documents an "IF
> EXISTS" option whose documentation reads, in relevant part,
> "Otherwise, REVOKE executes normally; if the user does not exist, the
> statement raises an error."

Hmm, I don't think that's quite what's at stake here. We do throw
error if either named role doesn't exist:

regression=# revoke foo from joe;
ERROR: role "joe" does not exist
regression=# create user joe;
CREATE ROLE
regression=# revoke foo from joe;
ERROR: role "foo" does not exist
regression=# create role foo;
CREATE ROLE
regression=# revoke foo from joe;
WARNING: role "joe" has not been granted membership in role "foo" by role "postgres"
REVOKE ROLE

What the OP is on about is that that last case issues WARNING not
ERROR.

Reading further down in the mysql page you cite, it looks like their
IF EXISTS conflates "role doesn't exist" with "role wasn't granted",
and suppresses errors for both those cases. I'm not in favor of
changing things here, but if we did, I sure wouldn't want to adopt
those exact semantics.

regards, tom lane