Supabase•6h ago

Select returning empty array on project but query works on supabase site

Updating, creating, and deleting work only select returns an empty array. I've tried the exact query on the Supabase site itself, and it works, returning the right number of rows. However, on the Node.js server, it doesn't. I have disabled RLS, enabled RLS, added a policy, and yet the problem persists.

15 Replies

inder•6h ago

You'll need to show your RLS policies. Querying data after disabling RLS should've worked.

Fieryduck82579•6h ago

If you're querying through the PostgREST API, the table also has to be in a publicly exposed schema.

inder•6h ago

I believe it wouldn't have returned empty array otherwise.

Fieryduck82579•6h ago

Good point, you might be right there.

الشيخOP•6h ago

This is the policy I am using.

alter policy "Enable read access for all users"

on "public"."comments"

to public

using (
 true
);

alter policy "Enable read access for all users"

on "public"."comments"

to public

using (
 true
);

also I'm using this library to query supabase

الشيخOP•6h ago

https://github.com/porsager/postgres

GitHub

GitHub - porsager/postgres: Postgres.js - The Fastest full featured...

Postgres.js - The Fastest full featured PostgreSQL client for Node.js, Deno, Bun and CloudFlare - porsager/postgres

inder•6h ago

Show the query you make using this lib

Fieryduck82579•6h ago

~~Preferably the whole policy including the TO clause.~~ I just noticed the public. I meant the FOR.

الشيخOP•6h ago

 
app.post("/get-dislikes", authenticateToken, async (req, res) => {
    const dislikes = await sql`SELECT * FROM COMMENTS WHERE comment_id IN (${req.body.commentIds.map(id => `'${id}'`).join(",")})`;
    res.json({ dislikes })
})

 
app.post("/get-dislikes", authenticateToken, async (req, res) => {
    const dislikes = await sql`SELECT * FROM COMMENTS WHERE comment_id IN (${req.body.commentIds.map(id => `'${id}'`).join(",")})`;
    res.json({ dislikes })
})

The exact query thats being sent is

SELECT * FROM COMMENTS WHERE comment_id IN ('https://www.youtube.com/watch?v=mWDOPcFKsfg&lc=Ugxywn7APVAEHPVgyXN4AaABAg','https://www.youtube.com/watch?v=mWDOPcFKsfg&lc=UgwNZEGTE8dxz9MxHup4AaABAg&pp=0gcJCSIANpG00pGi')

which works on supabase

inder•6h ago

And as fieryduck mentioned, show the full policy.

الشيخOP•5h ago

I cant seem to find it

Fieryduck82579•5h ago

The SELECT policy looks permissive enough. Can you try querying all rows from the Node? SELECT * FROM comments

inder•5h ago

When you login as postgres user with a direct connection string, RLS policies are bypassed. Use a simple query with a LIMIT. I believe its an issue with your query

await sql`SELECT * FROM COMMENTS LIMIT 5`

await sql`SELECT * FROM COMMENTS LIMIT 5`

For RLS to take effect with a direct connection refer to this thread. https://discord.com/channels/839993398554656828/1351855856450408518

Fieryduck82579•4h ago

Not related to the issue at hand, and I have not used Postgres.js before, but based on what I read in the docs, the way you're constructing the IN ARRAY seems to be vulnerable to an SQL injection (see note).

الشيخOP•3h ago

it was 100% an issue with the query I wasnt following their advice properly in documentation when it came to arrays. Also I changed it from an "IN" statement to an "ANY" statement and it works like a charm. Thank you guys for your help

Gaming

Programming

Select returning empty array on project but query works on supabase site

Did you find this page helpful?