Re: Web users as database users?

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Michael Lewis <mlewis(at)entrata(dot)com>
Cc: "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Re: Web users as database users?
Date: 2020-03-11 15:25:20
Message-ID: 6223.1583940320@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Michael Lewis <mlewis(at)entrata(dot)com> writes:
> On Fri, Sep 20, 2019 at 8:19 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> There is a restriction on how many distinct GRANTs you can
>> issue against any one object --- performance will get bad if the ACL
>> list gets too large.

> Any ballpark numbers here? Are we talking 50 or 8000?

More like the former. aclitems are 12 bytes each, so once you get
past ~100 items in an object's ACL list, the array is going to
get large enough to be subject to toasting, greatly increasing the
time to access it.

That's not even counting the question of whether scanning lots
of items to determine access privileges is expensive. The code
that deals with ACL lists isn't terribly well optimized.

I'm not aware that anyone's done formal performance testing,
but if you want to have a lot of roles in the system, my
expectation is that you'd be better off granting privileges
to a small number of group roles and then granting group
role membership as appropriate.

regards, tom lane

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Stefan Blanke 2020-03-11 15:26:09 Re: ERROR: invalid memory alloc request size 1073741824
Previous Message Torsten Krah 2020-03-11 15:13:27 Re: Force WAL cleanup on running instance