Change owner of all schema objects

Question

I want to change the owner of all objects in a schema. To do this I use the following function:

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;

(https://www.garysieling.com/blog/postgres-change-owner-all-objects-in-schema)

Unfortunately, the function doesn't work as expected in some cases.

CREATE ROLE user_old LOGIN;
CREATE ROLE user_new LOGIN;

CREATE SCHEMA schema_a AUTHORIZATION user_old;

CREATE TABLE schema_a.test_a (
id serial NOT NULL,
CONSTRAINT test_a_pkey PRIMARY KEY (id)
);

ALTER TABLE schema_a.test_a OWNER TO user_old;

SELECT chown('schema_a', 'user_new')

NOTICE: Changing ownership of sequence schema_a.test_a_id_seq to user_new
ERROR: cannot change owner of sequence “test_a_id_seq”
DETAIL: Sequence “test_a_id_seq” is linked to table “test_a”.
KONTEXT: SQL statement “alter sequence schema_a.test_a_id_seq owner to user_new”
PL/pgSQL function chown(character varying,character varying) line 27 at EXECUTE

********** Error **********

ERROR: cannot change owner of sequence “test_a_id_seq”
SQL state: 0A000
Detail: Sequence “test_a_id_seq” is linked to table “test_a”.
Context: SQL statement “alter sequence schema_a.test_a_id_seq owner to user_new”
PL/pgSQL function chown(character varying,character varying) line 27 at EXECUTE

Does anyone know how to fix this?

Answer 1

to avoid mentionned error, try changing the order:

CREATE OR REPLACE FUNCTION public.chown(in_schema character varying, new_owner character varying)
 RETURNS void
 LANGUAGE plpgsql
AS $function$
DECLARE
  object_types VARCHAR[];
  object_classes VARCHAR[];
  object_type record;

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

  FOR object_type IN
      SELECT unnest(object_types) type_name,
                unnest(object_classes) code
  loop
    FOR r IN
      EXECUTE format('
          select n.nspname, c.relname
          from pg_class c, pg_namespace n
          where n.oid = c.relnamespace
            and nspname = %I
            and relkind = %L',in_schema,object_type.code)
    loop
      raise notice 'Changing ownership of % %.% to %',
                  object_type.type_name,
                  r.nspname, r.relname, new_owner;
      EXECUTE format(
        'alter %s %I.%I owner to %I'
        , object_type.type_name, r.nspname, r.relname,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 format(
       'alter function %I.%I (%s) owner to %I', r.nspname, r.proname, r.args, 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 format(
       'alter text search dictionary %I.%I owner to %I', r.nspname, r.dictname, new_owner );
  END LOOP;
END;
$function$

Also read about REASSIGN OWNED

Alse here is info on relkind if you wander why I changed the order:

r = ordinary table, i = index, S = sequence, v = view, m = materialized view, c = composite type, t = TOAST table, f = foreign table