Re: [HACKERS] proposal: session server side variables

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...
     SELECT sha256sum(@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.