Re: [HACKERS] Row level security (RLS) for updatable views

[Daurnimator]

On 24 December 2015 at 11:03, Caleb Meredith  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]:
> 

[HACKERS] Row level security (RLS) for updatable views

[Caleb Meredith]

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