From: | Floris Van Nee <florisvannee(at)Optiver(dot)com> |
---|---|
To: | Michael Paquier <michael(at)paquier(dot)xyz> |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, "pgsql-bugs(at)lists(dot)postgresql(dot)org" <pgsql-bugs(at)lists(dot)postgresql(dot)org>, Ferdi Smit <FerdiSmit(at)Optiver(dot)com>, Stephen Frost <sfrost(at)snowman(dot)net> |
Subject: | Re: incorrect pg_dump output due to not handling dropped roles correctly |
Date: | 2019-11-18 09:16:06 |
Message-ID: | 1574068566573.13088@Optiver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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 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
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Paquier | 2019-11-18 11:56:16 | Re: incorrect pg_dump output due to not handling dropped roles correctly |
Previous Message | Thomas Munro | 2019-11-18 06:51:29 | Re: BUG #16123: DST not respected for America/Sao_Paulo in `timestamp` function |