> ## Documentation Index
> Fetch the complete documentation index at: https://docs.seekr.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Connect SeekrFlow to PostgreSQL

> Create SeekrFlow's databases and login roles in your PostgreSQL instance.

SeekrFlow stores its data in PostgreSQL. You created the instance in [Provision PostgreSQL](/self-managed/flow/provision/postgresql). This page creates SeekrFlow's databases and login roles inside it:

* **Databases:** `seekrflow`, `agents`, `user_auth`, `metaflow`, and `mlflow`, plus `keycloak` if you enable Keycloak. Each has its own read-write and read-only roles.
* **Login roles:** `seekrflow_app`, which SeekrFlow runs as, and `seekrflow_keycloak` if you enable Keycloak.

Keycloak gets its own role because it stores password hashes and client secrets. SeekrFlow's role can't read Keycloak's tables, and Keycloak's role can't reach the other databases.

Run these steps as your instance's admin account, the one you set when you created the instance. SeekrFlow also uses this account for database migrations.

## Create the databases and roles

<Tabs>
  <Tab title="With the sfe CLI">
    `sfe` reads the admin and `seekrflow_app` credentials from the `pg-creds` secret you created in [Create SeekrFlow secrets](/self-managed/flow/prepare/secrets-and-tokens). It reads the Keycloak credentials from `keycloak-credentials`.

    ```bash theme={null}
    sfe db setup -p <profile-name> -r <version>
    ```

    Include `-r`. Without it, `sfe` can't tell whether you enabled Keycloak, and asks for a Keycloak password.

    If your database is reachable only from inside your network, add `--in-cluster`. `sfe` then runs the setup as a job inside the cluster.
  </Tab>

  <Tab title="Without the sfe CLI">
    Run this script with `psql`, connected to the `postgres` database as your admin account. It prompts for the `seekrflow_app` and `seekrflow_keycloak` passwords, which must match your `pg-creds` and `keycloak-credentials` secrets. If you don't enable Keycloak, delete the two Keycloak sections first.

    <Accordion title="SeekrFlow database script">
      ```sql theme={null}
      -- SeekrFlow databases and roles. Run with psql, connected to the postgres database as your admin account.
      -- If you don't enable Keycloak, delete the 06_keycloak and 08_keycloak_user sections.

      \prompt 'Password for seekrflow_app: ' app_password
      \prompt 'Password for seekrflow_keycloak: ' keycloak_password


      -- 01_seekrflow
      \connect postgres
      -- [GLOBAL] runs against the 'postgres' admin database
      CREATE DATABASE seekrflow;

      DO $$
      BEGIN
        IF NOT EXISTS (SELECT FROM pg_catalog.pg_roles WHERE rolname = 'seekrflow_read_write') THEN
          CREATE ROLE seekrflow_read_write NOLOGIN;
        END IF;
        IF NOT EXISTS (SELECT FROM pg_catalog.pg_roles WHERE rolname = 'seekrflow_read_only') THEN
          CREATE ROLE seekrflow_read_only NOLOGIN;
        END IF;
      END $$;

      -- 01_seekrflow
      \connect seekrflow
      REVOKE CONNECT ON DATABASE seekrflow FROM PUBLIC;
      REVOKE ALL ON SCHEMA public FROM PUBLIC;

      GRANT CONNECT, TEMPORARY, CREATE ON DATABASE seekrflow TO current_user;
      GRANT ALL ON SCHEMA public TO current_user;

      -- Everything below acts on tables this admin may not hold. On a managed
      -- instance the admin is not a superuser, and its membership in the role that
      -- owns the tables can be granted WITH INHERIT FALSE -- pg_has_role then reports
      -- MEMBER true and USAGE false, and every GRANT and ALTER on those tables is
      -- refused with "permission denied for table". Re-granting is allowed because
      -- the admin holds ADMIN OPTION; the plain form takes its INHERIT from rolinherit
      -- and so needs no version-gated syntax.
      DO $$
      DECLARE r record;
      BEGIN
        FOR r IN
          SELECT DISTINCT pg_get_userbyid(c.relowner) AS owner
          FROM pg_class c
          JOIN pg_namespace n ON n.oid = c.relnamespace
          WHERE n.nspname = 'public'
            AND c.relkind IN ('r', 'p', 'S', 'v')
            AND NOT pg_has_role(current_user, c.relowner, 'USAGE')
        LOOP
          EXECUTE format('GRANT %I TO current_user', r.owner);
        END LOOP;
      END $$;

      GRANT CONNECT ON DATABASE seekrflow TO seekrflow_read_only;
      GRANT USAGE ON SCHEMA public TO seekrflow_read_only;
      GRANT SELECT ON ALL TABLES IN SCHEMA public TO seekrflow_read_only;
      GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO seekrflow_read_only;
      ALTER DEFAULT PRIVILEGES IN SCHEMA public
      GRANT SELECT ON TABLES TO seekrflow_read_only;

      GRANT seekrflow_read_only TO seekrflow_read_write;
      GRANT CONNECT, TEMPORARY, CREATE ON DATABASE seekrflow TO seekrflow_read_write;
      GRANT ALL ON SCHEMA public TO seekrflow_read_write;
      GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO seekrflow_read_write;
      GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO seekrflow_read_write;
      ALTER DEFAULT PRIVILEGES IN SCHEMA public
      GRANT ALL ON TABLES TO seekrflow_read_write;
      ALTER DEFAULT PRIVILEGES IN SCHEMA public
      GRANT ALL ON SEQUENCES TO seekrflow_read_write;

      -- Ownership, not just privileges. ALTER TABLE requires ownership and GRANT ALL does
      -- not confer it, so an app that runs DDL at startup (mlflow's alembic) fails with
      -- "must be owner" on tables an earlier release created under a different role.
      -- Owning them as the group role lets any member migrate them and survives an
      -- app-role rename. current_user is granted the role first so the admin that runs
      -- this script does not lose the ability to manage what it just gave away.
      -- Re-running repairs ownership on an existing install; on a fresh one the loops
      -- find nothing and this is a no-op.
      GRANT seekrflow_read_write TO current_user;
      DO $$
      DECLARE r record;
      BEGIN
        FOR r IN SELECT tablename FROM pg_tables WHERE schemaname = 'public' LOOP
          EXECUTE format('ALTER TABLE public.%I OWNER TO seekrflow_read_write', r.tablename);
        END LOOP;
        FOR r IN SELECT sequencename FROM pg_sequences WHERE schemaname = 'public' LOOP
          EXECUTE format('ALTER SEQUENCE public.%I OWNER TO seekrflow_read_write', r.sequencename);
        END LOOP;
        FOR r IN SELECT viewname FROM pg_views WHERE schemaname = 'public' LOOP
          EXECUTE format('ALTER VIEW public.%I OWNER TO seekrflow_read_write', r.viewname);
        END LOOP;
      END $$;

      -- 02_agents
      \connect postgres
      -- [GLOBAL] runs against the 'postgres' admin database
      CREATE DATABASE agents;

      DO $$
      BEGIN
        IF NOT EXISTS (SELECT FROM pg_catalog.pg_roles WHERE rolname = 'agents_read_write') THEN
          CREATE ROLE agents_read_write NOLOGIN;
        END IF;
        IF NOT EXISTS (SELECT FROM pg_catalog.pg_roles WHERE rolname = 'agents_read_only') THEN
          CREATE ROLE agents_read_only NOLOGIN;
        END IF;
      END $$;

      -- 02_agents
      \connect agents
      REVOKE CONNECT ON DATABASE agents FROM PUBLIC;
      REVOKE ALL ON SCHEMA public FROM PUBLIC;

      GRANT CONNECT, TEMPORARY, CREATE ON DATABASE agents TO current_user;
      GRANT ALL ON SCHEMA public TO current_user;

      -- Everything below acts on tables this admin may not hold. On a managed
      -- instance the admin is not a superuser, and its membership in the role that
      -- owns the tables can be granted WITH INHERIT FALSE -- pg_has_role then reports
      -- MEMBER true and USAGE false, and every GRANT and ALTER on those tables is
      -- refused with "permission denied for table". Re-granting is allowed because
      -- the admin holds ADMIN OPTION; the plain form takes its INHERIT from rolinherit
      -- and so needs no version-gated syntax.
      DO $$
      DECLARE r record;
      BEGIN
        FOR r IN
          SELECT DISTINCT pg_get_userbyid(c.relowner) AS owner
          FROM pg_class c
          JOIN pg_namespace n ON n.oid = c.relnamespace
          WHERE n.nspname = 'public'
            AND c.relkind IN ('r', 'p', 'S', 'v')
            AND NOT pg_has_role(current_user, c.relowner, 'USAGE')
        LOOP
          EXECUTE format('GRANT %I TO current_user', r.owner);
        END LOOP;
      END $$;

      GRANT CONNECT ON DATABASE agents TO agents_read_only;
      GRANT USAGE ON SCHEMA public TO agents_read_only;
      GRANT SELECT ON ALL TABLES IN SCHEMA public TO agents_read_only;
      GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO agents_read_only;
      ALTER DEFAULT PRIVILEGES IN SCHEMA public
      GRANT SELECT ON TABLES TO agents_read_only;

      GRANT agents_read_only TO agents_read_write;
      GRANT CONNECT, TEMPORARY, CREATE ON DATABASE agents TO agents_read_write;
      GRANT ALL ON SCHEMA public TO agents_read_write;
      GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO agents_read_write;
      GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO agents_read_write;
      ALTER DEFAULT PRIVILEGES IN SCHEMA public
      GRANT ALL ON TABLES TO agents_read_write;
      ALTER DEFAULT PRIVILEGES IN SCHEMA public
      GRANT ALL ON SEQUENCES TO agents_read_write;

      -- Ownership, not just privileges. ALTER TABLE requires ownership and GRANT ALL does
      -- not confer it, so an app that runs DDL at startup (mlflow's alembic) fails with
      -- "must be owner" on tables an earlier release created under a different role.
      -- Owning them as the group role lets any member migrate them and survives an
      -- app-role rename. current_user is granted the role first so the admin that runs
      -- this script does not lose the ability to manage what it just gave away.
      -- Re-running repairs ownership on an existing install; on a fresh one the loops
      -- find nothing and this is a no-op.
      GRANT agents_read_write TO current_user;
      DO $$
      DECLARE r record;
      BEGIN
        FOR r IN SELECT tablename FROM pg_tables WHERE schemaname = 'public' LOOP
          EXECUTE format('ALTER TABLE public.%I OWNER TO agents_read_write', r.tablename);
        END LOOP;
        FOR r IN SELECT sequencename FROM pg_sequences WHERE schemaname = 'public' LOOP
          EXECUTE format('ALTER SEQUENCE public.%I OWNER TO agents_read_write', r.sequencename);
        END LOOP;
        FOR r IN SELECT viewname FROM pg_views WHERE schemaname = 'public' LOOP
          EXECUTE format('ALTER VIEW public.%I OWNER TO agents_read_write', r.viewname);
        END LOOP;
      END $$;

      -- 03_userauth
      \connect postgres
      -- [GLOBAL] runs against the 'postgres' admin database
      CREATE DATABASE user_auth;

      DO $$
      BEGIN
        IF NOT EXISTS (SELECT FROM pg_catalog.pg_roles WHERE rolname = 'user_auth_read_write') THEN
          CREATE ROLE user_auth_read_write NOLOGIN;
        END IF;
        IF NOT EXISTS (SELECT FROM pg_catalog.pg_roles WHERE rolname = 'user_auth_read_only') THEN
          CREATE ROLE user_auth_read_only NOLOGIN;
        END IF;
      END $$;

      -- 03_userauth
      \connect user_auth
      REVOKE CONNECT ON DATABASE user_auth FROM PUBLIC;
      REVOKE ALL ON SCHEMA public FROM PUBLIC;

      GRANT CONNECT, TEMPORARY, CREATE ON DATABASE user_auth TO current_user;
      GRANT ALL ON SCHEMA public TO current_user;

      -- Everything below acts on tables this admin may not hold. On a managed
      -- instance the admin is not a superuser, and its membership in the role that
      -- owns the tables can be granted WITH INHERIT FALSE -- pg_has_role then reports
      -- MEMBER true and USAGE false, and every GRANT and ALTER on those tables is
      -- refused with "permission denied for table". Re-granting is allowed because
      -- the admin holds ADMIN OPTION; the plain form takes its INHERIT from rolinherit
      -- and so needs no version-gated syntax.
      DO $$
      DECLARE r record;
      BEGIN
        FOR r IN
          SELECT DISTINCT pg_get_userbyid(c.relowner) AS owner
          FROM pg_class c
          JOIN pg_namespace n ON n.oid = c.relnamespace
          WHERE n.nspname = 'public'
            AND c.relkind IN ('r', 'p', 'S', 'v')
            AND NOT pg_has_role(current_user, c.relowner, 'USAGE')
        LOOP
          EXECUTE format('GRANT %I TO current_user', r.owner);
        END LOOP;
      END $$;

      GRANT CONNECT ON DATABASE user_auth TO user_auth_read_only;
      GRANT USAGE ON SCHEMA public TO user_auth_read_only;
      GRANT SELECT ON ALL TABLES IN SCHEMA public TO user_auth_read_only;
      GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO user_auth_read_only;
      ALTER DEFAULT PRIVILEGES IN SCHEMA public
      GRANT SELECT ON TABLES TO user_auth_read_only;

      GRANT user_auth_read_only TO user_auth_read_write;
      GRANT CONNECT, TEMPORARY, CREATE ON DATABASE user_auth TO user_auth_read_write;
      GRANT ALL ON SCHEMA public TO user_auth_read_write;
      GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO user_auth_read_write;
      GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO user_auth_read_write;
      ALTER DEFAULT PRIVILEGES IN SCHEMA public
      GRANT ALL ON TABLES TO user_auth_read_write;
      ALTER DEFAULT PRIVILEGES IN SCHEMA public
      GRANT ALL ON SEQUENCES TO user_auth_read_write;

      -- Ownership, not just privileges. ALTER TABLE requires ownership and GRANT ALL does
      -- not confer it, so an app that runs DDL at startup (mlflow's alembic) fails with
      -- "must be owner" on tables an earlier release created under a different role.
      -- Owning them as the group role lets any member migrate them and survives an
      -- app-role rename. current_user is granted the role first so the admin that runs
      -- this script does not lose the ability to manage what it just gave away.
      -- Re-running repairs ownership on an existing install; on a fresh one the loops
      -- find nothing and this is a no-op.
      GRANT user_auth_read_write TO current_user;
      DO $$
      DECLARE r record;
      BEGIN
        FOR r IN SELECT tablename FROM pg_tables WHERE schemaname = 'public' LOOP
          EXECUTE format('ALTER TABLE public.%I OWNER TO user_auth_read_write', r.tablename);
        END LOOP;
        FOR r IN SELECT sequencename FROM pg_sequences WHERE schemaname = 'public' LOOP
          EXECUTE format('ALTER SEQUENCE public.%I OWNER TO user_auth_read_write', r.sequencename);
        END LOOP;
        FOR r IN SELECT viewname FROM pg_views WHERE schemaname = 'public' LOOP
          EXECUTE format('ALTER VIEW public.%I OWNER TO user_auth_read_write', r.viewname);
        END LOOP;
      END $$;

      -- 04_metaflow
      \connect postgres
      -- [GLOBAL] runs against the 'postgres' admin database
      CREATE DATABASE metaflow;

      DO $$
      BEGIN
        IF NOT EXISTS (SELECT FROM pg_catalog.pg_roles WHERE rolname = 'metaflow_read_write') THEN
          CREATE ROLE metaflow_read_write NOLOGIN;
        END IF;
        IF NOT EXISTS (SELECT FROM pg_catalog.pg_roles WHERE rolname = 'metaflow_read_only') THEN
          CREATE ROLE metaflow_read_only NOLOGIN;
        END IF;
      END $$;

      -- 04_metaflow
      \connect metaflow
      REVOKE CONNECT ON DATABASE metaflow FROM PUBLIC;
      REVOKE ALL ON SCHEMA public FROM PUBLIC;

      GRANT CONNECT, TEMPORARY, CREATE ON DATABASE metaflow TO current_user;
      GRANT ALL ON SCHEMA public TO current_user;

      -- Everything below acts on tables this admin may not hold. On a managed
      -- instance the admin is not a superuser, and its membership in the role that
      -- owns the tables can be granted WITH INHERIT FALSE -- pg_has_role then reports
      -- MEMBER true and USAGE false, and every GRANT and ALTER on those tables is
      -- refused with "permission denied for table". Re-granting is allowed because
      -- the admin holds ADMIN OPTION; the plain form takes its INHERIT from rolinherit
      -- and so needs no version-gated syntax.
      DO $$
      DECLARE r record;
      BEGIN
        FOR r IN
          SELECT DISTINCT pg_get_userbyid(c.relowner) AS owner
          FROM pg_class c
          JOIN pg_namespace n ON n.oid = c.relnamespace
          WHERE n.nspname = 'public'
            AND c.relkind IN ('r', 'p', 'S', 'v')
            AND NOT pg_has_role(current_user, c.relowner, 'USAGE')
        LOOP
          EXECUTE format('GRANT %I TO current_user', r.owner);
        END LOOP;
      END $$;

      GRANT CONNECT ON DATABASE metaflow TO metaflow_read_only;
      GRANT USAGE ON SCHEMA public TO metaflow_read_only;
      GRANT SELECT ON ALL TABLES IN SCHEMA public TO metaflow_read_only;
      GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO metaflow_read_only;
      ALTER DEFAULT PRIVILEGES IN SCHEMA public
      GRANT SELECT ON TABLES TO metaflow_read_only;

      GRANT metaflow_read_only TO metaflow_read_write;
      GRANT CONNECT, TEMPORARY, CREATE ON DATABASE metaflow TO metaflow_read_write;
      GRANT ALL ON SCHEMA public TO metaflow_read_write;
      GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO metaflow_read_write;
      GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO metaflow_read_write;
      ALTER DEFAULT PRIVILEGES IN SCHEMA public
      GRANT ALL ON TABLES TO metaflow_read_write;
      ALTER DEFAULT PRIVILEGES IN SCHEMA public
      GRANT ALL ON SEQUENCES TO metaflow_read_write;

      -- Ownership, not just privileges. ALTER TABLE requires ownership and GRANT ALL does
      -- not confer it, so an app that runs DDL at startup (mlflow's alembic) fails with
      -- "must be owner" on tables an earlier release created under a different role.
      -- Owning them as the group role lets any member migrate them and survives an
      -- app-role rename. current_user is granted the role first so the admin that runs
      -- this script does not lose the ability to manage what it just gave away.
      -- Re-running repairs ownership on an existing install; on a fresh one the loops
      -- find nothing and this is a no-op.
      GRANT metaflow_read_write TO current_user;
      DO $$
      DECLARE r record;
      BEGIN
        FOR r IN SELECT tablename FROM pg_tables WHERE schemaname = 'public' LOOP
          EXECUTE format('ALTER TABLE public.%I OWNER TO metaflow_read_write', r.tablename);
        END LOOP;
        FOR r IN SELECT sequencename FROM pg_sequences WHERE schemaname = 'public' LOOP
          EXECUTE format('ALTER SEQUENCE public.%I OWNER TO metaflow_read_write', r.sequencename);
        END LOOP;
        FOR r IN SELECT viewname FROM pg_views WHERE schemaname = 'public' LOOP
          EXECUTE format('ALTER VIEW public.%I OWNER TO metaflow_read_write', r.viewname);
        END LOOP;
      END $$;

      -- 05_mlflow
      \connect postgres
      -- [GLOBAL] runs against the 'postgres' admin database
      CREATE DATABASE mlflow;

      DO $$
      BEGIN
        IF NOT EXISTS (SELECT FROM pg_catalog.pg_roles WHERE rolname = 'mlflow_read_write') THEN
          CREATE ROLE mlflow_read_write NOLOGIN;
        END IF;
        IF NOT EXISTS (SELECT FROM pg_catalog.pg_roles WHERE rolname = 'mlflow_read_only') THEN
          CREATE ROLE mlflow_read_only NOLOGIN;
        END IF;
      END $$;

      -- 05_mlflow
      \connect mlflow
      REVOKE CONNECT ON DATABASE mlflow FROM PUBLIC;
      REVOKE ALL ON SCHEMA public FROM PUBLIC;

      GRANT CONNECT, TEMPORARY, CREATE ON DATABASE mlflow TO current_user;
      GRANT ALL ON SCHEMA public TO current_user;

      -- Everything below acts on tables this admin may not hold. On a managed
      -- instance the admin is not a superuser, and its membership in the role that
      -- owns the tables can be granted WITH INHERIT FALSE -- pg_has_role then reports
      -- MEMBER true and USAGE false, and every GRANT and ALTER on those tables is
      -- refused with "permission denied for table". Re-granting is allowed because
      -- the admin holds ADMIN OPTION; the plain form takes its INHERIT from rolinherit
      -- and so needs no version-gated syntax.
      DO $$
      DECLARE r record;
      BEGIN
        FOR r IN
          SELECT DISTINCT pg_get_userbyid(c.relowner) AS owner
          FROM pg_class c
          JOIN pg_namespace n ON n.oid = c.relnamespace
          WHERE n.nspname = 'public'
            AND c.relkind IN ('r', 'p', 'S', 'v')
            AND NOT pg_has_role(current_user, c.relowner, 'USAGE')
        LOOP
          EXECUTE format('GRANT %I TO current_user', r.owner);
        END LOOP;
      END $$;

      GRANT CONNECT ON DATABASE mlflow TO mlflow_read_only;
      GRANT USAGE ON SCHEMA public TO mlflow_read_only;
      GRANT SELECT ON ALL TABLES IN SCHEMA public TO mlflow_read_only;
      GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO mlflow_read_only;
      ALTER DEFAULT PRIVILEGES IN SCHEMA public
      GRANT SELECT ON TABLES TO mlflow_read_only;

      GRANT mlflow_read_only TO mlflow_read_write;
      GRANT CONNECT, TEMPORARY, CREATE ON DATABASE mlflow TO mlflow_read_write;
      GRANT ALL ON SCHEMA public TO mlflow_read_write;
      GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO mlflow_read_write;
      GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO mlflow_read_write;
      ALTER DEFAULT PRIVILEGES IN SCHEMA public
      GRANT ALL ON TABLES TO mlflow_read_write;
      ALTER DEFAULT PRIVILEGES IN SCHEMA public
      GRANT ALL ON SEQUENCES TO mlflow_read_write;

      -- Ownership, not just privileges. ALTER TABLE requires ownership and GRANT ALL does
      -- not confer it, so an app that runs DDL at startup (mlflow's alembic) fails with
      -- "must be owner" on tables an earlier release created under a different role.
      -- Owning them as the group role lets any member migrate them and survives an
      -- app-role rename. current_user is granted the role first so the admin that runs
      -- this script does not lose the ability to manage what it just gave away.
      -- Re-running repairs ownership on an existing install; on a fresh one the loops
      -- find nothing and this is a no-op.
      GRANT mlflow_read_write TO current_user;
      DO $$
      DECLARE r record;
      BEGIN
        FOR r IN SELECT tablename FROM pg_tables WHERE schemaname = 'public' LOOP
          EXECUTE format('ALTER TABLE public.%I OWNER TO mlflow_read_write', r.tablename);
        END LOOP;
        FOR r IN SELECT sequencename FROM pg_sequences WHERE schemaname = 'public' LOOP
          EXECUTE format('ALTER SEQUENCE public.%I OWNER TO mlflow_read_write', r.sequencename);
        END LOOP;
        FOR r IN SELECT viewname FROM pg_views WHERE schemaname = 'public' LOOP
          EXECUTE format('ALTER VIEW public.%I OWNER TO mlflow_read_write', r.viewname);
        END LOOP;
      END $$;

      -- 06_keycloak
      \connect postgres
      -- [GLOBAL] runs against the 'postgres' admin database
      CREATE DATABASE keycloak;

      DO $$
      BEGIN
        IF NOT EXISTS (SELECT FROM pg_catalog.pg_roles WHERE rolname = 'keycloak_read_write') THEN
          CREATE ROLE keycloak_read_write NOLOGIN;
        END IF;
        IF NOT EXISTS (SELECT FROM pg_catalog.pg_roles WHERE rolname = 'keycloak_read_only') THEN
          CREATE ROLE keycloak_read_only NOLOGIN;
        END IF;
      END $$;

      -- 06_keycloak
      \connect keycloak
      REVOKE CONNECT ON DATABASE keycloak FROM PUBLIC;
      REVOKE ALL ON SCHEMA public FROM PUBLIC;

      GRANT CONNECT, TEMPORARY, CREATE ON DATABASE keycloak TO current_user;
      GRANT ALL ON SCHEMA public TO current_user;

      -- Everything below acts on tables this admin may not hold. On a managed
      -- instance the admin is not a superuser, and its membership in the role that
      -- owns the tables can be granted WITH INHERIT FALSE -- pg_has_role then reports
      -- MEMBER true and USAGE false, and every GRANT and ALTER on those tables is
      -- refused with "permission denied for table". Re-granting is allowed because
      -- the admin holds ADMIN OPTION; the plain form takes its INHERIT from rolinherit
      -- and so needs no version-gated syntax.
      DO $$
      DECLARE r record;
      BEGIN
        FOR r IN
          SELECT DISTINCT pg_get_userbyid(c.relowner) AS owner
          FROM pg_class c
          JOIN pg_namespace n ON n.oid = c.relnamespace
          WHERE n.nspname = 'public'
            AND c.relkind IN ('r', 'p', 'S', 'v')
            AND NOT pg_has_role(current_user, c.relowner, 'USAGE')
        LOOP
          EXECUTE format('GRANT %I TO current_user', r.owner);
        END LOOP;
      END $$;

      GRANT CONNECT ON DATABASE keycloak TO keycloak_read_only;
      GRANT USAGE ON SCHEMA public TO keycloak_read_only;
      GRANT SELECT ON ALL TABLES IN SCHEMA public TO keycloak_read_only;
      GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO keycloak_read_only;
      ALTER DEFAULT PRIVILEGES IN SCHEMA public
      GRANT SELECT ON TABLES TO keycloak_read_only;

      GRANT keycloak_read_only TO keycloak_read_write;
      GRANT CONNECT, TEMPORARY, CREATE ON DATABASE keycloak TO keycloak_read_write;
      GRANT ALL ON SCHEMA public TO keycloak_read_write;
      GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO keycloak_read_write;
      GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO keycloak_read_write;
      ALTER DEFAULT PRIVILEGES IN SCHEMA public
      GRANT ALL ON TABLES TO keycloak_read_write;
      ALTER DEFAULT PRIVILEGES IN SCHEMA public
      GRANT ALL ON SEQUENCES TO keycloak_read_write;

      -- Ownership, not just privileges. ALTER TABLE requires ownership and GRANT ALL does
      -- not confer it, so an app that runs DDL at startup (mlflow's alembic) fails with
      -- "must be owner" on tables an earlier release created under a different role.
      -- Owning them as the group role lets any member migrate them and survives an
      -- app-role rename. current_user is granted the role first so the admin that runs
      -- this script does not lose the ability to manage what it just gave away.
      -- Re-running repairs ownership on an existing install; on a fresh one the loops
      -- find nothing and this is a no-op.
      GRANT keycloak_read_write TO current_user;
      DO $$
      DECLARE r record;
      BEGIN
        FOR r IN SELECT tablename FROM pg_tables WHERE schemaname = 'public' LOOP
          EXECUTE format('ALTER TABLE public.%I OWNER TO keycloak_read_write', r.tablename);
        END LOOP;
        FOR r IN SELECT sequencename FROM pg_sequences WHERE schemaname = 'public' LOOP
          EXECUTE format('ALTER SEQUENCE public.%I OWNER TO keycloak_read_write', r.sequencename);
        END LOOP;
        FOR r IN SELECT viewname FROM pg_views WHERE schemaname = 'public' LOOP
          EXECUTE format('ALTER VIEW public.%I OWNER TO keycloak_read_write', r.viewname);
        END LOOP;
      END $$;

      -- 07_users
      \connect postgres
      -- [GLOBAL] runs against the 'postgres' admin database
      CREATE ROLE "seekrflow_app" WITH LOGIN PASSWORD :'app_password';

      -- Must sort LAST: every role granted here is created by an earlier script.
      GRANT seekrflow_read_write TO "seekrflow_app";
      GRANT agents_read_write TO "seekrflow_app";
      GRANT user_auth_read_write TO "seekrflow_app";
      GRANT metaflow_read_write TO "seekrflow_app";
      GRANT mlflow_read_write TO "seekrflow_app";

      ALTER ROLE "seekrflow_app" SET search_path TO public;

      -- 08_keycloak_user
      \connect postgres
      -- [GLOBAL] runs against the 'postgres' admin database
      --
      -- Keycloak gets its own login role rather than sharing the app role. It stores
      -- password hashes and client secrets, so the app role must not reach its tables,
      -- and its own credential must not reach the rest of the data plane.
      --
      CREATE ROLE "seekrflow_keycloak" WITH LOGIN PASSWORD :'keycloak_password';

      GRANT keycloak_read_write TO "seekrflow_keycloak";

      ALTER ROLE "seekrflow_keycloak" SET search_path TO public;
      ```
    </Accordion>

    Save it as `seekrflow-databases.sql`, then run it from a machine that can reach the database:

    ```bash theme={null}
    psql -h <database-hostname> -U <admin> -d postgres -f seekrflow-databases.sql
    ```

    Run the whole script rather than parts of it. It switches between databases partway through, and later sections depend on roles that earlier sections create.
  </Tab>
</Tabs>


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.