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.
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:
https://www.postgresql.org/message-id/flat/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