new row violates row-level security policy for table "credit_transactions"
I have been encountering issues with the RLS policy of supabase. My production environment has been reporting this error in vercel. The Policies in my relevant table are as follows. SUPABASE_SERVICE_ROLE_KEY is also correctly configured and loaded, and has been debugged according to different schemes. The result was still the same error.
9 Replies
Not around more tonight, but if you think you are using service_role you are not if you get an RLS error. Service_role totally bypasses RLS and you will never get that error, does not matter if you do TO service_role or not.
If you have a service role client though and allow a user session to get into it then it is no longer service_role.
Other than that you don't say your operation. If you are doing user (not service_role) operations like insert and returning data then you need to meet insert and select policies.
You should post what code is getting the error and possibly the table column info. RLS is relevant IF you are not doing service_role.
I'll check back in morning if no one responds tonight.
I'm not sure if the RLS corruption is caused by residual database configuration. I have taken the following actions:
My code explicitly uses the Service Key (createAdminClient()).
Vercel Logs confirm the existence of the Key.
I have changed the Service Key.
But the 42501 error still occurred.
In addition, let me add my business logic:
When a user logs in to the website for the first time, 2 points will be given as a gift. There will be a record of this gift in the transaction history. However, it is currently being tested in a production environment, and there is no record of giving points in the transaction history. The console also shows totalEarned: 0. The credit_transactions table of supabase does not show any records, and the logs of vercel shows an error. However, SUPABASE_SERVICE_ROLE_KEY is also correct and has been loaded. Why is this so?
You can look in the dashboard logs of Supabase. API Gateway log. Down in the details there will be apikey and authorization header. Both should be service_role. If authorization header is anon or a user JWT then you are using your service role admin client in a situation where it gets a user session into it.
Service_role will NEVER generate an RLS. Only anon or a user JWT might.
Indeed, as you said, the apikey is anon, the request carries the user session (jwt), and the value of request.sb[0].jwt[0].authorization[0].payload[0].role is authenticated, as shown in Figure 1.
Then the modifications I made were: creating a global, one-time Admin client to ensure it is completely isolated from any user request context, modifying the server.ts file, and changing createAdminClient to directly export the client instance, as shown in Figure 2.
The credits-service.ts file was also modified, as shown in Figure 3.
After such modification, the Service Role Key client will be completely independent of the user request, thereby solving the RLS conflict problem, but the result is still the same error report.
Later, I ran SELECT has_table_privilege('service_role', 'public.credit_transactions', 'INSERT'); The query returned: false. So I ran these commands again to grant the necessary insertion permissions to service_role, as shown in Figure 4. Run SELECT has_table_privilege('service_role', 'public.credit_transactions', 'INSERT') again; The confirmation result has become t (true).
Then I tested the production environment. Now the points statistics and transaction history on the front-end page have been recorded. The totalEarned: in the console also shows 2, and the credit_transactions also have data. But why is there still an error of 'new row violates row-level security policy for table "credit_transactions"' in vercel 'logs? Does the API Gateway of supabase still display 403? As shown in Figures 5, 6, 7, 8 and 9.
Recently I've been driven crazy by this 'new row violates row-level security policy for table 'credit_transactions' error. It's been a week and still hasn't been resolved.ðŸ˜
By the way, if a project is rebuilt in supabase and the database structure is migrated, will there be no such problem?😂
That call is not coming from your supabaseAdmin client unless you have your SUPABASE_SERVICE_ROLE_KEY messed up. The reason I say this is the apikey is ANON. Also the client does have a user session in it as indicated by Authenticated.
I'm actually a technical novice. Thank you very much for your patience in answering my questions. Based on your response and the chat records from the AI Q&A session, I asked Gemini again. The answer they gave me is as follows. I wonder if the method Gemini provided me can ultimately solve the problem:
The problem I encountered was not a code logic error. Instead, in the Next.js Serverless environment, the configuration of the Service Role client instance was overwritten by the context of the user session (JWT and public Key), resulting in the administrator Key being completely invalid.
Therefore, the solution we must adopt is: Stop relying on the Service Key in Vercel Function and instead use the database RPC of SECURITY DEFINER, allowing the database to perform insertion operations with the highest privileges by itself and completely ignore any keys or JWTS sent by the client.
The correct answer is is to have 2 different clients. One for service_role only used for nothing else but admin calls. Then another client for using with user calls and sessions initiated with anon key.
It turns out that the method of using SECURITY DEFINER RPC doesn't work either, which is 'new row violates row-level security policy for table "credit_transactions"'. The reason for my failure was not that I didn't create two clients, but my code architecture (especially coupling the high-privilege write logic in the read logic of credits-service.ts).
Are you saying that the Admin client must remain clean and independent? If the new project architecture is redesigned in this way, the extent of the modification will be relatively large.