Use indirection in RLS policies for figuring out user_id
benjie opened this issue · 4 comments
Currently you use current_setting('jwt.claims.user_id')
directly in your RLS policies. I recommend instead using a level of indirection, such as:
create or replace function auth_public.current_user_id() returns integer as $$
select current_setting('jwt.claims.user_id', true)::integer;
$$ language sql stable;
CREATE POLICY update_user ON auth_public.user FOR UPDATE TO auth_authenticated
using (id = current_user_id());
This means that instead of having to update all your policies to make a change you only have to replace one function. Reasons you might make a change include:
- accounting for new functionality, e.g. adding the
missing_ok
field as I have done above - allowed other methods of indicating the current user_id (e.g. for microservices that perform tasks against the db); this might just involve changing the function to use a
coalesce(current_setting(...), current_setting(...))
call - if you decide to replace the current solution with a more secure one, such as one that looks up the current user ID based on a secure session ID hash (preventing the risk SQL injection allowing the user to switch to a different user ID via
set local jwt.claims.user_id to 7
)
I hope this is helpful 👍
Awesome! I'll take a stab at it when I get a second. I wasn't particularly familiar with the current_setting
stuff in Postgres, so it was mostly a copy/paste job from the tutorial. PostGraphQL has been an excellent avenue to learn about Postgres though. Thanks for all the work @benjie, I appreciate you taking a peek.
No problem; was just a quick scan as I was trying to figure out who uses PostGraphQL. 👍
Just got around to implementing this, upgraded to Postgres 9.6 to see what the missing_ok
was about - that's handy in this context. Only realized after implementing it where the error was originally manifesting. Thanks for the tip.
You’re welcome 👍