创建示例中所需的表，并对其正确赋权。
-- ============================================================
-- 1. 建表
-- ============================================================
-- 商品表
CREATE TABLE products (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name TEXT NOT NULL,
category TEXT,
description TEXT,
created_at TIMESTAMPTZ DEFAULT now(),
updated_at TIMESTAMPTZ DEFAULT now()
);
-- 商品定价表
CREATE TABLE product_pricings (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
product_id BIGINT NOT NULL REFERENCES products(id) ON DELETE CASCADE,
tier TEXT NOT NULL,
price NUMERIC(10,2) NOT NULL,
unit TEXT DEFAULT '美元/月',
effective_from DATE DEFAULT current_date,
created_at TIMESTAMPTZ DEFAULT now()
);
-- ============================================================
-- 2. 启用RLS（Realtime postgres_changes依赖RLS判断可见性）
-- ============================================================
ALTER TABLE products ENABLE ROW LEVEL SECURITY;
ALTER TABLE product_pricings ENABLE ROW LEVEL SECURITY;
-- ============================================================
-- 3. RLS策略
-- ============================================================
-- anon只读（生产环境推荐）
CREATE POLICY "anon_select_products" ON public.products FOR SELECT TO anon USING (true);
CREATE POLICY "anon_select_product_pricings" ON public.product_pricings FOR SELECT TO anon USING (true);
-- anon 全量 CRUD（开发/测试用，取消注释启用）
-- CREATE POLICY "anon_insert_products" ON public.products FOR INSERT TO anon WITH CHECK (true);
-- CREATE POLICY "anon_insert_product_pricings" ON public.product_pricings FOR INSERT TO anon WITH CHECK (true);
-- CREATE POLICY "anon_update_products" ON public.products FOR UPDATE TO anon USING (true) WITH CHECK (true);
-- CREATE POLICY "anon_update_product_pricings" ON public.product_pricings FOR UPDATE TO anon USING (true) WITH CHECK (true);
-- CREATE POLICY "anon_delete_products" ON public.products FOR DELETE TO anon USING (true);
-- CREATE POLICY "anon_delete_product_pricings" ON public.product_pricings FOR DELETE TO anon USING (true);
-- authenticated 可增删改
CREATE POLICY "authenticated_select_products" ON public.products FOR SELECT TO authenticated USING (true);
CREATE POLICY "authenticated_select_product_pricings" ON public.product_pricings FOR SELECT TO authenticated USING (true);
CREATE POLICY "authenticated_insert_products" ON public.products FOR INSERT TO authenticated WITH CHECK (true);
CREATE POLICY "authenticated_insert_product_pricings" ON public.product_pricings FOR INSERT TO authenticated WITH CHECK (true);
CREATE POLICY "authenticated_update_products" ON public.products FOR UPDATE TO authenticated USING (true) WITH CHECK (true);
CREATE POLICY "authenticated_update_product_pricings" ON public.product_pricings FOR UPDATE TO authenticated USING (true) WITH CHECK (true);
CREATE POLICY "authenticated_delete_products" ON public.products FOR DELETE TO authenticated USING (true);
CREATE POLICY "authenticated_delete_product_pricings" ON public.product_pricings FOR DELETE TO authenticated USING (true);
-- service_role无需策略，默认绕过RLS
-- ============================================================
-- 4. 表级权限（RLS是行级过滤，GRANT是表级门控，两者缺一不可）
-- ============================================================
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE public.products TO anon;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE public.product_pricings TO anon;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE public.products TO authenticated;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE public.product_pricings TO authenticated;
GRANT ALL ON TABLE product_pricings TO service_role;
GRANT ALL ON ALL TABLES IN SCHEMA public TO service_role;
-- ============================================================
-- 5. Realtime：将表加入已有publication
-- ============================================================
ALTER PUBLICATION supabase_realtime ADD TABLE public.products;
ALTER PUBLICATION supabase_realtime ADD TABLE public.product_pricings;
-- ============================================================
-- 6. 示例数据
-- ============================================================
INSERT INTO products (name, category, description) VALUES
('云存储服务', '软件服务', '企业级对象存储服务，安全可靠'),
('GPU 云主机', '硬件', '高性能 GPU 计算实例，适用于 AI 训练与推理'),
('API 网关', '软件服务', '统一 API 入口管理，支持限流、鉴权、监控');
INSERT INTO product_pricings (product_id, tier, price, unit) VALUES
(1, '基础版', 99.00, '美元/月'),
(1, '专业版', 299.00, '美元/月'),
(1, '企业版', 899.00, '美元/月'),
(2, '标准型', 1280.00, '美元/月'),
(2, '增强型', 3580.00, '美元/月'),
(2, '旗舰型', 8990.00, '美元/月'),
(3, '入门版', 49.00, '美元/月'),
(3, '专业版', 199.00, '美元/月');
-- 刷新PostgREST schema缓存
NOTIFY pgrst, 'reload schema'; 
，选择区域。
从环境变量配置中获取对应的应用访问地址和Anon API Key。详见表1。
