DataOps-platform/deploy/docker/postgres/init/010-kestra-runtime.sql

-- Keep the orchestration runtime isolated from the DataOps application role.
-- This file is safe both during first-volume initialization and as a compose
-- bootstrap job for an existing local acceptance volume.
DO $$
BEGIN
    CREATE ROLE kestra_runtime LOGIN PASSWORD 'kestra-local-test-password';
EXCEPTION
    WHEN duplicate_object THEN
        ALTER ROLE kestra_runtime LOGIN PASSWORD 'kestra-local-test-password';
END $$;

SELECT 'CREATE DATABASE kestra OWNER kestra_runtime'
WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = 'kestra')\gexec

ALTER DATABASE kestra OWNER TO kestra_runtime;

\connect kestra
DO $$
DECLARE
    item record;
    object_kind text;
BEGIN
    FOR item IN
        SELECT n.nspname AS schema_name, c.relname AS object_name, c.relkind
        FROM pg_class c
        JOIN pg_namespace n ON n.oid = c.relnamespace
        JOIN pg_roles r ON r.oid = c.relowner
        WHERE r.rolname = 'dataops'
          AND n.nspname NOT IN ('pg_catalog', 'information_schema')
          AND n.nspname !~ '^pg_toast'
          AND c.relkind IN ('r', 'p', 'v', 'm')
    LOOP
        object_kind := CASE item.relkind
            WHEN 'S' THEN 'SEQUENCE'
            WHEN 'v' THEN 'VIEW'
            WHEN 'm' THEN 'MATERIALIZED VIEW'
            ELSE 'TABLE'
        END;
        EXECUTE format(
            'ALTER %s %I.%I OWNER TO kestra_runtime',
            object_kind,
            item.schema_name,
            item.object_name
        );
    END LOOP;
END $$;
GRANT ALL ON SCHEMA public TO kestra_runtime;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO kestra_runtime;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO kestra_runtime;