> 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