change_postgres_ownership.sql

change_postgres_ownership.sql

DO
  $$
DECLARE
    rec RECORD;
BEGIN
    -- 更改表 (Tables)
    FOR rec IN 
        SELECT n.nspname, c.relname 
        FROM pg_class c
        JOIN pg_namespace n ON c.relnamespace = n.oid
        WHERE c.relkind = 'r' 
          AND n.nspname NOT LIKE 'pg_%' 
          AND n.nspname != 'information_schema'
    LOOP
        EXECUTE 'ALTER TABLE ' || quote_ident(rec.nspname) || '.' || quote_ident(rec.relname) || ' OWNER TO your_new_owner';
        RAISE NOTICE 'Changed owner of table %.% to your_new_owner', rec.nspname, rec.relname;
    END LOOP;

    -- 更改序列 (Sequences)
    FOR rec IN 
        SELECT n.nspname, c.relname 
        FROM pg_class c
        JOIN pg_namespace n ON c.relnamespace = n.oid
        WHERE c.relkind = 'S' 
          AND n.nspname NOT LIKE 'pg_%' 
          AND n.nspname != 'information_schema'
    LOOP
        EXECUTE 'ALTER SEQUENCE ' || quote_ident(rec.nspname) || '.' || quote_ident(rec.relname) || ' OWNER TO your_new_owner';
        RAISE NOTICE 'Changed owner of sequence %.% to your_new_owner', rec.nspname, rec.relname;
    END LOOP;

    -- 更改視圖 (Views)
    FOR rec IN 
        SELECT n.nspname, c.relname 
        FROM pg_class c
        JOIN pg_namespace n ON c.relnamespace = n.oid
        WHERE c.relkind = 'v' 
          AND n.nspname NOT LIKE 'pg_%' 
          AND n.nspname != 'information_schema'
    LOOP
        EXECUTE 'ALTER VIEW ' || quote_ident(rec.nspname) || '.' || quote_ident(rec.relname) || ' OWNER TO your_new_owner';
        RAISE NOTICE 'Changed owner of view %.% to your_new_owner', rec.nspname, rec.relname;
    END LOOP;

    -- 更改物化視圖 (Materialized Views)
    FOR rec IN 
        SELECT n.nspname, c.relname 
        FROM pg_class c
        JOIN pg_namespace n ON c.relnamespace = n.oid
        WHERE c.relkind = 'm' 
          AND n.nspname NOT LIKE 'pg_%' 
          AND n.nspname != 'information_schema'
    LOOP
        EXECUTE 'ALTER MATERIALIZED VIEW ' || quote_ident(rec.nspname) || '.' || quote_ident(rec.relname) || ' OWNER TO your_new_owner';
        RAISE NOTICE 'Changed owner of materialized view %.% to your_new_owner', rec.nspname, rec.relname;
    END LOOP;

    -- 更改 ENUM 類型 (Enums)
    FOR rec IN 
        SELECT n.nspname, t.typname 
        FROM pg_type t
        JOIN pg_namespace n ON t.typnamespace = n.oid
        WHERE t.typcategory = 'E' 
          AND n.nspname NOT LIKE 'pg_%' 
          AND n.nspname != 'information_schema'
    LOOP
        EXECUTE 'ALTER TYPE ' || quote_ident(rec.nspname) || '.' || quote_ident(rec.typname) || ' OWNER TO your_new_owner';
        RAISE NOTICE 'Changed owner of enum %.% to your_new_owner', rec.nspname, rec.typname;
    END LOOP;

    -- 更改域 (Domains)
    FOR rec IN 
        SELECT n.nspname, t.typname 
        FROM pg_type t
        JOIN pg_namespace n ON t.typnamespace = n.oid
        WHERE t.typcategory = 'D' 
          AND n.nspname NOT LIKE 'pg_%' 
          AND n.nspname != 'information_schema'
    LOOP
        EXECUTE 'ALTER DOMAIN ' || quote_ident(rec.nspname) || '.' || quote_ident(rec.typname) || ' OWNER TO your_new_owner';
        RAISE NOTICE 'Changed owner of domain %.% to your_new_owner', rec.nspname, rec.typname;
    END LOOP;

    -- 更改函數 (Functions)，包含參數才是唯一值
    FOR rec IN 
        SELECT 
    	n.nspname, 
    	p.proname, 
    	pg_get_function_identity_arguments(p.oid) AS args 
        FROM pg_proc p
        JOIN pg_namespace n ON p.pronamespace = n.oid
        WHERE n.nspname NOT LIKE 'pg_%' 
          AND n.nspname != 'information_schema'
    LOOP
        EXECUTE 'ALTER FUNCTION ' || quote_ident(rec.nspname) || '.' || quote_ident(rec.proname) 
    	    || '(' || rec.args || ')' || ' OWNER TO your_new_owner';
        RAISE NOTICE 'Changed owner of function %.%(%) to your_new_owner', rec.nspname, rec.proname, rec.args;
    END LOOP;
    -- 可選：更改 schema 本身的擁有者
    FOR rec IN 
        SELECT nspname 
        FROM pg_namespace 
        WHERE nspname NOT LIKE 'pg_%' 
          AND nspname != 'information_schema'
    LOOP
        EXECUTE 'ALTER SCHEMA ' || quote_ident(rec.nspname) || ' OWNER TO your_new_owner';
        RAISE NOTICE 'Changed owner of schema % to your_new_owner', rec.nspname;
    END LOOP;
END $$;