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

út 5. 1. 2021 v 20:23 odesílatel Joel Jacobson <joel@compiler.org> napsal:

ú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:

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.

yes, it is supported. More the schema variables supports RESET to default on transaction end, and supports access rights for usage in security definer functions.

So some users can set this value and others can only read.

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"?

Maybe - I don't know what "Statement variables" :). Other databases do not have similarly named features. There are two concepts (these concepts can be mixed). Maybe - you can see there how non tabular objects can be accessed in queries with.

1. using some global temporary objects - the object is persistent, but data not (and data are not shared). These objects should be assigned to some persistent space - Oracle has packages, I propose schema, DB2 has schemas, ANSI SQL has modules. It's designed primarily for stored procedures as global variables (some are shared, some not). These objects can be locally temporal - like our temporary tables. I prefer the name "schema variables" due similarity with DB2 schema variables. Oracle's package variables are just ADA language package variables.

2. session variables - these exist dynamically only in session, and are not assigned with some persistent space. Usually they are created by DECLARE statement (MSSQL) or are created by first usage (MySQL). In this concept, the session variables are very dynamic objects - in MSSQL or MySQL very primitive without possibility to set access rights (but it can be implementation detail). The little bit strange thing is fact, so these objects are in specific address space - MSSQL, MySQL uses special notation - it starts by @. This script language in PgAdmin III supports this syntax too. The open question is stability of values stored in these variables. MySQL variables are volatile, MSSQL I don't know. It has an impact on behaviour. MySQL variables can be used for implementation of a row counter, but because they are not stable, the query cannot be optimized well (or this optimization can be not correct).

Schema variables are designed for usage for stored procedures and for usage from applications. Session variables are good for writing ad hoc queries, and adhoc interactive work. Although what I know, the other databases doesn support both concepts, I think so both concepts can be supported. There can be differences in syntax - session variables are not qualified,but the main difference is only in lifetime and constructor syntax CREATE or DECLARE - CREATE TEMP VARIABLE is functional equivalent of DECLARE var

Also, do you know if Schema variables are part of the SQL standard?

ANSI SQL defines modules, and some variables can be declared in module scope. Modules are like our schemas with the possibility to define private objects. But I don't know any implementation of this part of the standard in some widely used database . It is like a mix of package concepts (Oracle) with schemas, because modules can hold private database objects like tables or temporary tables. So my proposed schema variables are not part of SQL standard, because related features depend on modules. Functionally it is similar +/-. Personally I don't like concepts of modules (or packages) too much. The schemas are a good replacement for 90% and the current system of qualified names and search path, that is same for tables and same for procedures, is very simple and good enough). So instead of introducing modules, I prefer enhanced schemas about some features like private objects. But it is in the category "nice to have" rather than a necessary feature.

Regards

Pavel

 

/Joel