高级 pgTAP 测试

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
create extension if not exists http with schema extensions;create extension if not exists pg_tle;drop extension if exists "supabase-dbdev";select pgtle.uninstall_extension_if_exists('supabase-dbdev');select    pgtle.install_extension(        'supabase-dbdev',        resp.contents ->> 'version',        'PostgreSQL package manager',        resp.contents ->> 'sql'    )from http(    (        'GET',        'https://api.database.dev/rest/v1/'        || 'package_versions?select=sql,version'        || '&package_name=eq.supabase-dbdev'        || '&order=version.desc'        || '&limit=1',        array[            ('apiKey', 'eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJzdXBhYmFzZSIsInJlZiI6InhtdXB0cHBsZnZpaWZyYndtbXR2Iiwicm9sZSI6ImFub24iLCJpYXQiOjE2ODAxMDczNzIsImV4cCI6MTk5NTY4MzM3Mn0.z2CN0mvO2No8wSi46Gw59DFGCTJrzM0AQKsu_5k134s')::http_header        ],        null,        null    )) x,lateral (    select        ((row_to_json(x) -> 'content') #>> '{}')::json -> 0) resp(contents);create extension "supabase-dbdev";select dbdev.install('supabase-dbdev');-- 删除并重新创建扩展以确保干净安装drop extension if exists "supabase-dbdev";create extension "supabase-dbdev";

1
2
select dbdev.install('basejump-supabase_test_helpers');create extension if not exists "basejump-supabase_test_helpers" version '0.0.6';

1
2
3
4
5
6
7
8
begin;select plan(1);-- 验证public模式中所有表都启用了RLSselect tests.rls_enabled('public');select * from finish();rollback;

1
supabase test new 000-setup-tests-hooks

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
-- 安装测试工具-- 安装用于测试的 pgtap 扩展create extension if not exists pgtap with schema extensions;/*------------------------- 安装 dbdev --------------------------需要：  - pg_tle: https://github.com/aws/pg_tle  - pgsql-http: https://github.com/pramsey/pgsql-http*/create extension if not exists http with schema extensions;create extension if not exists pg_tle;drop extension if exists "supabase-dbdev";select pgtle.uninstall_extension_if_exists('supabase-dbdev');select    pgtle.install_extension(        'supabase-dbdev',        resp.contents ->> 'version',        'PostgreSQL 包管理器',        resp.contents ->> 'sql'    )from http(    (        'GET',        'https://api.database.dev/rest/v1/'        || 'package_versions?select=sql,version'        || '&package_name=eq.supabase-dbdev'        || '&order=version.desc'        || '&limit=1',        array[            ('apiKey', 'eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJzdXBhYmFzZSIsInJlZiI6InhtdXB0cHBsZnZpaWZyYndtbXR2Iiwicm9sZSI6ImFub24iLCJpYXQiOjE2ODAxMDczNzIsImV4cCI6MTk5NTY4MzM3Mn0.z2CN0mvO2No8wSi46Gw59DFGCTJrzM0AQKsu_5k134s')::http_header        ],        null,        null    )) x,lateral (    select        ((row_to_json(x) -> 'content') #>> '{}')::json -> 0) resp(contents);create extension "supabase-dbdev";select dbdev.install('supabase-dbdev');drop extension if exists "supabase-dbdev";create extension "supabase-dbdev";-- 安装测试辅助工具select dbdev.install('basejump-supabase_test_helpers');create extension if not exists "basejump-supabase_test_helpers" version '0.0.6';-- 通过无操作测试验证设置begin;select plan(1);select ok(true, '预测试钩子执行成功');select * from finish();rollback;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
begin;-- 假设存在000-setup-tests-hooks.sql文件来使用测试辅助工具select plan(4);-- 设置测试数据-- 创建测试用的Supabase用户select tests.create_supabase_user('user1@test.com');select tests.create_supabase_user('user2@test.com');-- 创建测试数据insert into public.todos (task, user_id) values  ('用户1的任务1', tests.get_supabase_uid('user1@test.com')),  ('用户1的任务2', tests.get_supabase_uid('user1@test.com')),  ('用户2的任务1', tests.get_supabase_uid('user2@test.com'));-- 以用户1身份测试select tests.authenticate_as('user1@test.com');-- 测试1：用户1应该只能看到自己的待办事项select results_eq(  'select count(*) from todos',  ARRAY[2::bigint],  '用户1应该只能看到自己的2个待办事项');-- 测试2：用户1可以创建自己的待办事项select lives_ok(  $$insert into todos (task, user_id) values ('新任务', tests.get_supabase_uid('user1@test.com'))$$,  '用户1可以创建自己的待办事项');-- 以用户2身份测试select tests.authenticate_as('user2@test.com');-- 测试3：用户2应该只能看到自己的待办事项select results_eq(  'select count(*) from todos',  ARRAY[1::bigint],  '用户2应该只能看到自己的1个待办事项');-- 测试4：用户2不能修改用户1的待办事项SELECT results_ne(    $$ update todos set task = '被黑了！' where user_id = tests.get_supabase_uid('user1@test.com') returning 1 $$,    $$ values(1) $$,    '用户2不能修改用户1的待办事项');select * from finish();rollback;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
create table public.profiles (  id uuid references auth.users(id) primary key,  username text unique not null,  full_name text,  bio text,  created_at timestamptz default now(),  updated_at timestamptz default now());create table public.organizations (  id bigint primary key generated always as identity,  name text not null,  slug text unique not null,  plan_type text not null check (plan_type in ('free', 'pro', 'enterprise')),  max_posts int not null default 5,  created_at timestamptz default now());create table public.org_members (  org_id bigint references public.organizations(id) on delete cascade,  user_id uuid references auth.users(id) on delete cascade,  role text not null check (role in ('owner', 'admin', 'editor', 'viewer')),  created_at timestamptz default now(),  primary key (org_id, user_id));create table public.posts (  id bigint primary key generated always as identity,  title text not null,  content text not null,  author_id uuid references public.profiles(id) not null,  org_id bigint references public.organizations(id),  status text not null check (status in ('draft', 'published', 'archived')),  is_premium boolean default false,  scheduled_for timestamptz,  category text,  view_count int default 0,  published_at timestamptz,  created_at timestamptz default now(),  updated_at timestamptz default now());create table public.comments (  id bigint primary key generated always as identity,  post_id bigint references public.posts(id) on delete cascade,  author_id uuid references public.profiles(id),  content text not null,  is_deleted boolean default false,  created_at timestamptz default now(),  updated_at timestamptz default now());

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
-- 创建一个私有模式来存储所有安全定义函数工具-- 这些函数不应出现在API暴露的模式中create schema if not exists private;-- 角色检查辅助函数create or replace function private.get_user_org_role(org_id bigint, user_id uuid)returns textset search_path = ''as $$  select role from public.org_members  where org_id = $1 and user_id = $2;-- 注意使用security definer避免RLS检查递归问题-- 参见：https://supabase.com/docs/guides/database/postgres/row-level-security#use-security-definer-functions$$ language sql security definer;-- 检查组织是否低于最大帖子限制的辅助工具create or replace function private.can_add_post(org_id bigint)returns booleanset search_path = ''as $$  select (select count(*)          from public.posts p          where p.org_id = $1) < o.max_posts  from public.organizations o  where o.id = $1$$ language sql security definer;-- 为所有表启用RLSalter table public.profiles enable row level security;alter table public.organizations enable row level security;alter table public.org_members enable row level security;alter table public.posts enable row level security;alter table public.comments enable row level security;-- 用户资料策略create policy "公开资料所有人可见"  on public.profiles for select using (true);create policy "用户可以创建自己的资料"  on public.profiles for insert with check ((select auth.uid()) = id);create policy "用户可以更新自己的资料"  on public.profiles for update using ((select auth.uid()) = id)  with check ((select auth.uid()) = id);-- 组织策略create policy "组织信息对所有人可见"  on public.organizations for select using (true);create policy "组织管理仅限于所有者"  on public.organizations for all using (    private.get_user_org_role(id, (select auth.uid())) = 'owner'  );-- 组织成员策略create policy "成员信息对组织成员可见"  on public.org_members for select using (    private.get_user_org_role(org_id, (select auth.uid())) is not null  );create policy "成员管理仅限于管理员和所有者"  on public.org_members for all using (    private.get_user_org_role(org_id, (select auth.uid())) in ('owner', 'admin')  );-- 帖子策略create policy "复杂的帖子可见性规则"  on public.posts for select using (    -- 已发布的非付费帖子对所有人可见    (status = 'published' and not is_premium)    or    -- 付费帖子仅对组织成员可见    (status = 'published' and is_premium and    private.get_user_org_role(org_id, (select auth.uid())) is not null)    or    -- 所有帖子对编辑及以上角色可见    private.get_user_org_role(org_id, (select auth.uid())) in ('owner', 'admin', 'editor')  );create policy "帖子创建规则"  on public.posts for insert with check (    -- 必须是具有适当角色的组织成员    private.get_user_org_role(org_id, (select auth.uid())) in ('owner', 'admin', 'editor')    and    -- 检查免费计划的组织帖子限制    (      (select o.plan_type != 'free'      from organizations o      where o.id = org_id)      or      (select private.can_add_post(org_id))    )  );create policy "帖子更新规则"  on public.posts for update using (    exists (      select 1      where        -- 编辑可以更新未发布的帖子        (private.get_user_org_role(org_id, (select auth.uid())) = 'editor' and status != 'published')        or        -- 管理员和所有者可以更新任何帖子        private.get_user_org_role(org_id, (select auth.uid())) in ('owner', 'admin')    )  );-- 评论策略create policy "已发布帖子的评论对所有人可见"  on public.comments for select using (    exists (      select 1 from public.posts      where id = post_id      and status = 'published'    )    and not is_deleted  );create policy "认证用户可以创建评论"  on public.comments for insert with check ((select auth.uid()) = author_id);create policy "用户可以更新自己的评论"  on public.comments for update using (author_id = (select auth.uid()));

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
-- 假设我们已经有了可以使用的测试辅助文件：000-setup-tests-hooks.sqlbegin;-- 声明测试总数select plan(10);-- 创建测试用户select tests.create_supabase_user('org_owner', 'owner@test.com');select tests.create_supabase_user('org_admin', 'admin@test.com');select tests.create_supabase_user('org_editor', 'editor@test.com');select tests.create_supabase_user('premium_user', 'premium@test.com');select tests.create_supabase_user('free_user', 'free@test.com');select tests.create_supabase_user('scheduler', 'scheduler@test.com');select tests.create_supabase_user('free_author', 'free_author@test.com');-- 为测试用户创建个人资料insert into profiles (id, username, full_name)values  (tests.get_supabase_uid('org_owner'), 'org_owner', '组织所有者'),  (tests.get_supabase_uid('org_admin'), 'org_admin', '组织管理员'),  (tests.get_supabase_uid('org_editor'), 'org_editor', '组织编辑'),  (tests.get_supabase_uid('premium_user'), 'premium_user', '高级用户'),  (tests.get_supabase_uid('free_user'), 'free_user', '免费用户'),  (tests.get_supabase_uid('scheduler'), 'scheduler', '调度用户'),  (tests.get_supabase_uid('free_author'), 'free_author', '免费作者');-- 首先以服务角色身份认证，绕过RLS进行初始设置select tests.authenticate_as_service_role();-- 创建测试组织并设置数据with new_org as (  insert into organizations (name, slug, plan_type, max_posts)  values    ('测试组织', 'test-org', 'pro', 100),    ('高级组织', 'premium-org', 'enterprise', 1000),    ('调度组织', 'schedule-org', 'pro', 100),    ('免费组织', 'free-org', 'free', 2)  returning id, slug),-- 设置成员和文章member_setup as (  insert into org_members (org_id, user_id, role)  select    org.id,    user_id,    role  from new_org org cross join (    values      (tests.get_supabase_uid('org_owner'), 'owner'),      (tests.get_supabase_uid('org_admin'), 'admin'),      (tests.get_supabase_uid('org_editor'), 'editor'),      (tests.get_supabase_uid('premium_user'), 'viewer'),      (tests.get_supabase_uid('scheduler'), 'editor'),      (tests.get_supabase_uid('free_author'), 'editor')  ) as members(user_id, role)  where org.slug = 'test-org'     or (org.slug = 'premium-org' and role = 'viewer')     or (org.slug = 'schedule-org' and role = 'editor')     or (org.slug = 'free-org' and role = 'editor'))-- 设置初始文章insert into posts (title, content, org_id, author_id, status, is_premium, scheduled_for)select  title,  content,  org.id,  author_id,  status,  is_premium,  scheduled_forfrom new_org org cross join (  values    ('高级文章', '高级内容', tests.get_supabase_uid('premium_user'), 'published', true, null),    ('免费文章', '免费内容', tests.get_supabase_uid('premium_user'), 'published', false, null),    ('未来文章', '未来内容', tests.get_supabase_uid('scheduler'), 'published', false, '2024-01-02 12:00:00+00'::timestamptz)) as posts(title, content, author_id, status, is_premium, scheduled_for)where org.slug in ('premium-org', 'schedule-org');-- 测试所有者权限select tests.authenticate_as('org_owner');select lives_ok(  $$    update organizations    set name = '更新后的组织'    where id = (select id from organizations limit 1)  $$,  '所有者可以更新组织信息');-- 测试管理员权限select tests.authenticate_as('org_admin');select results_eq(    $$select count(*) from org_members$$,    ARRAY[6::bigint],    '管理员可以查看所有成员');-- 测试编辑限制select tests.authenticate_as('org_editor');select throws_ok(  $$    insert into org_members (org_id, user_id, role)    values (      (select id from organizations limit 1),      (select tests.get_supabase_uid('org_editor')),      'viewer'    )  $$,  '42501',  'new row violates row-level security policy for table "org_members"',  '编辑不能管理成员');-- 高级内容访问测试select tests.authenticate_as('premium_user');select results_eq(    $$select count(*) from posts where org_id = (select id from organizations where slug = 'premium-org')$$,    ARRAY[3::bigint],    '高级用户可以看到所有文章');select tests.clear_authentication();select results_eq(    $$select count(*) from posts where org_id = (select id from organizations where slug = 'premium-org')$$,    ARRAY[2::bigint],    '匿名用户只能看到免费文章');-- 基于时间的发布测试select tests.authenticate_as('scheduler');select tests.freeze_time('2024-01-01 12:00:00+00'::timestamptz);select results_eq(    $$select count(*) from posts where scheduled_for > now() and org_id = (select id from organizations where slug = 'schedule-org')$$,    ARRAY[1::bigint],    '可以看到预定发布的文章');select tests.freeze_time('2024-01-02 13:00:00+00'::timestamptz);select results_eq(    $$select count(*) from posts where scheduled_for < now() and org_id = (select id from organizations where slug = 'schedule-org')$$,    ARRAY[1::bigint],    '可以在预定时间后看到文章');select tests.unfreeze_time();-- 计划限制测试select tests.authenticate_as('free_author');select lives_ok(  $$    insert into posts (title, content, org_id, author_id, status)    select '文章1', '内容1', id, auth.uid(), 'draft'    from organizations where slug = 'free-org' limit 1  $$,  '第一篇文章创建成功');select lives_ok(  $$    insert into posts (title, content, org_id, author_id, status)    select '文章2', '内容2', id, auth.uid(), 'draft'    from organizations where slug = 'free-org' limit 1  $$,  '第二篇文章创建成功');select throws_ok(  $$    insert into posts (title, content, org_id, author_id, status)    select '文章3', '内容3', id, auth.uid(), 'draft'    from organizations where slug = 'free-org' limit 1  $$,  '42501',  'new row violates row-level security policy for table "posts"',  '不能超过免费计划文章限制');select * from finish();rollback;