Re: REVOKE FROM warning on grantor

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.
> (The spelling of the message has changed over the years, but giving a
> warning not an error appears to go all the way back to 99b8f8451
> where we implemented user groups.) It is certain that there are
> applications out there that rely on this behavior and would break.

I got curious about the behavior of other database systems.

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

https://community.snowflake.com/s/article/Access-Control-Error-Message-When-Revoking-a-Non-existent-Role-Grant-From-a-Role-or-User
is kind of interesting. It says that such commands used to fail with
an error but that's been changed; now they don't.

I couldn't find a clear reference for Oracle or DB-2 or SQL server,
but it doesn't look like any of them have an IF EXISTS option, and the
examples they show don't mention this being an issue AFAICS, so I'm
guessing that all of them accept commands of this type without error.

On the whole, it seems like we might be taking the majority position
here, but I can't help but feel some sympathy with people who don't
like it. Maybe we need a REVOKE OR ELSE command. :-)

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