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