If a Supabase query returns no rows—or a write fails despite an apparently correct policy—test row-level security (RLS) separately from SQL grants, using the same database role and JWT claims as the application. A local pgTAP suite run with supabase test db gives you repeatable checks for both permitted and denied access.
First distinguish a row filter from a permission error
Supabase access has two separate gates. SQL grants decide whether a database role may perform an operation on a table at all; RLS policies decide which rows that role can read or affect. A missing grant can raise SQLSTATE 42501 before policy evaluation. Creating a policy does not add or remove grants. Check both instead of loosening a row predicate to fix a privilege problem. Supabase’s RLS guide explains the distinction.
As an Amazon Associate I earn from qualifying purchases.
An empty data array is different: it can mean RLS filtered out the row because no policy matched or the request failed its predicate. It can also mean an ordinary query filter excluded the row. Supabase’s empty-select troubleshooting guide recommends checking the request’s authenticated session and authorization context, the policy’s target role, and its condition.
Free tools Windows power users keep installed
One-click scans. No signup required.
Check the role and identity your request actually uses
A policy that calls auth.uid() relies on the identity in the request’s JWT. With no authenticated user, auth.uid() returns null; a comparison such as user_id = auth.uid() is therefore not true. A test run as a database owner or with missing claims may not reproduce a client request.
Supabase distinguishes the anon database role from an anonymous Auth user: an anonymous user signed in through Auth assumes the authenticated role. Server-side code using the privileged service_role bypasses RLS, so a successful server request does not prove that a normal client is authorized. See Supabase’s role and RLS documentation and its testing overview.
- Record the intended database role for each request:
anon,authenticated, or an elevated server-side role. - For authenticated cases, set a realistic JWT identity and claims in the test.
- Test unauthenticated behavior explicitly rather than assuming it matches a signed-in user.
- Check the query’s own filters as well as the policy predicate.
Write a separate policy test for each operation
SELECT, INSERT, UPDATE, and DELETE have different policy behavior. State the applicable role with TO so the policy’s audience is explicit. For row selection and deletion, policies use USING; INSERT uses WITH CHECK. UPDATE normally needs both: USING decides whether the existing row can be targeted, while WITH CHECK validates the row after the change. Supabase also notes that UPDATE needs a corresponding SELECT policy to work as expected. The RLS guide covers these clauses.
For every operation the application exposes, test an allowed case and a denied case for each meaningful role or relationship. If rows are owner-scoped, try both the owner and a different user. If access depends on membership, test a permitted member and a non-member; include a member who is not the owner if the policy is meant to allow that case. For UPDATE, try changing protected ownership data, such as user_id, and assert that the change is rejected.
The Tool Desk
Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →Build a repeatable local pgTAP suite
- Inventory access. List each table operation, the roles allowed to perform it, and the identity or membership conditions that should permit it. Check that RLS is enabled and that grants are limited to intended operations; policies do not revoke grants.
- Create a SQL test file. Supabase’s example location is
supabase/tests/, such assupabase/tests/profiles_rls.test.sql. The documented CLI command to create a test issupabase test new <table>_rls.test. - Prepare realistic fixtures. Insert test rows representing the relevant owners, members, and non-members. Set the executing role and JWT claims deliberately; the testing overview calls out
set local role authenticatedandrequest.jwt.claimsas useful checks. - Assert both outcomes. Verify the intended user can complete each operation and that a role or identity outside the policy cannot. Check the resulting row after updates, not merely whether a statement ran.
- Isolate and run the suite. Wrap test changes in a transaction and roll them back so test data does not persist. Run tests against the local database after migrations with
supabase test db, then run them in CI. Supabase documents the CLI test command and test setup practices.
Supabase’s documentation puts the purpose plainly: “Until the suite passes, you don’t know whether the policies do what you intended.”
Choose the test layer that matches the question
| Approach | What it exercises | Useful for | Limit |
|---|---|---|---|
| SQL/pgTAP through Supabase CLI | Database schema, roles, claims, policy behavior, and test data | Repeatable assertions close to the database and CI checks of policy boundaries | Does not by itself exercise the full application client request path |
| Client-level integration test | Requests through a Supabase client using the application-language client path | Checking how application code and client requests behave together | Less direct for isolating a database policy failure than a focused SQL test |
Supabase describes both database tests and client-level integration tests in its testing documentation. They serve different purposes; the documentation does not establish a performance comparison between them.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Triage common failures by symptom
SELECT returns an empty array
Confirm the request has the expected signed-in session and JWT authorization context. Then check that the policy applies to the request’s database role and that its predicate matches the row. Also remove or inspect ordinary query filters that might exclude it. If the policy uses auth.uid(), verify the test identity is present. Temporarily disabling RLS can help isolate the cause during diagnosis, but it is not a production fix; restore protection and correct the role, grant, request context, or policy.
A statement raises a permission error
Check the SQL grant for the requested operation before editing RLS. A missing grant can produce 42501 before a policy is considered.
Crashes, No Sound, or Screen Glitches?
Random freezes, missing sound and display glitches usually trace back to one bad driver. Find and replace yours safely.Free scan · under a minuteWindows Errors? Fix Them Before They Spread
Repair common Windows errors and clear accumulated junk for a smoother, more stable PC - no reinstall needed.Free scan · no reinstallINSERT succeeds but the response or upload reports an RLS error
Some APIs return inserted rows. For a Storage upload, the insert may be allowed while the SELECT policy does not allow reading the new object’s metadata for the API’s response. Test the insert and the read needed to return its result. Supabase describes this case in its Storage upload RLS troubleshooting guide.
UPDATE appears to do nothing
Check three things independently: the row is visible under the applicable SELECT policy, the UPDATE USING condition accepts the existing row, and WITH CHECK accepts the updated row. Also confirm the role has the relevant grant.
A view exposes data unexpectedly
Supabase warns that views can bypass underlying RLS by default because they are commonly created with security definer. On Postgres 15 and later, set security_invoker = true for the view so it obeys underlying policies for anon and authenticated. On older Postgres versions, revoke those roles’ access to the view or place it in an unexposed schema. See Supabase’s view guidance.
Keep security testing within your project
Local policy tests are ordinary development work. If assessing a hosted Supabase project, limit testing to projects you control and follow Supabase’s security testing policy, which prohibits cross-tenant attacks, denial-of-service testing, and request flooding.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
