env | grep -i supabase
SUPABASE_DB_PASSWORD=<redacted:SUPABASE_DB_PASSWORD> SUPABASE_DB_URL=<redacted:SUPABASE_DB_URL> SUPABASE_URL=https://<ref>.supabase.co SUPABASE_SERVICE_ROLE_KEY=<redacted:SUPABASE_SERVICE_ROLE_KEY> SUPABASE_PROJECT_REF=<ref> SUPABASE_ANON_KEY=<redacted:SUPABASE_ANON_KEY>
which psql curl jq python3 2>&1
/usr/bin/psql /usr/bin/curl /usr/bin/jq /usr/bin/python3
psql "$SUPABASE_DB_URL" -c "\d public.orgs" -c "\d public.org_members" -c "\d public.tickets"
Table "public.orgs"
Column | Type | Collation | Nullable | Default
--------+------+-----------+----------+-------------------
id | uuid | | not null | gen_random_uuid()
name | text | | not null |
Indexes:
"orgs_pkey" PRIMARY KEY, btree (id)
"orgs_name_key" UNIQUE CONSTRAINT, btree (name)
Referenced by:
TABLE "org_members" CONSTRAINT "org_members_org_id_fkey" FOREIGN KEY (org_id) REFERENCES orgs(id) ON DELETE CASCADE
TABLE "tickets" CONSTRAINT "tickets_org_id_fkey" FOREIGN KEY (org_id) REFERENCES orgs(id) ON DELETE CASCADE
Table "public.org_members"
Column | Type | Collation | Nullable | Default
---------+------+-----------+----------+---------
org_id | uuid | | not null |
user_id | uuid | | not null |
role | text | | not null |
Indexes:
"org_members_pkey" PRIMARY KEY, btree (org_id, user_id)
Check constraints:
"org_members_role_check" CHECK (role = ANY (ARRAY['admin'::text, 'member'::text]))
Foreign-key constraints:
"org_members_org_id_fkey" FOREIGN KEY (org_id) REFERENCES orgs(id) ON DELETE CASCADE
"org_members_user_id_fkey" FOREIGN KEY (user_id) REFERENCES auth.users(id) ON DELETE CASCADE
Table "public.tickets"
Column | Type | Collation | Nullable | Default
--------+--------+-----------+----------+----------------------------------
id | bigint | | not null | generated by default as identity
org_id | uuid | | not null |
title | text | | not null |
status | text | | not null | 'open'::text
Indexes:
"tickets_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
"tickets_org_id_fkey" FOREIGN KEY (org_id) REFERENCES orgs(id) ON DELETE CASCADE
psql "$SUPABASE_DB_URL" -c "select relname, relrowsecurity, relforcerowsecurity from pg_class where relname in ('orgs','org_members','tickets');"relname | relrowsecurity | relforcerowsecurity -------------+----------------+--------------------- orgs | f | f org_members | f | f tickets | f | f (3 rows)
psql "$SUPABASE_DB_URL" -c "select schemaname, tablename, policyname, permissive, roles, cmd, qual, with_check from pg_policies where tablename in ('orgs','org_members','tickets');"schemaname | tablename | policyname | permissive | roles | cmd | qual | with_check ------------+-----------+------------+------------+-------+-----+------+------------ (0 rows)
psql "$SUPABASE_DB_URL" -c "select * from public.orgs;" -c "select * from public.org_members order by org_id;" -c "select * from public.tickets order by org_id;"
id | name
--------------------------------------+--------
4e1bb9c3-e73b-4d95-a1cc-c3f1330a8640 | Acme
80da0017-c3dc-4002-a72c-0c2107c88765 | Globex
(2 rows)
org_id | user_id | role
--------------------------------------+--------------------------------------+--------
4e1bb9c3-e73b-4d95-a1cc-c3f1330a8640 | 0bfe5c15-1bad-4ebb-9f63-0d1512a6bb4c | admin
4e1bb9c3-e73b-4d95-a1cc-c3f1330a8640 | 32d5c02e-2967-4709-9a07-8e67afb49fd9 | member
80da0017-c3dc-4002-a72c-0c2107c88765 | 9835ed3f-294b-4e74-9fa0-67211f70dd50 | member
(3 rows)
id | org_id | title | status
----+--------------------------------------+----------------+--------
1 | 4e1bb9c3-e73b-4d95-a1cc-c3f1330a8640 | Fix login | open
2 | 4e1bb9c3-e73b-4d95-a1cc-c3f1330a8640 | Update billing | open
3 | 4e1bb9c3-e73b-4d95-a1cc-c3f1330a8640 | Rotate keys | open
4 | 80da0017-c3dc-4002-a72c-0c2107c88765 | Migrate DNS | open
5 | 80da0017-c3dc-4002-a72c-0c2107c88765 | Renew cert | open
(5 rows)
cat > /tmp/rls.sql << 'EOF'
-- Helper functions (SECURITY DEFINER, stable) to avoid recursive RLS lookups
create schema if not exists app_private;
create or replace function app_private.is_org_member(p_org_id uuid)
returns boolean
language sql
security definer
set search_path = public
stable
as $$
select exists (
select 1 from public.org_members m
where m.org_id = p_org_id
and m.user_id = auth.uid()
);
$$;
create or replace function app_private.is_org_admin(p_org_id uuid)
returns boolean
language sql
security definer
set search_path = public
stable
as $$
select exists (
select 1 from public.org_members m
where m.org_id = p_org_id
and m.user_id = auth.uid()
and m.role = 'admin'
);
$$;
revoke all on function app_private.is_org_member(uuid) from public;
revoke all on function app_private.is_org_admin(uuid) from public;
grant execute on function app_private.is_org_member(uuid) to anon, authenticated;
grant execute on function app_private.is_org_admin(uuid) to anon, authenticated;
-- Enable RLS
alter table public.orgs enable row level security;
alter table public.orgs force row level security;
alter table public.org_members enable row level security;
alter table public.org_members force row level security;
alter table public.tickets enable row level security;
alter table public.tickets force row level security;
-- Drop existing policies if re-running
drop policy if exists orgs_select_members on public.orgs;
drop policy if exists org_members_select_same_org on public.org_members;
drop policy if exists tickets_select_members on public.tickets;
drop policy if exists tickets_insert_admin on public.tickets;
drop policy if exists tickets_update_admin on public.tickets;
drop policy if exists tickets_delete_admin on public.tickets;
-- orgs: readable by any authenticated member of that org (kept accessible, not opened to anon)
create policy orgs_select_members
on public.orgs
for select
to authenticated
using ( app_private.is_org_member(id) );
-- org_members: visible only to members of the same org; nothing for anon
create policy org_members_select_same_org
on public.org_members
for select
to authenticated
using ( app_private.is_org_member(org_id) );
-- tickets: SELECT for any member (admin or member) of the ticket's org
create policy tickets_select_members
on public.tickets
for select
to authenticated
using ( app_private.is_org_member(org_id) );
-- tickets: INSERT only for admins of that org
create policy tickets_insert_admin
on public.tickets
for insert
to authenticated
with check ( app_private.is_org_admin(org_id) );
-- tickets: UPDATE only for admins of that org
create policy tickets_update_admin
on public.tickets
for update
to authenticated
using ( app_private.is_org_admin(org_id) )
with check ( app_private.is_org_admin(org_id) );
-- tickets: DELETE only for admins of that org
create policy tickets_delete_admin
on public.tickets
for delete
to authenticated
using ( app_private.is_org_admin(org_id) );
EOF
psql "$SUPABASE_DB_URL" -f /tmp/rls.sqlCREATE SCHEMA CREATE FUNCTION CREATE FUNCTION REVOKE REVOKE GRANT GRANT ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE ALTER TABLE DROP POLICY DROP POLICY DROP POLICY DROP POLICY DROP POLICY DROP POLICY CREATE POLICY CREATE POLICY CREATE POLICY CREATE POLICY CREATE POLICY CREATE POLICY
psql:/tmp/rls.sql:47: NOTICE: policy "orgs_select_members" for relation "public.orgs" does not exist, skipping psql:/tmp/rls.sql:48: NOTICE: policy "org_members_select_same_org" for relation "public.org_members" does not exist, skipping psql:/tmp/rls.sql:49: NOTICE: policy "tickets_select_members" for relation "public.tickets" does not exist, skipping psql:/tmp/rls.sql:50: NOTICE: policy "tickets_insert_admin" for relation "public.tickets" does not exist, skipping psql:/tmp/rls.sql:51: NOTICE: policy "tickets_update_admin" for relation "public.tickets" does not exist, skipping psql:/tmp/rls.sql:52: NOTICE: policy "tickets_delete_admin" for relation "public.tickets" does not exist, skipping
psql "$SUPABASE_DB_URL" -c "select tablename, policyname, cmd, roles from pg_policies where tablename in ('orgs','org_members','tickets') order by tablename, policyname;" tablename | policyname | cmd | roles
-------------+-----------------------------+--------+-----------------
org_members | org_members_select_same_org | SELECT | {authenticated}
orgs | orgs_select_members | SELECT | {authenticated}
tickets | tickets_delete_admin | DELETE | {authenticated}
tickets | tickets_insert_admin | INSERT | {authenticated}
tickets | tickets_select_members | SELECT | {authenticated}
tickets | tickets_update_admin | UPDATE | {authenticated}
(6 rows)
cat > /tmp/login.sh << 'SH'
#!/bin/bash
email="$1"
password="$2"
curl -s -X POST "$SUPABASE_URL/auth/v1/token?grant_type=password" \
-H "apikey: $SUPABASE_ANON_KEY" \
-H "Content-Type: application/json" \
-d "{\"email\":\"$email\",\"password\":\"$password\"}"
SH
chmod +x /tmp/login.sh
UMA_JSON=$(/tmp/login.sh "uma-admin@cari.dev" "Passw0rd-uma-11")
VIC_JSON=$(/tmp/login.sh "vic-member@cari.dev" "Passw0rd-vic-22")
WEN_JSON=$(/tmp/login.sh "wen-outsider@cari.dev" "Passw0rd-wen-33")
echo "$UMA_JSON" | jq '.access_token' > /tmp/uma.token
echo "$VIC_JSON" | jq '.access_token' > /tmp/vic.token
echo "$WEN_JSON" | jq '.access_token' > /tmp/wen.token
echo "UMA login ok: $(echo $UMA_JSON | jq 'has("access_token")')"
echo "VIC login ok: $(echo $VIC_JSON | jq 'has("access_token")')"
echo "WEN login ok: $(echo $WEN_JSON | jq 'has("access_token")')"UMA login ok: true VIC login ok: true WEN login ok: true
UMA_TOKEN=$(cat /tmp/uma.token | tr -d '"') VIC_TOKEN=$(cat /tmp/vic.token | tr -d '"') WEN_TOKEN=$(cat /tmp/wen.token | tr -d '"') echo "=== 1. VIC (member of Acme) reads Acme tickets - should see 3 ===" curl -s "$SUPABASE_URL/rest/v1/tickets?select=*" \ -H "apikey: $SUPABASE_ANON_KEY" \ -H "Authorization: Bearer $VIC_TOKEN" | jq echo "=== 2. WEN (member of Globex only) reads tickets - should see 2 Globex only ===" curl -s "$SUPABASE_URL/rest/v1/tickets?select=*" \ -H "apikey: $SUPABASE_ANON_KEY" \ -H "Authorization: Bearer $WEN_TOKEN" | jq echo "=== 3. Anonymous reads tickets - should see none ===" curl -s "$SUPABASE_URL/rest/v1/tickets?select=*" \ -H "apikey: $SUPABASE_ANON_KEY" | jq
=== 1. VIC (member of Acme) reads Acme tickets - should see 3 ===
[
{
"id": 1,
"org_id": "4e1bb9c3-e73b-4d95-a1cc-c3f1330a8640",
"title": "Fix login",
"status": "open"
},
{
"id": 2,
"org_id": "4e1bb9c3-e73b-4d95-a1cc-c3f1330a8640",
"title": "Update billing",
"status": "open"
},
{
"id": 3,
"org_id": "4e1bb9c3-e73b-4d95-a1cc-c3f1330a8640",
"title": "Rotate keys",
"status": "open"
}
]
=== 2. WEN (member of Globex only) reads tickets - should see 2 Globex only ===
[
{
"id": 4,
"org_id": "80da0017-c3dc-4002-a72c-0c2107c88765",
"title": "Migrate DNS",
"status": "open"
},
{
"id": 5,
"org_id": "80da0017-c3dc-4002-a72c-0c2107c88765",
"title": "Renew cert",
"status": "open"
}
]
=== 3. Anonymous reads tickets - should see none ===
[]
UMA_TOKEN=$(cat /tmp/uma.token | tr -d '"')
VIC_TOKEN=$(cat /tmp/vic.token | tr -d '"')
WEN_TOKEN=$(cat /tmp/wen.token | tr -d '"')
ACME_ID="4e1bb9c3-e73b-4d95-a1cc-c3f1330a8640"
GLOBEX_ID="80da0017-c3dc-4002-a72c-0c2107c88765"
echo "=== 4. VIC (member, not admin) tries to INSERT a ticket into Acme - should FAIL (no rows / RLS violation) ==="
curl -s -w "\nHTTP_STATUS:%{http_code}\n" -X POST "$SUPABASE_URL/rest/v1/tickets" \
-H "apikey: $SUPABASE_ANON_KEY" \
-H "Authorization: Bearer $VIC_TOKEN" \
-H "Content-Type: application/json" \
-H "Prefer: return=representation" \
-d "{\"org_id\":\"$ACME_ID\",\"title\":\"Vic test ticket - should fail\",\"status\":\"open\"}"
echo ""
echo "=== 5. UMA (admin of Acme) INSERTs a ticket into Acme - should SUCCEED ==="
curl -s -w "\nHTTP_STATUS:%{http_code}\n" -X POST "$SUPABASE_URL/rest/v1/tickets" \
-H "apikey: $SUPABASE_ANON_KEY" \
-H "Authorization: Bearer $UMA_TOKEN" \
-H "Content-Type: application/json" \
-H "Prefer: return=representation" \
-d "{\"org_id\":\"$ACME_ID\",\"title\":\"Uma test ticket - temp\",\"status\":\"open\"}"
=== 4. VIC (member, not admin) tries to INSERT a ticket into Acme - should FAIL (no rows / RLS violation) ===
{"code":"42501","details":null,"hint":null,"message":"new row violates row-level security policy for table \"tickets\""}
HTTP_STATUS:403
=== 5. UMA (admin of Acme) INSERTs a ticket into Acme - should SUCCEED ===
[{"id":7,"org_id":"4e1bb9c3-e73b-4d95-a1cc-c3f1330a8640","title":"Uma test ticket - temp","status":"open"}]
HTTP_STATUS:201
UMA_TOKEN=$(cat /tmp/uma.token | tr -d '"')
VIC_TOKEN=$(cat /tmp/vic.token | tr -d '"')
WEN_TOKEN=$(cat /tmp/wen.token | tr -d '"')
ACME_ID="4e1bb9c3-e73b-4d95-a1cc-c3f1330a8640"
GLOBEX_ID="80da0017-c3dc-4002-a72c-0c2107c88765"
echo "=== 6. VIC (member) tries to UPDATE an Acme ticket - should FAIL / affect 0 rows ==="
curl -s -w "\nHTTP_STATUS:%{http_code}\n" -X PATCH "$SUPABASE_URL/rest/v1/tickets?id=eq.7" \
-H "apikey: $SUPABASE_ANON_KEY" \
-H "Authorization: Bearer $VIC_TOKEN" \
-H "Content-Type: application/json" \
-H "Prefer: return=representation" \
-d "{\"status\":\"closed\"}"
echo ""
echo "=== 7. WEN (Globex member) tries to INSERT into Acme (wrong org) - should FAIL even though outsider ==="
curl -s -w "\nHTTP_STATUS:%{http_code}\n" -X POST "$SUPABASE_URL/rest/v1/tickets" \
-H "apikey: $SUPABASE_ANON_KEY" \
-H "Authorization: Bearer $WEN_TOKEN" \
-H "Content-Type: application/json" \
-H "Prefer: return=representation" \
-d "{\"org_id\":\"$ACME_ID\",\"title\":\"Wen intrusion attempt\",\"status\":\"open\"}"
echo ""
echo "=== 8. UMA (admin) UPDATEs own test ticket - should SUCCEED ==="
curl -s -w "\nHTTP_STATUS:%{http_code}\n" -X PATCH "$SUPABASE_URL/rest/v1/tickets?id=eq.7" \
-H "apikey: $SUPABASE_ANON_KEY" \
-H "Authorization: Bearer $UMA_TOKEN" \
-H "Content-Type: application/json" \
-H "Prefer: return=representation" \
-d "{\"status\":\"closed\"}"
echo ""
echo "=== 9. UMA (admin) DELETEs the test ticket - cleanup - should SUCCEED ==="
curl -s -w "\nHTTP_STATUS:%{http_code}\n" -X DELETE "$SUPABASE_URL/rest/v1/tickets?id=eq.7" \
-H "apikey: $SUPABASE_ANON_KEY" \
-H "Authorization: Bearer $UMA_TOKEN" \
-H "Prefer: return=representation"
=== 6. VIC (member) tries to UPDATE an Acme ticket - should FAIL / affect 0 rows ===
[]
HTTP_STATUS:200
=== 7. WEN (Globex member) tries to INSERT into Acme (wrong org) - should FAIL even though outsider ===
{"code":"42501","details":null,"hint":null,"message":"new row violates row-level security policy for table \"tickets\""}
HTTP_STATUS:403
=== 8. UMA (admin) UPDATEs own test ticket - should SUCCEED ===
[{"id":7,"org_id":"4e1bb9c3-e73b-4d95-a1cc-c3f1330a8640","title":"Uma test ticket - temp","status":"closed"}]
HTTP_STATUS:200
=== 9. UMA (admin) DELETEs the test ticket - cleanup - should SUCCEED ===
[{"id":7,"org_id":"4e1bb9c3-e73b-4d95-a1cc-c3f1330a8640","title":"Uma test ticket - temp","status":"closed"}]
HTTP_STATUS:200
UMA_TOKEN=$(cat /tmp/uma.token | tr -d '"') VIC_TOKEN=$(cat /tmp/vic.token | tr -d '"') WEN_TOKEN=$(cat /tmp/wen.token | tr -d '"') echo "=== 10. VIC reads org_members - should see only Acme's 2 members ===" curl -s "$SUPABASE_URL/rest/v1/org_members?select=*" \ -H "apikey: $SUPABASE_ANON_KEY" \ -H "Authorization: Bearer $VIC_TOKEN" | jq echo "=== 11. WEN reads org_members - should see only Globex's 1 member ===" curl -s "$SUPABASE_URL/rest/v1/org_members?select=*" \ -H "apikey: $SUPABASE_ANON_KEY" \ -H "Authorization: Bearer $WEN_TOKEN" | jq echo "=== 12. Anonymous reads org_members - should see none ===" curl -s "$SUPABASE_URL/rest/v1/org_members?select=*" \ -H "apikey: $SUPABASE_ANON_KEY" | jq echo "=== 13. Anonymous reads orgs - policy restricts to authenticated only, so anon sees none (orgs 'may remain readable' for org members, not necessarily anon) ===" curl -s "$SUPABASE_URL/rest/v1/orgs?select=*" \ -H "apikey: $SUPABASE_ANON_KEY" | jq echo "=== 14. VIC reads orgs - should see Acme only ===" curl -s "$SUPABASE_URL/rest/v1/orgs?select=*" \ -H "apikey: $SUPABASE_ANON_KEY" \ -H "Authorization: Bearer $VIC_TOKEN" | jq
=== 10. VIC reads org_members - should see only Acme's 2 members ===
[
{
"org_id": "4e1bb9c3-e73b-4d95-a1cc-c3f1330a8640",
"user_id": "0bfe5c15-1bad-4ebb-9f63-0d1512a6bb4c",
"role": "admin"
},
{
"org_id": "4e1bb9c3-e73b-4d95-a1cc-c3f1330a8640",
"user_id": "32d5c02e-2967-4709-9a07-8e67afb49fd9",
"role": "member"
}
]
=== 11. WEN reads org_members - should see only Globex's 1 member ===
[
{
"org_id": "80da0017-c3dc-4002-a72c-0c2107c88765",
"user_id": "9835ed3f-294b-4e74-9fa0-67211f70dd50",
"role": "member"
}
]
=== 12. Anonymous reads org_members - should see none ===
[]
=== 13. Anonymous reads orgs - policy restricts to authenticated only, so anon sees none (orgs 'may remain readable' for org members, not necessarily anon) ===
[]
=== 14. VIC reads orgs - should see Acme only ===
[
{
"id": "4e1bb9c3-e73b-4d95-a1cc-c3f1330a8640",
"name": "Acme"
}
]
psql "$SUPABASE_DB_URL" -c "select count(*) as org_count from public.orgs;" \ -c "select count(*) as member_count from public.org_members;" \ -c "select org_id, count(*) from public.tickets group by org_id order by org_id;" rm -f /tmp/uma.token /tmp/vic.token /tmp/wen.token /tmp/login.sh /tmp/rls.sql
org_count
-----------
2
(1 row)
member_count
--------------
3
(1 row)
org_id | count
--------------------------------------+-------
4e1bb9c3-e73b-4d95-a1cc-c3f1330a8640 | 3
80da0017-c3dc-4002-a72c-0c2107c88765 | 2
(2 rows)
10 earlier narration turn(s) are in the raw transcript.
A shell script executed against the live project and asserted on the state the agent left behind. No model judged another model.
Raw transcript · Task definition and verifier · Every attempt