MIME-Version: 1.0
References: <CAM+6J97Jd5WnxdyFu1-s+wvJrWPL_YvP3yeuQVwy3HJvK=KUTQ@mail.gmail.com>
 <202411131029.qchduffwgzhm@alvherre.pgsql>
In-Reply-To: <202411131029.qchduffwgzhm@alvherre.pgsql>
From: Dominique Devienne <ddevienne@gmail.com>
Date: Wed, 13 Nov 2024 11:52:08 +0100
Message-ID: <CAFCRh-9Le3fkJMWGuHdFPP-kfHdEAAchFZf0msEq3hNijixbFw@mail.gmail.com>
Subject: Re: Fwd: A million users
To: Alvaro Herrera <alvherre@alvh.no-ip.org>
Cc: Vijaykumar Jain <vijaykumarjain.github@gmail.com>, 
	pgsql-general <pgsql-general@postgresql.org>, "kaare@jasonic.dk" <kaare@jasonic.dk>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CAFCRh-9Le3fkJMWGuHdFPP-kfHdEAAchFZf0msEq3hNijixbFw%40mail.gmail.com>
Precedence: bulk

On Wed, Nov 13, 2024 at 11:29=E2=80=AFAM Alvaro Herrera <alvherre@alvh.no-i=
p.org> wrote:
> On 2024-Nov-13, Vijaykumar Jain wrote:
> > I tried to grant select permissions to 5000 different roles on one tabl=
e,
> > It failed with row size too big already at 2443.
>
> But you can grant select to one "reader" role, and grant that one role
> to however many other roles you want.  This way you can have an
> arbitrary number of roles with indirect access to the table.  In
> real-world usage, this is more convenient that granting access to
> individual roles on individual tables; likely, you'll grant access to
> sets of tables/views/functions/etc rather than exactly one, and you can
> manage that more easily if you have one intermediate role to modify than
> if you have to mess with 5000 individual roles.

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 predicate=
s,
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 i=
nto
how PostgreSQL scales with many roles (O(N)? O(logN)? O(NlogN)?),
I'd be interested in pointers to that research or those benchmarks.

Thanks, --DD