REVOKE to an user that doesn't exist

From: Moreno Andreo <moreno(dot)andreo(at)evolu-s(dot)it>
To: PostgreSQL mailing lists <pgsql-general(at)postgresql(dot)org>
Subject: REVOKE to an user that doesn't exist
Date: 2018-12-12 13:11:21
Message-ID: c9bdaeb8-5685-d86c-a18e-6694088a6b78@evolu-s.it
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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.-

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Chris Withers 2018-12-12 13:45:20 why would postgres be throttling a streaming replication slot's sending?
Previous Message Dmitry Lazurkin 2018-12-12 12:52:03 Re: Does PostgreSQL use atomic file creation of FS?