4 ms·
SET ROLE "user" The biggest issue with this is roles are identifiers, which means you can't use parameters for them.
by snuxoll 9y ago
SET ROLE "user"
The biggest issue with this is roles are identifiers, which means you can't use parameters for them.
- virmundi 9y agoCan I do that inline with a statement? I don't think I'd mind outsourcing rights to the DB.
- snuxoll 9y agoNo, you'll need to call SET/RESET role as their own statements. The easiest way to do this in Java is to ensure RESET ROLE is called when checking a connection back into the pool, and calling SET ROLE in a servlet interceptor (probably the same one that handles authentication).
- s_kilk 9y agoSo, if I'm following this correctly, you'd need to start a new connection with the end-users credentials? Which would be slow and rule out connection pooling and other such niceties?
- snuxoll 9y agoYou can use connection pooling, the user you are connected with needs to be granted the role you are switching to (preferably with the NOINHERIT flag) - that's the purpose behind the SET ROLE and RESET ROLE statements.