On Wed, Nov 13, 2024 at 12:02 PM <walther@xxxxxxxxxxxxxxx> wrote:
I don't have any benchmarks, but the following data point:
We use PostgREST [1] which connects to the database with one
"authenticator" role. For each request it handles, it does a SET ROLE to
a role defined in the Authorization Header (JWT).
Some numbers we are running with currently:
- 1 authenticator role
- ~ 127.000 user roles granted to "authenticator"
- ~ 14.000 "scope" roles granted to the user roles (tenants, groups, ..)
- ~ 15 "context" roles granted to user roles ("admin", "user", ...)
- ~ 50 "access" roles granted to context roles ("view_x", "do_y", ...)
Only the access roles have any direct privileges granted.
We currently have ~ 700 RLS policies defined. Those are created TO the
context roles. The policies check the current role's scope roles to
select "allowed" rows.
In total, we have ~370.000 roles granted to each other (pg_auth_members).
Except for one thing, we have never had any real problems with this. We
didn't observe anything getting massively worse with many roles, even
though we use them extensively. RLS policies need to be carefully
written to get any performance, though.
The one problem we found is:
The first time the authenticator role does a SET ROLE in a session it's
**terribly** slow. With fewer users back then it took 6-7 minutes to do
it. Any SET ROLE afterwards in the same session would be fast. Even more
annoying - killing the session with SET ROLE running would not work
properly and leave zombie processes. Giving the authenticator role the
SUPERUSER privilege avoids the problem and makes it instant. However..
that's not very desirable.
There were some improvements, IIRC in the 17 cycle (?), in that area,
but I had not have the time to test it with that. We are still on v15
and the last time I tested this was ~ two years ago. I still wasn't able
to put together a simple reproducer either.
You should *probably* be better off with your different LOGIN roles, I
assume the role cache builds up much quicker in that case.
I'm really interested in how this works. Role-per-user or even the ability to have many roles (370k??) seems like a dream come true. But I always was wary of it because:
a) A connection-per-role hits the ceiling pretty quickly because connections can't be pooled and shared between users and take up a lot of memory etc.
b) One could try to get around this with an authenticator role as you describe, but isn't it then possible to do a RESET ROLE and then another SET ROLE to get access to another user? This of course would have to be through SQL injection or some such, but it seems like that defeats at least some of the purpose of RLS.
Did you find some way to prevent RESET ROLE? I once advocated for a NO RESET option on SET ROLE [1] so that RESET ROLE would be impossible for the rest of the session. Still think it would be helpful.
Thanks,
Eric
