Re: [pgsql-ru-general] [ADMIN] Re: Rewrite SELECT WHERE clause on per-session bases. Modifing view source code in one session (user's) from another session (admin's)?

>Search pgFoundry for veil; I believe it will do what you want.
Veil uses a functions calls in views to restrict access.
So it will be more slowly then a explicite conditions in views.

From demo:
====================================
create or replace
function i_have_global_priv(int4) returns bool as '
declare
priv_id alias for $1;
connection_id int4;
result bool;
begin
select into connection_id, result
veil_int4_get(''person_id''),
veil_bitmap_testbit(''global_context'', priv_id);
if connection_id is null then
return false;
else
return result;
end if;
end;
' language plpgsql
stable
security definer;
====================================

and then
====================================
create view privileges(
privilege_id,
privilege_name) as
select privilege_id,
privilege_name
from vdemo_owner.privileges
where i_have_global_priv(10001);
^^^^^^^^^^^^^^^^^^^^^^^^^^
====================================

so we have function call (with even more selects within it) for EACH row in the protected table!
And the planner cannot use indexes and optimize query - it will be FULL SCAN on a table.
Inshort veil scheme is:
create view data_view(
field1,
field2) as
select field1,
field12
from table
where decision_function(something_about_record);

create or replace
function decision_function returns bool as '
begin
select_permisssions_from_some_tables_check_and_return_yes_or_now
...
end

I wish to avoid this: apllication atumatically create temporary view for each user's session with the built in checks like in this example:

create view data_view(
field1,
field2) as
select field1,
field12
from table
where
field1 = value1 and field2 = value2 ... other conditions; (or what ever condition or even joins I want)

So planner will have all information for optimization.

Certainly conditions will undertake from some policy tables,
but it will occur ONLY ONCE at view creation at session begining.
I only would like to have flexible way to create/modify views on the fly.

And Vail is not in standart PostgreSQL...
And I don't wanna (re)compiling anything

--
Sent via pgsql-ru-general mailing list (pgsql-ru-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-ru-general