set -euo pipefail
command -v psql
psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 -P pager=off -c "select current_user, version();" -c "select table_name, column_name, data_type, udt_name from information_schema.columns where table_schema='public' and table_name in ('orgs','org_members','tickets') order by table_name, ordinal_position;" -c "select schemaname, tablename, policyname, permissive, roles, cmd, qual, with_check from pg_policies where schemaname='public' and tablename in ('orgs','org_members','tickets') order by tablename, policyname;"/usr/bin/psql current_user | version --------------+------------------------------------------------------------------------------ postgres | PostgreSQL 17.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 15.2.0, 64-bit (1 row) table_name | column_name | data_type | udt_name -------------+-------------+-----------+---------- org_members | org_id | uuid | uuid org_members | user_id | uuid | uuid org_members | role | text | text orgs | id | uuid | uuid orgs | name | text | text tickets | id | bigint | int8 tickets | org_id | uuid | uuid tickets | title | text | text tickets | status | text | text (9 rows) schemaname | tablename | policyname | permissive | roles | cmd | qual | with_check ------------+-----------+------------+------------+-------+-----+------+------------ (0 rows)
set -euo pipefail
psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 -P pager=off <<'SQL'
SELECT c.relname, c.relrowsecurity, c.relforcerowsecurity, pg_get_userbyid(c.relowner) owner
FROM pg_class c JOIN pg_namespace n ON n.oid=c.relnamespace
WHERE n.nspname='public' AND c.relname IN ('orgs','org_members','tickets');
SELECT grantee, table_name, privilege_type
FROM information_schema.role_table_grants
WHERE table_schema='public' AND table_name IN ('orgs','org_members','tickets')
AND grantee IN ('anon','authenticated')
ORDER BY table_name, grantee, privilege_type;
SQL relname | relrowsecurity | relforcerowsecurity | owner
-------------+----------------+---------------------+----------
org_members | f | f | postgres
orgs | f | f | postgres
tickets | f | f | postgres
(3 rows)
grantee | table_name | privilege_type
---------------+-------------+----------------
anon | org_members | DELETE
anon | org_members | INSERT
anon | org_members | REFERENCES
anon | org_members | SELECT
anon | org_members | TRIGGER
anon | org_members | TRUNCATE
anon | org_members | UPDATE
authenticated | org_members | DELETE
authenticated | org_members | INSERT
authenticated | org_members | REFERENCES
authenticated | org_members | SELECT
authenticated | org_members | TRIGGER
authenticated | org_members | TRUNCATE
authenticated | org_members | UPDATE
anon | orgs | DELETE
anon | orgs | INSERT
anon | orgs | REFERENCES
anon | orgs | SELECT
anon | orgs | TRIGGER
anon | orgs | TRUNCATE
anon | orgs | UPDATE
authenticated | orgs | DELETE
authenticated | orgs | INSERT
authenticated | orgs | REFERENCES
authenticated | orgs | SELECT
authenticated | orgs | TRIGGER
authenticated | orgs | TRUNCATE
authenticated | orgs | UPDATE
anon | tickets | DELETE
anon | tickets | INSERT
anon | tickets | REFERENCES
anon | tickets | SELECT
anon | tickets | TRIGGER
anon | tickets | TRUNCATE
anon | tickets | UPDATE
authenticated | tickets | DELETE
authenticated | tickets | INSERT
authenticated | tickets | REFERENCES
authenticated | tickets | SELECT
authenticated | tickets | TRIGGER
authenticated | tickets | TRUNCATE
authenticated | tickets | UPDATE
(42 rows)
set -euo pipefail
psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 <<'SQL'
BEGIN;
CREATE SCHEMA IF NOT EXISTS app_private;
REVOKE ALL ON SCHEMA app_private FROM PUBLIC, anon;
GRANT USAGE ON SCHEMA app_private TO authenticated;
CREATE OR REPLACE FUNCTION app_private.is_org_member(target_org_id uuid)
RETURNS boolean
LANGUAGE sql
STABLE
SECURITY DEFINER
SET search_path = ''
AS $$
SELECT EXISTS (
SELECT 1
FROM public.org_members AS om
WHERE om.org_id = target_org_id
AND om.user_id = auth.uid()
);
$$;
CREATE OR REPLACE FUNCTION app_private.is_org_admin(target_org_id uuid)
RETURNS boolean
LANGUAGE sql
STABLE
SECURITY DEFINER
SET search_path = ''
AS $$
SELECT EXISTS (
SELECT 1
FROM public.org_members AS om
WHERE om.org_id = target_org_id
AND om.user_id = auth.uid()
AND om.role = 'admin'
);
$$;
ALTER FUNCTION app_private.is_org_member(uuid) OWNER TO postgres;
ALTER FUNCTION app_private.is_org_admin(uuid) OWNER TO postgres;
REVOKE ALL ON FUNCTION app_private.is_org_member(uuid) FROM PUBLIC, anon;
REVOKE ALL ON FUNCTION app_private.is_org_admin(uuid) FROM PUBLIC, anon;
GRANT EXECUTE ON FUNCTION app_private.is_org_member(uuid) TO authenticated;
GRANT EXECUTE ON FUNCTION app_private.is_org_admin(uuid) TO authenticated;
ALTER TABLE public.tickets ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.org_members ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.orgs ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS tickets_member_select ON public.tickets;
DROP POLICY IF EXISTS tickets_admin_insert ON public.tickets;
DROP POLICY IF EXISTS tickets_admin_update ON public.tickets;
DROP POLICY IF EXISTS tickets_admin_delete ON public.tickets;
DROP POLICY IF EXISTS org_members_member_select ON public.org_members;
DROP POLICY IF EXISTS orgs_read ON public.orgs;
CREATE POLICY tickets_member_select
ON public.tickets FOR SELECT TO authenticated
USING (app_private.is_org_member(org_id));
CREATE POLICY tickets_admin_insert
ON public.tickets FOR INSERT TO authenticated
WITH CHECK (app_private.is_org_admin(org_id));
CREATE POLICY tickets_admin_update
ON public.tickets FOR UPDATE TO authenticated
USING (app_private.is_org_admin(org_id))
WITH CHECK (app_private.is_org_admin(org_id));
CREATE POLICY tickets_admin_delete
ON public.tickets FOR DELETE TO authenticated
USING (app_private.is_org_admin(org_id));
CREATE POLICY org_members_member_select
ON public.org_members FOR SELECT TO authenticated
USING (app_private.is_org_member(org_id));
CREATE POLICY orgs_read
ON public.orgs FOR SELECT TO anon, authenticated
USING (true);
COMMIT;
SQL
psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 -P pager=off -c "select tablename, policyname, roles, cmd, qual, with_check from pg_policies where schemaname='public' and tablename in ('orgs','org_members','tickets') order by tablename, policyname;"BEGIN CREATE SCHEMA REVOKE GRANT
NOTICE: schema "app_private" already exists, skipping ERROR: cannot change name of input parameter "p_org_id" HINT: Use DROP FUNCTION app_private.is_org_member(uuid) first.
set -euo pipefail psql "$SUPABASE_DB_URL" -P pager=off -c "select n.nspname, p.proname, pg_get_function_identity_arguments(p.oid) args, pg_get_functiondef(p.oid) from pg_proc p join pg_namespace n on n.oid=p.pronamespace where n.nspname='app_private';"
nspname | proname | args | pg_get_functiondef
-------------+---------------+---------------+---------------------------------------------------------------------
app_private | is_org_admin | p_org_id uuid | CREATE OR REPLACE FUNCTION app_private.is_org_admin(p_org_id uuid) +
| | | RETURNS boolean +
| | | LANGUAGE sql +
| | | STABLE SECURITY DEFINER +
| | | SET search_path TO 'public' +
| | | AS $function$ +
| | | 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' +
| | | ); +
| | | $function$ +
| | |
app_private | is_org_member | p_org_id uuid | CREATE OR REPLACE FUNCTION app_private.is_org_member(p_org_id uuid)+
| | | RETURNS boolean +
| | | LANGUAGE sql +
| | | STABLE SECURITY DEFINER +
| | | SET search_path TO 'public' +
| | | AS $function$ +
| | | select exists ( +
| | | select 1 from public.org_members m +
| | | where m.org_id = p_org_id +
| | | and m.user_id = auth.uid() +
| | | ); +
| | | $function$ +
| | |
(2 rows)
set -euo pipefail psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 <<'SQL' BEGIN; CREATE SCHEMA IF NOT EXISTS app_private; REVOKE ALL ON SCHEMA app_private FROM PUBLIC, anon; GRANT USAGE ON SCHEMA app_private TO authenticated; CREATE OR REPLACE FUNCTION app_private.is_org_member(p_org_id uuid) RETURNS boolean LANGUAGE sql STABLE SECURITY DEFINER SET search_path = '' AS $$ SELECT EXISTS (SELECT 1 FROM public.org_members om WHERE om.org_id=p_org_id AND om.user_id=auth.uid()); $$; CREATE OR REPLACE FUNCTION app_private.is_org_admin(p_org_id uuid) RETURNS boolean LANGUAGE sql STABLE SECURITY DEFINER SET search_path = '' AS $$ SELECT EXISTS (SELECT 1 FROM public.org_members om WHERE om.org_id=p_org_id AND om.user_id=auth.uid() AND om.role='admin'); $$; ALTER FUNCTION app_private.is_org_member(uuid) OWNER TO postgres; ALTER FUNCTION app_private.is_org_admin(uuid) OWNER TO postgres; REVOKE ALL ON FUNCTION app_private.is_org_member(uuid) FROM PUBLIC, anon; REVOKE ALL ON FUNCTION app_private.is_org_admin(uuid) FROM PUBLIC, anon; GRANT EXECUTE ON FUNCTION app_private.is_org_member(uuid) TO authenticated; GRANT EXECUTE ON FUNCTION app_private.is_org_admin(uuid) TO authenticated; ALTER TABLE public.tickets ENABLE ROW LEVEL SECURITY; ALTER TABLE public.org_members ENABLE ROW LEVEL SECURITY; ALTER TABLE public.orgs ENABLE ROW LEVEL SECURITY; DROP POLICY IF EXISTS tickets_member_select ON public.tickets; DROP POLICY IF EXISTS tickets_admin_insert ON public.tickets; DROP POLICY IF EXISTS tickets_admin_update ON public.tickets; DROP POLICY IF EXISTS tickets_admin_delete ON public.tickets; DROP POLICY IF EXISTS org_members_member_select ON public.org_members; DROP POLICY IF EXISTS orgs_read ON public.orgs; CREATE POLICY tickets_member_select ON public.tickets FOR SELECT TO authenticated USING (app_private.is_org_member(org_id)); CREATE POLICY tickets_admin_insert ON public.tickets FOR INSERT TO authenticated WITH CHECK (app_private.is_org_admin(org_id)); CREATE POLICY tickets_admin_update ON public.tickets FOR UPDATE TO authenticated USING (app_private.is_org_admin(org_id)) WITH CHECK (app_private.is_org_admin(org_id)); CREATE POLICY tickets_admin_delete ON public.tickets FOR DELETE TO authenticated USING (app_private.is_org_admin(org_id)); CREATE POLICY org_members_member_select ON public.org_members FOR SELECT TO authenticated USING (app_private.is_org_member(org_id)); CREATE POLICY orgs_read ON public.orgs FOR SELECT TO anon, authenticated USING (true); COMMIT; SQL
BEGIN CREATE SCHEMA REVOKE GRANT CREATE FUNCTION CREATE FUNCTION ALTER FUNCTION ALTER FUNCTION REVOKE REVOKE GRANT GRANT 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 COMMIT
NOTICE: schema "app_private" already exists, skipping NOTICE: policy "tickets_member_select" for relation "public.tickets" does not exist, skipping NOTICE: policy "tickets_admin_insert" for relation "public.tickets" does not exist, skipping NOTICE: policy "tickets_admin_update" for relation "public.tickets" does not exist, skipping NOTICE: policy "tickets_admin_delete" for relation "public.tickets" does not exist, skipping NOTICE: policy "org_members_member_select" for relation "public.org_members" does not exist, skipping NOTICE: policy "orgs_read" for relation "public.orgs" does not exist, skipping
set -euo pipefail
base="${SUPABASE_URL%/}"
login() { curl -fsS "$base/auth/v1/token?grant_type=password" -H "apikey: $SUPABASE_ANON_KEY" -H 'Content-Type: application/json' --data "{\"email\":\"$1\",\"password\":\"$2\"}" | jq -r .access_token; }
uma=$(login 'uma-admin@cari.dev' 'Passw0rd-uma-11')
vic=$(login 'vic-member@cari.dev' 'Passw0rd-vic-22')
wen=$(login 'wen-outsider@cari.dev' 'Passw0rd-wen-33')
get() { curl -fsS "$base/rest/v1/$1" -H "apikey: $SUPABASE_ANON_KEY" ${2:+-H "Authorization: Bearer $2"}; }
orgs=$(get 'orgs?select=id,name&order=name' "$uma")
acme=$(jq -r '.[]|select(.name=="Acme")|.id' <<<"$orgs")
globex=$(jq -r '.[]|select(.name=="Globex")|.id' <<<"$orgs")nprintf 'orgs=%s\n' "$orgs"
for who in anon uma vic wen; do
tok=''; case $who in uma) tok=$uma;; vic) tok=$vic;; wen) tok=$wen;; esac
tickets=$(get 'tickets?select=id,org_id,title,status&order=id' "$tok")
members=$(get 'org_members?select=org_id,user_id,role&order=org_id,user_id' "$tok")
printf '%s tickets=%s member_rows=%s\n' "$who" "$(jq length <<<"$tickets")" "$(jq length <<<"$members")"
printf ' ticket_orgs=%s member_orgs=%s\n' "$(jq -c '[.[].org_id]|unique' <<<"$tickets")" "$(jq -c '[.[].org_id]|unique' <<<"$members")"
done
# A member cannot insert; this must fail and therefore cannot alter data.
code=$(curl -sS -o /tmp/rls_insert_response -w '%{http_code}' "$base/rest/v1/tickets" \
-X POST -H "apikey: $SUPABASE_ANON_KEY" -H "Authorization: Bearer $vic" -H 'Content-Type: application/json' \
--data "{\"org_id\":\"$acme\",\"title\":\"RLS probe - must not persist\",\"status\":\"open\"}")
printf 'member_insert_http=%s response=%s\n' "$code" "$(cat /tmp/rls_insert_response)"
test "$code" -ge 400
# Verify original counts remain exactly unchanged.
printf 'final_counts acme=%s globex=%s\n' \
"$(get "tickets?select=id&org_id=eq.$acme" "$uma" | jq length)" \
"$(get "tickets?select=id&org_id=eq.$globex" "$wen" | jq length)"
bash: line 10: orgs=%s\n: command not found
set -euo pipefail
base="${SUPABASE_URL%/}"
login() { curl -fsS "$base/auth/v1/token?grant_type=password" -H "apikey: $SUPABASE_ANON_KEY" -H 'Content-Type: application/json' --data "{\"email\":\"$1\",\"password\":\"$2\"}" | jq -r .access_token; }
uma=$(login 'uma-admin@cari.dev' 'Passw0rd-uma-11'); vic=$(login 'vic-member@cari.dev' 'Passw0rd-vic-22'); wen=$(login 'wen-outsider@cari.dev' 'Passw0rd-wen-33')
get() { if [ -n "${2:-}" ]; then curl -fsS "$base/rest/v1/$1" -H "apikey: $SUPABASE_ANON_KEY" -H "Authorization: Bearer $2"; else curl -fsS "$base/rest/v1/$1" -H "apikey: $SUPABASE_ANON_KEY"; fi; }
orgs=$(get 'orgs?select=id,name&order=name' "$uma")
acme=$(jq -r '.[]|select(.name=="Acme")|.id' <<<"$orgs"); globex=$(jq -r '.[]|select(.name=="Globex")|.id' <<<"$orgs")
printf 'orgs=%s\n' "$orgs"
for who in anon uma vic wen; do
tok=''; case $who in uma) tok=$uma;; vic) tok=$vic;; wen) tok=$wen;; esac
tickets=$(get 'tickets?select=id,org_id,title,status&order=id' "$tok"); members=$(get 'org_members?select=org_id,user_id,role&order=org_id,user_id' "$tok")
printf '%s tickets=%s member_rows=%s\n' "$who" "$(jq length <<<"$tickets")" "$(jq length <<<"$members")"
printf ' ticket_orgs=%s member_orgs=%s\n' "$(jq -c '[.[].org_id]|unique' <<<"$tickets")" "$(jq -c '[.[].org_id]|unique' <<<"$members")"
done
code=$(curl -sS -o /tmp/rls_insert_response -w '%{http_code}' "$base/rest/v1/tickets" -X POST -H "apikey: $SUPABASE_ANON_KEY" -H "Authorization: Bearer $vic" -H 'Content-Type: application/json' --data "{\"org_id\":\"$acme\",\"title\":\"RLS probe - must not persist\",\"status\":\"open\"}")
printf 'member_insert_http=%s response=%s\n' "$code" "$(cat /tmp/rls_insert_response)"; test "$code" -ge 400
printf 'final_counts acme=%s globex=%s\n' "$(get "tickets?select=id&org_id=eq.$acme" "$uma" | jq length)" "$(get "tickets?select=id&org_id=eq.$globex" "$wen" | jq length)"
orgs=[{"id":"d7e95966-3abc-4d89-b6e4-9085960958af","name":"Acme"},
{"id":"61ea3634-91fd-42da-bcbb-db5f6bdd8847","name":"Globex"}]
anon tickets=0 member_rows=0
ticket_orgs=[] member_orgs=[]
uma tickets=3 member_rows=2
ticket_orgs=["d7e95966-3abc-4d89-b6e4-9085960958af"] member_orgs=["d7e95966-3abc-4d89-b6e4-9085960958af"]
vic tickets=3 member_rows=2
ticket_orgs=["d7e95966-3abc-4d89-b6e4-9085960958af"] member_orgs=["d7e95966-3abc-4d89-b6e4-9085960958af"]
wen tickets=2 member_rows=1
ticket_orgs=["61ea3634-91fd-42da-bcbb-db5f6bdd8847"] member_orgs=["61ea3634-91fd-42da-bcbb-db5f6bdd8847"]
member_insert_http=403 response={"code":"42501","details":null,"hint":null,"message":"new row violates row-level security policy for table \"tickets\""}
final_counts acme=3 globex=2
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