Re:Securing Your PostgreSQL DB with Roles and Privileges
by pphysch on Saturday August 12, 2023 @12:12PM
(#37101641)
Has anyone run into issues with too many roles? Like if you want to use RLS and have a role per application-user, with millions of users.
[ Parent
| on HN ]
Re:Securing Your PostgreSQL DB with Roles and Privileges
by paulryanrogers on Saturday August 12, 2023 @12:20PM
(#37101722)
Roles/users are backed by PG tables, so I imagine storing them should be no more difficult. (Assuming partitioning and other native features are available.) Yet I'm not sure how well it would scale all the row checks for read and write access, especially if you also use column level security.
Proxying connections for so many different users would be awkward too, though some proxies apparently can take on a different role for the session and revert when client-side disconnects.
Re:Securing Your PostgreSQL DB with Roles and Privileges
by programmarchy on Saturday August 12, 2023 @12:18PM
(#37101701)
Why have a role per user instead of just defining the row policy with the user directly?
Re:Securing Your PostgreSQL DB with Roles and Privileges
by hn_throwaway_99 on Saturday August 12, 2023 @12:24PM
(#37101766)
In Postgres a "role" really is equivalent to a user. A user in Postgres is just a role with the ability to log in.
Re:Securing Your PostgreSQL DB with Roles and Privileges
by programmarchy on Saturday August 12, 2023 @06:10PM
(#37104678)
Yes but I’m presuming the person I was responding to meant “role” in the context you’re talking about, but by “user” they meant a row in some “users” or “customers” table corresponding to their application. Questioning the need to “create role” for every application user.
Re:Securing Your PostgreSQL DB with Roles and Privileges
by pphysch on Saturday August 12, 2023 @12:21PM
(#37101740)
How does that work? With per "user" roles, I can SET ROLE "user-1000" and enter their authz context without changing any of my queries. How would this work without per-user roles?
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.
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.
Re:Securing Your PostgreSQL DB with Roles and Privileges
by aeonsky on Saturday August 12, 2023 @02:25PM
(#37102861)
Yes, my team had a direct issue with this on Aurora Postgres, at least. This is PG9 but then kept happening all the way into PG12 until we got rid of all but like 5 roles. Above like 4000 roles we experienced a significant lag on every query, sometimes on the order of seconds. At scaled somewhat linearly. I even wrote to Tom Lane and he said that area of Postgres is poorly optimized.