Re: REVOKE to an user that doesn't exist

Moreno Andreo <moreno(dot)andreo(at)evolu-s(dot)it> writes:
> I'm trying to pg_upgrade a cluster from 9.1 to 10 (Windows 10, but I
> don't think it matters).

> At a certain point an error is thrown while parsing a trigger:
> could not execute query: ERROR:  role "1067431" does not exist
> command was: REVOKE ALL ON FUNCTION "x"() FROM PUBLIC;
> GRANT ALL ON FUNCTION "public"."x"() TO "1067431" WITH GRANT OPTION;

Hm. We've seen similar bugs before; the mechanism is that at some
point the function owner granted privileges to somebody else, and
at some later point the somebody-else role got dropped, but the
privilege grant stayed behind because the system had lost, or never
made, the pg_shdepend entry indicating that this function had an ACL
entry mentioning that role. The extra ACL entry is harmless, until
you wonder why pg_dump is printing a nonsensical command due to it.

We fixed a couple of bugs of that ilk just last month [1], but they were
for cases involving types and schemas, not functions. The last case
involving function privileges that I see in a quick trawl of the commit
log predates 9.0 release [2]. I wonder how old this cluster is ...

> How to REVOKE that non-existing user so pg_upgrade can proceed?

The safest way to clean it up manually would be to set the pg_proc.proacl
field for that function to NULL. If there are other grants about the
function, you could try removing the bad entry, but it would likely be
safer to just re-grant after the upgrade.

regards, tom lane

[1] https://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=fa2952d8e
[2] https://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=d691cb914