Обсуждение: Can I use Postgres rules to reset session variables before/after queries?
I am using Postgres row level security and I wish to ensure it is not possible for a specific session variable (that holds a tenant id) to remain set in between transactions / queries.
The reason is because this is a web application that pools sessions and the session is shared between user web 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.
A google search says there are not many answers relating to "postgres rules" and "session variables"
Can anyone suggest if session variables can be SET/RESET using Postgres rules, or optionally perhaps there is a better way to do so?
thanks
Re: Can I use Postgres rules to reset session variables before/after queries?
> On Jan 24, 2021, at 21:00, Andrew Stuart <andrew@salesgrid.com.au> wrote: > Can anyone suggest if session variables can be SET/RESET using Postgres rules, or optionally perhaps there is a betterway to do so? PostgreSQL poolers generally use the RESET ALL command when reassigning a session to clear the session state: https://www.postgresql.org/docs/current/sql-reset.html You probably want to do this rather than try to intercept every single operation in order to the reset at the end. -- -- Christophe Pettus xof@thebuild.com
Christophe Pettus <xof@thebuild.com> writes: >> On Jan 24, 2021, at 21:00, Andrew Stuart <andrew@salesgrid.com.au> wrote: >> Can anyone suggest if session variables can be SET/RESET using Postgres rules, or optionally perhaps there is a betterway to do so? > PostgreSQL poolers generally use the RESET ALL command when reassigning a session to clear the session state: > https://www.postgresql.org/docs/current/sql-reset.html See also DISCARD, which clears even more session state: https://www.postgresql.org/docs/current/sql-discard.html I concur that trying to use rules for this is unlikely to work well. regards, tom lane