1

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.

klinetic12
  • 41
  • 5
  • "*wish to write a web application with a very small server layer*" - you might want to take a look at tools like postgrest, postgraphile or hasura – Bergi May 25 '22 at 22:14
  • "*User A can view User B's profile*" - so they need to be able to select any user from the database? Not sure how you'd apply RLS to this. What do you really want to prevent, just the enumeration? – Bergi May 25 '22 at 22:17
  • Aye, I'm using Supabase which offers a postgrest interface. I would like for User A to be able to view User B, C, D's profile if they know their username or followed a link. But I want to prevent User A from using the postgrest API to fetch all users. If that makes sense. – klinetic12 May 25 '22 at 22:30
  • The official docs have a pretty good write up on [Row Security Policies](https://www.postgresql.org/docs/current/ddl-rowsecurity.html) with several examples. I don't know how you would would implement this idea of User A to view User B if they know the username or follow a link. – bfris May 25 '22 at 22:57
  • @klinetic12 Then no, you cannot use RLS for that. The postgrest docs [suggest using a proxy to hide that route](https://postgrest.org/en/stable/admin.html#block-fulltable). There's also a [`db-max-rows` configuration variable](https://postgrest.org/en/stable/configuration.html#db-max-rows) but that would apply to all tables, not just the profiles. – Bergi May 26 '22 at 01:53
  • 1
    @klinetic12 Of course, if all the profiles are public, you can still enumerate them with postgrest one at a time. There is no security gained here, so you might as well just allow fetching the list. – Bergi May 26 '22 at 01:56

3 Answers3

0

The most common way of handling access to a subset of columns is to create VIEWs of the table defining the subsets. Then you can grant access to the VIEW, but not the TABLE, to a database user you wish to configure for limited access.

In most robust web apps, the web-server layer (the code in nodejs, php, django, etc) uses just one database user name to access the database layer. The web app may have tens of thousands of users, but each of those users does not have their own database user name.

The web-server layer takes responsibility for enforcing rules about which user can see which rows and columns of data. So, to follow up on your example, if user A is logged in they can see all their information, but only some information about user B.

The web-server code's security purpose is to prevent direct access from the public network to the database. All requests and responses necessarily must, in a secure app, go through the web-server code.

If it's possible to figure out from web-server logs how you might defeat the web-server rules about access to data, then your web app is not secure. Sensitive information should never appear in the query parameters of GET requests, but rather in the parameters of POST requests (or PUT requests if you're doing the REST thing).

(Some web apps use two database user names, one with more privileges than the other. The privileged one is used only for administrative operations like new-account creation, billing, and similar. But that's an advanced setup.)

O. Jones
  • 103,626
  • 17
  • 118
  • 172
  • 1
    I know how I would design this web application if I were using a web-server layer - exactly as you described. However, for educational reasons I'm trying to implement a similar application using row-level security principles. A client side web app that communicates directly with the database using JWT auth. It's a bit new and weird to me, but a little exciting too. – klinetic12 May 25 '22 at 21:56
  • https://www.enterprisedb.com/postgres-tutorials/how-implement-column-and-row-level-security-postgresql This is the guide I was following, but unfortunately it never tackles the SELECT * FROM users problem. It's possible there is no solution (atm). – klinetic12 May 25 '22 at 21:59
  • 1
    Tne VIEW approach I mentioned might serve. – O. Jones May 25 '22 at 22:40
  • Hmm, a good suggestion, but it doesn't look like you can add an RLS policy to a view. (rather, it inherits the policies of its associated tables) – klinetic12 May 25 '22 at 22:50
  • I think I have to use a web server layer for this, but I think I can design it such that the users table is the only thing I need to put behind the server. Thanks though! – klinetic12 May 25 '22 at 22:51
0

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

You certainly don't want to make people's email address public.

I think the question you want to ask yourself if is public.users table public or, is there a certain condition that has to be met before someone can see a person's profile, like they have to be followed by a person, or something.

If there are information that can be completely public to the world such as username, and information that you want to restrict access to such as email, you can create two separate tables and make the first one public, and add further restrictions to the second one using more advanced RLS.

dshukertjr
  • 15,244
  • 11
  • 57
  • 94
0

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.

ejucovy
  • 907
  • 9
  • 10