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.
--
john r pierce, recycling bits in santa cruz