On Jan 14, Tomas Vondra modulated: ... > 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. > Sorry for being unclear, I was trying to respond to too many sub-topics at once. The performance comment was just a tangential aside from my experiments using RLS for web client authorization. Indeed, I use the current_setting() function in RLS policy statements to access ephemeral web client context stored via set_config(). In the rest of my message, I was trying to respond to the topic of being able to "set role" and limit the connection privileges to reflect a web client's identity for RLS or other fine-grained authorization. I thought this was the main topic of the thread---the desire to push down web client authorization decisions into the normal postgres mechanisms, and all the problems arising from this sort of approach. The concern was raised about the risk of the subsequent queries being able to reverse the "set role" to subvert such authorization. Others in the thread provided the common advice of parametric queries to prevent query injection, but I think you need more than that. I think one would like a stronger isolation between the connection setup/security config layer and the fancier (and possibly flawed) application layer. We struggle with this because all these mechanisms are multiplexed as commands in one SQL connection, instead of having an in-band versus out-of-band protocol layering available to the web application, so the more risky data-dependent SQL can flow in-band while the basic request-handler lifecycle could configure the security context out-of-band. > 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. > I was thinking that this mechanism would support the usual shared connection pool to support all web users. The web server would repeatedly redefine the restricted security context and execute application queries under that context. The pooling problems come from the suggestion to open connections w/ different per-user authentication and then not have any user-switching that can easily reconfigure the connection, right? karl -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
