Can I use Postgres rules to reset session variables before/after queries?

Started by Andrew Stuartabout 5 years ago3 messagesgeneral
Jump to latest
#1Andrew Stuart
andrew@salesgrid.com.au

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

#2Christophe Pettus
xof@thebuild.com
In reply to: Andrew Stuart (#1)
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 better way 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

#3Tom Lane
tgl@sss.pgh.pa.us
In reply to: Christophe Pettus (#2)
Re: Can I use Postgres rules to reset session variables before/after queries?

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 better way 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