| From: | Andrew Stuart <andrew(at)salesgrid(dot)com(dot)au> |
|---|---|
| To: | pgsql-general(at)lists(dot)postgresql(dot)org |
| Subject: | Can I use Postgres rules to reset session variables before/after queries? |
| Date: | 2021-01-25 05:00:47 |
| Message-ID: | CAFVsTWwnmXOE-=n5CBtxyT0ubOY6upkVUU=2iPqStg1xpdcuNA@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Christophe Pettus | 2021-01-25 05:03:32 | Re: Can I use Postgres rules to reset session variables before/after queries? |
| Previous Message | Demitri Muna | 2021-01-25 02:27:00 | Re: CLUSTER, VACUUM, and TABLESPACEs (oh my) |