Re: Fwd: A million users

On Wed, Nov 13, 2024 at 12:02 PM <walther(at)technowledgy(dot)de> 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

[1]
https://www.postgresql.org/message-id/flat/CACA6kxgdzt-oForijaxfXHHhnZ1WBoVGMXVwFrJqUu-Hg3C-jA%40mail.gmail.com