Re: Row level security (RLS) for updatable views

On 24 December 2015 at 11:03, Caleb Meredith <calebmeredith8(at)gmail(dot)com> wrote:
> There should be a way to do separate read/write security barriers for
> updatable views. I'll start by addressing the problem, state some potential
> solutions with the current software, and finally end with 2 proposals to
> solve the problem in the best way possible.
>
> ## Problem
> I want the user to see more rows then they can edit, a common scenario. Like
> a blog, the user can read all the posts but they can only edit their own.
>
> Users of my database are directly reading and writing to and from views, I
> have chosen to use views to hide implementation details from the user and
> add extra metadata columns. However, in doing so I have lost the row level
> security capabilities for tables that postgres 9.5 provides.
>
> More specifically I'm using the [PostgREST][1] API which detects relations
> in a postgres schema and exposes an HTTP REST interface.
>
> ## Exploration
> I asked [this][2] question on stack overflow for clarification on why
> currently postgres does not allow row level security for views. I also
> explored some other mechanisms to provide this functionality:
>
> 1. Row level security on the parent table: This removes information about
> the user making the request and mixes view schema details with table schema
> details, I'd prefer to not have to do that.
> 2. Two views: One which is the general selection view, and the second which
> is a security definer view which selects everything from the general view
> and adds a where clause. This is what I'm currently using, but it's not
> optimal because it requires a naming convention (I'm using "people" and
> "~people") and it requires a little more domain knowledge + decreases
> interoperability.
> 3. Triggers/rules: Use a trigger to override the behavior of the view when
> writing to the database. This requires 3 triggers/rules (INSERT, UPDATE,
> DELETE) and kinda defeats the entire purpose of having an updatable view.
> 4. Conditional triggers/rules: Have a trigger which throws an error when the
> condition is true (using the WHEN keyword). This just doesn't work because
> a) triggers can only replace operations on views (no BEFORE or AFTER) and b)
> the WHEN keyword doesn't work on triggers which replace operations.
>
> ## Proposal 1: Add RLS to views
> Therefore I propose adding support for to views. The syntax would be the
> same:
>
> ALTER VIEW … ENABLE ROW LEVEL SECURITY;
>
> and the corresponding:
>
> CREATE POLICY …
>
> command would work the same. The most important part of this implementation
> would be that the row level security `current_user` be the invoker and *not*
> the definer.
>
> Theoretically I think this would be simple enough to implement as row level
> security seemingly is just adding a couple extra WHERE conditions to a query
> on the relation, and there is already some support for views which are
> security definers. Row level security of this nature could only be enabled
> on updatable views.
>
> This would be my preferred solution to the problem.
>
> ## Proposal 2: Different where condition for reads and writes
> This might be simpler to implement, but also not as verbose as the first
> proposal. It involves extending the CREATE VIEW syntax for updatable views
> with a WITH BARRIER expression. Similar to how WITH CHECK works for RLS
> policies it would be added to the view's select statement on INSERT, UPDATE,
> and DELETE. It might look like the following:
>
> CREATE VIEW posts
> WITH (check_option = 'cascaded', security_barrier)
> AS SELECT p.id, p.headline, p.text
> FROM private.posts as p
> WITH BARRIER (p.author = current_user);
>
> This would allow any user to look at all the views, but only ever write to
> their own. All operations of the view are the same except the barrier is
> appended to INSERT, UPDATE, and DELETEs.
>
> The weakness of this approach comes in the following:
>
> CREATE VIEW posts
> WITH (check_option = 'cascaded', security_barrier)
> AS SELECT p.id, p.headline, p.text
> FROM private.posts as p
> WHERE p.published = true
> WITH BARRIER (p.author = current_user);
>
> The above view would show all published posts to all users, but owners of
> unpublished posts could not edit their posts. This might be solved by making
> the barrier action specific so maybe WITH BARRIER INSERT, UPDATE, DELETE (…)
> and WITH BARRIER SELECT (…)?
>
> This second proposal might be easier to implement and works well with how
> views currently function, however it is not preferred because it cannot add
> different barriers for different users.
>
> Thanks for your time, these are just some rough ideas I have had to solve my
> problem. I hope this can be resolved for all developers looking to build
> advanced systems with postgres.
>
> – Caleb Meredith
>
> [1]: https://github.com/begriffs/postgrest
> [2]:
> http://stackoverflow.com/questions/33858030/why-isnt-row-level-security-enabled-for-postgres-views
>

Did anything ever come out of this post?

I'm in a very similar situation.

A different useful solution might be if views had a 'security invoker' mode.