Re: [GENERAL] Are new connection/security features in order, givenconnection pooling?

On 1/10/2017 11:32 PM, Guyren Howe wrote:

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.

the only model I can see working is for the app server to maintain a pool of connections for active user sessions, and match the http user session to its corresponding database connection. now, http is nasty, its sessionless, and each user+browser can invoke multiple http connections concurrently, so with this model, your app server would need to ensure that each user session only can execute one piece of business logic at a time that touches the database.

you'd need cleanup code to close idle sessions since http sessions can just vanish without logging out or otherwise notifying you.    you'd need code to handle user sessions that come back after that cleanup code, so you can reestablish that sessions connection.

its all a big mess.    oh, and you'd need a seperate database connection, or some alternative persistent storage, to associate incoming http requests to the proper user so you'd even know which session connection to use.

I don’t think there's any turnkey solutions to this.