Hacker News Reader

Comment by pphysch | Hacker News Reader

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.

View on HN

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.

Interesting. Why so many roles initially, and how did you safely consolidate to 5?

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.

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.

Do I have to add that context to every query, or is it something I can set per cursor/transaction?

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.

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!

Supabase has pretty good docs and a nice Ui to play around with this, btw.

Why have a role per user instead of just defining the row policy with the user directly?

In Postgres a "role" really is equivalent to a user. A user in Postgres is just a role with the ability to log in.

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.

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?

Reply on news.ycombinator.com