Re: set_config() documentation clarification - Mailing list pgsql-hackers

út 5. 1. 2021 v 18:51 odesílatel Bruce Momjian <bruce@momjian.us> 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:

On Tue, Jan 5, 2021, at 18:59, Pavel Stehule wrote:

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