On Jan 11, 2017, at 0:07 , John R Pierce <pierce@xxxxxxxxxxxx> wrote:
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.
I’m not following. What I would like is just a lightweight way to switch the connections to use a different role, or some moral equivalent, that would prevent an SQL injection from wrecking havoc. I’m not proposing anything that will change anything else about how the application is using the database.
SET ROLE doesn’t work, because an SQL injection can just SET ROLE back to the privileged user.
I would like a regime where there is no permanent privileged relationship between the client application and the database; a user would need to supply validating information that we can use to switch a connection to something with minimal privileges for just that role, for the duration of one session or transaction.
Opening a new connection each time would do that, but is too heavyweight. I’m just looking for a connection pool-friendly moral equivalent. |
