public inbox for [email protected]
help / color / mirror / Atom feedFrom: [email protected]
To: Dominique Devienne <[email protected]>
To: Alvaro Herrera <[email protected]>
Cc: Vijaykumar Jain <[email protected]>
Cc: pgsql-general <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Re: Fwd: A million users
Date: Wed, 13 Nov 2024 19:01:58 +0100
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAFCRh-9Le3fkJMWGuHdFPP-kfHdEAAchFZf0msEq3hNijixbFw@mail.gmail.com>
References: <CAM+6J97Jd5WnxdyFu1-s+wvJrWPL_YvP3yeuQVwy3HJvK=KUTQ@mail.gmail.com>
<[email protected]>
<CAFCRh-9Le3fkJMWGuHdFPP-kfHdEAAchFZf0msEq3hNijixbFw@mail.gmail.com>
Dominique Devienne:
> Hi. Sure, that's a good point, and a best practice IMHO.
> But I already do that, and the original question remain,
> i.e. how does PostgreSQL with thousands or millions of roles?
> In my use case, 1000 LOGIN users, and 10'000 schema related ROLEs,
> is possible, with can translate to millions of GRANTs.
>
> It matters to me especially, since I'm using pg_has_role() in RLS predicates,
> in addition to using ROLEs for access to schemas as usual (see above).
> I'm not in a point to test that myself at this time, but if anyone looked into
> how PostgreSQL scales with many roles (O(N)? O(logN)? O(NlogN)?),
> I'd be interested in pointers to that research or those benchmarks.
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.
Hope that helps.
Best,
Wolfgang
[1]: https://postgrest.org
view thread (9+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: Fwd: A million users
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox