Skip to content

Commit d640db4

Browse files
riderxclaude
riderx
and
claude
committed
fix(audit-logs): capture user_id for API key authenticated operations
The audit_log_trigger was calling get_identity() without parameters, which only checks auth.uid() (JWT authentication). This caused API key authenticated users (CLI/API) to have NULL user_id in audit logs. Fixed by passing key_mode parameter to get_identity() to also check for API key authentication. Added SQL test (40_test_audit_log_apikey.sql) and end-to-end tests verifying that INSERT/UPDATE/DELETE operations on app_versions via API key are properly logged with the correct user_id. 🤖 Generated with [Claude Code](https://claude.com/claude-code) Co-Authored-By: Claude Haiku 4.5 <noreply@anthropic.com>
1 parent 97368dd commit d640db4

3 files changed

Lines changed: 496 additions & 1 deletion

File tree

‎supabase/migrations/20251228063320_fix_audit_log_apikey.sql‎

Lines changed: 102 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,102 @@
1+
-- Fix audit_log_trigger to properly identify users authenticated via API keys
2+
-- Previously, the trigger called get_identity() without parameters, which only checks auth.uid()
3+
-- This meant API key users (CLI, API) were not logged because get_identity() returned NULL
4+
-- Now we call get_identity with key_mode parameter to also check for API key authentication
5+
6+
CREATE OR REPLACE FUNCTION "public"."audit_log_trigger"()
7+
RETURNS TRIGGER
8+
LANGUAGE plpgsql
9+
SECURITY DEFINER
10+
SET search_path = ''
11+
AS $$
12+
DECLARE
13+
v_old_record JSONB;
14+
v_new_record JSONB;
15+
v_changed_fields TEXT[];
16+
v_org_id UUID;
17+
v_record_id TEXT;
18+
v_user_id UUID;
19+
v_key TEXT;
20+
v_org_exists BOOLEAN;
21+
BEGIN
22+
-- Skip audit logging for org DELETE operations
23+
-- When an org is deleted, we can't insert into audit_logs because the org_id
24+
-- foreign key would reference a non-existent org
25+
IF TG_TABLE_NAME = 'orgs' AND TG_OP = 'DELETE' THEN
26+
RETURN OLD;
27+
END IF;
28+
29+
-- Get current user from auth context or API key
30+
-- Uses get_identity() WITH key_mode parameter to support both JWT auth and API key authentication
31+
-- This is the fix: previously called get_identity() without parameters which only checked auth.uid()
32+
v_user_id := public.get_identity('{read,upload,write,all}'::public.key_mode[]);
33+
34+
-- Skip audit logging if no user is identified
35+
-- We only want to log actions performed by authenticated users
36+
IF v_user_id IS NULL THEN
37+
RETURN COALESCE(NEW, OLD);
38+
END IF;
39+
40+
-- Convert records to JSONB based on operation type
41+
IF TG_OP = 'DELETE' THEN
42+
v_old_record := to_jsonb(OLD);
43+
v_new_record := NULL;
44+
ELSIF TG_OP = 'INSERT' THEN
45+
v_old_record := NULL;
46+
v_new_record := to_jsonb(NEW);
47+
ELSE -- UPDATE
48+
v_old_record := to_jsonb(OLD);
49+
v_new_record := to_jsonb(NEW);
50+
51+
-- Calculate changed fields by comparing old and new values
52+
FOR v_key IN SELECT jsonb_object_keys(v_new_record)
53+
LOOP
54+
IF v_old_record->v_key IS DISTINCT FROM v_new_record->v_key THEN
55+
v_changed_fields := array_append(v_changed_fields, v_key);
56+
END IF;
57+
END LOOP;
58+
END IF;
59+
60+
-- Get org_id and record_id based on table being modified
61+
CASE TG_TABLE_NAME
62+
WHEN 'orgs' THEN
63+
v_org_id := COALESCE(NEW.id, OLD.id);
64+
v_record_id := COALESCE(NEW.id, OLD.id)::TEXT;
65+
WHEN 'apps' THEN
66+
v_org_id := COALESCE(NEW.owner_org, OLD.owner_org);
67+
v_record_id := COALESCE(NEW.app_id, OLD.app_id)::TEXT;
68+
WHEN 'channels' THEN
69+
v_org_id := COALESCE(NEW.owner_org, OLD.owner_org);
70+
v_record_id := COALESCE(NEW.id, OLD.id)::TEXT;
71+
WHEN 'app_versions' THEN
72+
v_org_id := COALESCE(NEW.owner_org, OLD.owner_org);
73+
v_record_id := COALESCE(NEW.id, OLD.id)::TEXT;
74+
WHEN 'org_users' THEN
75+
v_org_id := COALESCE(NEW.org_id, OLD.org_id);
76+
v_record_id := COALESCE(NEW.id, OLD.id)::TEXT;
77+
ELSE
78+
-- Fallback for any other table (shouldn't happen with current triggers)
79+
v_org_id := NULL;
80+
v_record_id := NULL;
81+
END CASE;
82+
83+
-- Only insert if we have a valid org_id and the org still exists
84+
-- This handles edge cases where related tables are deleted after the org
85+
IF v_org_id IS NOT NULL THEN
86+
-- Check if the org still exists (important for DELETE operations on child tables)
87+
SELECT EXISTS(SELECT 1 FROM public.orgs WHERE id = v_org_id) INTO v_org_exists;
88+
89+
IF v_org_exists THEN
90+
INSERT INTO "public"."audit_logs" (
91+
table_name, record_id, operation, user_id, org_id,
92+
old_record, new_record, changed_fields
93+
) VALUES (
94+
TG_TABLE_NAME, v_record_id, TG_OP, v_user_id, v_org_id,
95+
v_old_record, v_new_record, v_changed_fields
96+
);
97+
END IF;
98+
END IF;
99+
100+
RETURN COALESCE(NEW, OLD);
101+
END;
102+
$$;

‎supabase/tests/40_test_audit_log_apikey.sql‎

Lines changed: 210 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,210 @@
1+
-- Test that audit logs are created correctly when using API key authentication
2+
-- This verifies the fix for the issue where CLI/API users were not logged in audit_logs
3+
-- because get_identity() was called without key_mode parameter
4+
BEGIN;
5+
6+
-- Use existing seed identities:
7+
-- API key: ae6e7458-c46d-4c00-aa3b-153b0b8520ea (mode: all, user: 6aa76066-55ef-4238-ade6-0b32334a4097)
8+
-- Org: 046a36ac-e03c-4590-9257-bd6c9dba9ee8
9+
-- App: com.demo.app
10+
11+
SELECT plan(6);
12+
13+
-- Test 1: Verify get_identity returns user_id when API key header is set
14+
DO $$
15+
BEGIN
16+
PERFORM set_config('request.headers', '{"capgkey": "ae6e7458-c46d-4c00-aa3b-153b0b8520ea"}', true);
17+
END $$;
18+
19+
SELECT
20+
is(
21+
public.get_identity('{read,upload,write,all}'::public.key_mode[]),
22+
'6aa76066-55ef-4238-ade6-0b32334a4097'::uuid,
23+
'get_identity with key_mode returns API key user_id'
24+
);
25+
26+
-- Test 2: Verify get_identity WITHOUT parameters returns NULL for API key (the old broken behavior)
27+
-- Note: This shows the original bug - parameterless get_identity doesn't check API keys
28+
SELECT
29+
is(
30+
public.get_identity(),
31+
NULL,
32+
'get_identity without key_mode returns NULL for API key (original bug)'
33+
);
34+
35+
-- Test 3: Insert app_version with API key context and verify audit log is created
36+
DO $$
37+
DECLARE
38+
v_version_id bigint;
39+
v_audit_count int;
40+
BEGIN
41+
-- Set API key context
42+
PERFORM set_config('request.headers', '{"capgkey": "ae6e7458-c46d-4c00-aa3b-153b0b8520ea"}', true);
43+
44+
-- Insert a new app_version
45+
INSERT INTO public.app_versions (app_id, name, owner_org, user_id, storage_provider)
46+
VALUES ('com.demo.app', '99.0.0-test-audit', '046a36ac-e03c-4590-9257-bd6c9dba9ee8', '6aa76066-55ef-4238-ade6-0b32334a4097', 'r2')
47+
RETURNING id INTO v_version_id;
48+
49+
-- Check that an audit log was created
50+
SELECT COUNT(*) INTO v_audit_count
51+
FROM public.audit_logs
52+
WHERE table_name = 'app_versions'
53+
AND record_id = v_version_id::text
54+
AND operation = 'INSERT'
55+
AND user_id = '6aa76066-55ef-4238-ade6-0b32334a4097';
56+
57+
IF v_audit_count = 0 THEN
58+
RAISE EXCEPTION 'No audit log created for app_version INSERT with API key';
59+
END IF;
60+
61+
RAISE NOTICE 'Audit log created for app_version INSERT (version_id: %)', v_version_id;
62+
END $$;
63+
64+
SELECT ok(true, 'app_version INSERT with API key creates audit log');
65+
66+
-- Test 4: Update app_version with API key context and verify audit log is created
67+
DO $$
68+
DECLARE
69+
v_audit_count int;
70+
BEGIN
71+
-- Set API key context
72+
PERFORM set_config('request.headers', '{"capgkey": "ae6e7458-c46d-4c00-aa3b-153b0b8520ea"}', true);
73+
74+
-- Update the app_version
75+
UPDATE public.app_versions
76+
SET comment = 'Updated via API key test'
77+
WHERE name = '99.0.0-test-audit' AND app_id = 'com.demo.app';
78+
79+
-- Check that an audit log was created for the UPDATE
80+
SELECT COUNT(*) INTO v_audit_count
81+
FROM public.audit_logs
82+
WHERE table_name = 'app_versions'
83+
AND operation = 'UPDATE'
84+
AND user_id = '6aa76066-55ef-4238-ade6-0b32334a4097'
85+
AND 'comment' = ANY(changed_fields);
86+
87+
IF v_audit_count = 0 THEN
88+
RAISE EXCEPTION 'No audit log created for app_version UPDATE with API key';
89+
END IF;
90+
91+
RAISE NOTICE 'Audit log created for app_version UPDATE';
92+
END $$;
93+
94+
SELECT ok(true, 'app_version UPDATE with API key creates audit log with changed_fields');
95+
96+
-- Test 5: Delete app_version with API key context and verify audit log is created
97+
DO $$
98+
DECLARE
99+
v_version_id bigint;
100+
v_audit_count int;
101+
BEGIN
102+
-- Set API key context
103+
PERFORM set_config('request.headers', '{"capgkey": "ae6e7458-c46d-4c00-aa3b-153b0b8520ea"}', true);
104+
105+
-- Get the version id before deleting
106+
SELECT id INTO v_version_id
107+
FROM public.app_versions
108+
WHERE name = '99.0.0-test-audit' AND app_id = 'com.demo.app';
109+
110+
-- Delete the app_version
111+
DELETE FROM public.app_versions
112+
WHERE name = '99.0.0-test-audit' AND app_id = 'com.demo.app';
113+
114+
-- Check that an audit log was created for the DELETE
115+
SELECT COUNT(*) INTO v_audit_count
116+
FROM public.audit_logs
117+
WHERE table_name = 'app_versions'
118+
AND record_id = v_version_id::text
119+
AND operation = 'DELETE'
120+
AND user_id = '6aa76066-55ef-4238-ade6-0b32334a4097';
121+
122+
IF v_audit_count = 0 THEN
123+
RAISE EXCEPTION 'No audit log created for app_version DELETE with API key';
124+
END IF;
125+
126+
RAISE NOTICE 'Audit log created for app_version DELETE (version_id: %)', v_version_id;
127+
END $$;
128+
129+
SELECT ok(true, 'app_version DELETE with API key creates audit log');
130+
131+
-- Test 6: Verify audit log contains correct old_record and new_record data
132+
DO $$
133+
DECLARE
134+
v_version_id bigint;
135+
v_audit_record record;
136+
BEGIN
137+
-- Set API key context
138+
PERFORM set_config('request.headers', '{"capgkey": "ae6e7458-c46d-4c00-aa3b-153b0b8520ea"}', true);
139+
140+
-- Insert a new app_version
141+
INSERT INTO public.app_versions (app_id, name, owner_org, user_id, storage_provider, comment)
142+
VALUES ('com.demo.app', '99.0.1-test-audit', '046a36ac-e03c-4590-9257-bd6c9dba9ee8', '6aa76066-55ef-4238-ade6-0b32334a4097', 'r2', 'Initial comment')
143+
RETURNING id INTO v_version_id;
144+
145+
-- Check the INSERT audit log
146+
SELECT * INTO v_audit_record
147+
FROM public.audit_logs
148+
WHERE table_name = 'app_versions'
149+
AND record_id = v_version_id::text
150+
AND operation = 'INSERT'
151+
ORDER BY created_at DESC
152+
LIMIT 1;
153+
154+
IF v_audit_record.old_record IS NOT NULL THEN
155+
RAISE EXCEPTION 'INSERT audit log should have NULL old_record';
156+
END IF;
157+
158+
IF v_audit_record.new_record IS NULL THEN
159+
RAISE EXCEPTION 'INSERT audit log should have non-NULL new_record';
160+
END IF;
161+
162+
IF v_audit_record.new_record->>'name' != '99.0.1-test-audit' THEN
163+
RAISE EXCEPTION 'INSERT audit log new_record should contain the version name';
164+
END IF;
165+
166+
-- Update the version
167+
UPDATE public.app_versions
168+
SET comment = 'Updated comment'
169+
WHERE id = v_version_id;
170+
171+
-- Check the UPDATE audit log
172+
SELECT * INTO v_audit_record
173+
FROM public.audit_logs
174+
WHERE table_name = 'app_versions'
175+
AND record_id = v_version_id::text
176+
AND operation = 'UPDATE'
177+
ORDER BY created_at DESC
178+
LIMIT 1;
179+
180+
IF v_audit_record.old_record IS NULL THEN
181+
RAISE EXCEPTION 'UPDATE audit log should have non-NULL old_record';
182+
END IF;
183+
184+
IF v_audit_record.new_record IS NULL THEN
185+
RAISE EXCEPTION 'UPDATE audit log should have non-NULL new_record';
186+
END IF;
187+
188+
IF v_audit_record.old_record->>'comment' != 'Initial comment' THEN
189+
RAISE EXCEPTION 'UPDATE audit log old_record should contain the old comment';
190+
END IF;
191+
192+
IF v_audit_record.new_record->>'comment' != 'Updated comment' THEN
193+
RAISE EXCEPTION 'UPDATE audit log new_record should contain the new comment';
194+
END IF;
195+
196+
-- Cleanup
197+
DELETE FROM public.app_versions WHERE id = v_version_id;
198+
199+
RAISE NOTICE 'Audit log old_record and new_record verification passed';
200+
END $$;
201+
202+
SELECT ok(true, 'audit log contains correct old_record and new_record data');
203+
204+
-- Finish
205+
SELECT *
206+
FROM
207+
finish();
208+
209+
-- Roll back any changes done in this test
210+
ROLLBACK;

0 commit comments

Comments
 (0)