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;
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.
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
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
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.
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