REVOKE to an user that doesn't exist

On 12/12/18 5:11 AM, Moreno Andreo wrote:

Hi all,

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;

Here's the evidence :-)

The below is from the 9.1 cluster, correct?

postgres=# select * from pg_roles;
ￜￜￜￜ rolnameￜￜￜￜￜ | rolsuper | rolinherit | rolcreaterole | rolcreatedb
| rolcatupdate | rolcanlogin | rolreplication | rolconnlimit |
rolpassword | rolvaliduntil | rolconfig |ￜￜ oid
------------------+----------+------------+---------------+-------------+--------------+-------------+----------------+--------------+-------------+---------------+-----------+---------

ￜpostgresￜￜￜￜￜￜￜￜ | tￜￜￜￜￜￜￜ | tￜￜￜￜￜￜￜￜￜ | tￜￜￜￜￜￜￜￜￜￜￜￜ | t |
tￜￜￜￜￜￜￜￜￜￜￜ | tￜￜￜￜￜￜￜￜￜￜ | t |ￜￜￜￜￜￜￜￜￜￜ -1 | ******** |
|ￜￜￜￜￜￜￜￜￜￜ |ￜￜￜￜￜ 10
ￜuser1 ￜￜￜￜ | tￜￜￜￜￜￜￜ | tￜￜￜￜￜￜￜￜￜ | tￜￜￜￜￜￜￜￜￜￜￜￜ | t | t |
tￜￜￜￜￜￜￜￜￜￜ | tￜￜￜￜￜￜￜￜￜￜￜￜￜ |ￜￜￜￜￜￜￜￜￜￜ -1 | ******** |
|ￜￜￜￜￜￜￜￜￜￜ |ￜￜ 16393
ￜuser2 ￜￜￜ | tￜￜￜￜￜￜￜ | tￜￜￜￜￜￜￜￜￜ | tￜￜￜￜￜￜￜￜￜￜￜￜ | tￜￜￜￜￜￜￜￜￜￜ |
tￜￜￜￜￜￜￜￜￜￜￜ | tￜￜￜￜￜￜￜￜￜￜ | tￜￜￜￜￜￜￜￜￜￜￜￜￜ |ￜￜￜￜￜￜￜￜￜￜ -1 | ********
|ￜￜￜￜￜￜￜￜￜￜￜￜￜￜ |ￜￜￜￜￜￜￜￜￜￜ |ￜￜ 16394
ￜuser3 ￜￜￜￜￜ | fￜￜￜￜￜￜￜ | tￜￜￜￜￜￜￜￜￜ | fￜￜￜￜￜￜￜￜￜￜￜￜ | f | f |
tￜￜￜￜￜￜￜￜￜￜ | fￜￜￜￜￜￜￜￜￜￜￜￜￜ |ￜￜￜￜￜￜￜￜￜￜ -1 | ******** |
|ￜￜￜￜￜￜￜￜￜￜ |ￜￜ 16395
ￜuser4 | fￜￜￜￜￜￜￜ | tￜￜￜￜￜￜￜￜￜ | tￜￜￜￜￜￜￜￜￜￜￜￜ | tￜￜￜￜￜￜￜￜￜￜ |
fￜￜￜￜￜￜￜￜￜￜￜ | tￜￜￜￜￜￜￜￜￜￜ | fￜￜￜￜￜￜￜￜￜￜￜￜￜ |ￜￜￜￜￜￜￜￜￜￜ -1 | ********
|ￜￜￜￜￜￜￜￜￜￜￜￜￜￜ |ￜￜￜￜￜￜￜￜￜￜ | 1067432
ￜuser5 ￜￜￜￜￜￜￜￜ | fￜￜￜￜￜￜￜ | tￜￜￜￜￜￜￜￜￜ | tￜￜￜￜￜￜￜￜￜￜￜￜ | tￜￜￜￜￜￜￜￜￜￜ |
fￜￜￜￜￜￜￜￜￜￜￜ | tￜￜￜￜￜￜￜￜￜￜ | f |ￜￜￜￜￜￜￜￜￜￜ -1 | ******** |
|ￜￜￜￜￜￜￜￜￜￜ |ￜￜ 30602
(6 rows)

So, no user with that OID. I checked in the trigger pointed to the error
and I found

GRANT EXECUTE ON FUNCTION x() TO "1067431" WITH GRANT OPTION;

I am not following as a trigger would not have that in its code.

Are you referring to the function x()?

If so is the GRANT in the function?

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

thanks

Moreno.-

--
Adrian Klaver
adrian.klaver@aklaver.com

Moreno Andreo <moreno.andreo@evolu-s.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]https://git.postgresql.org/gitweb/?p=postgresql.git&amp;a=commitdiff&amp;h=fa2952d8e, 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]https://git.postgresql.org/gitweb/?p=postgresql.git&amp;a=commitdiff&amp;h=d691cb914. 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&amp;a=commitdiff&amp;h=fa2952d8e
[2]: https://git.postgresql.org/gitweb/?p=postgresql.git&amp;a=commitdiff&amp;h=d691cb914

Il 12/12/2018 15:39, Adrian Klaver ha scritto:

On 12/12/18 5:11 AM, Moreno Andreo wrote:

Hi all,

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;

Here's the evidence :-)

The below is from the 9.1 cluster, correct?

Correct, 9.1.6, IIRC

postgres=# select * from pg_roles;
ￜￜￜￜￜ rolnameￜￜￜￜￜ | rolsuper | rolinherit | rolcreaterole |
rolcreatedb | rolcatupdate | rolcanlogin | rolreplication |
rolconnlimit | rolpassword | rolvaliduntil | rolconfig |ￜￜ oid
------------------+----------+------------+---------------+-------------+--------------+-------------+----------------+--------------+-------------+---------------+-----------+---------

ￜￜpostgresￜￜￜￜￜￜￜￜ | tￜￜￜￜￜￜￜ | tￜￜￜￜￜￜￜￜￜ | tￜￜￜￜￜￜￜￜￜￜￜￜ | t |
tￜￜￜￜￜￜￜￜￜￜￜ | tￜￜￜￜￜￜￜￜￜￜ | t |ￜￜￜￜￜￜￜￜￜￜ -1 | ********
|ￜￜￜￜￜￜￜￜￜￜￜￜￜￜ |ￜￜￜￜￜￜￜￜￜￜ |ￜￜￜￜￜ 10
ￜￜuser1 ￜￜￜￜ | tￜￜￜￜￜￜￜ | tￜￜￜￜￜￜￜￜￜ | tￜￜￜￜￜￜￜￜￜￜￜￜ | t | t |
tￜￜￜￜￜￜￜￜￜￜ | tￜￜￜￜￜￜￜￜￜￜￜￜￜ |ￜￜￜￜￜￜￜￜￜￜ -1 | ********
|ￜￜￜￜￜￜￜￜￜￜￜￜￜￜ |ￜￜￜￜￜￜￜￜￜￜ |ￜￜ 16393
ￜￜuser2 ￜￜￜ | tￜￜￜￜￜￜￜ | tￜￜￜￜￜￜￜￜￜ | tￜￜￜￜￜￜￜￜￜￜￜￜ | tￜￜￜￜￜￜￜￜￜￜ |
tￜￜￜￜￜￜￜￜￜￜￜ | tￜￜￜￜￜￜￜￜￜￜ | t |ￜￜￜￜￜￜￜￜￜￜ -1 | ********
|ￜￜￜￜￜￜￜￜￜￜￜￜￜￜ |ￜￜￜￜￜￜￜￜￜￜ |ￜￜ 16394
ￜￜuser3 ￜￜￜￜￜ | fￜￜￜￜￜￜￜ | tￜￜￜￜￜￜￜￜￜ | fￜￜￜￜￜￜￜￜￜￜￜￜ | f | f |
tￜￜￜￜￜￜￜￜￜￜ | fￜￜￜￜￜￜￜￜￜￜￜￜￜ |ￜￜￜￜￜￜￜￜￜￜ -1 | ********
|ￜￜￜￜￜￜￜￜￜￜￜￜￜￜ |ￜￜￜￜￜￜￜￜￜￜ |ￜￜ 16395
ￜￜuser4 | fￜￜￜￜￜￜￜ | tￜￜￜￜￜￜￜￜￜ | tￜￜￜￜￜￜￜￜￜￜￜￜ | tￜￜￜￜￜￜￜￜￜￜ |
fￜￜￜￜￜￜￜￜￜￜￜ | tￜￜￜￜￜￜￜￜￜￜ | fￜￜￜￜￜￜￜￜￜￜￜￜￜ |ￜￜￜￜￜￜￜￜￜￜ -1 | ********
|ￜￜￜￜￜￜￜￜￜￜￜￜￜￜ |ￜￜￜￜￜￜￜￜￜￜ | 1067432
ￜￜuser5 ￜￜￜￜￜￜￜￜ | fￜￜￜￜￜￜￜ | tￜￜￜￜￜￜￜￜￜ | tￜￜￜￜￜￜￜￜￜￜￜￜ |
tￜￜￜￜￜￜￜￜￜￜ | fￜￜￜￜￜￜￜￜￜￜￜ | tￜￜￜￜￜￜￜￜￜￜ | f |ￜￜￜￜￜￜￜￜￜￜ -1 |
******** |ￜￜￜￜￜￜￜￜￜￜￜￜￜￜ |ￜￜￜￜￜￜￜￜￜￜ |ￜￜ 30602
(6 rows)

So, no user with that OID. I checked in the trigger pointed to the
error and I found

GRANT EXECUTE ON FUNCTION x() TO "1067431" WITH GRANT OPTION;

I am not following as a trigger would not have that in its code.

Are you referring to the function x()?

If so is the GRANT in the function?

Sorry, the grant above is extracted from the CREATE statement that
PgAdmin3 shows when you click on the trigger

Il 12/12/2018 16:01, Tom Lane ha scritto:

Moreno Andreo <moreno.andreo@evolu-s.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.

That's the case. The customer never complained about any problem but
today, while upgrading his cluster, we bumped into 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 ...

The cluster is version 9.1.6, IIRC

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.

Is it not the case to edit proacl column to just remove the unwanted
role? Is it faster and safer to null the column and just reGRANT?

Thanks

Moreno.-

Il 12/12/2018 16:01, Tom Lane ha scritto:

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.

Setting the column (and several others from other tables such as
pg_class) to null made me possible to upgrade the cluster. After the
upgrade, I issued the necessary GRANTs and everything is up & running on
Postgres 10 now!

Thanks again

Moreno.-