For my Love Your Database Project:
I’m trying to see how a typical web developer might use Postgres’ roles and row-level security to implement their authorization.
What I’m struggling with is that connection pooling seems to make straightforward use of the roles to enforce access impossible.
If I’m using a connection pool, then I’m not re-connecting to Postgres with the user for the current transaction. But then my only option is to use SET ROLE. But that is not much security at all, because the current user can just do SET ROLE back to the (presumably privileged) default, or to any other user’s role.
What am I missing here?