How to check if your Supabase table is open to public

These days tools like Lovable is getting better and know how to handle Supabase table better and more secure way but by the time I generate my code, it wasn't really putting RLS properly (for reading data) so please read on if you have a slightest doubt that you might be victim of this.

Where I'm coming from

If you read Reddit, there are many people warn you about the potential security issue, but I know I have RLS enabled so I'm fine - I was so WRONG! Just because you have RLS for add, update and delete (at least in the old days), Lovable tend to leave read wide open. Also there are so many discussions about this and some people are offering app that we can check security etc. but I wonder if I can do this check by myself so I asked Chat GPT this prompt:

Can you show me without tightening, how can someone query my table?
(this way I can test myself to see how information get exposed?)

and I got this super simple node script

import { createClient } from '@supabase/supabase-js'

const supabase = createClient(
  'https://YOUR_PROJECT.supabase.co',
  'YOUR_PUBLIC_ANON_KEY'
)

async function testLeak() {
  const { data, error } = await supabase
    .from('{repalce-this-with-your-table-name}')
    .select('*') // <-- selects ALL columns

  console.log({ data, error })
}

testLeak()

This is extremely simple - all it's doing is to use information that is fairly easily extractable from Lovable app (especially if you know how to check what kind of network activity are happening within app) to create Supabase client and then execute simple SELECT * - this can't be right ... this is joke, until, I have to admit it actually returned data for me.

I was shocked ... and glad that I didn't rush to ship my app to public quickly. I'm not blaming tool at all - security should be responsibility for all of us who build app (whether if let AI generate or hand coded, doesn't matter) I also want to share that Lovable added quite bit of security feature and it now properly flag these issues (even offer free security check) and almost prevent user to publish app unless you resolve these critical security issues.

If you know how to set up node, add package.json etc. above code example is probably enough but with bit of help from Gemini Code Assistant (read as "I was lazy to do this myself"), I created this repo:

https://github.com/tomokat/supabase-security-checker

that you can checkout and try it - it's all in your local so no need to worry about leaking anything but please note, this is the simplest and almost dumbest way to check if your table is publicly exposed (you should kick yourself if this returns table data, especially that table contains sensitive user data) and just because this says secure, it doesn't mean anything and you should definitely do the more thorough, proper security review/audit (as AI generated and call this out properly in README)

I will also put another small warning - depends on when you generate your code and how much of your code uses that table, fixing these issues may not be as easy as adding RLS (or removing SELECT privilege from admin/public) because your code might reference it exactly that way! (This is one of the reason I believe when I ask Lovable to auto fix security issues it flag as errors, it often break app) - I'm still in the middle of doing this myself but I'm carefully changing code (and this is one of the reason why I strongly feel I want to have local Supabase AGAIN - but since I didn't use it for while, it was no longer really in synch and migration scripts are erroring out - if you are in the same situation as I am, I just wrote another article talking about how I resolve it so please check it out)