Connect SeekrFlow to PostgreSQL

SeekrFlow stores its data in PostgreSQL. You created the instance in 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

With the sfe CLI
Without the sfe CLI

sfe reads the admin and seekrflow_app credentials from the pg-creds secret you created in Create SeekrFlow secrets. It reads the Keycloak credentials from keycloak-credentials.

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.

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.

SeekrFlow database script

-- 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;

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

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.

SeekrFlow Self-Managed

Plan a SeekrFlow deployment

Provision SeekrFlow dependencies

Prepare to install SeekrFlow

Deploy SeekrFlow

Operate SeekrFlow

Reference

Create the databases and roles

​Create the databases and roles

Create the databases and roles