I am using Postgres row based security and I wish to ensure it is not possible for a specific session variable to remain set in between transactions / queries.
The reason is because the session is shared between user queries – if that session variable is set but not cleared, then it is possible that the next query – which is for a different application user – might be executed using the session variable from the previous query.
I wish to ensure that the only session variables that can be used are LOCAL session variables (which must be used only in a transaction and are temporary and do not remain set in the session after the transaction has ended).
So the solution I have come up with is to use the Postgres rules system, and clear the session variable using RESET or SET in between every transaction or query. RESET https://www.postgresql.org/docs/9.1/sql-reset.html or SET https://www.postgresql.org/docs/9.1/sql-set.html both before and after every SELECT, INSERT, UPDATE, DELETE query.
Although the Postgres rules system appears to have the capabilities to do this but I cannot find much detail.
As you can see here, a google search doesn’t return much useful:
Can anyone direct me to information that might help me work out how to do this?