Skip to content

Instantly share code, notes, and snippets.

@CHERTS

CHERTS/pg_change_owner.sql

Last active September 2, 2024 14:17
Show Gist options
  • Save CHERTS/7046a7c45af97e516b18a27c96bd5af8 to your computer and use it in GitHub Desktop.
Save CHERTS/7046a7c45af97e516b18a27c96bd5af8 to your computer and use it in GitHub Desktop.
Download ZIP
How to change owner of PostgreSQL database/schema/table/sequence/views/functions/types?
Raw
pg_change_owner.sql
DO $$
DECLARE
sch RECORD;
tab RECORD;
seq RECORD;
viw RECORD;
mat RECORD;
fun RECORD;
ctype RECORD;
v_schema_exclude text[] := '{pg_catalog,information_schema,monitor,repack}';
v_newowner varchar := 'migrations';
BEGIN
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
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);
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);
END LOOP;
FOR viw IN
SELECT table_name
FROM information_schema.views
WHERE table_schema = sch.schemaname
AND table_name NOT LIKE 'pg_stat_%'
ORDER BY 1
LOOP
RAISE INFO 'ALTER VIEW %.% OWNER TO %;', sch.schemaname, viw.table_name, v_newowner;
EXECUTE format('ALTER VIEW %I.%I OWNER TO %I;', sch.schemaname, viw.table_name, v_newowner);
END LOOP;
FOR mat IN
SELECT matviewname
FROM pg_matviews
WHERE schemaname = sch.schemaname
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);
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 r.rolname NOT IN ('postgres')
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);
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
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
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);
END 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);
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);
END;
$$;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment