Is there a means to set the owner of all objects in a PostgreSQL database at the same time?

The Stack Overflow Q & A Modify OWNER on all tables simultaneously in PostgreSQL describes some nifty ways to change table and other objects to a specific user, and it works swimmingly, however all the suggestions seem to ignore the functions I created.

Is there a fairly easy way to reset the owner of ALL objects in the database, including the functions? Doing it by hand is highly undesirable.

You should only ever manipulate system catalogs directly, if you know exactly what you are doing. It may have unexpected side effects. Or you can corrupt the database (or the whole database cluster) beyond repair.

Jeremy's answer, while basically doing the trick, is not advisable for the general public. It unconditionally changes all functions in a schema. Are you sure there are no system functions affected or functions installed by an additional module?

It would also be pointless to change the owner of functions that already belong to the designated owner.

First, check if REASSIGN OWNED could work for you:

change the ownership of database objects owned by a database role

You have to list all roles to be disowned explicitly. But it also reassigns functions.

To assign all functions (and no other objects) in a given schema to a new owner (optionally regardless of previous owner):

SELECT string_agg('ALTER FUNCTION '|| oid::regprocedure || ' OWNER TO foo;', E'\n') AS ddl
FROM   pg_catalog.pg_proc
WHERE  pronamespace = 'public'::regnamespace
-- AND proowner <> 'foo'::regrole
-- AND proname ~~ 'f_%'

This generates the canonical SQL commands ALTER FUNCTION ... to change all functions (in the specified schema). Inspect the commands before executing - one by one or all at once:

ALTER FUNCTION public.bar(text, text) OWNER TO foo;
ALTER FUNCTION public.foo(x integer) OWNER TO foo;
...

The cast to regprocedure produces a valid function name with parameters, double-quoted where necessary, schema-qualified where necessary for the current search_path.

Also using the object identifier types regnamespace and regrole for simplicity.

I added some commented WHERE clauses you might want to use to filter the results.

You could put all of this into a DO statement or a function like demonstrated in this related answer:

• Drop all functions in a namespace? (Execute generated DDL commands?)

For Postgres 9.4 or older:

SELECT string_agg('ALTER FUNCTION ' || oid::regprocedure || ' OWNER TO foo;', E'\n') AS ddl
FROM   pg_catalog.pg_proc p
JOIN   pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE  n.nspname = 'public'
-- AND p.proowner <> (SELECT oid FROM pg_roles WHERE rolname = 'foo')
-- AND p.proname ~~ 'f_%'

The aggregate function string_agg() requires PostgreSQL 9.0 or later. In older version substitute with array_agg() and array_to_string().

StackExchange Database Administrators Q#9708, answer score: 26