Обсуждение: Web users as database users?
Hi - I’m working on a database that will be accessed via a web app. I’m used to a setup where there is one account/role that the app would use to access the database, but in my current scenario I’m interested in row level security and auditing enforced by the database. In this scenario, would it make sense to have a user account on the database to mirror the user account from the web app? Is that an unusual practice?
On 20/09/2019 12:50, David Gallagher wrote: > Hi - I’m working on a database that will be accessed via a web app. > I’m used to a setup where there is one account/role that the app would > use to access the database, but in my current scenario I’m interested > in row level security and auditing enforced by the database. In this > scenario, would it make sense to have a user account on the database > to mirror the user account from the web app? Is that an unusual practice? Not at all, we're doing it Tim Clarke Telephone: Witham: +44(0)1376 503500 | London: +44 (0)20 3009 0853 | Frankfurt: +49 (0)69 7191 6000 | Hong Kong: +852 58031687 | Toronto: +1 647 503 2848 Web: https://www.manifest.co.uk/ Minerva Analytics Ltd - A Solactive Company 9 Freebournes Court | Newland Street | Witham | Essex | CM8 2BL | United Kingdom ________________________________ Copyright: This e-mail may contain confidential or legally privileged information. If you are not the named addressee youmust not use or disclose such information, instead please report it to admin@minerva.info<mailto:admin@minerva.info> Legal: Minerva Analytics is the trading name of: Minerva Analytics Ltd: Registered in England Number 11260966 & The ManifestVoting Agency Ltd: Registered in England Number 2920820 Registered Office at above address. Please Click Here https://www.manifest.co.uk/legal/for further information.
On 20/9/19 2:50 μ.μ., David Gallagher wrote: > Hi - I’m working on a database that will be accessed via a web app. I’m used to a setup where there is one account/rolethat the app would use to access the database, but in my current scenario I’m > interested in row level security and auditing enforced by the database. In this scenario, would it make sense to have auser account on the database to mirror the user account from the web app? Is > that an unusual practice? I never regretted it. Just place pgbouncer in front and configure it right. -- Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt
> On Sep 20, 2019, at 6:15 AM, Tim Clarke <tim.clarke@minerva.info> wrote: > >> On 20/09/2019 12:50, David Gallagher wrote: >> Hi - I’m working on a database that will be accessed via a web app. >> I’m used to a setup where there is one account/role that the app would >> use to access the database, but in my current scenario I’m interested >> in row level security and auditing enforced by the database. In this >> scenario, would it make sense to have a user account on the database >> to mirror the user account from the web app? Is that an unusual practice? > > > Not at all, we're doing it > > > Tim Clarke > But you likely want a many-to-one mapping of actual user to permission group
Rob Sargent <robjsargent@gmail.com> writes:
>> On Sep 20, 2019, at 6:15 AM, Tim Clarke <tim.clarke@minerva.info> wrote:
> On 20/09/2019 12:50, David Gallagher wrote:
>>> ... would it make sense to have a user account on the database
>>> to mirror the user account from the web app? Is that an unusual practice?
>> Not at all, we're doing it
> But you likely want a many-to-one mapping of actual user to permission group
Yeah. You're likely to end up with a *lot* of user accounts in this
scenario. 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. However, you can add lots of users to any group
role. So put the users into appropriate group(s) and issue database
permissions on the group level.
regards, tom lane
On Fri, Sep 20, 2019 at 8:19 AM Tom Lane <tgl@sss.pgh.pa.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?
I used to use a different approach:
1. Create auth() pl/python procedure as follows:
create or replace
function auth(auser_id integer) returns void as $$
GD['user_id'] = auser_id
$$ language plpythonu;
This procedure is supposed to be called after a sucesseful authorisation (in a database or on application side).
2. Create get_current_user() procedure:
create or replace
function get_current_user() returns integer as $$
return GD.get('user_id')
$$ language plpythonu stable security definer;
Now you can get current user id from every SQL query or stored procedure. It works fast because Python shared array GD is always present in memory.
On 11. Mar 2020, at 15:46, Michael Lewis <mlewis@entrata.com> wrote:On Fri, Sep 20, 2019 at 8:19 AM Tom Lane <tgl@sss.pgh.pa.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?
Michael Lewis <mlewis@entrata.com> writes:
> On Fri, Sep 20, 2019 at 8:19 AM Tom Lane <tgl@sss.pgh.pa.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
Greetings, * Tom Lane (tgl@sss.pgh.pa.us) wrote: > 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. Right- keep the ACL lists small for individual objects, then grant membership in the appropriate roles to the actual users who log in and such. Having lots of roles works out a lot better that way. Thanks, Stephen