"David G. Johnston" <david.g.johnston@gmail.com> writes: > Would a scheme whereby you basically only get to SET ROLE one time work? > Basically the connection layer logs in and immediately SET SESSION > AUTHORIZATION AND SET ROLE [WITH SETTINGS?] to another role. For all > intents and purposes the session now looks as if that role was the one that > performed the login. However, that role is forbidden from changing its > identity. This removes attack vectors but also means that applications > cannot made use of finer grained grants without the main role inheriting > all of them. I can see this being an acceptable trade-off in some/many > uses.
> When the session is returned to a pool it can either be kept around waiting > for another request by the same user or it would have to be closed and > reestablished should the connection need to be freed up for another user.
Doesn't seem like this'd actually provide any useful functionality for a connection pooler. It still has to restrict any one underlying connection to be used by only one role. You've added more bookkeeping (because there's a state where a connection's role is unassigned) but no flexibility.
Y
ou basically delegate authentication for users to the pooler. Only the pooler needs to be setup with credentials and given access via pg_hba.conf. Though pg_hba isn't really a problem, you'd just allow everyone in from the same machine. So mostly its about not have credentials and giving the pooler some level of play with managing resources.
Every connection would have a role assigned just like it does today. The difference is that now certain roles will have an added attribute forbidding them from being SET ROLEd away from. I'm sure there is a lot more to it than that but the concept of a one-way switch should remove many of the hazards present today. The point of the password was to continue to allow multiple-switching - but securely. That seems to be much more complicated setup to implement and so the question should be asked whether that capability is even necessary.
I'll admit that the trade-offs are not appealing for large scale but resource constrained setups but I suspect for those setups sharding is going to end up being the area of attention; not delegation/proxy.
