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

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

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

Kostya
Hi
We are using dynamic settings via SET LOCAL for row level security.
I would like to clarify that I'm seeing the correct behaviour
I create a new session to the database, I run the following query:

SELECT current_setting("session.my_tenant_id");

I will get the following error:
pq: unrecognized configuration parameter \"session.my_tenant_id\"

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.

Thanks in advance.
Reply | Threaded
Open this post in threaded view
|

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

Tom Lane-2
Kostya <[hidden email]> 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@...

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


Reply | Threaded
Open this post in threaded view
|

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

Kostya
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 <[hidden email]> wrote:
Kostya <[hidden email]> 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@...

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