Re: DROP OWNED BY fails to clean out pg_init_privs grants - Mailing list pgsql-hackers
From | Hannu Krosing |
---|---|
Subject | Re: DROP OWNED BY fails to clean out pg_init_privs grants |
Date | |
Msg-id | CAMT0RQSVgv48G5GArUvOVhottWqZLrvC5wBzBa4HrUdXe9VRXw@mail.gmail.com Whole thread Raw |
In response to | Re: DROP OWNED BY fails to clean out pg_init_privs grants ("David G. Johnston" <david.g.johnston@gmail.com>) |
Responses |
Re: DROP OWNED BY fails to clean out pg_init_privs grants
|
List | pgsql-hackers |
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.g.johnston@gmail.com> wrote: > > On Monday, April 29, 2024, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> >> "David G. Johnston" <david.g.johnston@gmail.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 predefinedroles, this indeed can be avoided. So I think my test still needs to check for 10 even if some other superuseris 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 wouldneed to do this as well to remove the current requirement that my tests be run by the bootstrap superuser. > > David J. >
pgsql-hackers by date: