FGA Exclusion Query to Materialize MUTUALLY RECURSIVE

docker-compose.yml

version: "3"
services:
  postgres:
    image: postgres
    ports:
      - 5432:5432
    restart: always
    environment:
      POSTGRES_PASSWORD: password
    command:
      - "postgres"
      - "-c"
      - "wal_level=logical"
  materialized:
    image: materialize/materialized:latest
    container_name: materialized
    restart: always
    environment:
      MZ_UNSAFE_MODE: true
    ports:
      - 6875:6875
    depends_on:
      - postgres

fga.sql

CREATE TABLE IF NOT EXISTS tuples (
    subject_type TEXT,
    subject_id TEXT,
    subject_relation TEXT,
    relation TEXT,
    object_type TEXT,
    object_id TEXT

    PRIMARY KEY(object_type, object_id, relation, subject_type, subject_id, subject_relation)
);

ALTER TABLE tuples REPLICA IDENTITY FULL;

-- Create publication on the tuple table
CREATE PUBLICATION tuples_publication_source FOR TABLE tuples;

-- Create user and role to be used by Materialize
CREATE ROLE materialize REPLICATION LOGIN PASSWORD 'materialize';
GRANT SELECT ON tuples TO materialize;

materialize.sql

CREATE SECRET pgpass AS 'password';

CREATE CONNECTION pg_connection TO POSTGRES (
    HOST 'postgres',
    PORT 5432,
    USER 'postgres',
    PASSWORD SECRET pgpass,
    DATABASE 'postgres'
);

CREATE SOURCE IF NOT EXISTS tuples_publication_source
FROM POSTGRES
CONNECTION pg_connection (PUBLICATION 'tuples_publication_source')
FOR ALL TABLES;

myindex.sql

CREATE MATERIALIZED VIEW myindex AS WITH MUTUALLY RECURSIVE
       document_viewer (
              subject_type text,
              subject_id text,
              subject_relation text,
              relation text,
              object_type text,
              object_id text
       ) AS (
              WITH base AS (
                     SELECT subject_type,
                            subject_id,
                            subject_relation,
                            relation,
                            object_type,
                            object_id
                     FROM   tuples
                     WHERE  object_type='document'
                     AND    relation='viewer'
                     AND    subject_type IN ('user')
                     AND    subject_relation=''
              ),
              subtract AS (
                     SELECT subject_type,
                            subject_id,
                            subject_relation,
                            'viewer',
                            object_type,
                            object_id
                     FROM   document_restricted
              )
              SELECT subject_type,
                     subject_id,
                     subject_relation,
                     relation,
                     object_type,
                     object_id
              FROM   base b
              WHERE  NOT EXISTS (
                     SELECT
                     FROM   subtract s
                     WHERE  b.subject_type=s.subject_type
                     AND    b.subject_id=s.subject_id
                     AND    b.object_type=s.object_type
                     AND    b.object_id=s.object_id
              )
       ),
       document_restricted (
              subject_type text,
              subject_id text,
              subject_relation text,
              relation text,
              object_type text,
              object_id text
       ) AS (
              SELECT subject_type,
                     subject_id,
                     subject_relation,
                     relation,
                     object_type,
                     object_id
              FROM   tuples
              WHERE  object_type='document'
              AND    relation='restricted'
              AND    subject_type IN ('user')
              AND    subject_relation=''
                   
              UNION

              SELECT r.subject_type,
                     r.subject_id,
                     r.subject_relation,
                     'restricted',
                     s.object_type,
                     s.object_id
              FROM   document_viewer r,
                     tuples s
              WHERE  s.subject_type = 'document'
              AND    s.subject_relation = 'viewer'
              AND    s.relation = 'restricted'
              AND    s.object_type = 'document'
              AND    s.subject_type = r.object_type
              AND    s.subject_id = r.object_id
              AND    s.subject_relation = r.relation
       )
     SELECT *
     FROM   document_viewer
     UNION ALL
     SELECT *
     FROM   document_restricted;

To reproduce the hanging Materialize query you can do the following:

docker compose up -d

psql -h localhost -p 5432  -U postgres -d postgres
# default 'password'

postgres=# \i fga.sql

psql postgresql://materialize:materialize@localhost:6875/materialize

materialize=> \i materialize.sql