pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Floris Van Nee <florisvannee@Optiver.com>
To: Michael Paquier <michael@paquier.xyz>
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 09:16:06 +0000
Message-ID: <1574068566573.13088@Optiver.com> (raw)
In-Reply-To: <20191118041433.GB1543@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>


> The dependencies related to the ACL entries exist in pg_shdepend
> between the role and the revoked objects, and these get removed when
> issuing DROP OWNED BY.  So it seems to me that the cleanup needs to
> happen when issuing the DROP OWNED BY query, and not DROP ROLE.
> Looking at the code, it seems to me that we should just patch
> shdepDropOwned() to handle properly the removal of the role in ACL
> objects in pg_init_privs for all the objects we are removing a
> dependency on.  I am just diving into a patch..

Forgive me for not following the logic here completely, as I haven't done a deep dive into the code.
I agree doing it in the DROP OWNED BY makes more sense, however I was suggesting 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 the permissions and then drop the role, without using DROP OWNED BY.
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.

-- before this, create role role, assign default privs and then create extension, then:

postgres=# select * from pg_catalog.pg_init_privs
where objoid=(select 'pg_stat_statements'::regclass);
 objoid | classoid | objsubid | privtype |                                   initprivs                                   
--------+----------+----------+----------+-------------------------------------------------------------------------------
  24583 |     1259 |        0 | e        | {florisvannee=arwdDxt/florisvannee,test=arwdDxt/florisvannee,=r/florisvannee}
(1 row)

postgres=# alter default privileges in schema public revoke all privileges on tables from test;
ALTER DEFAULT PRIVILEGES

postgres=# revoke all on pg_stat_statements from test;
REVOKE

postgres=# drop role test;
DROP ROLE
postgres=# select * from pg_catalog.pg_init_privs
where objoid=(select 'pg_stat_statements'::regclass);
 objoid | classoid | objsubid | privtype |                                   initprivs                                    
--------+----------+----------+----------+--------------------------------------------------------------------------------
  24583 |     1259 |        0 | e        | {florisvannee=arwdDxt/florisvannee,24578=arwdDxt/florisvannee,=r/florisvannee}
(1 row)


-Floris





view thread (14+ messages)  latest in thread

Message-ID: <1574068566573.13088@Optiver.com>
Permalink:  ../1574068566573.13088@Optiver.com/
Also on:    postgresql.org/message-id/1574068566573.13088@Optiver.com

 · 

reply

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: florisvannee@Optiver.com, michael@paquier.xyz, 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: <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>

* 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