Re: [ADMIN] Re: 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-ru-general(at)postgresql(dot)org
Subject: Re: [ADMIN] Re: 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-23 07:54:55
Message-ID: 1001607139.20080723135455@mail.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-ru-general

>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

Browse pgsql-ru-general by date

  From Date Subject
Next Message Ivan Zolotukhin 2008-07-25 11:41:21 Онлайн-конференция с ведущими экспертами PostgreSQL
Previous Message silly_sad 2008-07-03 13:05:30 Re: [pgsql-ru-general] Re[2]: [pgsql-ru-general] Роли: управление доступом к другим ролям. Роли как объекты системы безопасности.