3 ms·

Main question, how do you deal with authorization? PostgreSQL Row Level Auth is horrible to use when you scale up compared to implementing auth on the app level

by melony 4y ago

Main question, how do you deal with authorization? PostgreSQL Row Level Auth is horrible to use when you scale up compared to implementing auth on the app level. Both Supabase and Hasura suffer from this problem. The moment your data auth needs become slightly complex, you end up with a mess of calling out to external functions and ad-hoc SQL functions in the database.

dragonwriter 4y ago
> PostgreSQL Row Level Auth is horrible to use when you scale up compared to implementing auth on the app level. In what way?
- melony 4y ago
  When you have RBAC based on multiple different conditions, e.g. "does user have XYZ?" you will have to architect your entire app around putting these claims into JWT so the GraphQL engine can properly map those onto RLS. In a traditional app auth is on the app level and defined in code, not a mix of code, SQL, JWT claims and Hasura/Supabase configs.
  - rlili 4y ago
    You don't necessarily have to put those claims into a JWT (other than the user ID). You can just store the "XYZ" that the user has into a table.
    - burggraf 4y ago
      That is definitely true (and probably easier than doing custom claims), but the reason I use claims is that it saves doing a join against that extra table inside your RLS policies, which can sometimes have a big performance impact. Those claims are also available on the client side, too, saving a round trip to the server if you need to check the claims.
  - dragonwriter 4y ago
    > When you have RBAC based on multiple different conditions, e.g. "does user have XYZ?" you will have to architect your entire app around putting these claims into JWT so the GraphQL engine can properly map those onto RLS. If it's RBAC, why am I not just giving creating per-user postgres roles inheriting from appropriate roles for the relevant features? > In a traditional app auth is on the app level and defined in code, not a mix of code, SQL, JWT claims and Hasura/Supabase configs. Doing authz via DB-side permissions attached to per user DB accounts is an equally, and more well-established, traditional app approach.
  - burggraf 4y ago
    I just wrote an article on this (adding custom claims to the JWT) for Supabase projects: https://dev.to/supabase/supabase-custom-claims-34l2 https://dev.to/supabase/supabase-custom-claims-34l2
ccakes 4y ago
> ad-hoc SQL functions in the database If you use tools like Sqitch, Metagration or any other suitable migration management system then functions in your database stop being scary. Sqitch: https://sqitch.org/ https://sqitch.org/ Metagration: https://github.com/michelp/metagration https://github.com/michelp/metagration

by melony 4y ago

dragonwriter 4y ago

> PostgreSQL Row Level Auth is horrible to use when you scale up compared to implementing auth on the app level. In what way?

melony 4y ago
When you have RBAC based on multiple different conditions, e.g. "does user have XYZ?" you will have to architect your entire app around putting these claims into JWT so the GraphQL engine can properly map those onto RLS. In a traditional app auth is on the app level and defined in code, not a mix of code, SQL, JWT claims and Hasura/Supabase configs.
- rlili 4y ago
  You don't necessarily have to put those claims into a JWT (other than the user ID). You can just store the "XYZ" that the user has into a table.
  - burggraf 4y ago
    That is definitely true (and probably easier than doing custom claims), but the reason I use claims is that it saves doing a join against that extra table inside your RLS policies, which can sometimes have a big performance impact. Those claims are also available on the client side, too, saving a round trip to the server if you need to check the claims.
- dragonwriter 4y ago
  > When you have RBAC based on multiple different conditions, e.g. "does user have XYZ?" you will have to architect your entire app around putting these claims into JWT so the GraphQL engine can properly map those onto RLS. If it's RBAC, why am I not just giving creating per-user postgres roles inheriting from appropriate roles for the relevant features? > In a traditional app auth is on the app level and defined in code, not a mix of code, SQL, JWT claims and Hasura/Supabase configs. Doing authz via DB-side permissions attached to per user DB accounts is an equally, and more well-established, traditional app approach.
- burggraf 4y ago
  I just wrote an article on this (adding custom claims to the JWT) for Supabase projects: https://dev.to/supabase/supabase-custom-claims-34l2 https://dev.to/supabase/supabase-custom-claims-34l2

ccakes 4y ago

> ad-hoc SQL functions in the database If you use tools like Sqitch, Metagration or any other suitable migration management system then functions in your database stop being scary. Sqitch: https://sqitch.org/ https://sqitch.org/ Metagration: https://github.com/michelp/metagration https://github.com/michelp/metagration