0

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?

4

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

  • I'll check REASSIGN. The function is still not working. Can you test it? – Lunar Sea Apr 23 '17 at 13:26
  • yes - object_types array had extra "table" next to last key. now should work – Vao Tsun Apr 23 '17 at 14:48
  • Now the function works just fine, but it seems to miss END loop;, END; and $$ before LANGUAGE plpgsql;. I do not understand why your function is much shorter than the one posted in the question. – Lunar Sea Apr 24 '17 at 6:36
  • It was shorter, because iterm "ate" the tail - updated with real definition – Vao Tsun Apr 24 '17 at 7:04
  • @LunarSea: My downvote is for concatenating user input into a dynamic SQL string without escaping. NEVER do this, not even for supposedly very private functions. Identifiers have to be treated as user input and escaped. Else you are open to SQL injection and other lesser problems. How to fix? See: stackoverflow.com/a/18386034/939860 (Vao, I'll happily I'll rescind the vote if you fix this and ping me.) – Erwin Brandstetter May 19 '17 at 14:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.