On 24 December 2015 at 11:03, Caleb Meredith <calebmeredi...@gmail.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. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
