Skip to content

Commit a71e935

Browse files
WcaleNieWolnyWcaleNieWolny
committed
feat: reject_access_due_to_2fa_for_app
1 parent 949e24c commit a71e935

2 files changed

Lines changed: 343 additions & 0 deletions

File tree

‎supabase/migrations/20251228150000_reject_access_due_to_2fa_for_app.sql‎

Lines changed: 69 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,69 @@
1+
-- ============================================================================
2+
-- Public function to check if access should be rejected due to 2FA enforcement
3+
-- for a given app. This is intended for CLI and frontend use.
4+
-- ============================================================================
5+
6+
-- Function to check if access should be rejected due to 2FA enforcement for an app
7+
-- Takes app_id, gets the owner_org, gets current user identity, and checks 2FA compliance
8+
-- Returns true if access should be REJECTED, false if access should be ALLOWED
9+
CREATE OR REPLACE FUNCTION "public"."reject_access_due_to_2fa_for_app"("app_id" character varying)
10+
RETURNS boolean
11+
LANGUAGE "plpgsql" SECURITY DEFINER
12+
SET "search_path" TO ''
13+
AS $$
14+
DECLARE
15+
v_owner_org uuid;
16+
v_user_id uuid;
17+
v_org_enforcing_2fa boolean;
18+
BEGIN
19+
-- Get the owner organization for this app
20+
SELECT owner_org INTO v_owner_org
21+
FROM public.apps
22+
WHERE public.apps.app_id = reject_access_due_to_2fa_for_app.app_id;
23+
24+
-- If app not found or no owner_org, reject access
25+
IF v_owner_org IS NULL THEN
26+
RETURN true;
27+
END IF;
28+
29+
-- Get the current user identity (works for both JWT auth and API key)
30+
-- Using get_identity with key_mode array to support CLI API key authentication
31+
v_user_id := public.get_identity('{read,upload,write,all}'::public.key_mode[]);
32+
33+
-- If no user identity found, reject access
34+
IF v_user_id IS NULL THEN
35+
RETURN true;
36+
END IF;
37+
38+
-- Check if org has 2FA enforcement enabled
39+
SELECT enforcing_2fa INTO v_org_enforcing_2fa
40+
FROM public.orgs
41+
WHERE public.orgs.id = v_owner_org;
42+
43+
-- If org not found, reject access
44+
IF v_org_enforcing_2fa IS NULL THEN
45+
RETURN true;
46+
END IF;
47+
48+
-- If org does not enforce 2FA, allow access
49+
IF v_org_enforcing_2fa = false THEN
50+
RETURN false;
51+
END IF;
52+
53+
-- If org enforces 2FA and user doesn't have 2FA enabled, reject access
54+
-- Use has_2fa_enabled(user_id) to check the specific user (works for API key auth)
55+
IF v_org_enforcing_2fa = true AND NOT public.has_2fa_enabled(v_user_id) THEN
56+
RETURN true;
57+
END IF;
58+
59+
-- Otherwise, allow access
60+
RETURN false;
61+
END;
62+
$$;
63+
64+
ALTER FUNCTION "public"."reject_access_due_to_2fa_for_app"("app_id" character varying) OWNER TO "postgres";
65+
66+
-- Grant permissions - accessible to authenticated users (CLI and frontend)
67+
GRANT EXECUTE ON FUNCTION "public"."reject_access_due_to_2fa_for_app"("app_id" character varying) TO "authenticated";
68+
GRANT EXECUTE ON FUNCTION "public"."reject_access_due_to_2fa_for_app"("app_id" character varying) TO "service_role";
69+

‎supabase/tests/41_test_reject_access_due_to_2fa_for_app.sql‎

Lines changed: 274 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,274 @@
1+
-- Tests for reject_access_due_to_2fa_for_app function
2+
-- This function is PUBLIC and can be called by authenticated users and via API keys
3+
BEGIN;
4+
5+
SELECT plan(13);
6+
7+
-- Create test users
8+
DO $$
9+
BEGIN
10+
PERFORM tests.create_supabase_user('test_2fa_user_app', '2fa_app@test.com');
11+
PERFORM tests.create_supabase_user('test_no_2fa_user_app', 'no2fa_app@test.com');
12+
END $$;
13+
14+
-- Create entries in public.users for the test members
15+
INSERT INTO public.users (id, email, created_at, updated_at)
16+
VALUES
17+
(
18+
tests.get_supabase_uid('test_2fa_user_app'),
19+
'2fa_app@test.com',
20+
now(),
21+
now()
22+
),
23+
(
24+
tests.get_supabase_uid('test_no_2fa_user_app'),
25+
'no2fa_app@test.com',
26+
now(),
27+
now()
28+
)
29+
ON CONFLICT (id) DO NOTHING;
30+
31+
-- Create test orgs and apps
32+
DO $$
33+
DECLARE
34+
org_with_2fa_enforcement_id uuid;
35+
org_without_2fa_enforcement_id uuid;
36+
test_2fa_user_id uuid;
37+
test_no_2fa_user_id uuid;
38+
BEGIN
39+
org_with_2fa_enforcement_id := extensions.uuid_generate_v4();
40+
org_without_2fa_enforcement_id := extensions.uuid_generate_v4();
41+
test_2fa_user_id := tests.get_supabase_uid('test_2fa_user_app');
42+
test_no_2fa_user_id := tests.get_supabase_uid('test_no_2fa_user_app');
43+
44+
-- Create org WITH 2FA enforcement
45+
INSERT INTO public.orgs (id, created_by, name, management_email, enforcing_2fa)
46+
VALUES (org_with_2fa_enforcement_id, test_2fa_user_id, '2FA Enforced Org App', '2fa_app@org.com', true);
47+
48+
-- Create org WITHOUT 2FA enforcement
49+
INSERT INTO public.orgs (id, created_by, name, management_email, enforcing_2fa)
50+
VALUES (org_without_2fa_enforcement_id, test_2fa_user_id, 'No 2FA Org App', 'no2fa_app@org.com', false);
51+
52+
-- Add members to org WITH 2FA enforcement
53+
INSERT INTO public.org_users (org_id, user_id, user_right)
54+
VALUES
55+
(org_with_2fa_enforcement_id, test_2fa_user_id, 'admin'::public.user_min_right),
56+
(org_with_2fa_enforcement_id, test_no_2fa_user_id, 'read'::public.user_min_right);
57+
58+
-- Add members to org WITHOUT 2FA enforcement
59+
INSERT INTO public.org_users (org_id, user_id, user_right)
60+
VALUES
61+
(org_without_2fa_enforcement_id, test_2fa_user_id, 'admin'::public.user_min_right),
62+
(org_without_2fa_enforcement_id, test_no_2fa_user_id, 'read'::public.user_min_right);
63+
64+
-- Create app in org WITH 2FA enforcement
65+
INSERT INTO public.apps (app_id, owner_org, name, icon_url)
66+
VALUES ('com.test.2fa.enforced.app', org_with_2fa_enforcement_id, 'Test 2FA Enforced App', 'https://example.com/icon.png');
67+
68+
-- Create app in org WITHOUT 2FA enforcement
69+
INSERT INTO public.apps (app_id, owner_org, name, icon_url)
70+
VALUES ('com.test.no2fa.app', org_without_2fa_enforcement_id, 'Test No 2FA App', 'https://example.com/icon.png');
71+
72+
-- Store org IDs and app IDs for later use
73+
PERFORM set_config('test.org_with_2fa_app', org_with_2fa_enforcement_id::text, false);
74+
PERFORM set_config('test.org_without_2fa_app', org_without_2fa_enforcement_id::text, false);
75+
PERFORM set_config('test.app_with_2fa', 'com.test.2fa.enforced.app', false);
76+
PERFORM set_config('test.app_without_2fa', 'com.test.no2fa.app', false);
77+
78+
-- Create API key for test_2fa_user_app
79+
INSERT INTO public.apikeys (user_id, key, mode, name)
80+
VALUES (
81+
test_2fa_user_id,
82+
'test-2fa-apikey-for-app',
83+
'all'::public.key_mode,
84+
'Test 2FA API Key'
85+
);
86+
87+
-- Create API key for test_no_2fa_user_app
88+
INSERT INTO public.apikeys (user_id, key, mode, name)
89+
VALUES (
90+
test_no_2fa_user_id,
91+
'test-no2fa-apikey-for-app',
92+
'all'::public.key_mode,
93+
'Test No 2FA API Key'
94+
);
95+
END $$;
96+
97+
-- Set up MFA factors
98+
DO $$
99+
DECLARE
100+
test_2fa_user_id uuid;
101+
BEGIN
102+
test_2fa_user_id := tests.get_supabase_uid('test_2fa_user_app');
103+
104+
-- Insert verified MFA factor for test_2fa_user_app
105+
INSERT INTO auth.mfa_factors (id, user_id, friendly_name, factor_type, status, created_at, updated_at)
106+
VALUES (
107+
extensions.uuid_generate_v4(),
108+
test_2fa_user_id,
109+
'Test TOTP App',
110+
'totp'::auth.factor_type,
111+
'verified'::auth.factor_status,
112+
now(),
113+
now()
114+
);
115+
END $$;
116+
117+
-- ============================================================================
118+
-- Tests for reject_access_due_to_2fa_for_app function
119+
-- ============================================================================
120+
121+
-- Test 1: User WITH 2FA accessing app in org WITH 2FA enforcement returns false (no rejection)
122+
SELECT tests.authenticate_as('test_2fa_user_app');
123+
SELECT
124+
is(
125+
reject_access_due_to_2fa_for_app(current_setting('test.app_with_2fa')),
126+
false,
127+
'reject_access_due_to_2fa_for_app test - user with 2FA accessing app in org with 2FA enforcement returns false'
128+
);
129+
SELECT tests.clear_authentication();
130+
131+
-- Test 2: User WITHOUT 2FA accessing app in org WITH 2FA enforcement returns true (rejection)
132+
SELECT tests.authenticate_as('test_no_2fa_user_app');
133+
SELECT
134+
is(
135+
reject_access_due_to_2fa_for_app(current_setting('test.app_with_2fa')),
136+
true,
137+
'reject_access_due_to_2fa_for_app test - user without 2FA accessing app in org with 2FA enforcement returns true'
138+
);
139+
SELECT tests.clear_authentication();
140+
141+
-- Test 3: User WITH 2FA accessing app in org WITHOUT 2FA enforcement returns false (no rejection)
142+
SELECT tests.authenticate_as('test_2fa_user_app');
143+
SELECT
144+
is(
145+
reject_access_due_to_2fa_for_app(
146+
current_setting('test.app_without_2fa')
147+
),
148+
false,
149+
'reject_access_due_to_2fa_for_app test - user with 2FA accessing app in org without 2FA enforcement returns false'
150+
);
151+
SELECT tests.clear_authentication();
152+
153+
-- Test 4: User WITHOUT 2FA accessing app in org WITHOUT 2FA enforcement returns false (no rejection)
154+
SELECT tests.authenticate_as('test_no_2fa_user_app');
155+
SELECT
156+
is(
157+
reject_access_due_to_2fa_for_app(
158+
current_setting('test.app_without_2fa')
159+
),
160+
false,
161+
'reject_access_due_to_2fa_for_app test - user without 2FA accessing app in org without 2FA enforcement returns false'
162+
);
163+
SELECT tests.clear_authentication();
164+
165+
-- Test 5: Non-existent app returns true (rejection)
166+
SELECT tests.authenticate_as('test_2fa_user_app');
167+
SELECT
168+
is(
169+
reject_access_due_to_2fa_for_app('com.nonexistent.app.12345'),
170+
true,
171+
'reject_access_due_to_2fa_for_app test - non-existent app returns true'
172+
);
173+
SELECT tests.clear_authentication();
174+
175+
-- Test 6: User WITH 2FA using API key accessing app in org WITH 2FA enforcement returns false
176+
DO $$
177+
BEGIN
178+
PERFORM set_config('request.headers', '{"capgkey": "test-2fa-apikey-for-app"}', true);
179+
END $$;
180+
SELECT
181+
is(
182+
reject_access_due_to_2fa_for_app(current_setting('test.app_with_2fa')),
183+
false,
184+
'reject_access_due_to_2fa_for_app test - user with 2FA via API key accessing app in org with 2FA enforcement returns false'
185+
);
186+
DO $$
187+
BEGIN
188+
PERFORM set_config('request.headers', '{}', true);
189+
END $$;
190+
191+
-- Test 7: User WITHOUT 2FA using API key accessing app in org WITH 2FA enforcement returns true
192+
DO $$
193+
BEGIN
194+
PERFORM set_config('request.headers', '{"capgkey": "test-no2fa-apikey-for-app"}', true);
195+
END $$;
196+
SELECT
197+
is(
198+
reject_access_due_to_2fa_for_app(current_setting('test.app_with_2fa')),
199+
true,
200+
'reject_access_due_to_2fa_for_app test - user without 2FA via API key accessing app in org with 2FA enforcement returns true'
201+
);
202+
DO $$
203+
BEGIN
204+
PERFORM set_config('request.headers', '{}', true);
205+
END $$;
206+
207+
-- Test 8: User WITHOUT 2FA using API key accessing app in org WITHOUT 2FA enforcement returns false
208+
DO $$
209+
BEGIN
210+
PERFORM set_config('request.headers', '{"capgkey": "test-no2fa-apikey-for-app"}', true);
211+
END $$;
212+
SELECT
213+
is(
214+
reject_access_due_to_2fa_for_app(
215+
current_setting('test.app_without_2fa')
216+
),
217+
false,
218+
'reject_access_due_to_2fa_for_app test - user without 2FA via API key accessing app in org without 2FA enforcement returns false'
219+
);
220+
DO $$
221+
BEGIN
222+
PERFORM set_config('request.headers', '{}', true);
223+
END $$;
224+
225+
-- Test 9: Anonymous user (no auth) returns true (rejection)
226+
SELECT tests.clear_authentication();
227+
SELECT
228+
is(
229+
reject_access_due_to_2fa_for_app(current_setting('test.app_with_2fa')),
230+
true,
231+
'reject_access_due_to_2fa_for_app test - anonymous user returns true'
232+
);
233+
234+
-- Test 10: Verify function exists
235+
SELECT
236+
ok(
237+
pg_get_functiondef(
238+
'reject_access_due_to_2fa_for_app(character varying)'::regprocedure
239+
) IS NOT null,
240+
'reject_access_due_to_2fa_for_app test - function exists'
241+
);
242+
243+
-- Test 11: Service role CAN call the function
244+
SELECT tests.authenticate_as_service_role();
245+
SELECT
246+
ok(
247+
reject_access_due_to_2fa_for_app(
248+
current_setting('test.app_with_2fa')
249+
) IS NOT null,
250+
'reject_access_due_to_2fa_for_app test - service_role can call function'
251+
);
252+
SELECT tests.clear_authentication();
253+
254+
-- Test 12: User WITH 2FA accessing app multiple times (should always return false)
255+
SELECT tests.authenticate_as('test_2fa_user_app');
256+
SELECT
257+
is(
258+
reject_access_due_to_2fa_for_app(current_setting('test.app_with_2fa')),
259+
false,
260+
'reject_access_due_to_2fa_for_app test - user with 2FA accessing app returns false (first call)'
261+
);
262+
SELECT
263+
is(
264+
reject_access_due_to_2fa_for_app(current_setting('test.app_with_2fa')),
265+
false,
266+
'reject_access_due_to_2fa_for_app test - user with 2FA accessing app returns false (second call)'
267+
);
268+
SELECT tests.clear_authentication();
269+
270+
SELECT *
271+
FROM
272+
finish();
273+
274+
ROLLBACK;

0 commit comments

Comments
 (0)