REVOKE to an user that doesn't exist - Mailing list pgsql-general

From Moreno Andreo
Subject REVOKE to an user that doesn't exist
Date
Msg-id c9bdaeb8-5685-d86c-a18e-6694088a6b78@evolu-s.it
Whole thread Raw
Responses Re: REVOKE to an user that doesn't exist
Re: REVOKE to an user that doesn't exist
List pgsql-general
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 :-)

postgres=# \du
                                 List of roles
     Role name    |                    Attributes                    | 
Member of
------------------+-------------------------------------------------+-----------
  user5          | Create role, Create DB                         | {}
  user2     | Superuser, Create role, Create DB, Replication | {}
  user4 | Create role, Create DB                         | {}
  user3       |                                                 | {}
  user1      | Superuser, Create role, Create DB, Replication | {}
  postgres         | Superuser, Create role, Create DB, Replication | {}

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;

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

thanks

Moreno.-




pgsql-general by date:

Previous
From: Dmitry Lazurkin
Date:
Subject: Re: Does PostgreSQL use atomic file creation of FS?
Next
From: Chris Withers
Date:
Subject: why would postgres be throttling a streaming replication slot'ssending?