I am using Postgres row based 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 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/current/sql-reset.html or SET https://www.postgresql.org/docs/current/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:

enter image description here

Can anyone direct me to information that might help me work out how to do this?



These are really not great matches for a search. I wonder what in the world made you consider rules for this.

You should use RESET ALL or, even better, DISCARD ALL.

I think that the idea to set such custom parameters with SET LOCAL is a really good one, as long as it is guaranteed that the connection will return to the pool after the transaction ends. The drawback is that an attacker can run RESET LOCAL any time and continue working with escalated privileges. A solution might be a proper parameter defined in C code that refuses being reset.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.