pg_init_privs corruption.

From: Kirill Reshke <reshke(at)double(dot)cloud>
To: pgsql-hackers(at)lists(dot)postgresql(dot)org
Subject: pg_init_privs corruption.
Date: 2023-02-17 16:31:30
Message-ID: CADVKa1Wq7FcXy1xyqN-26_2TnW5Lva9A8S+J1kvdVM08E3hGBw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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.

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

Attachment Content-Type Size
v1-0001-Fix-pg_init_prevs-corruption.patch application/octet-stream 4.9 KB

Responses

Browse pgsql-hackers by date

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