Hi, I'm trying to use the PostgreSQL roles system as the user base for a web application. The common wisdom seems to be Don't Do This, because it requires a connection per-user which doesn't scale. However, thinking it through, I'm wondering it there might be a workaround using "sandbox transactions", a scheme where a connection pooler connects as a superuser, but immediately runs a
SET LOCAL ROLE 'joe_regular_user'; The problem with this of course is that the user could then just issue a RESET ROLE and go back to superuser. What would be the implications of adding a NO RESET clause to SET LOCAL ROLE? If the user were to ever end the local transaction, the system would need to kick them out of the connection, and they would need to reconnect inside another sandbox transaction. Could this work? How hard would it be, and what are the security implications? Thanks, Eric
