[paulryanrogers]

Some features just don't scale or cannot easily integrate into app layers which need them. For example Pg connections are expensive, so you need a Pooler, now you don't want a DB user per end user. FK constraints too can prove hard to scale as one ends up with extra writes and contention, or do sharing.

[timando]

> now you don't want a DB user per end user.

`RESET ROLE; SET ROLE app_username;` could be done for each query / transaction / when fetching the connection from the pool.

[paulryanrogers]

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?

[sekun]

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.