claude-opus-4-8 · d1_crud · rep 1

env | grep -i supabase | sed 's/=.*/=<set>/'; echo "---"; which psql curl jq 2>/dev/null

SUPABASE_DB_PASSWORD=<set>
SUPABASE_DB_URL=<set>
SUPABASE_URL=<set>
SUPABASE_SERVICE_ROLE_KEY=<set>
SUPABASE_PROJECT_REF=<set>
SUPABASE_ANON_KEY=<set>
---
/usr/bin/psql
/usr/bin/curl
/usr/bin/jq

psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 <<'SQL'
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
);

TRUNCATE 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);

SELECT * FROM public.products ORDER BY sku;
SQL

CREATE TABLE
TRUNCATE TABLE
INSERT 0 5
   sku    |   name   | price_cents | stock 
----------+----------+-------------+-------
 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
(5 rows)

echo "=== anon read BEFORE policy ==="; curl -s "$SUPABASE_URL/rest/v1/products?select=*&order=sku" -H "apikey: $SUPABASE_ANON_KEY" -H "Authorization: Bearer $SUPABASE_ANON_KEY"; echo

=== anon read BEFORE policy ===
[{"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}]

psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 <<'SQL'
ALTER TABLE public.products ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "Public read access" ON public.products;
CREATE POLICY "Public read access"
  ON public.products
  FOR SELECT
  TO anon, authenticated
  USING (true);

-- ensure table-level privileges allow the read
GRANT SELECT ON public.products TO anon, authenticated;

SELECT relrowsecurity AS rls_enabled FROM pg_class WHERE oid = 'public.products'::regclass;
SELECT polname, cmd, roles FROM pg_policies_view LIMIT 0;
SQL

ALTER TABLE
DROP POLICY
CREATE POLICY
GRANT
 rls_enabled 
-------------
 t
(1 row)

NOTICE:  policy "Public read access" for relation "public.products" does not exist, skipping
ERROR:  relation "pg_policies_view" does not exist
LINE 1: SELECT polname, cmd, roles FROM pg_policies_view LIMIT 0;
                                        ^

psql "$SUPABASE_DB_URL" -c "SELECT policyname, cmd, roles, qual FROM pg_policies WHERE schemaname='public' AND tablename='products';"

     policyname     |  cmd   |        roles         | qual 
--------------------+--------+----------------------+------
 Public read access | SELECT | {anon,authenticated} | true
(1 row)

echo "=== anon read AFTER enabling RLS + policy (anon key ONLY) ==="
resp=$(curl -s "$SUPABASE_URL/rest/v1/products?select=*&order=sku" -H "apikey: $SUPABASE_ANON_KEY" -H "Authorization: Bearer $SUPABASE_ANON_KEY")
echo "$resp" | jq .
echo "--- row count: $(echo "$resp" | jq 'length')"

=== anon read AFTER enabling RLS + policy (anon key ONLY) ===
[
  {
    "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
  }
]
--- row count: 5

echo "=== anon INSERT attempt (should be blocked by RLS) ==="
curl -s -o /dev/null -w "HTTP %{http_code}\n" -X POST "$SUPABASE_URL/rest/v1/products" \
  -H "apikey: $SUPABASE_ANON_KEY" -H "Authorization: Bearer $SUPABASE_ANON_KEY" \
  -H "Content-Type: application/json" \
  -d '{"sku":"HACK-001","name":"x","price_cents":1,"stock":1}'

=== anon INSERT attempt (should be blocked by RLS) ===
HTTP 401