Re: sepgsql and materialized views

2013/2/4 Kevin Grittner <kgrittn(at)ymail(dot)com>:
> Kohei KaiGai <kaigai(at)kaigai(dot)gr(dot)jp> wrote:
>> 2013/2/3 Kevin Grittner <kgrittn(at)ymail(dot)com>:
>>> I'm hoping that someone familiar with sepgsql can review this
>>> portion of the materialized view patch and comment on whether it is
>>> the best approach for dealing with the integration of these two
>>> features. Basically, the patch as it stands treats a materialized
>>> view as a table for purposes of sepgsql labels. I initially
>>> invented new lables, but Robert suggested that this would make
>>> materialized views unusable in an SE environment until the
>>> corresponding labels were added at the OS level. It seems sane to
>>> me because a materialized view exists on disk the same as a table,
>>> but is populated differently -- from a view-like rule.
>>>
>>> The portion of the patch which affects the contrib/sepgsql/ tree is
>>> attached.
>>>
>> Hi Kevin,
>>
>> Sorry, I have not been involved this discussion.
>> I briefly checked your patch. Indeed, it performs like a table, even though
>> it is named materialized-view.
>>
>> Probably, we have two standpoints.
>>
>> First, materialized-view shall have a security label corresponding to table,
>> and related checks handle references to materialized-views as if user
>> references regular-tables. This is an idea.
>> I briefly checked your latest patch. ExecRefreshMatView is a unique entry
>> point to update a particular materialized-view, and REFRESH MATERIALIZED
>> VIEW command is only way to kick this function. It also checks permissions to
>> reference underlying tables. So, it means update of materialized-view is a stuff
>> like writing a table with contents read from other tables by a particular users.
>>
>> However, I'm worried whether this design continues forever, or not.
>> IIRC, you have a plan to refresh materialized-view asynchronously using
>> background worker stuff, don't you?
>
> The goal is to build on this to support other timings of updates to
> the materialized view. In general, I think this will take the form
> of identifying, for the given rewrite rules associated with the
> materialized view, what changes to the underlying data can affect
> the view and determining whether incremental updates can be
> supported for the MV. If incremental update is possible, then
> various timings can be chosen for applying them. I think that all
> timing should go through a queue of change requests, although that
> is not yet designed, and I'm just waving my hands around and
> speculating about any details. Timings likely to be supported
> range from on-demand incremental updates (of all accumlated
> changes) to applying the changes from a transaction at COMMIT time,
> or possibly as the underlying changes are made within a
> transaction. I suspect that the most popular timing will involve a
> background process working from the queue asynchronously to keep
> the MV updated asynchronously but without any artificial delay.
>
> In all cases, a query against the view does not reach below the MV
> table to the underlying tables or functions used to build the data
> -- it will always read the "materialized" data, so I"m not sure
> that the normal concerns about leaky views apply here.
>
Let me confirm a significant point. Do you never plan a feature that
allows to update/refresh materialized-views out of user session?
I had an impression on asynchronous update of MV something like
a feature that moves data from regular tables to MV with batch-jobs
in mid-night, but under the privilege that bypass regular permission
checks.
It it is never planned, my concern was pointless.

>> Once we support an internal stuff (thus,
>> it can bypass valid security checks) to write out confidential contents into
>> unconfidential zone, it does not make sense to keep data confidentiality.
>
> If the person who creates the materialized view has authority to
> query the underlying tables, and grants access to the materialized
> view as desired, and those selecting from the materialized view
> only see the contents of the table which is being populated by the
> underlying pg_rewrite rule, I'm not understanding your concern.
> Can you elaborate?
>
My concern is future development that allows to update/refresh MV
asynchronously, out of privilege control.
As long as all the update/refresh operation is under privilege control
with user-id/security label of the current session, here is no difference
from regular writer operation of tables with contents read from other
tables.
Can I explain where is my concern about well?

BTW, please clarify expected behavior in case when MV contains
WHERE clause that returns different result depending on privilege
of current session, such as:
... WHERE underlying_table.uname = CURRENT_USER

It seems to me this MV saves just a snapshot from a standpoint of
a particular user who refreshed this MV; typically its owner.
If bob has privilege to reference this MV, he will see rows to be visible
for alice. Of course, it does not contradictory, because all alice doing
is just writing data she can see into a table being visible for public.

>> So, I'd like to suggest second way; that handles a materialized-view as a
>> view.
>
> I don't see why that should apply to anyone selecting from the MV.
> Certainly it must apply to anyone creating the MV. I'm not at all
> clear why anything special would be required for REFRESH or
> updating the underlying tables which will eventually generate
> incremental changes. I might be missing something, but could you
> explain any risks you see?
>
Even if MV's contents were moved in out of privilege controls,
we can ensure the current user has rights to reference data of MV,
as long as he has privileges to reference underlying data source.
On the other hand, it can make problems if some internal stuff
moves data from regular tables with "confidential" label into MV
with "unconfidential" label; that works official information leak channel.

Only point I'm concerned about is whether we will support a feature
that refresh materialized-view without appropriate privilege control,
or not.

Thanks,
--
KaiGai Kohei <kaigai(at)kaigai(dot)gr(dot)jp>