Thread: Security and performance

I'm writing the database backend to a web application.  Being paranoid I
want to limit the damage/exposure that the application can do.

One way would be to create a database user for each application user
(i.e. login name) and to create views for each user, not giving them any
permissions on sensitive tables but only letting them see their own data
through the views.  How would that affect the database as the number of
users climbs through the hundreds to the thousands?  Would the thousands
of views slow the database down?  Is there an upper limit to the number
of views?

--
Bruce

Bitterly it mathinketh me, that I spent mine wholle lyf in the lists
against the ignorant.
        -- Roger Bacon, "Doctor Mirabilis"

On Wed, 2 May 2001 pgsql@itsbruce.uklinux.net wrote:

> I'm writing the database backend to a web application.  Being paranoid I
> want to limit the damage/exposure that the application can do.
>
> One way would be to create a database user for each application user
> (i.e. login name) and to create views for each user, not giving them any
> permissions on sensitive tables but only letting them see their own data
> through the views.  How would that affect the database as the number of
> users climbs through the hundreds to the thousands?  Would the thousands
> of views slow the database down?  Is there an upper limit to the number
> of views?

Instead of making a kajillion views, could you use a RULE that
checks their identity against some field, and either does the right thing
or does nothing, depending on this info?

It would seem *MUCH* easier to maintain.

--
Joel Burton   <jburton@scw.org>
Director of Information Systems, Support Center of Washington

On Wed, May 02, 2001 at 05:59:37PM -0400, Joel Burton wrote:
>
> Instead of making a kajillion views, could you use a RULE that
> checks their identity against some field, and either does the right thing
> or does nothing, depending on this info?
>
> It would seem *MUCH* easier to maintain.

I considered it, but it could have a negative impact on performance.
Consider: the rule would recheck for each record.  The rule has to be
implemented in each table which the user accesses.

A view, in contrast, only checks the user once.  It pulls the data a
user needs to see (and only that data) together.

I may do this with a small set of randomly generated postgresql users
rather than having a one-to-one postgres-to-application mapping.  When
the application validates a user login I could have the database
create a new user (triggered by the application reading from or writing
to a special view) with a randomly generated name and create views for
that database user which could only see the data of the logged-in
application user.  It would take some careful coding but be more
manageable.

--
Bruce

I see a mouse.  Where?  There, on the stair.  And its clumsy wooden
footwear makes it easy to trap and kill.
        -- Harry Hill

> Instead of making a kajillion views

I limit what others can see using php scripting:
I set a level [1...9] (1= generic employee, 3= manager, 5 = principle)
and groups [HR, Tech, Accounting].

Then I can say if level > 3 and group == 'HR' do and show this and that.
I find this pretty powerful :)

Jeff

On Wed, May 02, 2001 at 05:50:49PM -0700, jeff.fitzmyers@managestar.com wrote:
> > Instead of making a kajillion views
>
> I limit what others can see using php scripting:

Wrong approach for me.  I am not writing the php scripts and I don't
want to trust those who are.  Even if I were writing the scripts, I
would not trust me.  Get the security right on the database side and you
don't have to worry about mistakes on the application side.

If I wanted to trust the application, I'd be using mySQL.

--
Bruce

It is impolite to tell a man who is carrying you on his shoulders that
his head smells.

On Thu, 3 May 2001, Bruce Richardson wrote:

> On Wed, May 02, 2001 at 05:59:37PM -0400, Joel Burton wrote:
> >
> > Instead of making a kajillion views, could you use a RULE that
> > checks their identity against some field, and either does the right thing
> > or does nothing, depending on this info?
> >
> > It would seem *MUCH* easier to maintain.
>
> I considered it, but it could have a negative impact on performance.
> Consider: the rule would recheck for each record.  The rule has to be
> implemented in each table which the user accesses.
>
> A view, in contrast, only checks the user once.  It pulls the data a
> user needs to see (and only that data) together.
>
> I may do this with a small set of randomly generated postgresql users
> rather than having a one-to-one postgres-to-application mapping.  When
> the application validates a user login I could have the database
> create a new user (triggered by the application reading from or writing
> to a special view) with a randomly generated name and create views for
> that database user which could only see the data of the logged-in
> application user.  It would take some careful coding but be more
> manageable.

Yep -- confirmed.

Trying this:


CREATE FUNCTION user_test () RETURNS bool AS '
begin
  raise notice ''foo!'';
  return true;
end;
' language 'plpgsql';

CREATE TABLE test (
  id int,
  f1 text );

CREATE VIEW test_security AS SELECT *, user_test() FROM test;


should work (assuming you have a better user_test ;-) ), but it does call
the function every single time.

However, unless you're results are very large, or speed is a dramatic
factor, this wouldn't seem so bad, if you could (a) code the function in
C, and (b) have the security check not involve any other table access.

Is this possible?

--
Joel Burton   <jburton@scw.org>
Director of Information Systems, Support Center of Washington