pg_init_privs corruption. - Mailing list pgsql-hackers

From Kirill Reshke
Subject pg_init_privs corruption.
Date
Msg-id CADVKa1Wq7FcXy1xyqN-26_2TnW5Lva9A8S+J1kvdVM08E3hGBw@mail.gmail.com
Whole thread Raw
Responses Re: pg_init_privs corruption.
List pgsql-hackers
Hi hackers!

Recently we faced a problem with one of our production clusters. Problem was with pg_upgrade,
the reason was an invalid pg_dump of cluster schema. in pg_dump sql there was strange records like

REVOKE SELECT,INSERT,DELETE,UPDATE ON TABLE *relation* FROM "144841";

but there is no role "144841"
We did dig in, and it turns out that 144841 was OID of previously-deleted role.

I have reproduced issue using simple test extension yoext(1).

SQL script:

create role user1;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT select ON TABLES TO user1;
create extension yoext;
drop owned by user1;
select * from pg_init_privs  where privtype = 'e';
drop role user1;
select * from pg_init_privs  where privtype = 'e';

result of execution (executed on fest master from commit 17feb6a566b77bf62ca453dec215adcc71755c20):

psql (16devel)
Type "help" for help.

postgres=#
postgres=#
postgres=# create role user1;
CREATE ROLE
postgres=# ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT select ON TABLES TO user1;
ALTER DEFAULT PRIVILEGES
postgres=# create extension yobaext ;
CREATE EXTENSION
postgres=# drop owned by user1;
DROP OWNED
postgres=# select * from pg_init_privs  where privtype = 'e';
 objoid | classoid | objsubid | privtype |                     initprivs
--------+----------+----------+----------+---------------------------------------------------
  16387 |     1259 |        0 | e        | {reshke=arwdDxtm/reshke,user1=r/reshke,=r/reshke}
(1 row)

postgres=# drop role user1;
DROP ROLE
postgres=# select * from pg_init_privs  where privtype = 'e';
 objoid | classoid | objsubid | privtype |                     initprivs
--------+----------+----------+----------+---------------------------------------------------
  16387 |     1259 |        0 | e        | {reshke=arwdDxtm/reshke,16384=r/reshke,=r/reshke}
(1 row)


As you can see, after drop role there is invalid records in pg_init_privs system relation. After this, pg_dump generate sql statements, some of which are based on content of pg_init_privs, resulting in invalid dump.

PFA fix.

The idea of fix is simply drop records from pg_init_privs while dropping role.
Records with grantor of grantee equal to oid of dropped role will erase. after that, pg_dump works ok.

Implementation comment: i failed to find proper way to alloc acl array, so defined some acl.c internal function `allocacl` in header. Need to improve this somehow.

Attachment

pgsql-hackers by date:

Previous
From: "Jonathan S. Katz"
Date:
Subject: Re: The output sql generated by pg_dump for a create function refers to a modified table name
Next
From: "Jonah H. Harris"
Date:
Subject: Re: Reducing System Allocator Thrashing of ExecutorState to Alleviate FDW-related Performance Degradations