Web users as database users?

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 5803 1687 | 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 you must 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 Manifest Voting 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/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?

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.

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