4 ms·
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.
by pphysch 3y ago
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.
- programmarchy 3y agoWhy have a role per user instead of just defining the row policy with the user directly?
- hn_throwaway_99 3y agoIn Postgres a "role" really is equivalent to a user. A user in Postgres is just a role with the ability to log in.
- programmarchy 3y agoYes 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.
- pphysch 3y agoHow 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?
- paulryanrogers 3y agoRoles/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.
- anarazel 3y agoFWIW, 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.
- pphysch 3y agoDo I have to add that context to every query, or is it something I can set per cursor/transaction?
- anarazel 3y agoEither. 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.
- pphysch 3y agoOh 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!
- edmundsauto 3y agoSupabase has pretty good docs and a nice Ui to play around with this, btw.
- aeonsky 3y agoYes, 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.
- pphysch 3y agoInteresting. Why so many roles initially, and how did you safely consolidate to 5?