From: Michael Paquier <michael@paquier.xyz>
To: Floris Van Nee <florisvannee@Optiver.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Cc: Ferdi Smit <FerdiSmit@Optiver.com>
Cc: Stephen Frost <sfrost@snowman.net>
Subject: Re: incorrect pg_dump output due to not handling dropped roles correctly
Date: Mon, 18 Nov 2019 20:56:16 +0900
Message-ID: <20191118115616.GF1543@paquier.xyz> (raw)
In-Reply-To: <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>
<1574068566573.13088@Optiver.com>
On Mon, Nov 18, 2019 at 09:16:06AM +0000, Floris Van Nee wrote:
> So, if we do it during DROP OWNED BY, we should also handle it
> during one of the below REVOKE commands. Perhaps DROP OWNED BY
> already calls one of these functions internally - in that case you
> can ignore my comment. Just wanted to make sure we catch all
> possible cases this can occur.
One problem here is that you would need to scan and track down all the
entries in pg_init_privs if you'd like to make sure that all the
traces of the role have been removed, so that's really not good for
performance. The fix I was actually imagining upthread would be to
add a new type of entry in pg_shdepend linking the role with
pg_init_privs for the object where an ACL is added. This would
prevent DROP ROLE to complete with your scenarios, and if the role
ownerships are dropped we could use the link between the object and
the role to remove directly the ACL for the role in the object.
That's not really backpatchable unfortunately. On top of the new
pg_depend type we would need a new cleanup routine similar to what
happens for policy ACLs when dropping a role.
--
Michael
Attachments:
signature.asc (application/pgp-signature, 832B) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-bugs@postgresql.org
Cc: michael@paquier.xyz, florisvannee@Optiver.com, tgl@sss.pgh.pa.us, FerdiSmit@Optiver.com, sfrost@snowman.net
Subject: Re: incorrect pg_dump output due to not handling dropped roles correctly
In-Reply-To: <20191118115616.GF1543@paquier.xyz>
References: <1573808483712.96817@Optiver.com> <29913.1573835973@sss.pgh.pa.us> <1574024173786.96623@Optiver.com> <20191118031059.GA1543@paquier.xyz> <20191118041433.GB1543@paquier.xyz> <1574068566573.13088@Optiver.com> <20191118115616.GF1543@paquier.xyz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox