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
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] Роли: управление доступом к другим ролям. Роли как объекты системы безопасности. |