Closed knz closed 3 years ago
REASSIGN OWNED only processes databases, schemas, types and tables.
It mistakenly skips over views and sequences.
These must be processed too. This prevents a user from successfully using REASSIGN OWNED to drop a user account
Here's an example:
CREATE USER blah; GRANT CREATE ON DATABASE defaultdb TO blah; CREATE USER blih WITH PASSWORD 'abc'; GRANT CREATE ON DATABASE defaultdb TO blih;
Now log into the database as blih, then create both a table and a view that depends on it.
blih
Then on the original session:
REVOKE CREATE ON DATABASE defaultdb FROM blih; REASSIGN OWNED BY blih TO blah; DROP USER blih;
gives:
ERROR: cannot drop role/user blih: grants still exist on defaultdb.public.my, defaultdb.public.myv
cc @solongordon @piyush-singh @vy-ton for triage
probably related to #57967
I was mistaken - this bug does not exist.
REASSIGN OWNED only processes databases, schemas, types and tables.
It mistakenly skips over views and sequences.
These must be processed too. This prevents a user from successfully using REASSIGN OWNED to drop a user account
Here's an example:
Now log into the database as
blih
, then create both a table and a view that depends on it.Then on the original session:
gives: