echo "SUPABASE_URL=$SUPABASE_URL"; echo "SUPABASE_PROJECT_REF=$SUPABASE_PROJECT_REF"; echo "SUPABASE_DB_URL=$SUPABASE_DB_URL"; echo "Keys set: anon=$(test -n "$SUPABASE_ANON_KEY" && echo yes), service=$(test -n "$SUPABASE_SERVICE_ROLE_KEY" && echo yes), dbpass=$(test -n "$SUPABASE_DB_PASSWORD" && echo yes)"
SUPABASE_URL=https://<ref>.supabase.co SUPABASE_PROJECT_REF=<ref> SUPABASE_DB_URL=<redacted:SUPABASE_DB_URL> Keys set: anon=yes, service=yes, dbpass=yes
which psql || (apt-get update -qq && apt-get install -y -qq postgresql-client 2>/dev/null)
/usr/bin/psql
export PGPASSWORD="$(echo "$SUPABASE_DB_URL" | sed -n 's/.*:\([^@]*\)@.*/\1/p')" # Use the full connection string psql "$SUPABASE_DB_URL" -c "\dt public.*" 2>&1 | head -40
List of tables Schema | Name | Type | Owner --------+-------------+-------+---------- public | org_members | table | postgres public | orgs | table | postgres public | tickets | table | postgres (3 rows)
psql "$SUPABASE_DB_URL" -c "\d public.orgs" -c "\d public.org_members" -c "\d public.tickets" 2>&1
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 relrowsecurity, relforcerowsecurity FROM pg_class WHERE relname IN ('tickets','org_members','orgs');" 2>&1relrowsecurity | relforcerowsecurity ----------------+--------------------- f | f f | f f | f (3 rows)
psql "$SUPABASE_DB_URL" -c "SELECT * FROM public.orgs;" -c "SELECT om.org_id, o.name as org_name, om.user_id, om.role FROM public.org_members om JOIN public.orgs o ON o.id = om.org_id;" -c "SELECT * FROM public.tickets;" 2>&1
id | name
--------------------------------------+--------
997eab46-181c-48bf-a73b-98156e1e8ab4 | Acme
c1a47cf9-f229-4050-a0ae-2589f888b7fa | Globex
(2 rows)
org_id | org_name | user_id | role
--------------------------------------+----------+--------------------------------------+--------
997eab46-181c-48bf-a73b-98156e1e8ab4 | Acme | f41d0893-f8e5-4a83-ae34-bed57132d18a | admin
997eab46-181c-48bf-a73b-98156e1e8ab4 | Acme | c10e72b0-8770-4784-8ccd-5c5db42ead1e | member
c1a47cf9-f229-4050-a0ae-2589f888b7fa | Globex | e285f61a-3b06-4bc7-91e9-cd19a5bc8c79 | member
(3 rows)
id | org_id | title | status
----+--------------------------------------+----------------+--------
1 | 997eab46-181c-48bf-a73b-98156e1e8ab4 | Fix login | open
2 | 997eab46-181c-48bf-a73b-98156e1e8ab4 | Update billing | open
3 | 997eab46-181c-48bf-a73b-98156e1e8ab4 | Rotate keys | open
4 | c1a47cf9-f229-4050-a0ae-2589f888b7fa | Migrate DNS | open
5 | c1a47cf9-f229-4050-a0ae-2589f888b7fa | Renew cert | open
(5 rows)
psql "$SUPABASE_DB_URL" -c "SELECT schemaname, tablename, policyname, permissive, roles, cmd, qual, with_check FROM pg_policies WHERE schemaname = 'public';" 2>&1
schemaname | tablename | policyname | permissive | roles | cmd | qual | with_check ------------+-----------+------------+------------+-------+-----+------+------------ (0 rows)
psql "$SUPABASE_DB_URL" -c "SELECT rolname, rolsuper, rolbypassrls FROM pg_roles WHERE rolname = 'postgres';" 2>&1
rolname | rolsuper | rolbypassrls ----------+----------+-------------- postgres | f | t (1 row)
cat <<'SQL' | psql "$SUPABASE_DB_URL" 2>&1
-- Helper functions: SECURITY DEFINER to avoid RLS recursion on org_members
CREATE OR REPLACE FUNCTION public.is_org_member(target_org uuid)
RETURNS boolean
LANGUAGE sql
SECURITY DEFINER
STABLE
SET search_path = public
AS $$
SELECT EXISTS (
SELECT 1 FROM public.org_members
WHERE org_id = target_org AND user_id = auth.uid()
);
$$;
CREATE OR REPLACE FUNCTION public.is_org_admin(target_org uuid)
RETURNS boolean
LANGUAGE sql
SECURITY DEFINER
STABLE
SET search_path = public
AS $$
SELECT EXISTS (
SELECT 1 FROM public.org_members
WHERE org_id = target_org AND user_id = auth.uid() AND role = 'admin'
);
$$;
-- Enable RLS on tickets and org_members (orgs stays publicly readable)
ALTER TABLE public.tickets ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.org_members ENABLE ROW LEVEL SECURITY;
-- ===== tickets policies =====
-- Members (any role) can read their org's tickets
CREATE POLICY tickets_select ON public.tickets
FOR SELECT TO authenticated
USING (public.is_org_member(org_id));
-- Only admins can insert
CREATE POLICY tickets_insert ON public.tickets
FOR INSERT TO authenticated
WITH CHECK (public.is_org_admin(org_id));
-- Only admins can update (both on old and new row)
CREATE POLICY tickets_update ON public.tickets
FOR UPDATE TO authenticated
USING (public.is_org_admin(org_id))
WITH CHECK (public.is_org_admin(org_id));
-- Only admins can delete
CREATE POLICY tickets_delete ON public.tickets
FOR DELETE TO authenticated
USING (public.is_org_admin(org_id));
-- ===== org_members policies =====
-- Members can see only their own org's member list
CREATE POLICY org_members_select ON public.org_members
FOR SELECT TO authenticated
USING (public.is_org_member(org_id));
SQLCREATE FUNCTION CREATE FUNCTION ALTER TABLE ALTER TABLE CREATE POLICY CREATE POLICY CREATE POLICY CREATE POLICY CREATE POLICY
which jq || (apt-get update -qq && apt-get install -y -qq jq 2>/dev/null) 1>&2 echo "---done---"
/usr/bin/jq ---done---
# Helper function to sign in and get JWT
get_token() {
local email="$1" pass="$2"
curl -sS "https://<ref>.supabase.co/auth/v1/token?grant_type=password" \
-H "apikey: $SUPABASE_ANON_KEY" \
-H "Content-Type: application/json" \
-d "{\"email\":\"$email\",\"password\":\"$pass\"}" | jq -r '.access_token // .error_description'
}
export -f get_token
echo "=== UMA (Acme admin) ==="
UMA=$(get_token "uma-admin@cari.dev" "Passw0rd-uma-11")
echo "token=${UMA:0:20}..."
echo ""
echo "--- UMA reads Acme tickets (expect 3) ---"
curl -sS "$SUPABASE_URL/rest/v1/tickets?select=*&order=id.asc" \
-H "apikey: $SUPABASE_ANON_KEY" -H "Authorization: Bearer $UMA" | jq .
echo ""
echo "--- UMA reads org_members (expect 2 Acme members) ---"
curl -sS "$SUPABASE_URL/rest/v1/org_members?select=*&order=org_id.asc" \
-H "apikey: $SUPABASE_ANON_KEY" -H "Authorization: Bearer $UMA" | jq .
echo ""
echo "--- UMA reads orgs (expect 2) ---"
curl -sS "$SUPABASE_URL/rest/v1/orgs?select=*&order=name.asc" \
-H "apikey: $SUPABASE_ANON_KEY" -H "Authorization: Bearer $UMA" | jq .=== UMA (Acme admin) ===
token=eyJhbGciOiJFUzI1NiIs...
--- UMA reads Acme tickets (expect 3) ---
[
{
"id": 1,
"org_id": "997eab46-181c-48bf-a73b-98156e1e8ab4",
"title": "Fix login",
"status": "open"
},
{
"id": 2,
"org_id": "997eab46-181c-48bf-a73b-98156e1e8ab4",
"title": "Update billing",
"status": "open"
},
{
"id": 3,
"org_id": "997eab46-181c-48bf-a73b-98156e1e8ab4",
"title": "Rotate keys",
"status": "open"
}
]
--- UMA reads org_members (expect 2 Acme members) ---
[
{
"org_id": "997eab46-181c-48bf-a73b-98156e1e8ab4",
"user_id": "c10e72b0-8770-4784-8ccd-5c5db42ead1e",
"role": "member"
},
{
"org_id": "997eab46-181c-48bf-a73b-98156e1e8ab4",
"user_id": "f41d0893-f8e5-4a83-ae34-bed57132d18a",
"role": "admin"
}
]
--- UMA reads orgs (expect 2) ---
[
{
"id": "997eab46-181c-48bf-a73b-98156e1e8ab4",
"name": "Acme"
},
{
"id": "c1a47cf9-f229-4050-a0ae-2589f888b7fa",
"name": "Globex"
}
]
get_token() {
local email="$1" pass="$2"
curl -sS "https://<ref>.supabase.co/auth/v1/token?grant_type=password" \
-H "apikey: $SUPABASE_ANON_KEY" \
-H "Content-Type: application/json" \
-d "{\"email\":\"$email\",\"password\":\"$pass\"}" | jq -r '.access_token // .error_description'
}
export -f get_token
echo "=== VIC (Acme member) ==="
VIC=$(get_token "vic-member@cari.dev" "Passw0rd-vic-22")
echo "token=${VIC:0:20}..."
echo ""
echo "--- VIC reads Acme tickets (expect 3) ---"
curl -sS "$SUPABASE_URL/rest/v1/tickets?select=*&order=id.asc" \
-H "apikey: $SUPABASE_ANON_KEY" -H "Authorization: Bearer $VIC" | jq -c .
echo ""
echo "--- VIC reads org_members (expect 2 Acme members) ---"
curl -sS "$SUPABASE_URL/rest/v1/org_members?select=*&order=org_id.asc" \
-H "apikey: $SUPABASE_ANON_KEY" -H "Authorization: Bearer $VIC" | jq -c .
echo ""
echo "--- VIC tries INSERT ticket (expect failure - member not admin) ---"
curl -sS -o /dev/null -w "HTTP %{http_code}" "$SUPABASE_URL/rest/v1/tickets" \
-H "apikey: $SUPABASE_ANON_KEY" -H "Authorization: Bearer $VIC" \
-H "Content-Type: application/json" \
-H "Prefer: return=representation" \
-d '{"org_id":"997eab46-181c-48bf-a73b-98156e1e8ab4","title":"Unauthorized insert","status":"open"}'
echo ""
echo ""
echo "--- VIC tries UPDATE ticket (expect failure) ---"
curl -sS -o /dev/null -w "HTTP %{http_code}" "$SUPABASE_URL/rest/v1/tickets?id=eq.1" \
-H "apikey: $SUPABASE_ANON_KEY" -H "Authorization: Bearer $VIC" \
-H "Content-Type: application/json" \
-H "Prefer: return=representation" \
-d '{"status":"closed"}'
echo ""
echo ""
echo "--- VIC tries DELETE ticket (expect failure) ---"
curl -sS -o /dev/null -w "HTTP %{http_code}" "$SUPABASE_URL/rest/v1/tickets?id=eq.1" \
-H "apikey: $SUPABASE_ANON_KEY" -H "Authorization: Bearer $VIC" \
-X DELETE
echo ""=== VIC (Acme member) ===
token=eyJhbGciOiJFUzI1NiIs...
--- VIC reads Acme tickets (expect 3) ---
[{"id":1,"org_id":"997eab46-181c-48bf-a73b-98156e1e8ab4","title":"Fix login","status":"open"},{"id":2,"org_id":"997eab46-181c-48bf-a73b-98156e1e8ab4","title":"Update billing","status":"open"},{"id":3,"org_id":"997eab46-181c-48bf-a73b-98156e1e8ab4","title":"Rotate keys","status":"open"}]
--- VIC reads org_members (expect 2 Acme members) ---
[{"org_id":"997eab46-181c-48bf-a73b-98156e1e8ab4","user_id":"c10e72b0-8770-4784-8ccd-5c5db42ead1e","role":"member"},{"org_id":"997eab46-181c-48bf-a73b-98156e1e8ab4","user_id":"f41d0893-f8e5-4a83-ae34-bed57132d18a","role":"admin"}]
--- VIC tries INSERT ticket (expect failure - member not admin) ---
HTTP 403
--- VIC tries UPDATE ticket (expect failure) ---
HTTP 403
--- VIC tries DELETE ticket (expect failure) ---
HTTP 204
# Verify no data was deleted - check ticket count psql "$SUPABASE_DB_URL" -c "SELECT count(*) FROM public.tickets;" -c "SELECT id, org_id, title FROM public.tickets ORDER BY id;" 2>&1
count
-------
5
(1 row)
id | org_id | title
----+--------------------------------------+----------------
1 | 997eab46-181c-48bf-a73b-98156e1e8ab4 | Fix login
2 | 997eab46-181c-48bf-a73b-98156e1e8ab4 | Update billing
3 | 997eab46-181c-48bf-a73b-98156e1e8ab4 | Rotate keys
4 | c1a47cf9-f229-4050-a0ae-2589f888b7fa | Migrate DNS
5 | c1a47cf9-f229-4050-a0ae-2589f888b7fa | Renew cert
(5 rows)
get_token() {
local email="$1" pass="$2"
curl -sS "https://<ref>.supabase.co/auth/v1/token?grant_type=password" \
-H "apikey: $SUPABASE_ANON_KEY" \
-H "Content-Type: application/json" \
-d "{\"email\":\"$email\",\"password\":\"$pass\"}" | jq -r '.access_token // .error_description'
}
export -f get_token
echo "=== WEN (Globex member, not in Acme) ==="
WEN=$(get_token "wen-outsider@cari.dev" "Passw0rd-wen-33")
echo "token=${WEN:0:20}..."
echo ""
echo "--- WEN reads tickets (expect only 2 Globex tickets, NOT Acme's 3) ---"
curl -sS "$SUPABASE_URL/rest/v1/tickets?select=*&order=id.asc" \
-H "apikey: $SUPABASE_ANON_KEY" -H "Authorization: Bearer $WEN" | jq -c .
echo ""
echo "--- WEN reads org_members (expect only 1 Globex member, NOT Acme's) ---"
curl -sS "$SUPABASE_URL/rest/v1/org_members?select=*" \
-H "apikey: $SUPABASE_ANON_KEY" -H "Authorization: Bearer $WEN" | jq -c .
echo ""
echo "--- WEN reads orgs (expect 2 - orgs table stays readable) ---"
curl -sS "$SUPABASE_URL/rest/v1/orgs?select=*&order=name.asc" \
-H "apikey: $SUPABASE_ANON_KEY" -H "Authorization: Bearer $WEN" | jq -c .
echo ""
echo "=== ANONYMOUS (no auth) ==="
echo "--- Anonymous reads tickets (expect empty []) ---"
curl -sS "$SUPABASE_URL/rest/v1/tickets?select=*" \
-H "apikey: $SUPABASE_ANON_KEY" | jq -c .
echo ""
echo "--- Anonymous reads org_members (expect empty []) ---"
curl -sS "$SUPABASE_URL/rest/v1/org_members?select=*" \
-H "apikey: $SUPABASE_ANON_KEY" | jq -c .
echo ""
echo "--- Anonymous reads orgs (expect 2 - still readable) ---"
curl -sS "$SUPABASE_URL/rest/v1/orgs?select=*" \
-H "apikey: $SUPABASE_ANON_KEY" | jq -c .=== WEN (Globex member, not in Acme) ===
token=eyJhbGciOiJFUzI1NiIs...
--- WEN reads tickets (expect only 2 Globex tickets, NOT Acme's 3) ---
[{"id":4,"org_id":"c1a47cf9-f229-4050-a0ae-2589f888b7fa","title":"Migrate DNS","status":"open"},{"id":5,"org_id":"c1a47cf9-f229-4050-a0ae-2589f888b7fa","title":"Renew cert","status":"open"}]
--- WEN reads org_members (expect only 1 Globex member, NOT Acme's) ---
[{"org_id":"c1a47cf9-f229-4050-a0ae-2589f888b7fa","user_id":"e285f61a-3b06-4bc7-91e9-cd19a5bc8c79","role":"member"}]
--- WEN reads orgs (expect 2 - orgs table stays readable) ---
[{"id":"997eab46-181c-48bf-a73b-98156e1e8ab4","name":"Acme"},{"id":"c1a47cf9-f229-4050-a0ae-2589f888b7fa","name":"Globex"}]
=== ANONYMOUS (no auth) ===
--- Anonymous reads tickets (expect empty []) ---
[]
--- Anonymous reads org_members (expect empty []) ---
[]
--- Anonymous reads orgs (expect 2 - still readable) ---
[{"id":"997eab46-181c-48bf-a73b-98156e1e8ab4","name":"Acme"},{"id":"c1a47cf9-f229-4050-a0ae-2589f888b7fa","name":"Globex"}]
get_token() {
local email="$1" pass="$2"
curl -sS "https://<ref>.supabase.co/auth/v1/token?grant_type=password" \
-H "apikey: $SUPABASE_ANON_KEY" \
-H "Content-Type: application/json" \
-d "{\"email\":\"$email\",\"password\":\"$pass\"}" | jq -r '.access_token // .error_description'
}
export -f get_token
UMA=$(get_token "uma-admin@cari.dev" "Passw0rd-uma-11")
ACME="997eab46-181c-48bf-a73b-98156e1e8ab4"
GLOBEX="c1a47cf9-f229-4050-a0ae-2589f888b7fa"
echo "--- UMA INSERT into Acme (expect 201 + returned row) ---"
INS=$(curl -sS "$SUPABASE_URL/rest/v1/tickets" \
-H "apikey: $SUPABASE_ANON_KEY" -H "Authorization: Bearer $UMA" \
-H "Content-Type: application/json" -H "Prefer: return=representation" \
-d "{\"org_id\":\"$ACME\",\"title\":\"temp_rls_test\",\"status\":\"open\"}" -w "\n%{http_code}")
CODE=$(echo "$INS" | tail -1)
BODY=$(echo "$INS" | head -n -1)
echo "HTTP $CODE"
echo "$BODY" | jq -c .
NEWID=$(echo "$BODY" | jq -r '.[0].id')
echo "New ticket id: $NEWID"
echo ""
echo "--- UMA UPDATE that ticket (expect 200, status=closed) ---"
curl -sS "$SUPABASE_URL/rest/v1/tickets?id=eq.$NEWID" \
-H "apikey: $SUPABASE_ANON_KEY" -H "Authorization: Bearer $UMA" \
-H "Content-Type: application/json" -H "Prefer: return=representation" \
-d '{"status":"closed"}' -w "\nHTTP %{http_code}\n" | jq -c .
echo ""
echo "--- UMA tries INSERT into Globex (expect 403 - not admin there) ---"
curl -sS -o /dev/null -w "HTTP %{http_code}" "$SUPABASE_URL/rest/v1/tickets" \
-H "apikey: $SUPABASE_ANON_KEY" -H "Authorization: Bearer $UMA" \
-H "Content-Type: application/json" -H "Prefer: return=representation" \
-d "{\"org_id\":\"$GLOBEX\",\"title\":\"cross_org_insert\",\"status\":\"open\"}"
echo ""
echo ""
echo "--- UMA tries UPDATE Globex ticket id=4 (expect 204 but no rows affected) ---"
curl -sS "$SUPABASE_URL/rest/v1/tickets?id=eq.4" \
-H "apikey: $SUPABASE_ANON_KEY" -H "Authorization: Bearer $UMA" \
-H "Content-Type: application/json" -H "Prefer: return=representation" \
-d '{"status":"closed"}' -w "\nHTTP %{http_code}\n"
echo ""
echo "--- UMA DELETE the temp ticket (cleanup, expect 204) ---"
curl -sS -o /dev/null -w "HTTP %{http_code}" "$SUPABASE_URL/rest/v1/tickets?id=eq.$NEWID" \
-H "apikey: $SUPABASE_ANON_KEY" -H "Authorization: Bearer $UMA" \
-X DELETE
echo ""--- UMA INSERT into Acme (expect 201 + returned row) ---
HTTP 201
[{"id":8,"org_id":"997eab46-181c-48bf-a73b-98156e1e8ab4","title":"temp_rls_test","status":"open"}]
New ticket id: 8
--- UMA UPDATE that ticket (expect 200, status=closed) ---
{"code":"42501","details":null,"hint":null,"message":"new row violates row-level security policy for table \"tickets\""}
--- UMA tries INSERT into Globex (expect 403 - not admin there) ---
HTTP 403
--- UMA tries UPDATE Globex ticket id=4 (expect 204 but no rows affected) ---
{"code":"42501","details":null,"hint":null,"message":"new row violates row-level security policy for table \"tickets\""}
HTTP 403
--- UMA DELETE the temp ticket (cleanup, expect 204) ---
HTTP 204
parse error: Invalid numeric literal at line 2, column 5
# Test the function directly as a query with auth.uid simulated
# First let's check if there's an issue with the function
psql "$SUPABASE_DB_URL" -c "
SELECT proname, proconfig, prosecdef FROM pg_proc WHERE proname IN ('is_org_admin','is_org_member') AND pronamespace = 'public'::regnamespace;
" 2>&1
echo "---"
# Check if there's a search_path issue - let's look at the function definitions
psql "$SUPABASE_DB_URL" -c "\df+ public.is_org_admin" -c "\df+ public.is_org_member" 2>&1 | head -30 proname | proconfig | prosecdef
---------------+----------------------+-----------
is_org_admin | {search_path=public} | t
is_org_member | {search_path=public} | t
(2 rows)
---
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Leakproof? | Access privileges | Language | Internal name | Description
--------+--------------+------------------+---------------------+------+------------+----------+----------+----------+------------+--------------------------+----------+---------------+-------------
public | is_org_admin | boolean | target_org uuid | func | stable | unsafe | postgres | definer | no | =X/postgres +| sql | |
| | | | | | | | | | postgres=X/postgres +| | |
| | | | | | | | | | anon=X/postgres +| | |
| | | | | | | | | | authenticated=X/postgres+| | |
| | | | | | | | | | service_role=X/postgres | | |
(1 row)
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Leakproof? | Access privileges | Language | Internal name | Description
--------+---------------+------------------+---------------------+------+------------+----------+----------+----------+------------+--------------------------+----------+---------------+-------------
public | is_org_member | boolean | target_org uuid | func | stable | unsafe | postgres | definer | no | =X/postgres +| sql | |
| | | | | | | | | | postgres=X/postgres +| | |
| | | | | | | | | | anon=X/postgres +| | |
| | | | | | | | | | authenticated=X/postgres+| | |
| | | | | | | | | | service_role=X/postgres | | |
(1 row)
get_token() {
local email="$1" pass="$2"
curl -sS "https://<ref>.supabase.co/auth/v1/token?grant_type=password" \
-H "apikey: $SUPABASE_ANON_KEY" \
-H "Content-Type: application/json" \
-d "{\"email\":\"$email\",\"password\":\"$pass\"}" | jq -r '.access_token // .error_description'
}
export -f get_token
UMA=$(get_token "uma-admin@cari.dev" "Passw0rd-uma-11")
ACME="997eab46-181c-48bf-a73b-98156e1e8ab4"
# Insert a fresh test ticket
echo "--- INSERT test ticket ---"
INS=$(curl -sS "$SUPABASE_URL/rest/v1/tickets" \
-H "apikey: $SUPABASE_ANON_KEY" -H "Authorization: Bearer $UMA" \
-H "Content-Type: application/json" -H "Prefer: return=representation" \
-d "{\"org_id\":\"$ACME\",\"title\":\"rls_update_test\",\"status\":\"open\"}")
echo "$INS" | jq -c .
NEWID=$(echo "$INS" | jq -r '.[0].id')
echo "id=$NEWID"
echo ""
echo "--- UPDATE (PATCH) status to closed ---"
curl -sS "$SUPABASE_URL/rest/v1/tickets?id=eq.$NEWID" \
-H "apikey: $SUPABASE_ANON_KEY" -H "Authorization: Bearer $UMA" \
-H "Content-Type: application/json" -H "Prefer: return=representation" \
-X PATCH \
-d '{"status":"closed"}' -w "\nHTTP %{http_code}\n"
echo ""
echo "--- Verify it was updated ---"
curl -sS "$SUPABASE_URL/rest/v1/tickets?id=eq.$NEWID&select=*" \
-H "apikey: $SUPABASE_ANON_KEY" -H "Authorization: Bearer $UMA" | jq -c .
echo ""
echo "--- Cleanup: DELETE it ---"
curl -sS -o /dev/null -w "HTTP %{http_code}\n" "$SUPABASE_URL/rest/v1/tickets?id=eq.$NEWID" \
-H "apikey: $SUPABASE_ANON_KEY" -H "Authorization: Bearer $UMA" -X DELETE--- INSERT test ticket ---
[{"id":12,"org_id":"997eab46-181c-48bf-a73b-98156e1e8ab4","title":"rls_update_test","status":"open"}]
id=12
--- UPDATE (PATCH) status to closed ---
[{"id":12,"org_id":"997eab46-181c-48bf-a73b-98156e1e8ab4","title":"rls_update_test","status":"closed"}]
HTTP 200
--- Verify it was updated ---
[{"id":12,"org_id":"997eab46-181c-48bf-a73b-98156e1e8ab4","title":"rls_update_test","status":"closed"}]
--- Cleanup: DELETE it ---
HTTP 204
get_token() {
local email="$1" pass="$2"
curl -sS "https://<ref>.supabase.co/auth/v1/token?grant_type=password" \
-H "apikey: $SUPABASE_ANON_KEY" \
-H "Content-Type: application/json" \
-d "{\"email\":\"$email\",\"password\":\"$pass\"}" | jq -r '.access_token // .error_description'
}
export -f get_token
UMA=$(get_token "uma-admin@cari.dev" "Passw0rd-uma-11")
echo "--- UMA tries to PATCH Globex ticket id=4 (expect no rows returned) ---"
curl -sS "$SUPABASE_URL/rest/v1/tickets?id=eq.4" \
-H "apikey: $SUPABASE_ANON_KEY" -H "Authorization: Bearer $UMA" \
-H "Content-Type: application/json" -H "Prefer: return=representation" \
-X PATCH -d '{"status":"closed"}' -w "\nHTTP %{http_code}\n"
echo ""
echo "--- UMA tries to DELETE Globex ticket id=4 (expect 204, no rows affected) ---"
curl -sS -o /dev/null -w "HTTP %{http_code}\n" "$SUPABASE_URL/rest/v1/tickets?id=eq.4" \
-H "apikey: $SUPABASE_ANON_KEY" -H "Authorization: Bearer $UMA" -X DELETE
echo ""
echo "--- VIC tries to PATCH Acme ticket (expect 403) ---"
VIC=$(get_token "vic-member@cari.dev" "Passw0rd-vic-22")
curl -sS "$SUPABASE_URL/rest/v1/tickets?id=eq.1" \
-H "apikey: $SUPABASE_ANON_KEY" -H "Authorization: Bearer $VIC" \
-H "Content-Type: application/json" -H "Prefer: return=representation" \
-X PATCH -d '{"status":"closed"}' -w "\nHTTP %{http_code}\n"--- UMA tries to PATCH Globex ticket id=4 (expect no rows returned) --- [] HTTP 200 --- UMA tries to DELETE Globex ticket id=4 (expect 204, no rows affected) --- HTTP 204 --- VIC tries to PATCH Acme ticket (expect 403) --- [] HTTP 200
# Final data integrity check
echo "--- Data integrity verification ---"
psql "$SUPABASE_DB_URL" -c "SELECT count(*) as total_tickets FROM public.tickets;" \
-c "SELECT id, org_id, title, status FROM public.tickets ORDER BY id;" \
-c "SELECT count(*) as total_org_members FROM public.org_members;" \
-c "SELECT count(*) as total_orgs FROM public.orgs;" 2>&1
echo ""
echo "--- RLS policies summary ---"
psql "$SUPABASE_DB_URL" -c "SELECT tablename, policyname, cmd, roles, qual, with_check FROM pg_policies WHERE schemaname='public' ORDER BY tablename, policyname;" 2>&1
echo ""
echo "--- RLS enabled status ---"
psql "$SUPABASE_DB_URL" -c "SELECT relname, relrowsecurity, relforcerowsecurity FROM pg_class WHERE relname IN ('tickets','org_members','orgs') ORDER BY relname;" 2>&1--- Data integrity verification ---
total_tickets
---------------
5
(1 row)
id | org_id | title | status
----+--------------------------------------+----------------+--------
1 | 997eab46-181c-48bf-a73b-98156e1e8ab4 | Fix login | open
2 | 997eab46-181c-48bf-a73b-98156e1e8ab4 | Update billing | open
3 | 997eab46-181c-48bf-a73b-98156e1e8ab4 | Rotate keys | open
4 | c1a47cf9-f229-4050-a0ae-2589f888b7fa | Migrate DNS | open
5 | c1a47cf9-f229-4050-a0ae-2589f888b7fa | Renew cert | open
(5 rows)
total_org_members
-------------------
3
(1 row)
total_orgs
------------
2
(1 row)
--- RLS policies summary ---
tablename | policyname | cmd | roles | qual | with_check
-------------+--------------------+--------+-----------------+-----------------------+----------------------
org_members | org_members_select | SELECT | {authenticated} | is_org_member(org_id) |
tickets | tickets_delete | DELETE | {authenticated} | is_org_admin(org_id) |
tickets | tickets_insert | INSERT | {authenticated} | | is_org_admin(org_id)
tickets | tickets_select | SELECT | {authenticated} | is_org_member(org_id) |
tickets | tickets_update | UPDATE | {authenticated} | is_org_admin(org_id) | is_org_admin(org_id)
(5 rows)
--- RLS enabled status ---
relname | relrowsecurity | relforcerowsecurity
-------------+----------------+---------------------
org_members | t | f
orgs | f | f
tickets | t | f
(3 rows)
5 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