[GENERAL] Re: Are new connection/security features in order, given connectionpooling?

On 01/12/2017 03:12 AM, Karl Czajkowski wrote:
> I can relate to the original plea from my own exploration of this
> topic.
>
> Before I get into that, I will mention as an aside that to date we
> have found RLS to be really slow for web client authorization, given
> that we have to use session parameters to store web client context
> and there doesn't seem to be a way to get the query planner to treat
> these settings as constants nor to lift static parts of RLS policy
> out of per-row loops. It's so slow that I wonder whether future
> releases will improve it before we are forced to move authorization
> back into the client-side application logic, making our generated SQL
> much more complex but better tuned to the query planner's
> capabilities.
>

I'm no expert in RLS, but I know others have been making the planner
smarter when it comes to RLS. It would be helpful to compile this into a
bunch of examples with queries/explain plans, and report it to
pgsql-performance. It'd provide practical feedback from real-world RLS
use, and maybe there already is a reasonable way to improve the plans.

I assume you use current_config() function to access the parameters? I
see that while it's marked as stable, it's not marked as leak-proof,
which might be why the planner can't apply some of the optimizations
when used in a RLS policy.

 >
> As background, our web applications essentially compile new SQL
> queries on the fly and send them through to PostgreSQL. There is a
> lot of metaprogramming involved in generating SQL statements to
> embody the work we want done on behalf of our clients, and this
> doesn't fit well into traditional prepared statements etc.
>

Sure, a lot of systems generate queries on the fly. Also, if the main
problem is poor plan choice due to RLS, I'm not sure how prepared
statements could help with that.

 >
> The web service needs to assert the client context and the
> client-level statements we generate and execute should not be able
> to override this. The service logic to determine and assert client
> context (once per web request) is dramatically simpler than the
> service logic producing all the client-level SQL statements, and it
> would be nice to have a restricted execution context to isolate the
> two kinds. We also like the idea that policy enforcement mechanisms
> could be applied orthogonally to the generated client-level SQL
> statements, as it feels like a better defense-in-depth architecture
> and is also easier to reason about. To do so in our service logic
> would mean replicating more and more of the query parsing and engine
> to do general query rewriting.
>
> So, it would be nice to have a tunneling mechanism where I can
> distinguish the control-plane operations I am performing from the
> application operations I am translating and pushing down to
> PostgreSQL on behalf of the web client. In my case, I might want to
> perform a mixture of service-level and client-level statements within
> the same transaction. The client-level statements cannot control
> transactions.
>
> I could imagine something like a two-level feature set. At the
> top-level in the connection, we can statefully manipulate our
> security contexts, set configurable privilege masks for the
> second-level execution context, set effective roles (reversibly, as
> today), manage transactions, etc. With some kind of nested execution
> block, we could submit less trusted statements to run within the
> second-level execution context:
>
>    EXECUTE RESTRICTED $guard$ app_query... $guard$ ;
>
> This would take the guarded query string, restart a safe parser on
> it, and only on successful parse go forward with planning and
> executing it in the restricted mode that has been configured for the
> connection.
>
> Eventually, I could see wanting more resource management controls on
> this restricted context too, i.e. setting limits of CPU/IO/RAM
> consumption or execution time. Both planning time limits (reject
> based on estimates) and runtime (abort query if limit is reached).
>

That might be an interesting feature, but it's also significantly more
complex than the topic of implementing a safe context for secrets,
making RLS less problematic with connection pools.


regards

--
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services