Re: REASSIGN OWNED doesn't for all objects, ALTER FUNCTION seems to fix it - Mailing list pgsql-general

Frank van Vugt <ftm.van.vugt@foxi.nl> writes:
> * upon issuing 'REASSIGN OWNED BY A TO postgres', all tables and _most_
> functions changed ownership, but not all.... a number of functions stay marked
> as owned by A, nothing weird in the logs, the reassign looked like it
> completed successfully

Hmmm .... look into pg_shdepend to see if there are entries linking
those functions to an owner.  For instance, after

regression=# create user joe;
CREATE ROLE
regression=# \c - joe
You are now connected to database "regression" as user "joe".
regression=> create function foo() returns int as 'select 1' language sql;
CREATE FUNCTION

I get

regression=> select * from pg_shdepend;
  dbid  | classid | objid  | objsubid | refclassid | refobjid | deptype
--------+---------+--------+----------+------------+----------+---------
 ...
 123822 |    1255 | 148691 |        0 |       1260 |   148690 | o
 ...

1255 = pg_proc, 1260 = pg_authid, and the other numbers are the OIDs of
the database, function, and role (user) respectively.  This data is what
REASSIGN OWNED works off of, and I suppose that some rows must be wrong
or missing in your pg_shdepend.

            regards, tom lane