Interesting. But won't that require the connection user be privileged enough to do that? And therefore you're still one SQL injection away from someone taking on another role for escalation or impersonation?
You can create a role whose sole job is to switch to the roles needed. Doesn’t require you to escalate to superuser-level privileges that way. But still, if SQL injections aren’t properly considered then it’s possible for a user to gain more privileges than planned. Although SQL injections are usually mitigated by the DB libraries these days.
Also, it’s more convenient to use SET LOCAL ROLE <ROLE_NAME>, since that only keeps the role for the transaction. Manually resetting it is error prone (IME), and forgetting will have the supposedly “temporary” role bleed to the next transaction.
Also, it’s more convenient to use SET LOCAL ROLE <ROLE_NAME>, since that only keeps the role for the transaction. Manually resetting it is error prone (IME), and forgetting will have the supposedly “temporary” role bleed to the next transaction.