The Story of Having Claude Code Check Supabase's Security Advisor and Build In App Protection

Introduction

If you ask Claude Code, an app using Supabase will be up and running in a short time. However, it is hard to say with confidence that it is fine to publish it as is. In this article, using a shopping list shared by a family as the subject, I compared how the app changes before and after telling Claude Code what I want to protect.

When I conveyed what I wanted to protect in the form of "who can do what" and also asked for a check with Security Advisor, protections matching my answers were built into the database side. However, some problems remained.

What is Supabase

Supabase is a service that centers on a PostgreSQL database and provides authentication, storage, and an API automatically generated from the database. Because it allows a setup in which the browser queries the database directly through the API, data protection is done through database-side settings.

What is Security Advisor

Security Advisor is a feature in the Supabase dashboard that inspects the database for security problems. It points out missing RLS settings, SECURITY DEFINER functions that logged-in users can call, and so on, using predetermined rules. The same inspection can also be run from the CLI with supabase db advisors.

Test environment

• Claude Code 2.1.304 (model Opus 5.5)
• Supabase CLI 2.121.0
• macOS

Intended audience

• People who have started building apps with Supabase using AI such as Claude Code
• People whose app works but who can't say with confidence that it is fine to publish
• People unsure how to ask an AI to design permissions

References

• Row Level Security (Supabase official documentation)
• Advisors (Supabase official documentation)

Background

In a Supabase app, a publishable key is embedded in the browser screen and the database is queried directly. The range each user can read and write is determined by the database's RLS (Row Level Security, row-level access control) and permission settings.

Even if you ask an AI to "share it with family only," the scope of the family, how to remove a member, and how to treat a removed person are not settled. Rules that are not settled end up being decided by the AI on its own, or decided by the AI asking the user questions.

This time, I examine and discuss how to tell an AI what to protect so that it is properly protected, what happened as a result of telling it, and how to verify that.

Test method

Subject and execution conditions

The subject is a web app for a shopping list shared by a family.

Beforehand, I completed supabase init and supabase link in the working directory. In .env, I placed only the URL and the publishable key. Claude Code was started in interactive mode with --safe-mode so that it would not load the global settings, plugins, and MCP in the author's environment. When asked for permission, I made decisions as I normally would as a user.

The first request I gave is as follows. It does not say anything in particular about protection.

Supabase を使って、家族だけで共有して編集できる買い物リストの Web アプリを作ってください。画面は HTML 1 枚で構いません。Supabase プロジェクトはこのディレクトリにリンク済みで、接続情報は .env にあります。データベースへの変更もあなたが反映してください。終わったら、作ったものと確認したことを報告してください。

After it was completed, I resumed the same session with --continue and told it what I wanted to protect and how to verify it.

公開する前に、次の守りたいことをデータベース側で守れるようにしてください。ブラウザ側の処理には頼らないでください。
- ログインしていない人と、家族のメンバーでない人は、品物を見ることも変えることもできない。
- 家族のメンバーは、品物を見る・追加する・変更する・削除することができる。
- 家族を作った人は、メンバーを外せる。外された人は、その後は品物を見ることも変えることもできない。
変更する前に、守りたいことのうち決めきれない点があれば質問してください。

終わったら、変えたことと確認したことを報告してください。確認では、守りたいことを1つずつ、複数のアカウントで実際に試してください。あわせて Supabase の Security Advisor の指摘を確認し、指摘と対応を報告してください。

Verification by the author

Separately from Claude Code's completion report, the author checked using the following methods.

• Permission tests: Using test users in different roles, I tried reads and writes from the REST API. For judgment, I used the state of the data after the operation, not the HTTP status. (* Because updates and deletes whose target became 0 rows due to RLS also return HTTP 200)
• Security Advisor: I ran supabase db advisors.

Results: when I did not convey what to protect

Claude Code proceeded without asking questions and completed the app in about 4 minutes. The scope of the family and how to remove members were decided by Claude Code itself. Under the rules it decided, anyone who knows the invite code can join. No administrator is set, and no one can remove other members.

In the completion report, it explained that being unable to remove other members was one of the protections.

- 作成者のなりすまし、品物を別の家族へ移す操作、他のメンバーの名前変更や追い出しも、データベース側で拒否します。

The implementation was as reported, and in the author's tests as well, the operation of the creator removing another member ended with 0 rows affected.

Also, the "automatic check" in the completion report was supabase db lint.

2. **データベースの自動チェック:** `supabase db lint` では問題が見つかりませんでした。

When the author ran Security Advisor, 4 WARNs appeared. All of them point out that functions that run with the owner's privileges (SECURITY DEFINER) can be called from the API by logged-in users. Here is an excerpt of the 4 WARNs.

WARN authenticated_security_definer_function_executable | Function `public.create_family(family_name text, member_name text)` can be executed by the `authenticated` role as a `SECURITY DEFINER` function via `/rest/v1/rpc/create_family`. Revoke `EXECUTE` or switch it to `SECURITY INVOKER` if that is not intentional.
WARN authenticated_security_definer_function_executable | Function `public.is_family_member(fid uuid)` can be executed by the `authenticated` role as a `SECURITY DEFINER` function via `/rest/v1/rpc/is_family_member`. Revoke `EXECUTE` or switch it to `SECURITY INVOKER` if that is not intentional.
WARN authenticated_security_definer_function_executable | Function `public.join_family(code text, member_name text)` can be executed by the `authenticated` role as a `SECURITY DEFINER` function via `/rest/v1/rpc/join_family`. Revoke `EXECUTE` or switch it to `SECURITY INVOKER` if that is not intentional.
WARN authenticated_security_definer_function_executable | Function `public.rotate_invite_code(fid uuid)` can be executed by the `authenticated` role as a `SECURITY DEFINER` function via `/rest/v1/rpc/rotate_invite_code`. Revoke `EXECUTE` or switch it to `SECURITY INVOKER` if that is not intentional.

Results: when I conveyed what to protect

Questions before the changes

Before making changes, Claude Code asked 3 questions with options. All of them are points that could not be settled from the protection requirements in the request alone.

Q: 外された人が、前に受け取った招待コードを覚えていた場合、また参加できてよいですか？
  - 作成者が許可するまで不可 (Recommended): 外された人をデータベースに記録し、作成者が解除するまで同じ家族には参加できないようにします。招待コードを知っていても戻れません。
  - 外すと同時にコードを再発行: 外した時点で招待コードを自動で変えます。新しいコードを誰かが渡せば戻れます。
  - 制限しない: 招待コードを知っていれば、また参加できます。
Q: 家族を作った人自身は、外されたり自分で抜けたりできるようにしますか？
  - 作成者は外せず、抜けられない (Recommended): 家族には常に作成者が残ります。作成者を外すことも、作成者が抜けることもデータベースで拒否します。
  - 作成者も抜けられる: 作成者が抜けると、その家族ではメンバーを外せる人がいなくなります。
Q: 作成者以外のメンバーについては、どこまで認めますか？
  - 自分で抜けるのは可、他人は外せない (Recommended): 今の動きと同じです。他のメンバーを外せるのは作成者だけです。
  - 自分で抜けることも不可: メンバーが家族から外れるのは、作成者に外されたときだけになります。

The author chose the Recommended option for all 3 questions.

Protections built in and verification results

Following my answers, Claude Code added to the database side a policy allowing only the creator to remove members, and a mechanism that records removed people and rejects their rejoining. It also addressed the Security Advisor findings, and explained in the completion report as follows.

守りたいこと 3 つは、すべてデータベース側（RLS、トリガー、関数）だけで守れるようにしました。複数のアカウントで実際に試して 68 項目すべて合格し、Security Advisor の警告は 4 件から 1 件に減りました。残る 1 件は意図して残したもので、理由は後述します。

The Security Advisor result the author ran was also 1 WARN (join_family), matching the report. The author's permission tests had 44 items, and all were as expected. Here are the results corresponding to the third protection requirement, side by side with those from before it was conveyed.

A path that remained even after passing the tests

The record of removed people is kept per account.

On the other hand, this app lets anyone register as a new user. So I tried a scenario in which a removed person creates another account and joins using the invite code they knew before being removed.

{"step":"X が S を外す","status":200}
{"step":"別アカウント S2 が外される前のコードで参加 (再発行なし)","status":200,"joined":true,"items":[{"name":"milk"}]}
{"step":"X が S2 を外す","status":200}
{"step":"X が招待コードを再発行","status":200}
{"step":"別アカウント S3 が古いコードで参加 (再発行後)","status":400,"returned":"invalid invite code","joined":false}

When removal was done without regenerating the invite code, the person could rejoin with a different account and read the items. When the code was regenerated after removal, joining with the old code was rejected.

Discussion

Both before and after conveying what to protect, Claude Code implemented according to the rules it had decided. I think what made the difference was the content of the rules. In particular, the sentence "if there are points you cannot settle, please ask questions" brought the points the user had not decided to the surface as questions with options.

In Supabase, read/write decisions are concentrated in database policies, so protection can be verified by trying reads and writes while switching accounts. Security Advisor raises findings through a route separate from the AI's report, so I felt it was effective as a means of verification.

On the other hand, even though all the tests passed, rejoining via a different account remained. If the protection requirements had included "a removed person cannot return even by creating another account," Claude Code might have included countermeasures in its questions or implementation. Alternatively, choosing the option "regenerate the code at the same time as removal" from the first question might also have given a better result. (* I did not try either of these this time, so this is speculation.)

Summary

When asking an AI to build a Supabase app, it seems good to include what to protect, questions, and a check with Security Advisor in the instructions, and before publishing, to also run tests with multiple accounts yourself. I hope this article is helpful to those building Supabase apps with AI.