Re: [HACKERS] proposal: session server side variables - Mailing list pgsql-hackers

Hello Pavel,

>> AFAICS they are shared between backends, [...] They constitute a 
>> consistent design.
>
> no
> http://stackoverflow.com/questions/2383061/scope-of-oracle-package-level-variables

If stackoverflow says so, too bad for me:-) Now I do not understand the 
point of the example I read on Oracle documentation: why having an 
employee count accessed by some functions if it is reset on each new 
session?

So I do retract "it constitute a consistent design". It looks more like a 
PL/SQL confined hack.

Note that Oracle also seems to have session variables with set with DEFINE 
and referenced with &variable.

>> [...] That could look like:
>>
>>   SET ROLE Admin;
>>   DECLARE @secure_variable INTEGER RESTRICT; -- only accessible to Admin
>>   SET @secure_variable = 3;
>>
>>   SET ROLE BasicUser;
>>   SELECT @secure_variable; -- say NULL or error does not exist...
>>
> what will be if BasicUser does DECLARE @secure_variable

Then there would be a distinct global @secure_variable unrelated to the 
previous one, that would be hidden from Admin who would see its own 
private @secure_variable. Maybe "restrict" is not the right word, though, 
let us use "private".
   SET ROLE User1;   -- use @var: does not exist in scope error   DECLARE @var INTEGER PRIVATE;   SET @var = 1;   --
use@var: get 1
 
   SET ROLE User2;   -- use @var: does not exist in scope error   DECLARE @var INTEGER PUBLIC;   SET @var = 2;   -- use
@var;get 2
 
   SET ROLE User1;   -- use @var: get 1 (private version)
   SET ROLE User3;   -- use @var: get 2 (public version created by User2).


> There are not any granularity of rights - you cannot to grant access ...

Indeed, at least directly. With the above version you can just control 
whether everybody or only the owner has access.

However with some minimal more effort the owner of a private session 
variable could provide a grantable function for accessing this variable: 
the benefit would be that the function is permanent, i.e. would not need 
to be granted each time the variable is used, it could be done once and 
for all.
  CREATE FUNCTION setSecret(INT) SECURITY DEFINER ... AS $$    DECLARE IF NOT EXISTS @secret TEXT PRIVATE;    SET
@secret= $1;  $$ LANGUAGE SQL;
 
  CREATE FUNCTION useSecret(TEXT) SECURITY DEFINER TEXT AS $$     -- would fail if @secret has not been set yet...
SELECTsha256sum(@secret || ':' || $1);  $$ LANGUAGE SQL;
 
  CREATE FUNCTION getSecret() RETURNS TEXT SECURITY DEFINER AS $$    DECLARE IF NOT EXISTS @secret TEXT PRIVATE;
SELECT@secret;  $$ LANGUAGE SQL;
 
  -- then  REVOKE/GRANT ... ON FUNCTION set/use/getSecret(...);

> I am sorry, I don't see benefit in your proposal.

The benefit I see is to have MS/MY-SQL/Oracle like light-weight 
(inexpensive, untransactional) session variables and still a minimal 
access control which might be enough for significant use cases.

If more is really needed, consider the function hack, or maybe some 
one-row table with all the power of grant. Ok, the table solution is more 
heavy weight, but then this is also for a special requirement, and it 
would work as well for persistence.

> Probably there will be only one agreement, so there are not agreement 
> between us :(

It seems so. I do believe that I am trying to propose a solution which 
take into account your use case as I understand it (you did not confirm 
nor infirm) which is to store securely but not safely some kind of 
temporary data between different function calls with SECURITY DEFINER 
within the same session.

I'm trying to avoid "special-case" medium-weight (i.e. pg_class-based) 
session variables with permissions, which could preclude MY/MS-SQL/Oracle 
like light-weight session variables which are I think interesting in their 
own right.

-- 
Fabien.