2 ms·

Ask HN: Example of row-level security in open source project?

Most multi-user software that relies on a database uses a single set of credentials to access the database. Access permissions are defined and enforced within application logic, and the database server knows nothing about the users, their roles or permissions. Postgraphile allows front-end code to use graphql to access data stored in a postgres database. So there's no traditional backend code to do the work of storing and enforcing user/role-based authorisation for certain data. The recommended way to handle this is by the use of Row-Level Security (RLS), a database feature which ensures that only permitted data is updated or returned, based on the end user who generated the query. [0] This sounds fine in principle but, because permissions are at row-level, you can't have different permissions for different columns in the same table. The solution is to split your tables into multiple, narrower tables. So, for example, if you don't want a user to be able to update their own username, the username column could be in a separate table from other demographic information. I understand this in theory, and I know that RLS isn't new and isn't restricted to postgraphile. But I'd love to see a non-trivial example of it in use, for an app whose only backend is a database (ideally postgres and postgraphile, but really any relational database that supports RLS would be fine). Can anyone point to a good repo or book that has a good example of schema design using RLS? (I spent some time searching, but didn't find anything promising.) [0] https://www.graphile.org/postgraphile/security/

throwaway888abc 6y ago
https://supabase.io/blog/2020/08/05/supabase-auth https://supabase.io/blog/2020/08/05/supabase-auth and discussion https://news.ycombinator.com/item?id=24072051 https://news.ycombinator.com/item?id=24072051
- kiwicopple 6y ago
  supabase cofounder here, thanks for the mention Also following up on OP's question: "Can anyone point to a good repo" One of our team (maintainer of PostgREST) has a great example repo to demonstrate an "advanced" scenario of building a social network with RLS: https://github.com/steve-chavez/socnet/ https://github.com/steve-chavez/socnet/ With Postgres you can elevate privileges to bypass RLS (which you may want to do in your middleware), so it's very flexible
  - rahimnathwani 6y ago
    Thanks, both. I was wondering how we: 1. Avoid having a postgres user for each application user, and 2. Ensure that the right user is passed to RLS. You answered this in the post GP linked: "when a user signs up to your app/project, GoTrue assigns them a unique UID, saves them in the auth schema, and returns a JWT with the user’s details. Our client library attached this JWT to every API request. PostgREST validates the JWT then sends the request through to Postgres." I guess a similar approach would work with Postgraphile, assuming Postgraphile can validate the JWT and add 'SET ...' at the start of each query to Postgres. I'll take a look at the schema in the repo you linked. Thanks!
    - kiwicopple 6y ago
      Yes you're right. Supabase uses PostgREST behind the scenes. You can read about how the roles work here: https://postgrest.org/en/stable/auth.html https://postgrest.org/en/stable/auth.html Basically, you have a role which is an "authenticator" - this is the one you connect to in the database with. This has basically no privileges except to "change into" a new role. When an API route is called, PostgREST checks the headers to see if there is a JWT then gets the "role" out of the JWT. This JWT maps to a PostgreSQL role, which will have slightly higher privileges. Inside the JWT is the User ID too - so you can use this inside your RLS policies. So in summary: > 1. Avoid having a postgres user for each application user, and You only need 2 roles - an "autheticator" role, and an "authenticated" role > 2. Ensure that the right user is passed to RLS. It's encoded int he JWT. > I guess a similar approach would work with Postgraphile, Definitely. Postgraphile works with RLS and JWT too, so it should be achievable

throwaway888abc 6y ago

https://supabase.io/blog/2020/08/05/supabase-auth https://supabase.io/blog/2020/08/05/supabase-auth and discussion https://news.ycombinator.com/item?id=24072051 https://news.ycombinator.com/item?id=24072051

kiwicopple 6y ago
supabase cofounder here, thanks for the mention Also following up on OP's question: "Can anyone point to a good repo" One of our team (maintainer of PostgREST) has a great example repo to demonstrate an "advanced" scenario of building a social network with RLS: https://github.com/steve-chavez/socnet/ https://github.com/steve-chavez/socnet/ With Postgres you can elevate privileges to bypass RLS (which you may want to do in your middleware), so it's very flexible
- rahimnathwani 6y ago
  Thanks, both. I was wondering how we: 1. Avoid having a postgres user for each application user, and 2. Ensure that the right user is passed to RLS. You answered this in the post GP linked: "when a user signs up to your app/project, GoTrue assigns them a unique UID, saves them in the auth schema, and returns a JWT with the user’s details. Our client library attached this JWT to every API request. PostgREST validates the JWT then sends the request through to Postgres." I guess a similar approach would work with Postgraphile, assuming Postgraphile can validate the JWT and add 'SET ...' at the start of each query to Postgres. I'll take a look at the schema in the repo you linked. Thanks!
  - kiwicopple 6y ago
    Yes you're right. Supabase uses PostgREST behind the scenes. You can read about how the roles work here: https://postgrest.org/en/stable/auth.html https://postgrest.org/en/stable/auth.html Basically, you have a role which is an "authenticator" - this is the one you connect to in the database with. This has basically no privileges except to "change into" a new role. When an API route is called, PostgREST checks the headers to see if there is a JWT then gets the "role" out of the JWT. This JWT maps to a PostgreSQL role, which will have slightly higher privileges. Inside the JWT is the User ID too - so you can use this inside your RLS policies. So in summary: > 1. Avoid having a postgres user for each application user, and You only need 2 roles - an "autheticator" role, and an "authenticated" role > 2. Ensure that the right user is passed to RLS. It's encoded int he JWT. > I guess a similar approach would work with Postgraphile, Definitely. Postgraphile works with RLS and JWT too, so it should be achievable