Re: incorrect pg_dump output due to not handling dropped rolescorrectly - Mailing list pgsql-bugs

From Floris Van Nee
Subject Re: incorrect pg_dump output due to not handling dropped rolescorrectly
Date
Msg-id 1574068566573.13088@Optiver.com
Whole thread Raw
In response to Re: incorrect pg_dump output due to not handling dropped rolescorrectly  (Michael Paquier <michael@paquier.xyz>)
Responses Re: incorrect pg_dump output due to not handling dropped rolescorrectly
List pgsql-bugs
> 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
itis at least not enough to do it *only* in the DROP OWNED BY. For example, we can also manually remove the permissions
andthen 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
OWNEDBY already calls one of these functions internally - in that case you can ignore my comment. Just wanted to make
surewe 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



pgsql-bugs by date:

Previous
From: Thomas Munro
Date:
Subject: Re: BUG #16123: DST not respected for America/Sao_Paulo in`timestamp` function
Next
From: Michael Paquier
Date:
Subject: Re: incorrect pg_dump output due to not handling dropped rolescorrectly