4 ms·
Does anyone know how to do these things in JDBC? Is it possible to issue `SET my.username = 'tomas'` before executing a query?
by virmundi 9y ago
Does anyone know how to do these things in JDBC? Is it possible to issue `SET my.username = 'tomas'` before executing a query?
- snuxoll 9y agoSET 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.