Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iVXTx-0008Kh-81 for pgsql-bugs@arkaria.postgresql.org; Fri, 15 Nov 2019 09:01:33 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1iVXTv-0006Ka-IQ for pgsql-bugs@arkaria.postgresql.org; Fri, 15 Nov 2019 09:01:31 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iVXTv-0006KT-Af for pgsql-bugs@lists.postgresql.org; Fri, 15 Nov 2019 09:01:31 +0000 Received: from smtp03.optiver.com ([195.246.114.70]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iVXTr-0006bT-0k for pgsql-bugs@lists.postgresql.org; Fri, 15 Nov 2019 09:01:30 +0000 Received: from pps.filterd (smtp03.optiver.com [127.0.0.1]) by smtp03.optiver.com (8.16.0.27/8.16.0.27) with SMTP id xAF8p7Qv025490 for ; Fri, 15 Nov 2019 10:01:25 +0100 Received: from opammb0561.comp.optiver.com (opammb0561.comp.optiver.com [10.131.160.1]) by smtp03.optiver.com with ESMTP id 2w9h028564-1 (version=TLSv1.2 cipher=ECDHE-RSA-AES256-SHA384 bits=256 verify=NOT) for ; Fri, 15 Nov 2019 10:01:25 +0100 Received: from opammb0561.comp.optiver.com (10.131.160.1) by opammb0561.comp.optiver.com (10.131.160.1) with Microsoft SMTP Server (TLS) id 15.0.1473.3; Fri, 15 Nov 2019 10:01:24 +0100 Received: from opammb0561.comp.optiver.com ([fe80::9d46:f4ee:846a:2858]) by opammb0561.comp.optiver.com ([fe80::9d46:f4ee:846a:2858%17]) with mapi id 15.00.1473.003; Fri, 15 Nov 2019 10:01:24 +0100 From: Floris Van Nee To: "pgsql-bugs@lists.postgresql.org" CC: Ferdi Smit Subject: incorrect pg_dump output due to not handling dropped roles correctly Thread-Topic: incorrect pg_dump output due to not handling dropped roles correctly Thread-Index: AQHVm46+3UFvMpH6ake1TtKsF5Kgaw== Date: Fri, 15 Nov 2019 09:01:23 +0000 Message-ID: <1573808483712.96817@Optiver.com> Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: x-ms-exchange-transport-fromentityheader: Hosted x-originating-ip: [10.131.160.182] x-tm-as-product-ver: SMEX-14.0.0.1158-8.5.1020-25044.006 x-tm-as-result: No-10--8.742700-8.000000 x-tmase-matchedrid: N862GpoHoW425XOhnouJ5RuurbgYp+HQcQ43DruBJVzJqq1xSxY7Fuld eefNodEkjSiAYevD2gFuPirDV8jVCcOaZkV4xtFoIf0TE1VduNuoCf7IdvPAJwskoGQVxX7EOPx 1SoHFsV1xAsDfY1IQ6YSSCnHtKCuhupdRnX5g3CZx5iprrF0KxP4159kh1JlcCDaSBZ23eppCPF 1UQ0y6amfjg89CZ7Y6ixbOkvhgaNrSduMc6RnI+XNjCNHNJIs46qG5M9QNAO2xW4YXFsTUDoNSC fL1dw7/3YVl4KvhQuWwpgZZFHR0/HZW0C+m8SohLFirdaqw+KEGchEhVwJY38z/SxKo9mJ4yaS/ 5aNY9R5vB9aPVLgItOTSlAoRgaTUzmRmmnTk3UHwoYkKJX7f8viH64jt3FfEcTldn9EapDXoNA8 afAv0tortCBXG4BIYxalm1s5cqa0ZZgPPPpM58+y12aeTj6CzyeUl7aCTy8glVFSTSjPtHetJb0 z5oGlqx0sGmQJO+E//voIzhn8wZtDew4wHcWTU4vM1YF6AJbY65tgsJWcFUWGupaw6fqLNGhI2+ mKTcVRhB//61dVgU/a/i+xu+wgkcfvcHExvhrV2madUWCN/8Q== x-tm-as-user-approved-sender: Yes x-tm-as-user-blocked-sender: No x-tmase-result: 10--8.742700-8.000000 x-tmase-version: SMEX-14.0.0.1158-8.5.1020-25044.006 x-tm-snts-smtp: 9691E0172CA6D7BB0F89DED7C3AA7423199C9676D689C99D8C8FDC26495A38B82000:8 Content-Type: multipart/alternative; boundary="_000_157380848371296817Optivercom_" MIME-Version: 1.0 X-Proofpoint-Virus-Version: vendor=fsecure engine=2.50.10434:,, definitions=2019-11-15_02:,, signatures=0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --_000_157380848371296817Optivercom_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I noticed I wasn't able to apply my usual pg_dump schema output without err= ors anymore after I dropped some roles. After some digging, I found it has = to do with Postgres not correctly updating the pg_init_privs table upon dro= pping roles. I can reproduce a similar scenario with the following steps (o= utput from v13devel, but AFAIK all versions affected, I ran into the issue = on v11.2): postgres=3D# create role test; CREATE ROLE postgres=3D# alter default privileges in schema public grant all privileges= on tables to test; ALTER DEFAULT PRIVILEGES postgres=3D# create extension pg_stat_statements; CREATE EXTENSION postgres=3D# select * from pg_catalog.pg_init_privs where objoid=3D(select 'pg_stat_statements'::regclass); objoid | classoid | objsubid | privtype | = initprivs --------+----------+----------+----------+---------------------------------= ---------------------------------------------- 16409 | 1259 | 0 | e | {florisvannee=3DarwdDxt/florisva= nnee,test=3DarwdDxt/florisvannee,=3Dr/florisvannee} (1 row) postgres=3D# drop owned by test; DROP OWNED postgres=3D# drop role test; DROP ROLE postgres=3D# select * from pg_catalog.pg_init_privs where objoid=3D(select 'pg_stat_statements'::regclass); objoid | classoid | objsubid | privtype | = initprivs --------+----------+----------+----------+---------------------------------= ----------------------------------------------- 16409 | 1259 | 0 | e | {florisvannee=3DarwdDxt/florisva= nnee,16404=3DarwdDxt/florisvannee,=3Dr/florisvannee} (1 row) If we do a pg_dump on this, there'll be a line like this in the output: REVOKE ALL ON TABLE public.pg_stat_statements FROM "16404"; This fails when restoring, because there's no role '16404'. Can I manually fix this by updating pg_init_privs catalog table? Eg. in the= example, I could run something like? update pg_catalog.pg_init_privs set initprivs=3D(select array_agg(p) from unnest(initprivs) p where not (p:= :text like '16404%')) where initprivs <> (select array_agg(p) from unnest(initprivs) p where not = (p::text like '16404%')) ; Dropping/recreating the extension seems to work too, but I'd like to avoid = that if possible (that may be a solution for pg_stat_statements, but isn't = necessarily possible for every extension). I'm pretty sure I once ran into a similar issue before, when doing a pg_upg= rade in-place from 10 to 11. I couldn't run pg_upgrade until - back then I = fixed it by dropping/recreating the extension, but didn't know exactly what= was causing it, so I didn't report it here. From glancing at the code, thi= s seems to be following some similar code paths in dump/restore. Just so yo= u know the impact may not be limited to manual pg_dump actions, but also po= tentially pg_upgrade if my memory is correct. -Floris --_000_157380848371296817Optivercom_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

I noticed I wasn't able to apply my usual pg_dump schema output without = errors anymore after I dropped some roles. After some digging, I found it h= as to do with Postgres not correctly updating the pg_init_privs table upon = dropping roles. I can reproduce a similar scenario with the following steps (output from v13devel, bu= t AFAIK all versions affected, I ran into the issue on v11.2):


postgres=3D# create role test;
CREATE ROLE
postgres=3D# alter default privileges in schema public grant al= l privileges on tables to test;
ALTER DEFAULT PRIVILEGES
postgres=3D# create extension pg_stat_statements;
CREATE EXTENSION
postgres=3D# select * from pg_catalog.pg_init_privs
where objoid=3D(select 'pg_stat_statements'::regclass);<= /div>
 objoid | classoid | objsubid | privtype |    &n= bsp;                     =          initprivs        &nbs= p;                     &n= bsp;    
--------+----------+----------+----------+-----= --------------------------------------------------------------------------<= /span>
  16409 |     1259 |      &n= bsp; 0 | e        | {florisvannee=3DarwdDxt/florisvanne= e,test=3DarwdDxt/florisvannee,=3Dr/florisvannee}
(1 row)

postgres=3D# drop owned by test;
DROP OWNED
postgres=3D# drop role test;
DROP ROLE
postgres=3D# select * from pg_catalog.pg_init_privs
where objoid=3D(select 'pg_stat_statements'::regclass);<= /div>
 objoid | classoid | objsubid | privtype |    &n= bsp;                     =          initprivs        &nbs= p;                     &n= bsp;     
--------+----------+----------+----------+-----= ---------------------------------------------------------------------------=
  16409 |     1259 |      &n= bsp; 0 | e        | {florisvannee=3DarwdDxt/florisvanne= e,16404=3DarwdDxt/florisvannee,=3Dr/florisvannee}
(1 row)


If we do a pg_dump on this, there'll be a line like this in the output:<= br>


REVOKE ALL ON TABLE public.pg_stat_statements FROM "16404&qu= ot;;


This fails when restoring, because there's no role '16404'.



Can I manually fix this by updating pg_init_privs catalog table? Eg. in = the example, I could run something like?


update pg_catalog.pg_init_privs
set initprivs=3D(select array_agg(p) from unnest(initprivs) p w= here not (p::text like '16404%'))
where initprivs <> (select array_agg(p) from unnest(initp= rivs) p where not (p::text like '16404%'))
;

Dropping/recreating the extension seems to work too, but I'd like to avoid = that if possible (that may be a solution for pg_stat_statements, but isn't = necessarily possible for every extension).


I'm pretty sure I once ran into a similar issue before, when doing = a pg_upgrade in-place from 10 to 11. I couldn't run pg_upgrade until - back= then I fixed it by dropping/recreating the extension, but didn't know exac= tly what was causing it, so I didn't report it here. From glancing at the code, this seems to be following= some similar code paths in dump/restore. Just so you know the impact may n= ot be limited to manual pg_dump actions, but also potentially pg_upgrade if= my memory is correct.



-Floris


--_000_157380848371296817Optivercom_--