Supabase 個人登入與審計追蹤實作：Auth、RLS、Triggers、防竄改測試與小型支援入口

If your team shares one login, an audit log can't tell you who did what. I wanted to see that for myself, then build the fix with Supabase Auth, RLS and database triggers, and try to break it. Everything is from my run on 2026-10-27 on a Free project. Prices change, so every number is dated.

How this was made: I wrote this with Claude's help: it drafted, and I ran every step, edited the text, and checked the results. The mistakes are mine. This is a personal learning project, not an official guide, and I don't work for Supabase.

The non-technical version (a shared key erases "who"): https://medium.com/p/125c58406e79
The code: https://github.com/tomozilla/audit-trail-portal-en-demo

What I did and did not do

• Did: two tables and two triggers on a Free project, three fictional demo users, and a Python drill that signs in as each, makes changes, then tries to tamper. $0. I used only the publishable key, never a secret or service_role key.
• Did not: test the service_role key (it bypasses RLS), organization-level audit logs, the contents of Auth logs, anonymous sign-ins, MFA, or many users.
• Numbers come from the pricing page of 2026-10-27. Emails are fictional (example.com).

Shared login vs one login per person

With a shared login, every line in the log has the same name. And to lock out someone who left, you change the password and lock out everyone.

The design

Two tables: support_requests (the work) and activity_log (who did what). Three ideas:

1. A trigger writes the log, not the page. Code in the page can be skipped, edited or forged.
2. "Who" comes from the login: auth.uid() and auth.jwt() ->> 'email' inside the trigger. Nothing the page sends is trusted.
3. The log is read-only for the API: there is no insert, update or delete policy, and I also revoked those privileges.

-- stamp who and when on the row itself: the client can't claim to be someone else
create function public.stamp_request() returns trigger language plpgsql set search_path = '' as $$
begin new.updated_by := auth.uid(); new.updated_at := now(); return new; end $$;

-- write the log; security definer = runs with the owner's rights, so it can write when the caller can't
create function public.log_request_change() returns trigger language plpgsql security definer set search_path = '' as $$
begin
  insert into public.activity_log (actor, actor_email, action, request_id, detail)
  values (auth.uid(), auth.jwt() ->> 'email', tg_op, coalesce(new.id, old.id), ...);
  return coalesce(new, old);
end $$;
revoke insert, update, delete on public.activity_log from anon, authenticated;

A security definer function is powerful, so I set search_path = '' and revoked execute rights from the API roles. The full file is sql/01_requests_and_log.sql.

The drill

I created three users under Authentication -> Users -> Add user -> Create new user (Auto confirm), with random passwords kept only on my machine:

• Case A: one shared login (shared-desk) used by two real people.
• Case B: each person uses their own login (staff-sato, staff-tanaka).

scripts/drill.py plays both cases using the publishable key and prints status codes only. Here is the log afterwards:

• Rows 1 and 2 (Case A): whichever person acted, the log says shared-desk@example.com.
• Rows 3 to 5 (Case B): sato added the request, tanaka moved it to in progress, sato marked it done.

Can the log be tampered with?

As staff-sato, I tried:

Stumble: the error message gave me a dangerous hint

The 403 body says: Grant the required privileges to the current role with: GRANT UPDATE ON public.activity_log TO authenticated;. Running that would remove the protection. I now treat error hints as suggestions to check against my intent, not commands.

Stumble: row 6

Row 6 is done -> done. It is my updated_by forgery attempt, recorded as an update that changed no status. A "nothing changed" update still gets logged, which is useful: the attempt is visible.

Two doors

Everything above is the app's door: the people who use your app. The console's door is a different list: organization members.

From the pricing page (2026-10-27): Free and Pro offer Owner, Admin and Developer; Read-only and project-scoped roles and dashboard SSO start at Team. The retention of Auth audit logs is 1 hour on Free, 7 days on Pro, 28 days on Team (I did not look at their contents).

The portal page

I wrapped it in a small page: sign in as yourself, add and update requests, and read the log.

It loads supabase-js from a CDN (version pinned to 2.118.0) and reads a git-ignored config.js with the URL and publishable key.

cd app && cp config.example.js config.js   # fill in your values
python3 -m http.server 8000                # open http://localhost:8000/
node test.js                               # 13 checks

A wrong password shows a plain message and reveals nothing:

After signing in as sato and adding a request:

Rows from the shared account get a red warning, and a badge counts the entries you cannot trace to a person. Seeing that number is more persuasive in a meeting than any explanation.

More stumbles

• A hidden panel that wasn't hidden. Before signing in, the empty Requests and Log panels were visible. I had set the hidden attribute, but my CSS said display: grid, and the CSS won. Fix: [hidden] { display: none !important; }.
• A "success" that changed nothing. An update that matches zero rows does not raise an error. The page checks the number of rows returned and says "Nothing changed" instead. I coded this but did not capture or reproduce that screen.
• Request #3 is missing. The ids go 1, 2, 4. I believe the refused anonymous insert used up a sequence value, but I did not verify it.

Limits (the honest part)

• Any signed-in user can change or delete any request (the policies are deliberately broad). Narrow them before real use.
• A log is after the fact. It does not prevent anything.
• Shared-account detection is only a list of emails you write down yourself.
• Not tested: service_role (it bypasses RLS), phone width, MFA, many users.

What it costs

• This walkthrough and the page: $0 (Free project, 3 users).
• Monthly active users: 50,000 included on Free and 100,000 on Pro, then $0.00325 per user (pricing page, 2026-10-27). The log table grows: plan retention and clean-up.
• Nothing was checked against an invoice.

Cleanup

I dropped both tables and both functions (sql/99_cleanup.sql) and deleted the three demo users.

If I took this further

Not tried: policies that let a person change only their own requests, a retention job for the log, and a leaver checklist that disables the account first.

Takeaways

• A shared login erases "who": one login per person.
• Write the log in the database, with "who" taken from the login, and make it read-only for the API.
• Don't run an error's suggested GRANT without checking what it undoes.
• Console members and app users are separate lists.
• A log is after the fact. Also decide who may change what.

This closes the series for now. The next step is a one-page summary of plans, organizations, costs, backups and access.