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.
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.
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.
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?
