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]:
> 

Re: [HACKERS] libpq bad async behaviour

[Daurnimator]

On 14 January 2015 at 08:40, Andres Freund and...@2ndquadrant.com wrote:

 I think that kind of solution isn't likely to be satisfying. The amount
 of porting work is just not going to be worth the cost. And it won't be
 easily hideable in the API at all as the callers will expect a normal
 fd.


All that consumers of the API need is something they can `select()` or
equivalent on.


  Yeah, this is a problem. Fixing it isn't easy, though, I think.

 I think
 extern PostgresPollingStatusType PQconnectPoll(PGconn *conn);
 has the right interface. It returns what upper layers need to wait
 for. I think we should extend pretty much that to more interfaces.


This would be a fine solution. That enum indeed has the correct
values/semantics.


 This
 likely means that we'll need extended versions of PQFlush() and
 PQconsumeInput() - afaics it shouldn't be much more?


PQping?
PQconnectPoll already has it.

Though, I think we could probably even reduce this down to a single common
function for all cases:
PQpoll() or similar.

[HACKERS] libpq bad async behaviour

[Daurnimator]

I'm worried about libpq blocking in some circumstances; particularly
around SSL renegotiations.
This came up while writing an async postgres library for lua, I
realised that this code was dangerous:
https://github.com/daurnimator/cqueues-pgsql/blob/ee9c3fc85c94669b8128386d99d730fe93d9dbad/cqueues-pgsql.lua#L121


e.g. 1:
When a PQ connection is in non-blocking mode, PQflush returns 1, the docs say:
 wait for the socket to be write-ready and call it again
However, if the SSL layer is waiting on data for a renegotiation,
write readiness is not enough:
Waiting for POLLOUT and calling PQflush again will (untested) just
return 1 again, and continue to do so until data is recieved.
This is a busy-loop, and will block the host application.

e.g. 2:
An SSL renegiation happens while trying to receive a response.
According to 'andres' on IRC, inside of `PQisBusy` there is a busy loop:
 14:22:32 andres You'll not see that. Even though the explanation for it is 
 absolutely horrid.
 14:23:32 andres There's a busy retry loop because of exactly that reason 
 inside libpq's ssl read function whenever it hits a WANT_WRITE.
 14:23:58 daurnimator so... libpq will block my process? :(
 14:24:25 andres daurnimator: That case is unlikely to be hit often luckily 
 because of the OS buffering. But yea, it's really unsatisfying.
 14:26:06 andres daurnimator: I think this'll need a new API to be properly 
 fixed.


One idea that came to mind if we want to keep the same api, is to hide
the socket behind an epoll file descriptor,
they always poll read ready when an fd in their set becomes ready.
I think this is also possible for kqueue on bsd, ports on solaris and
IOCP on windows.


Regards,
Daurnimator.


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers