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 1iWd8o-0003Qm-Ej for pgsql-bugs@arkaria.postgresql.org; Mon, 18 Nov 2019 09:16:14 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1iWd8n-0002cs-8I for pgsql-bugs@arkaria.postgresql.org; Mon, 18 Nov 2019 09:16:13 +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 1iWd8m-0002cl-W7 for pgsql-bugs@lists.postgresql.org; Mon, 18 Nov 2019 09:16:13 +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 1iWd8j-0005Bb-LF for pgsql-bugs@lists.postgresql.org; Mon, 18 Nov 2019 09:16:11 +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 xAI9Akq2011533; Mon, 18 Nov 2019 10:16:07 +0100 Received: from opammb0562.comp.optiver.com (opammb0562.comp.optiver.com [10.131.160.2]) by smtp03.optiver.com with ESMTP id 2wa7k80jr0-1 (version=TLSv1.2 cipher=ECDHE-RSA-AES256-SHA384 bits=256 verify=NOT); Mon, 18 Nov 2019 10:16:07 +0100 Received: from opammb0561.comp.optiver.com (10.131.160.1) by opammb0562.comp.optiver.com (10.131.160.2) with Microsoft SMTP Server (TLS) id 15.0.1473.3; Mon, 18 Nov 2019 10:16:07 +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; Mon, 18 Nov 2019 10:16:07 +0100 From: Floris Van Nee To: Michael Paquier CC: Tom Lane , "pgsql-bugs@lists.postgresql.org" , Ferdi Smit , Stephen Frost Subject: Re: 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: AQHVnfDO0LCVhrCvm0O6WkgvdJC1eg== Date: Mon, 18 Nov 2019 09:16:06 +0000 Message-ID: <1574068566573.13088@Optiver.com> References: <1573808483712.96817@Optiver.com> <29913.1573835973@sss.pgh.pa.us> <1574024173786.96623@Optiver.com> <20191118031059.GA1543@paquier.xyz>,<20191118041433.GB1543@paquier.xyz> In-Reply-To: <20191118041433.GB1543@paquier.xyz> 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-25050.005 x-tm-as-result: No-10--4.686900-8.000000 x-tmase-matchedrid: UuaOI1zLN1juo96mfIBuopzEHTUOuMX33dCmvEa6IiFJkYDIfDuRn4P+ YDa/Dhu9jjj7xmhW6vhLvm3q3PPQ+8c7x4DEI6P2qjZ865FPtpqL5MCc+du22NKpFIAeH1NQoSn PJtmDAFIHjlcKOd8oVcf31N19XxaAW2/uVVOXnn1ubyl3d/pPv35Lmbb/xUuag1Fv8ItHKD2Arq oIZrVn1w7/DB1cWVyLsWw/Ark0XpAy7bE5+DQZoi8s/ULwMh46MaTXeoefvJx1l69bBW+lDLI7V RYlSnIU3lR/flD8ufycNz++hPdLOjSpztbxlrn1KrDHzH6zmUWpvQ5ST6UgvR4CqxAg5RN9GKf/ vWX0b7BuX9YoPT7OmVfAMFhG3meyAOZWqwY0KQCeAiCmPx4NwHJnzNw42kCxxEHRux+uk8ifEzJ 5hPndGdnP/xaxEC9Op7LXUHZA3E0lc3ugjkCGFpGx2IF5m0I1DuUxQk/2OqjEb+UhcQGID2V1BU WOtWDmaOGUcKXzMM6J4AcAXZqPh+f3tOmTVFRyUysIYQW7SlZq9oascRUICTSAm54tW+m8Vlxr1 FJij9s= x-tm-as-user-approved-sender: Yes x-tm-as-user-blocked-sender: No x-tmase-result: 10--4.686900-8.000000 x-tmase-version: SMEX-14.0.0.1158-8.5.1020-25050.005 x-tm-snts-smtp: FCEF87F9B6127633EB4CB25F5BC225C26A738F25762E41EF11786D971291414C2000:8 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable MIME-Version: 1.0 X-Proofpoint-Virus-Version: vendor=fsecure engine=2.50.10434:,, definitions=2019-11-18_01:,, signatures=0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk =0A= > The dependencies related to the ACL entries exist in pg_shdepend=0A= > between the role and the revoked objects, and these get removed when=0A= > issuing DROP OWNED BY. So it seems to me that the cleanup needs to=0A= > happen when issuing the DROP OWNED BY query, and not DROP ROLE.=0A= > Looking at the code, it seems to me that we should just patch=0A= > shdepDropOwned() to handle properly the removal of the role in ACL=0A= > objects in pg_init_privs for all the objects we are removing a=0A= > dependency on. I am just diving into a patch..=0A= =0A= Forgive me for not following the logic here completely, as I haven't done a= deep dive into the code.=0A= I agree doing it in the DROP OWNED BY makes more sense, however I was sugge= sting to do it during 'DROP ROLE', because it is at least not enough to do = it *only* in the DROP OWNED BY. For example, we can also manually remove th= e permissions and then drop the role, without using DROP OWNED BY.=0A= So, if we do it during DROP OWNED BY, we should also handle it during one o= f the below REVOKE commands. Perhaps DROP OWNED BY already calls one of the= se functions internally - in that case you can ignore my comment. Just want= ed to make sure we catch all possible cases this can occur.=0A= =0A= -- before this, create role role, assign default privs and then create exte= nsion, then:=0A= =0A= postgres=3D# select * from pg_catalog.pg_init_privs=0A= where objoid=3D(select 'pg_stat_statements'::regclass);=0A= objoid | classoid | objsubid | privtype | = initprivs =0A= --------+----------+----------+----------+---------------------------------= ----------------------------------------------=0A= 24583 | 1259 | 0 | e | {florisvannee=3DarwdDxt/florisva= nnee,test=3DarwdDxt/florisvannee,=3Dr/florisvannee}=0A= (1 row)=0A= =0A= postgres=3D# alter default privileges in schema public revoke all privilege= s on tables from test;=0A= ALTER DEFAULT PRIVILEGES=0A= =0A= postgres=3D# revoke all on pg_stat_statements from test;=0A= REVOKE=0A= =0A= postgres=3D# drop role test;=0A= DROP ROLE=0A= postgres=3D# select * from pg_catalog.pg_init_privs=0A= where objoid=3D(select 'pg_stat_statements'::regclass);=0A= objoid | classoid | objsubid | privtype | = initprivs =0A= --------+----------+----------+----------+---------------------------------= -----------------------------------------------=0A= 24583 | 1259 | 0 | e | {florisvannee=3DarwdDxt/florisva= nnee,24578=3DarwdDxt/florisvannee,=3Dr/florisvannee}=0A= (1 row)=0A= =0A= =0A= -Floris=0A=