How to change owner of PostgreSQL database/schema/table/sequence/views/functions/types?

pg_change_owner.sql

CREATE OR REPLACE PROCEDURE change_owner_exec(sql_exec text) AS
$$
BEGIN
    RAISE INFO 'Exec: %', sql_exec;
    EXECUTE sql_exec;
    COMMIT;
    RETURN;
END;
$$
LANGUAGE plpgsql;

DO $$
DECLARE
	sql_text TEXT;
	sch RECORD;
	tab RECORD;
	seq RECORD;
	viw RECORD;
	mat RECORD;
	fun RECORD;
	ctype RECORD;
	v_schema_exclude text[] := '{pg_catalog,information_schema,monitor,repack,debezium,topology}';
	v_owner_exclude text[] := '{postgres}';
	v_newowner varchar := 'migrations';
	v_db_owner_role text := 'pg_database_owner';
	v_skip_check bool := false;
BEGIN
	IF NOT v_skip_check THEN
		IF v_db_owner_role NOT IN
		(SELECT role_name FROM information_schema.applicable_roles WHERE grantee = current_user)
		THEN
			RAISE WARNING 'Current user [%] is not membership in owner role [%]', current_user, v_db_owner_role;
			RETURN;
		END IF;
		IF v_newowner NOT IN
		(SELECT role_name FROM information_schema.applicable_roles WHERE grantee = current_user)
		THEN
			RAISE WARNING 'Current user [%] is not membership in role [%]', current_user, v_newowner;
			RETURN;
		END IF;
	END IF;
	RAISE INFO 'The new owner of tables, sequences, views, mat views, functions, schemas and the database will be [%]', v_newowner;
	FOR sch IN
		SELECT schemaname
		FROM pg_tables WHERE NOT (schemaname = ANY(v_schema_exclude))
		GROUP BY schemaname
	LOOP
		RAISE INFO 'Use schema %', sch.schemaname;
		FOR tab IN
			SELECT tablename
			FROM pg_tables
			WHERE schemaname = sch.schemaname AND NOT (tableowner = ANY(v_owner_exclude))
			ORDER BY 1
		LOOP
			--RAISE INFO 'ALTER TABLE %.% OWNER TO %;', sch.schemaname, tab.tablename, v_newowner;
			--EXECUTE format('ALTER TABLE %I.%I OWNER TO %I;', sch.schemaname, tab.tablename, v_newowner);
			sql_text := format('ALTER TABLE %I.%I OWNER TO %I;', sch.schemaname, tab.tablename, v_newowner);
			--RAISE INFO 'Exec: %', sql_text;
			CALL change_owner_exec(sql_text);
		END LOOP;
		FOR seq IN
			SELECT sequence_name 
			FROM information_schema.sequences
			WHERE sequence_schema = sch.schemaname
			ORDER BY 1
		LOOP
			--RAISE INFO 'ALTER SEQUENCE %.% OWNER TO %;', sch.schemaname, seq.sequence_name, v_newowner;
			--EXECUTE format('ALTER SEQUENCE %I.%I OWNER TO %I;', sch.schemaname, seq.sequence_name, v_newowner);
			sql_text := format('ALTER SEQUENCE %I.%I OWNER TO %I;', sch.schemaname, seq.sequence_name, v_newowner);
			CALL change_owner_exec(sql_text);
		END LOOP;
		FOR viw IN
			SELECT viewname FROM pg_catalog.pg_views
			WHERE schemaname = sch.schemaname
				AND NOT (viewowner = ANY(v_owner_exclude))
			ORDER BY 1
		LOOP
			--RAISE INFO 'ALTER VIEW %.% OWNER TO %;', sch.schemaname, viw.viewname, v_newowner;
			--EXECUTE format('ALTER VIEW %I.%I OWNER TO %I;', sch.schemaname, viw.viewname, v_newowner);
			sql_text := format('ALTER VIEW %I.%I OWNER TO %I;', sch.schemaname, viw.viewname, v_newowner);
			CALL change_owner_exec(sql_text);
		END LOOP;
		FOR mat IN
			SELECT matviewname
			FROM pg_catalog.pg_matviews
			WHERE schemaname = sch.schemaname
				AND NOT (matviewowner = ANY(v_owner_exclude))
			ORDER BY 1
		LOOP
			--RAISE INFO 'ALTER TABLE %.% OWNER TO %;', sch.schemaname, mat.matviewname, v_newowner;
			--EXECUTE format('ALTER TABLE %I.%I OWNER TO %I;', sch.schemaname, mat.matviewname, v_newowner);
			sql_text := format('ALTER TABLE %I.%I OWNER TO %I;', sch.schemaname, mat.matviewname, v_newowner);
			CALL change_owner_exec(sql_text);
		END LOOP;
		FOR fun IN
			SELECT p.proname AS fname, pg_get_function_identity_arguments(p.oid) AS fargs
			FROM pg_proc p JOIN pg_namespace nsp ON p.pronamespace = nsp.oid JOIN pg_roles r ON p.proowner = r.oid
			WHERE nsp.nspname = sch.schemaname AND NOT (r.rolname = ANY(v_owner_exclude)) AND p.proname NOT IN ('change_owner_exec')
			ORDER BY 1
		LOOP
			--RAISE INFO 'ALTER FUNCTION %.%(%) OWNER TO %;', sch.schemaname, fun.fname, fun.fargs, v_newowner;
			--EXECUTE format('ALTER FUNCTION %I.%I(%I) OWNER TO %I;', sch.schemaname, fun.fname, fun.fargs, v_newowner);
			IF fun.fargs = '' THEN
				sql_text := format('ALTER FUNCTION %I.%I OWNER TO %I;', sch.schemaname, fun.fname, v_newowner);
			ELSE
				sql_text := format('ALTER FUNCTION %I.%I(%I) OWNER TO %I;', sch.schemaname, fun.fname, fun.fargs, v_newowner);
			END IF;
			CALL change_owner_exec(sql_text);
		END LOOP;
		FOR ctype IN
			SELECT t.typname as type
			FROM pg_type t 
			LEFT JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace 
			LEFT JOIN pg_roles r ON r.oid = t.typowner
			WHERE (t.typrelid = 0 OR (SELECT c.relkind = 'c' FROM pg_catalog.pg_class c WHERE c.oid = t.typrelid)) 
			AND NOT EXISTS(SELECT 1 FROM pg_catalog.pg_type el WHERE el.oid = t.typelem AND el.typarray = t.oid)
			AND n.nspname = sch.schemaname AND NOT (r.rolname = ANY(v_owner_exclude))
		LOOP
			--RAISE INFO 'ALTER TYPE %.% OWNER TO %;', sch.schemaname, ctype.type, v_newowner;
			--EXECUTE format('ALTER TYPE %I.%I OWNER TO %I;', sch.schemaname, ctype.type, v_newowner);
			sql_text := format('ALTER TYPE %I.%I OWNER TO %I;', sch.schemaname, ctype.type, v_newowner);
			CALL change_owner_exec(sql_text);
		END LOOP;
	END LOOP;
	FOR sch IN
		SELECT schemaname
		FROM pg_tables WHERE NOT (schemaname = ANY(v_schema_exclude)) AND schemaname NOT IN ('public')
		GROUP BY schemaname
	LOOP
		--RAISE INFO 'ALTER SCHEMA % OWNER TO %;', quote_ident(sch.schemaname), v_newowner;
		--EXECUTE format('ALTER SCHEMA %I OWNER TO %I;', quote_ident(sch.schemaname), v_newowner);
		sql_text := format('ALTER SCHEMA %I OWNER TO %I;', quote_ident(sch.schemaname), v_newowner);
		CALL change_owner_exec(sql_text);
	END LOOP;
	--RAISE INFO 'ALTER DATABASE % OWNER TO %;', quote_ident(current_database()), v_newowner;
	--EXECUTE format('ALTER DATABASE %I OWNER TO %I;', quote_ident(current_database()), v_newowner);
	sql_text := format('ALTER DATABASE %I OWNER TO %I;', quote_ident(current_database()), v_newowner);
	CALL change_owner_exec(sql_text);
END;
$$;

DROP PROCEDURE IF EXISTS change_owner_exec;

This script can be used if you have limited access rights to the instance (for example, you use Managed Service for PostgreSQL at a Cloud provider).
If you have full rights and access is available under the postgres user - use the standard SQL command - REASSIGN OWNED