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 |
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 |