Can I use row-level security to prevent SELECT * FROM users?

Question

I just learned about RLS and wish to write a web application with a very small server layer (no server layer, if possible). I'm building a social media web application in particular.

I have a scenario:

User A can view their own profile at myapp.com/userA

User A can view User B's profile at myapp.com/userB

This part sounds easy to design. In order to accomplish this functionality, I grant select access to all users, and I only grant self-access to update/delete functions.

However, without a server layer, User A could potentially discover via their network request logs that they're allowed to make a query for all users. To describe how easy this is, I found a "Slack clone" built with Supabase here: http://supabase-slack-clone-supabase.vercel.app/ and modified a network request with a select statement on users by deleting the where clause, and the response contained every username in the database.

My question is: is there any way to protect against this at the database layer? Like a row limit or something?

Or is this a case where I need to revoke read access to the users table and have the server handle it? But then everything would need to be handled on the server because everything pretty much joins to the users table, right?

Do I just say screw it and let anyone select all users? And keep any sensitive information like email addresses out?

Thanks in advance for any suggestions here.

Answer 1

You can accomplish this with postgres functions. The pattern would be:

• Don't grant select to all on your user profile table. Instead, restrict access, and just grant select to the owning user, like you are doing for update and delete.

• Create a postgres function select_user that takes one argument select_username and returns the result of select ... from profiles where profiles.username=select_username as a record.

• Set up your function to use security definer.

• In your front end (assuming you're using supabase) use supabase.rpc('select_user', { select_username }) instead of supabase.from('profiles').select() whenever you want to select from a profile.

If you do this, you probably also want to move the profiles table to its own schema and limit that function's search path. I found https://stackoverflow.com/a/73282539/689985 and https://www.cybertec-postgresql.com/en/abusing-security-definer-functions/ helpful here.

At that point though, it might end up being simpler to have a backend that does this one query, especially if you're building on Vercel/Netlify/etc and can just toss up a serverless function that queries the database for this one table.