Subject: pg_init_privs corruption.

From: Kirill Reshke <[email-masked]>
Date: Fri, 17 Feb 2023 17:31:30 +0100

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.

[1] yoext https://github.com/reshke/yoext/