moonshotai/kimi-k3 · d1_crud · rep 1

env | grep -E '^SUPABASE_' | sed 's/=.*KEY.*/=<redacted>/; s/\(PASSWORD\)=.*/\1=<redacted>/'; echo '---'; which psql || echo 'no psql'; which python3 || echo 'no python3'

SUPABASE_DB_PASSWORD=<redacted>
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>
---
/usr/bin/psql
/usr/bin/python3

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

-- Ensure ownership/privileges so PostgREST roles can use it
ALTER TABLE public.products OWNER TO postgres;

-- Load exactly the five rows (idempotent: clear then insert)
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);

-- Public read access via anon key: grant SELECT and enable RLS with an anon read policy
GRANT SELECT ON public.products TO anon;
GRANT SELECT ON public.products TO authenticated;

ALTER TABLE public.products ENABLE ROW LEVEL SECURITY;

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

SELECT * FROM public.products ORDER BY sku;
SQL

CREATE TABLE
ALTER TABLE
TRUNCATE TABLE
INSERT 0 5
GRANT
GRANT
ALTER TABLE
DROP POLICY
CREATE POLICY
   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)

NOTICE:  policy "public read products" for relation "public.products" does not exist, skipping

psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 -c "NOTIFY pgrst, 'reload schema';" ; sleep 3; curl -sS -w '\nHTTP_STATUS:%{http_code}\n' "$SUPABASE_URL/rest/v1/products?select=sku,name,price_cents,stock&order=sku.asc" -H "apikey: $SUPABASE_ANON_KEY" -H "Authorization: Bearer $SUPABASE_ANON_KEY"

NOTIFY
[{"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_STATUS:200

psql "$SUPABASE_DB_URL" -c "SELECT relname, relrowsecurity FROM pg_class WHERE relname='products' AND relnamespace='public'::regnamespace;" -c "SELECT grantee, privilege_type FROM information_schema.role_table_grants WHERE table_schema='public' AND table_name='products' ORDER BY grantee, privilege_type;"

 relname  | relrowsecurity 
----------+----------------
 products | t
(1 row)

    grantee    | privilege_type 
---------------+----------------
 anon          | DELETE
 anon          | INSERT
 anon          | REFERENCES
 anon          | SELECT
 anon          | TRIGGER
 anon          | TRUNCATE
 anon          | UPDATE
 authenticated | DELETE
 authenticated | INSERT
 authenticated | REFERENCES
 authenticated | SELECT
 authenticated | TRIGGER
 authenticated | TRUNCATE
 authenticated | UPDATE
 postgres      | DELETE
 postgres      | INSERT
 postgres      | REFERENCES
 postgres      | SELECT
 postgres      | TRIGGER
 postgres      | TRUNCATE
 postgres      | UPDATE
 service_role  | DELETE
 service_role  | INSERT
 service_role  | REFERENCES
 service_role  | SELECT
 service_role  | TRIGGER
 service_role  | TRUNCATE
 service_role  | UPDATE
(28 rows)

psql "$SUPABASE_DB_URL" -v ON_ERROR_STOP=1 <<'SQL'
REVOKE INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER ON public.products FROM anon;
REVOKE INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER ON public.products FROM authenticated;
SQL
psql "$SUPABASE_DB_URL" -c "SELECT grantee, privilege_type FROM information_schema.role_table_grants WHERE table_schema='public' AND table_name='products' AND grantee IN ('anon','authenticated');"

REVOKE
REVOKE
    grantee    | privilege_type 
---------------+----------------
 anon          | SELECT
 authenticated | SELECT
(2 rows)

echo '=== READ with anon key only ==='
curl -sS -w '\nHTTP_STATUS:%{http_code}\n' "$SUPABASE_URL/rest/v1/products?select=sku,name,price_cents,stock&order=sku.asc" \
  -H "apikey: $SUPABASE_ANON_KEY" -H "Authorization: Bearer $SUPABASE_ANON_KEY"
echo '=== WRITE attempt with anon key (should be denied) ==='
curl -sS -w '\nHTTP_STATUS:%{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" -H "Prefer: return=minimal" \
  -d '{"sku":"HACK-001","name":"x","price_cents":1,"stock":1}'
echo '=== Row count after write attempt ==='
psql "$SUPABASE_DB_URL" -t -c "SELECT count(*) FROM public.products;"

=== READ with 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}]
HTTP_STATUS:200
=== WRITE attempt with anon key (should be denied) ===
{"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_STATUS:401
=== Row count after write attempt ===
     5