claude-opus-5 · d1_crud · rep 3

env | grep -i supabase | sed 's/\(KEY=.\{12\}\).*/\1.../' ; echo "---"; which psql curl python3

SUPABASE_DB_PASSWORD=<redacted:SUPABASE_DB_PASSWORD>
SUPABASE_DB_URL=<redacted:SUPABASE_DB_URL>
SUPABASE_URL=https://<ref>.supabase.co
SUPABASE_SERVICE_ROLE_KEY=eyJhbGciOiJI...
SUPABASE_PROJECT_REF=<ref>
SUPABASE_ANON_KEY=eyJhbGciOiJI...
---
/usr/bin/psql
/usr/bin/curl
/usr/bin/python3

curl -s -o /dev/null -w "%{http_code}\n" "$SUPABASE_URL/rest/v1/" -H "apikey: $SUPABASE_ANON_KEY"

401

psql "$SUPABASE_DB_URL" -c "select current_user, version();" 2>&1 | head -20

 current_user |                                   version                                    
--------------+------------------------------------------------------------------------------
 postgres     | PostgreSQL 17.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 15.2.0, 64-bit
(1 row)

curl -s -w "\nHTTP:%{http_code}\n" "$SUPABASE_URL/rest/v1/products?select=*" -H "apikey: $SUPABASE_ANON_KEY" -H "Authorization: Bearer $SUPABASE_ANON_KEY"

{"code":"PGRST205","details":null,"hint":null,"message":"Could not find the table 'public.products' in the schema cache"}
HTTP:404

cat > /tmp/setup.sql <<'SQL'
begin;

create table if not exists public.products (
  sku         text primary key,
  name        text    not null,
  price_cents integer not null,
  stock       integer not null default 0
);

-- Load exactly the five specified rows (idempotent, no duplicates/leftovers)
truncate table public.products;

insert into public.products (sku, name, price_cents, stock) values
  ('CARI-001', 'Anvil S',  1999, 12),
  ('CARI-002', 'Anvil M',  2999,  7),
  ('CARI-003', 'Anvil L',  4999,  3),
  ('CARI-004', 'Rope 30m', 1250, 40),
  ('CARI-005', 'Pulley',    780,  0);

commit;
SQL
psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 -f /tmp/setup.sql 2>&1

BEGIN
CREATE TABLE
TRUNCATE TABLE
INSERT 0 5
COMMIT

cat > /tmp/rls.sql <<'SQL'
begin;

-- Enable RLS so access is deny-by-default, then explicitly allow public reads only.
alter table public.products enable row level security;

drop policy if exists "products_public_read" on public.products;
create policy "products_public_read"
  on public.products
  for select
  to anon, authenticated
  using (true);

-- anon/authenticated get SELECT only: no insert/update/delete on the catalog.
grant usage on schema public to anon, authenticated;
grant select on table public.products to anon, authenticated;
revoke insert, update, delete, truncate, references, trigger
  on table public.products from anon, authenticated;

commit;
SQL
psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 -f /tmp/rls.sql 2>&1

BEGIN
ALTER TABLE
psql:/tmp/rls.sql:6: NOTICE:  policy "products_public_read" for relation "public.products" does not exist, skipping
DROP POLICY
CREATE POLICY
GRANT
GRANT
REVOKE
COMMIT

psql "$SUPABASE_DB_URL" -c "notify pgrst, 'reload schema';" 2>&1; sleep 6

NOTIFY

echo "=== anon key ONLY (apikey header, no Authorization) ==="
curl -s -w "\nHTTP:%{http_code}\n" \
  "$SUPABASE_URL/rest/v1/products?select=sku,name,price_cents,stock&order=sku" \
  -H "apikey: $SUPABASE_ANON_KEY"

=== anon key ONLY (apikey header, no Authorization) ===
[{"sku":"CARI-001","name":"Anvil S","price_cents":1999,"stock":12}, 
 {"sku":"CARI-002","name":"Anvil M","price_cents":2999,"stock":7}, 
 {"sku":"CARI-003","name":"Anvil L","price_cents":4999,"stock":3}, 
 {"sku":"CARI-004","name":"Rope 30m","price_cents":1250,"stock":40}, 
 {"sku":"CARI-005","name":"Pulley","price_cents":780,"stock":0}]
HTTP:200

echo "=== no credentials at all (must NOT succeed) ==="
curl -s -w "\nHTTP:%{http_code}\n" "$SUPABASE_URL/rest/v1/products?select=*"

=== no credentials at all (must NOT succeed) ===
{"message":"No API key found in request","hint":"No `apikey` request header or url param was found."}
HTTP:401

python3 - <<'PY'
import json,os,urllib.request
u=os.environ["SUPABASE_URL"]+"/rest/v1/products?select=sku,name,price_cents,stock&order=sku"
r=urllib.request.Request(u,headers={"apikey":os.environ["SUPABASE_ANON_KEY"]})
got=json.load(urllib.request.urlopen(r))
want=[{"sku":"CARI-001","name":"Anvil S","price_cents":1999,"stock":12},
      {"sku":"CARI-002","name":"Anvil M","price_cents":2999,"stock":7},
      {"sku":"CARI-003","name":"Anvil L","price_cents":4999,"stock":3},
      {"sku":"CARI-004","name":"Rope 30m","price_cents":1250,"stock":40},
      {"sku":"CARI-005","name":"Pulley","price_cents":780,"stock":0}]
print("row count:",len(got))
print("EXACT MATCH:", got==want)
if got!=want:
    print("got: ",json.dumps(got,indent=1)); print("want:",json.dumps(want,indent=1))
PY

row count: 5
EXACT MATCH: True

echo "=== anon INSERT attempt (should be blocked) ==="
curl -s -w "\nHTTP:%{http_code}\n" -X POST "$SUPABASE_URL/rest/v1/products" \
  -H "apikey: $SUPABASE_ANON_KEY" -H "Content-Type: application/json" \
  -d '{"sku":"HACK-001","name":"nope","price_cents":1,"stock":1}'
echo "=== anon DELETE attempt (should be blocked) ==="
curl -s -w "\nHTTP:%{http_code}\n" -X DELETE "$SUPABASE_URL/rest/v1/products?sku=eq.CARI-001" \
  -H "apikey: $SUPABASE_ANON_KEY"

=== anon INSERT attempt (should be blocked) ===
{"code":"42501","details":null,"hint":"Grant the required privileges to the current role with: GRANT INSERT ON public.products TO anon;","message":"permission denied for table products"}
HTTP:401
=== anon DELETE attempt (should be blocked) ===
{"code":"42501","details":null,"hint":"Grant the required privileges to the current role with: GRANT DELETE ON public.products TO anon;","message":"permission denied for table products"}
HTTP:401

psql "$SUPABASE_DB_URL" -c "\d public.products" -c "select relrowsecurity as rls_enabled from pg_class where oid='public.products'::regclass;" -c "select polname, polcmd, polroles::regrole[] from pg_policy where polrelid='public.products'::regclass;" 2>&1

                Table "public.products"
   Column    |  Type   | Collation | Nullable | Default 
-------------+---------+-----------+----------+---------
 sku         | text    |           | not null | 
 name        | text    |           | not null | 
 price_cents | integer |           | not null | 
 stock       | integer |           | not null | 0
Indexes:
    "products_pkey" PRIMARY KEY, btree (sku)
Policies:
    POLICY "products_public_read" FOR SELECT
      TO anon,authenticated
      USING (true)

 rls_enabled 
-------------
 t
(1 row)

       polname        | polcmd |       polroles       
----------------------+--------+----------------------
 products_public_read | r      | {anon,authenticated}
(1 row)