Tag: stored procedure

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’t 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:

alter function test(id int) set owner to gary;

To the best of my knowledge, the following script will change the owner of all objects in a schema, including some rarer types:

create or replace function chown(in_schema varchar, new_owner varchar) 
returns void as
$$
declare
  object_types varchar[];
  object_classes varchar[];
  object_type record;

  r record;
begin
  object_types = '{type,table,sequence,index,table,view}';
  object_classes = '{c,t,S,i,r,v}';

  for object_type in 
      select unnest(object_types) type_name, 
                unnest(object_classes) code
  loop
    for r in 
      execute '
          select n.nspname, c.relname 
          from pg_class c, pg_namespace n 
          where n.oid = c.relnamespace 
            and nspname = ''' || in_schema || '''
            and relkind = ''' || object_type.code || ''''
    loop 
      raise notice 'Changing ownership of % %.% to %', 
                  object_type.type_name, 
                  r.nspname, r.relname, new_owner;
      execute 
        'alter ' || object_type.type_name || ' '
                 || r.nspname || '.' || r.relname 
                 || ' owner to ' || new_owner;
    end loop;
  end loop;

  for r in 
    select  p.proname, n.nspname,
       pg_catalog.pg_get_function_identity_arguments(p.oid) args
    from    pg_catalog.pg_namespace n
    join    pg_catalog.pg_proc p
    on      p.pronamespace = n.oid
    where   n.nspname = in_schema
  LOOP
    raise notice 'Changing ownership of function %.%(%) to %', 
                 r.nspname, r.proname, r.args, new_owner;
    execute 
       'alter function ' || r.nspname || '.' || r.proname ||
       '(' || r.args || ') owner to ' || new_owner;
  end LOOP;

  for r in 
    select * 
    from pg_catalog.pg_namespace n
    join pg_catalog.pg_ts_dict d 
      on d.dictnamespace = n.oid
    where n.nspname = in_schema
  LOOP
    execute 
       'alter text search dictionary ' || r.nspname || '.' || r.dictname || 
       ' owner to ' || new_owner;
  end LOOP;
end;
$$
language plpgsql;