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;

5 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

    1. You need to change order of object_types:
      object_types = ‘{type,table,index,table,view,sequence,materialized view}’;
      object_classes = ‘{c,t,i,r,v,S,m}’;

      Change owner of table before sequence. Table sequence owner change with the alter table. And after, other sequence change with alter sequence.

      I had materialized view.

  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 to Johnny C. Cancel reply

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