Change the owner for objects in a Postgres schema

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;


Need help with Postgres? Contact me for Postgres consulting.

4 Replies to “Change the owner for objects in a Postgres schema”

  1. Finding this function really saved me a lot of time, but one small modification on line 24 where I wished to restrict the schemas this function would change is in error, I don’t understand why. My code now reads:

    EXECUTE ‘
    SELECT n.nspname, c.relname
    FROM pg_class c, pg_namespace n
    WHERE n.oid = c.relnamespace
    AND nspname ILIKE ‘dlk_%’
    AND relkind = ”’ || object_type.code || ””

    and I can run it successfully in psql with the variable substituted out, but it complains about a syntax error in the function.

  2. The function doesn’t work here:

    ERROR: column “schema_a” does not exist
    ZEILE 1: SELECT chown(schema_a,new_owner)
    ^
    ANFRAGE: SELECT chown(schema_a,new_owner)
    KONTEXT: PL/pgSQL function inline_code_block line 2 at PERFORM

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

    ERROR: column “schema_a” does not exist
    SQL state: 42703
    Context: PL/pgSQL function inline_code_block line 2 at PERFORM

  3. Sorry, I missed the quotes, but the function is still not working. Can you please help me?

    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

  4. I’m new to Postgres, lol.
    When I run the Alter table command, I have the error below:

    ERROR: “TABLE_NAME” is an error table
    DETAIL: ALTER TABLE is not allowed on error tables
    ********** Error **********
    ERROR: “” is an error table
    SQL state: 42809
    Detail: ALTER TABLE is not allowed on error tables

Leave a Reply

Your email address will not be published. Required fields are marked *