Re:Securing Your PostgreSQL DB with Roles and Privileges
by anarazel on Saturday August 12, 2023 @01:30PM
(#37102383)
FWIW, you don't need to use database roles if you want to use RLS. You can instead have some other context indicating the current "application user" and use that in your RLS policies.
[ Parent
| on HN ]
Re:Securing Your PostgreSQL DB with Roles and Privileges
by pphysch on Saturday August 12, 2023 @01:40PM
(#37102478)
Do I have to add that context to every query, or is it something I can set per transaction?
Re:Securing Your PostgreSQL DB with Roles and Privileges
by anarazel on Saturday August 12, 2023 @02:06PM
(#37102703)
Either. What the best approach is depends a bit on your needs / security model.
You can e.g. something like storing the session "application user" in a configuration variable (SET myapp.rls_user =...). But if the user can influence the SQL and that's part of the threat model, you need to do more, because that could be changed by further SQL.
Another solution is to just have a session level temp table indicating the current application user.
Re:Securing Your PostgreSQL DB with Roles and Privileges
by pphysch on Saturday August 12, 2023 @02:17PM
(#37102778)
Oh sweet. That approach makes a lot more sense. Access would be through a server-side ORM so users would not be able to run arbitrary SQL. Thanks!
Re:Securing Your PostgreSQL DB with Roles and Privileges
by edmundsauto on Saturday August 12, 2023 @05:06PM
(#37104227)
Supabase has pretty good docs and a nice Ui to play around with this, btw.