Created
August 5, 2017 09:19
-
-
Save mixerp/6f40ad4e5b038a40e29092cba154bb5d to your computer and use it in GitHub Desktop.
Make a user owner of the whole database.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| DO | |
| $$ | |
| DECLARE this record; | |
| BEGIN | |
| IF(CURRENT_USER = 'db_user') THEN | |
| RETURN; | |
| END IF; | |
| FOR this IN | |
| SELECT * FROM pg_tables | |
| WHERE NOT schemaname = ANY(ARRAY['pg_catalog', 'information_schema']) | |
| AND tableowner <> 'db_user' | |
| LOOP | |
| EXECUTE 'ALTER TABLE '|| this.schemaname || '.' || this.tablename ||' OWNER TO db_user;'; | |
| END LOOP; | |
| END | |
| $$ | |
| LANGUAGE plpgsql; | |
| DO | |
| $$ | |
| DECLARE this record; | |
| BEGIN | |
| IF(CURRENT_USER = 'db_user') THEN | |
| RETURN; | |
| END IF; | |
| FOR this IN | |
| SELECT oid::regclass::text as mat_view | |
| FROM pg_class | |
| WHERE relkind = 'm' | |
| LOOP | |
| EXECUTE 'ALTER TABLE '|| this.mat_view ||' OWNER TO db_user;'; | |
| END LOOP; | |
| END | |
| $$ | |
| LANGUAGE plpgsql; | |
| DO | |
| $$ | |
| DECLARE this record; | |
| BEGIN | |
| IF(CURRENT_USER = 'db_user') THEN | |
| RETURN; | |
| END IF; | |
| FOR this IN | |
| SELECT 'ALTER ' | |
| || CASE WHEN p.proisagg THEN 'AGGREGATE ' ELSE 'FUNCTION ' END | |
| || quote_ident(n.nspname) || '.' || quote_ident(p.proname) || '(' | |
| || pg_catalog.pg_get_function_identity_arguments(p.oid) || ') OWNER TO db_user;' AS sql | |
| FROM pg_catalog.pg_proc p | |
| JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace | |
| WHERE NOT n.nspname = ANY(ARRAY['pg_catalog', 'information_schema']) | |
| LOOP | |
| EXECUTE this.sql; | |
| END LOOP; | |
| END | |
| $$ | |
| LANGUAGE plpgsql; | |
| DO | |
| $$ | |
| DECLARE this record; | |
| BEGIN | |
| IF(CURRENT_USER = 'db_user') THEN | |
| RETURN; | |
| END IF; | |
| FOR this IN | |
| SELECT * FROM pg_views | |
| WHERE NOT schemaname = ANY(ARRAY['pg_catalog', 'information_schema']) | |
| AND viewowner <> 'db_user' | |
| LOOP | |
| EXECUTE 'ALTER VIEW '|| this.schemaname || '.' || this.viewname ||' OWNER TO db_user;'; | |
| END LOOP; | |
| END | |
| $$ | |
| LANGUAGE plpgsql; | |
| DO | |
| $$ | |
| DECLARE this record; | |
| BEGIN | |
| IF(CURRENT_USER = 'db_user') THEN | |
| RETURN; | |
| END IF; | |
| FOR this IN | |
| SELECT 'ALTER SCHEMA ' || nspname || ' OWNER TO db_user;' AS sql FROM pg_namespace | |
| WHERE nspname NOT LIKE 'pg_%' | |
| AND nspname <> 'information_schema' | |
| LOOP | |
| EXECUTE this.sql; | |
| END LOOP; | |
| END | |
| $$ | |
| LANGUAGE plpgsql; | |
| DO | |
| $$ | |
| DECLARE this record; | |
| BEGIN | |
| IF(CURRENT_USER = 'db_user') THEN | |
| RETURN; | |
| END IF; | |
| FOR this IN | |
| SELECT 'ALTER TYPE ' || n.nspname || '.' || t.typname || ' OWNER TO db_user;' AS sql | |
| 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 typtype NOT IN ('b') | |
| AND n.nspname NOT IN ('pg_catalog', 'information_schema') | |
| LOOP | |
| EXECUTE this.sql; | |
| END LOOP; | |
| END | |
| $$ | |
| LANGUAGE plpgsql; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment