fix postgre db migration related permissions

fix-db-ownership.sql

DO $$
    DECLARE
        v_role   text := current_database();
        v_schema record;
        v_obj    record;
    BEGIN
        -- Database privileges
        EXECUTE format('GRANT ALL PRIVILEGES ON DATABASE %I TO %I', v_role, v_role);

        FOR v_schema IN
            SELECT n.oid, n.nspname
            FROM pg_namespace n
            WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
              AND n.nspname NOT LIKE 'pg_toast%'
              AND n.nspname NOT LIKE 'pg_temp_%'
            LOOP
                -- Make the role owner of the schema itself
                EXECUTE format('ALTER SCHEMA %I OWNER TO %I', v_schema.nspname, v_role);

                -- Also grant normal schema privileges
                EXECUTE format('GRANT USAGE, CREATE ON SCHEMA %I TO %I', v_schema.nspname, v_role);

                -- Tables / views / matviews / foreign tables / partitioned tables
                FOR v_obj IN
                    SELECT c.relname, c.relkind
                    FROM pg_class c
                    WHERE c.relnamespace = v_schema.oid
                      AND c.relkind IN ('r','p','v','m','f')
                    LOOP
                        EXECUTE format(
                                'ALTER %s %I.%I OWNER TO %I',
                                CASE v_obj.relkind
                                    WHEN 'r' THEN 'TABLE'
                                    WHEN 'p' THEN 'TABLE'
                                    WHEN 'v' THEN 'VIEW'
                                    WHEN 'm' THEN 'MATERIALIZED VIEW'
                                    WHEN 'f' THEN 'FOREIGN TABLE'
                                    END,
                                v_schema.nspname,
                                v_obj.relname,
                                v_role
                                );
                    END LOOP;

                -- Sequences
                FOR v_obj IN
                    SELECT c.relname
                    FROM pg_class c
                    WHERE c.relnamespace = v_schema.oid
                      AND c.relkind = 'S'
                    LOOP
                        EXECUTE format(
                                'ALTER SEQUENCE %I.%I OWNER TO %I',
                                v_schema.nspname,
                                v_obj.relname,
                                v_role
                                );
                    END LOOP;

                -- Functions / procedures / aggregates
                FOR v_obj IN
                    SELECT p.oid, p.proname
                    FROM pg_proc p
                    WHERE p.pronamespace = v_schema.oid
                    LOOP
                        EXECUTE format(
                                'ALTER ROUTINE %I.%I(%s) OWNER TO %I',
                                v_schema.nspname,
                                v_obj.proname,
                                pg_get_function_identity_arguments(v_obj.oid),
                                v_role
                                );
                    END LOOP;

                -- Existing object privileges
                EXECUTE format('GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA %I TO %I', v_schema.nspname, v_role);
                EXECUTE format('GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA %I TO %I', v_schema.nspname, v_role);
                EXECUTE format('GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA %I TO %I', v_schema.nspname, v_role);

                -- Future object privileges for objects created by the role running this script
                EXECUTE format('ALTER DEFAULT PRIVILEGES IN SCHEMA %I GRANT ALL PRIVILEGES ON TABLES TO %I', v_schema.nspname, v_role);
                EXECUTE format('ALTER DEFAULT PRIVILEGES IN SCHEMA %I GRANT ALL PRIVILEGES ON SEQUENCES TO %I', v_schema.nspname, v_role);
                EXECUTE format('ALTER DEFAULT PRIVILEGES IN SCHEMA %I GRANT ALL PRIVILEGES ON ROUTINES TO %I', v_schema.nspname, v_role);
            END LOOP;
    END
$$;