Objects in Postgres can trivially have their ownership re-assigned, e.g.:<\/p>\n
\nalter table test_table set owner to gary;\n<\/pre>\nYou can also trivially re-assign ownership of all objects a user owns:<\/p>\n
\nREASSIGN OWNED BY test_user TO gary \n<\/pre>\nHowever, you can\u2019t list all objects in the same way, and there is not a simple way to change the owner for objects in a schema. Functions are particularly difficult, because you must know the arguments:<\/p>\n
\nalter function test(id int) set owner to gary;\n<\/pre>\nTo the best of my knowledge, the following script will change the owner of all objects in a schema, including some rarer types:<\/p>\n
\ncreate or replace function chown(in_schema varchar, new_owner varchar) \nreturns void as\n$$\ndeclare\n object_types varchar[];\n object_classes varchar[];\n object_type record;\n\n r record;\nbegin\n object_types = '{type,table,sequence,index,table,view}';\n object_classes = '{c,t,S,i,r,v}';\n\n for object_type in \n select unnest(object_types) type_name, \n unnest(object_classes) code\n loop\n for r in \n execute '\n select n.nspname, c.relname \n from pg_class c, pg_namespace n \n where n.oid = c.relnamespace \n and nspname = ''' || in_schema || '''\n and relkind = ''' || object_type.code || ''''\n loop \n raise notice 'Changing ownership of % %.% to %', \n object_type.type_name, \n r.nspname, r.relname, new_owner;\n execute \n 'alter ' || object_type.type_name || ' '\n || r.nspname || '.' || r.relname \n || ' owner to ' || new_owner;\n end loop;\n end loop;\n\n for r in \n select p.proname, n.nspname,\n pg_catalog.pg_get_function_identity_arguments(p.oid) args\n from pg_catalog.pg_namespace n\n join pg_catalog.pg_proc p\n on p.pronamespace = n.oid\n where n.nspname = in_schema\n LOOP\n raise notice 'Changing ownership of function %.%(%) to %', \n r.nspname, r.proname, r.args, new_owner;\n execute \n 'alter function ' || r.nspname || '.' || r.proname ||\n '(' || r.args || ') owner to ' || new_owner;\n end LOOP;\n\n for r in \n select * \n from pg_catalog.pg_namespace n\n join pg_catalog.pg_ts_dict d \n on d.dictnamespace = n.oid\n where n.nspname = in_schema\n LOOP\n execute \n 'alter text search dictionary ' || r.nspname || '.' || r.dictname || \n ' owner to ' || new_owner;\n end LOOP;\nend;\n$$\nlanguage plpgsql;\n<\/pre>\n","protected":false},"excerpt":{"rendered":"Objects in Postgres can trivially have their ownership re-assigned, e.g.: alter table test_table set owner to gary; You can also trivially re-assign ownership of all objects a user owns: REASSIGN OWNED BY test_user TO gary However, you can\u2019t list all objects in the same way, and there is not a simple way to change the … <\/p>\n