Re: DROP OWNED BY fails to clean out pg_init_privs grants

While the 'DROP OWNED BY fails to clean out pg_init_privs grants'
issue is now fixed,we have a similar issue with REASSIGN OWNED BY that
is still there:

Tested on fresh git checkout om May 20th

test=# create user privtestuser superuser;
CREATE ROLE
test=# set role privtestuser;
SET
test=# create extension pg_stat_statements ;
CREATE EXTENSION
test=# select * from pg_init_privs where privtype ='e';
objoid | classoid | objsubid | privtype |
initprivs
--------+----------+----------+----------+------------------------------------------------------
16405 | 1259 | 0 | e |
{privtestuser=arwdDxtm/privtestuser,=r/privtestuser}
16422 | 1259 | 0 | e |
{privtestuser=arwdDxtm/privtestuser,=r/privtestuser}
16427 | 1255 | 0 | e | {privtestuser=X/privtestuser}
(3 rows)

test=# reset role;
RESET
test=# reassign owned by privtestuser to hannuk;
REASSIGN OWNED
test=# select * from pg_init_privs where privtype ='e';
objoid | classoid | objsubid | privtype |
initprivs
--------+----------+----------+----------+------------------------------------------------------
16405 | 1259 | 0 | e |
{privtestuser=arwdDxtm/privtestuser,=r/privtestuser}
16422 | 1259 | 0 | e |
{privtestuser=arwdDxtm/privtestuser,=r/privtestuser}
16427 | 1255 | 0 | e | {privtestuser=X/privtestuser}
(3 rows)

test=# drop user privtestuser ;
DROP ROLE
test=# select * from pg_init_privs where privtype ='e';
objoid | classoid | objsubid | privtype | initprivs
--------+----------+----------+----------+---------------------------------
16405 | 1259 | 0 | e | {16390=arwdDxtm/16390,=r/16390}
16422 | 1259 | 0 | e | {16390=arwdDxtm/16390,=r/16390}
16427 | 1255 | 0 | e | {16390=X/16390}
(3 rows)

This will cause pg_dump to produce something that cant be loaded back
into the database:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements WITH SCHEMA public;
...
REVOKE ALL ON TABLE public.pg_stat_statements FROM "16390";
...

And this will, among other things, break pg_upgrade.

-----
Hannu

On Tue, Apr 30, 2024 at 6:40 AM David G. Johnston
<david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
>
> On Monday, April 29, 2024, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>
>> "David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
>> > My solution to this was to rely on the fact that the bootstrap superuser is
>> > assigned OID 10 regardless of its name.
>>
>> Yeah, I wrote it that way to start with too, but reconsidered
>> because
>>
>> (1) I don't like hard-coding numeric OIDs. We can avoid that in C
>> code but it's harder to do in SQL.
>
>
> If the tests don’t involve, e.g., the predefined role pg_monitor and its grantor of the memberships in the other predefined roles, this indeed can be avoided. So I think my test still needs to check for 10 even if some other superuser is allowed to produce the test output since a key output in my case was the bootstrap superuser and the initdb roles.
>
>>
>> (2) It's not clear to me that this test couldn't be run by a
>> non-bootstrap superuser. I think "current_user" is actually
>> the correct thing for the role executing the test.
>
>
> Agreed, testing against current_role is correct if the things being queried were created while executing the test. I would need to do this as well to remove the current requirement that my tests be run by the bootstrap superuser.
>
> David J.
>