Further to my recent inquiries about leveraging Postgres’ security features from client apps, it appears to me that some likely reasonably simple changes to those features would enable client apps to better leverage what are otherwise great features.
*IF* we give our end users roles in Postgres and we were to connect *as them*, then we have a brilliant range of authorization features available, what with the lovely role inheritance and the row security features. *BUT* because of practical considerations having to do with connection pooling, no-one opens a different connection for each request. And there appears to be no other simple way to use the row security. This seems a bit tragic. I would love to have some way of, say, opening a connection without a default role, and having to supply a role name and token with each actual request. Or perhaps there is some other solution along those lines. In any event, if there is some simple way of solving the same problem with the current architecture, I’d love to hear it. Alexander pointed me at http://blog.2ndquadrant.com/application-users-vs-row-level-security/ <http://blog.2ndquadrant.com/application-users-vs-row-level-security/> but that is a level of complexity that scares me off, particularly for a security feature. At the very least, is there a simple and well-tested library I could load up, rather than rolling my own security feature? If there *isn’t* a simple way to use PG’s authorization features for the likes of a web app, is a feature request in order?
