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

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(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Kostya <kostya(dot)y(at)gmail(dot)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:
>
>
> https://www.postgresql.org/message-id/flat/CAFj8pRDY+m9OOxfO10R7J0PAkCCauM-TweaTrdsrsLGMb1VbEQ(at)mail(dot)gmail(dot)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
>