SET LOCAL <var> doesn't become undefined after transaction is commited

Kostya <kostya.y@gmail.com> writes:

However, once I run the following transaction
BEGIN READ WRITE
SET LOCAL session.my_tenant_id="f00";
SELECT * FROM someTable;
COMMIT;

Then
SELECT current_setting("session.my_tenant_id");
will return an empty string "". Basically "session.my_tenant_id" becomes
defined.

Is this supposed to work like this or could this be a bug?
Is it possible to entirely undefine "session.my_tenant_id" after the
transaction was committed? Basically I would like to return to the initial
state of the PSQL connection.

It is supposed to work like that. There is no provision for rolling
back the existence of a GUC altogether, and if there were, it would
break the actually intended use-case, namely GUCs created by dynamically
loaded extensions. If an extension is loaded during a transaction,
it won't disappear if the transaction is rolled back, so neither
should its GUCs.

The real problem here is that you're abusing a feature that was never
meant to be used for user-defined variables. It does not have the
right behavior in corner cases (as you're seeing here), it does not
have the features you'd want (eg ability to declare the type of
a variable), and it definitely doesn't have the scalability to lots
of variables that I'd expect a user-focused feature to have.

There's a fairly long-running thread about creating a feature that
*is* meant for user variables:

/messages/by-id/CAFj8pRDY+m9OOxfO10R7J0PAkCCauM-TweaTrdsrsLGMb1VbEQ@mail.gmail.com

I haven't checked on the state of that lately, but you might read up
on it and help review/test it, or try to push the definition in the
direction you need if it doesn't seem like quite the right thing.

regards, tom lane

Thanks for the elaborate response.

I don't define many variables, basically only a single one.
We've implemented multi tenancy using row level policy and by utilizing the
SET LOCAL variable to pass the tenant id to enforce querying the right
tenant.

For example in the table "stuff" I have a column called "tenant_id".

Basically my table policy looks like this:

CREATE POLICY stuff_policy on stuff
USING (tenant_id::text = current_setting('session.my_tenant_id', true))
WITH CHECK (tenant_id::text = current_setting('session.my_tenant_id',
true));

So each query first does SET LOCAL and then SELECT xxx FROM stuff;

I realize its not a "tight" multi tenancy implementation, but it works well
for our needs.

Do you think this "abuse" is reasonable? It's pretty much working well for
us, but I'm now worried following your post that this may not scale well or
perhaps have some security issues I'm not aware of.

What is your take on this?

Thanks in advance.

On Wed, Mar 18, 2020 at 4:16 PM Tom Lane <tgl@sss.pgh.pa.us> wrote: