Skip site navigation (1) Skip section navigation (2)

Rewrite SELECT WHERE clause on per-session bases. Modifing view source code in one session (user's) from another session (admin's)?

From: sftf <sftf-misc(at)mail(dot)ru>
To: pgsql-admin(at)postgresql(dot)org
Subject: Rewrite SELECT WHERE clause on per-session bases. Modifing view source code in one session (user's) from another session (admin's)?
Date: 2008-07-14 05:26:05
Message-ID: 2810039107.20080714112605@mail.ru (view raw or flat)
Thread:
Lists: pgsql-admin
Hi!
In my application's security scheme I'm planing to restrict users to view only particular "types" of records,
where "types" is set of WHERE predicates for each view, dynamically defined in application.
I believe it's most efficient way to do such things, instead of calling some decision function for each row
in view's SELECT, for example.

So, for these porposes I need some mechanism allowing "on the fly" modification of view's SELECT's.
At the beggining I review CREATE RULE ON SELECT variant, but rules are "database-wide" not "session-wide" and
SELECT rules don't allow WHERE clause.

My current solution is creating temporary viwes for each application user with
appropriate WHERE restrictions at the session begining  (but rules could be more convient).
By this way view permissions (what "types" user can select fron view) are can't be changed during session
(user can't modify view by design).

So question is: is it possible to modify view source code in one session (user's) from another session (admin's)?
Or may be more convenient way to rewrite SELECT WHERE clause on per-session bases exist (similar to RULES)?
Thanks.


Responses

pgsql-admin by date

Next:From: Paul LibbrechtDate: 2008-07-14 13:13:21
Subject: accidental drop table recoverable?
Previous:From: Michelle KonzackDate: 2008-07-12 13:37:52
Subject: How to setup tablespace and table partitioning in a template?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group