Row Level Security

所有 public schema 下的 34 张业务表均已启用 RLS。未配置策略的表默认拒绝所有操作（仅 SECURITY DEFINER 函数和 service_role 可绕过）。

辅助函数

策略中大量复用两个权限判断函数，均从 JWT app_metadata.role 字段读取角色：

-- 仅 admin
CREATE FUNCTION is_admin() RETURNS boolean AS $$
DECLARE user_role text;
BEGIN
  user_role := (auth.jwt() -> 'app_metadata' ->> 'role');
  RETURN user_role = 'admin';
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- admin 或 editor
CREATE FUNCTION is_admin_or_editor() RETURNS boolean AS $$
DECLARE user_role text;
BEGIN
  user_role := (auth.jwt() -> 'app_metadata' ->> 'role');
  RETURN user_role IN ('admin', 'editor');
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

部分早期策略直接内联了 (auth.jwt() -> 'app_metadata' ->> 'role') = 'admin' 而非调用辅助函数，效果等价。

策略模式总览

项目中的 RLS 策略遵循以下几种固定模式：

模式	说明	典型表
公开读 + 管理写	所有人可读，admin/editor 可增删改	comics, chapters, categories, tags
公开读 + 用户自管理	所有人可读，用户只能操作自己的行	comic_likes, comic_ratings, comics_favorites
仅用户本人	用户只能访问自己的数据	notifications, reading_history
公开只读	所有人可读，仅 admin 可写	announcements, app_settings, products
条件可见	根据状态或标志位控制可见性	comments, kanban_tickets
无策略（默认拒绝）	RLS 已启用但未配置策略，仅函数内部可写	chapter_view_logs, free_user_daily_premium_views

漫画内容

comics

公开可读，admin/editor 完全管理。

策略名	操作	条件
Public read comics	SELECT	`true`
Admins and editors can insert comic	INSERT	`is_admin_or_editor()`
Admins and editors can update comic	UPDATE	`is_admin_or_editor()`
Admins and editors can delete comic	DELETE	`is_admin_or_editor()`

chapters

与 comics 相同的管理模式。

策略名	操作	条件
Public read chapters	SELECT	`true`
Admins and editors can insert chapters	INSERT	`is_admin_or_editor()`
Admins and editors can update chapters	UPDATE	`is_admin_or_editor()`
Admins and editors can delete chapters	DELETE	`is_admin_or_editor()`

策略名	操作	角色	条件
Public categories are viewable by everyone	SELECT	public	`true`
admin full access	ALL	public	`is_admin_or_editor()`

策略名	操作	角色	条件
Public tags are viewable by everyone	SELECT	public	`true`
Allow admin and editor full access	ALL	authenticated	`app_metadata.role IN ('admin','editor')`

comic_tags

策略名	操作	条件
Public read comic_tags	SELECT	`true`
Admins and editors can insert comic_tags	INSERT	`is_admin_or_editor()`
Admins and editors can update comic_tags	UPDATE	`is_admin_or_editor()`
Admins and editors can delete comic_tags	DELETE	`is_admin_or_editor()`

comic_relations

策略名	操作	条件
Public read relations	SELECT	`true`
admins and editor can do everything on relations	ALL	`is_admin_or_editor()`

用户交互

comic_likes

公开可读，用户只能操作自己的点赞。

策略名	操作	条件
Public can read likes	SELECT	`true`
Users can insert their own likes	INSERT	`auth.uid() = user_id`
Users can delete their own likes	DELETE	`auth.uid() = user_id`

comic_ratings

策略名	操作	条件
Public can read ratings	SELECT	`true`
Users can insert their own ratings	INSERT	`auth.uid() = user_id`
Users can update their own ratings	UPDATE	`auth.uid() = user_id`
Users can delete their own ratings	DELETE	`auth.uid() = user_id`

comics_favorites

策略名	操作	条件
public can read favorites	SELECT	`true`
User manages own favorites	ALL	`auth.uid() = user_id`

comment_likes

策略名	操作	条件
Public view likes	SELECT	`true`
Users can like	INSERT	`auth.uid() = user_id`
Users can unlike	DELETE	`auth.uid() = user_id`

comments

评论的可见性受 status 字段控制，用户始终能看到自己的评论（包括被审核的），管理员可见全部。

策略名	操作	条件
Comments visibility policy	SELECT	`status = 'public' OR auth.uid() = user_id OR is_admin_or_editor()`
Authenticated users can insert	INSERT	`auth.role() = 'authenticated' AND auth.uid() = user_id`
Comments update policy	UPDATE	`is_admin_or_editor()`
Comments delete policy	DELETE	`auth.uid() = user_id OR is_admin_or_editor()`

普通用户可以删除自己的评论，但不能编辑（编辑权限仅限管理员）。这是有意设计，防止用户修改已审核通过的评论内容。

用户与个人资料

profiles

策略名	操作	条件
Public profiles are viewable by everyone	SELECT	`true`
Users can insert their own profile	INSERT	`auth.uid() = id`
Users can update own profile	UPDATE	`auth.uid() = id`
Admins can update any profile	UPDATE	`is_admin()`

user_badges

策略名	操作	条件
Badges are viewable by everyone	SELECT	`true`
Users can only equip valid badges	UPDATE	USING: `auth.uid() = user_id`，WITH CHECK: `is_displayed = false OR expires_at IS NULL OR expires_at > now()`
admin has full access	ALL	`is_admin()`

用户装备徽章时，WITH CHECK 额外验证徽章未过期，防止装备已失效的徽章。

badge_definitions

策略名	操作	条件
Everyone can view badges	SELECT	`true`

阅读与通知

reading_history

策略名	操作	条件
User manages own history	ALL	`auth.uid() = user_id`

notifications

用户只能访问自己的通知，管理员可发送。

策略名	操作	条件
Users can view own notifications	SELECT	`auth.uid() = user_id`
Users can update own notifications	UPDATE	`auth.uid() = user_id`
admin and editor can send dms	INSERT	`is_admin_or_editor()`

chapter_view_logs

RLS 已启用但无任何策略，所有客户端操作均被拒绝。写入仅通过 SECURITY DEFINER 函数 count_chapter_view_if_new 完成。详见章节浏览计数。

free_user_daily_premium_views

RLS 已启用但无任何策略，所有客户端操作均被拒绝。写入仅通过 SECURITY DEFINER 函数 record_free_premium_view 完成。详见高清配额管理。

支付

products

策略名	操作	条件
Everyone can view products	SELECT	`true`
Admins can insert products	INSERT	`is_admin()`
Admins and Service Role can update products	UPDATE	`is_admin() OR auth.role() = 'service_role'`
Admins can delete products	DELETE	`is_admin()`

products 的 UPDATE 额外允许 service_role，因为支付回调的 Edge Function 需要用 service_role 更新商品状态。

orders

策略名	操作	条件
Users view own orders	SELECT	`auth.uid() = user_id`
Admins view all orders	SELECT	`is_admin()`
Users create orders	INSERT	`auth.uid() = user_id`
Service or Admin update orders	UPDATE	`is_admin() OR auth.role() = 'service_role'`

工单系统

kanban_tickets

工单的可见性由 is_public 字段和提交者身份共同控制。

策略名	操作	角色	条件
ticket_select	SELECT	anon, authenticated	`is_public = true OR reporter_id = auth.uid() OR is_admin_or_editor()`
ticket_insert_admin	INSERT	authenticated	`is_admin_or_editor()`
ticket_update	UPDATE	authenticated	USING/WITH CHECK: `(reporter_id = auth.uid() AND status IN ('RECORDED','TRACKING')) OR is_admin_or_editor()`
ticket_delete	DELETE	authenticated	`(reporter_id = auth.uid() AND status IN ('RECORDED','TRACKING')) OR is_admin()`

普通用户只能修改/删除自己提交的且状态为 RECORDED 或 TRACKING 的工单，进入后续阶段后仅管理员可操作。删除权限仅限 is_admin()（不含 editor）。

ticket_votes

策略名	操作	角色	条件
select_all_votes	SELECT	anon, authenticated	`true`
View own votes	SELECT	authenticated	`user_id = auth.uid()`
Vote on public tickets	INSERT	authenticated	子查询检查 `kanban_tickets.is_public = true`
insert_vote	INSERT	public	`auth.uid() IS NOT NULL AND user_id = auth.uid()` + 子查询检查公开且非自己提交的工单
Delete own vote	DELETE	authenticated	`user_id = auth.uid()`
delete_own_vote	DELETE	public	`user_id = auth.uid()`

ticket_votes 存在重复策略（同一操作有两条 PERMISSIVE 策略），这是迭代过程中的残留，功能上不影响（PERMISSIVE 策略取 OR 关系），但建议后续清理。

活动系统

app_events

策略名	操作	角色	条件
authenticated_read_events	SELECT	authenticated	`is_active = true`
admin can do all things	ALL	public	`is_admin()`

app_event_signups

报名表的 INSERT 策略包含子查询校验活动状态：

策略名	操作	角色	条件
users_read_signups	SELECT	public	`true`
users_insert_own_signup	INSERT	authenticated	`user_id = auth.uid() AND status = 'pending'` + 子查询检查活动 `is_active` 且在时间范围内
users_update_own_pending_signup	UPDATE	authenticated	`user_id = auth.uid() AND status = 'pending'`，WITH CHECK 额外要求 `reviewed_by/reviewed_at IS NULL`
users_delete_own_pending_signup	DELETE	authenticated	`user_id = auth.uid() AND status = 'pending'`
admin can do all things	ALL	authenticated	`is_admin()`

用户只能操作自己 pending 状态的报名，一旦被管理员审核（reviewed_by 非空），用户即无法修改或删除。

app_channel_members

策略名	操作	条件
admin_full_access	ALL	`is_admin()`

系统配置

app_settings

策略名	操作	角色	条件
Allow public read on app_settings	SELECT	public	`true`
Public read access	SELECT	public	`true`
Admin write access	ALL	authenticated	`is_admin()`

announcements

策略名	操作	条件
Allow public read on announcements	SELECT	`true`
admin can edit any announcements	ALL	`is_admin()`

home_banners

策略名	操作	条件
Allow public read on home_banners	SELECT	`true`

app_versions / app_release_infos

两张表策略完全相同：公开可读，admin 完全管理。

策略名	操作	条件
Public read versions / Public read releases	SELECT	`true`
Only admins can insert ...	INSERT	`is_admin()`
Only admins can update ...	UPDATE	`is_admin()`
Only admins can delete ...	DELETE	`is_admin()`

admin_logs

策略名	操作	条件
Admins can view logs	SELECT	`app_metadata.role = 'admin'`

download_tasks

策略名	操作	条件
public read	SELECT	`true`
all staff can manage webhook	ALL	`is_admin_or_editor()`

download_task_chapters

策略名	操作	条件
all-approve webhook	ALL	`is_admin_or_editor()`

anon_sd_access_log

策略名	操作	条件
Allow select from edge function	SELECT	`true`
Allow insert from edge function	INSERT	`true`

anon_sd_access_log 的 INSERT 策略为 true（允许所有人写入），这是因为匿名用户的 SD 图片访问记录由 Edge Function 以 anon key 写入，无法使用 auth.uid() 约束。

Row Level Security

On this page