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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
