public inbox for [email protected]  

help / color / mirror / Atom feed

From: 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 11:52:08 +0100
Message-ID: <CAFCRh-9Le3fkJMWGuHdFPP-kfHdEAAchFZf0msEq3hNijixbFw@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CAM+6J97Jd5WnxdyFu1-s+wvJrWPL_YvP3yeuQVwy3HJvK=KUTQ@mail.gmail.com>
	<[email protected]>

On Wed, Nov 13, 2024 at 11:29 AM Alvaro Herrera <[email protected]> wrote:
> On 2024-Nov-13, Vijaykumar Jain wrote:
> > I tried to grant select permissions to 5000 different roles on one table,
> > 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 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.

Thanks, --DD

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]
  Subject: Re: Fwd: A million users
  In-Reply-To: <CAFCRh-9Le3fkJMWGuHdFPP-kfHdEAAchFZf0msEq3hNijixbFw@mail.gmail.com>

* 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