Re: Security and performance

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(at)scw(dot)org>
Director of Information Systems, Support Center of Washington