Re: sepgsql and materialized views - Mailing list pgsql-hackers

Kohei KaiGai <kaigai@kaigai.gr.jp> wrote:
> 2013/2/3 Kevin Grittner <kgrittn@ymail.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.

> 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?

> 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?

> SELinux checks db_view:{expand} permissions and relevant permissions
> towards underlying tables. I don't think it is hard to implement because
> relation->rd_rules holds Query tree to reference underlying tables.
>
> Can you wait for a week? I'll adjust contrib/sepgsql portion to fit
> materialized-view with matter of existing view.

Before we code a different alternative, I would like to understand
why.  What risks do you see, exactly?

-Kevin