Re: 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.