út 5. 1. 2021 v 18:51 odesílatel Bruce Momjian <[email protected]> napsal:
> I think I tried to make this feature more visible a few years ago and
> some people said we might remove it someday, so don't do that. If that
> is false, I think we can make it more prominent.
I think it's false.
I'll try to give you a real-life context on how set_config() was useful to me
yesterday when implementing application-level Role-Based Access Control
built on top of PostgREST.
In the postgrest.conf I'm using the "pre-request" feature to call an auth()
function
that will raise an exception if the user is not authorized to access the
resource.
Before, I had to execute the code to authenticate the user by verifying a
UUID token in current_setting('request.cookie.access_token', TRUE)::uuid
in a query in a helper-function user_id() by looking it up in an access_tokens
table.
Since functions as well as security_definer views might restrict access
to rows based on application-level user_id, this user_id() function is called
from multiple different places possibly lots of times.
Now, using set_config(), I instead verify the access_token only once,
in my auth() function, and set the user_id there, and modified user_id()
to use current_setting() to read it.
Maybe it's not an improvement performance-wise since user_id() is marked STABLE
so maybe its query would only be executed once per transaction anyway.
But I think it's cleaner to do all the authenticate and authorize operations
at one place, make a decision, and then use the constant result of that
decision,
instead of relying on caching of functions.
Here is the code for the scenario described:
https://github.com/truthly/uniphant/commit/da12465818a62e69355dad126648c15a11871c12#diff-fe0f50327c9772bdf4baa8944856716da16706f0998e844820b51fc6077a7c7aR21
On Tue, Jan 5, 2021, at 18:59, Pavel Stehule wrote:
> Schema variables are designed specially for described purposes
> https://commitfest.postgresql.org/31/1608/.
Many thanks Pavel for working on Schema variables, looks like a very nice
feature.
Is it possible to get the behaviour of set_config(..., ..., TRUE) i.e. "the new
value will only apply for the current transaction" by using CREATE TEMP
VARIABLE .. ON TRANSACTION END?
This is what I need for my purpose, I don't want the value to survive the
transaction.
I noted "LET" has been suggested as an alternative name for the command. This
reminds me of what I brought up in the other thread "LET clause". But instead
of "Schema variables" I guess a descriptive sentence for what I talked about
would be "Statement variables" i.e. variables that are declared and exists on a
per-statement level. Do you think the "Schema variables" code would be useful
to look at if I would try to implement a PoC of "Statement variables"?
Also, do you know if Schema variables are part of the SQL standard?
/Joel
