Re: proposal: session server side variables

2016-12-26 13:08 GMT+01:00 Fabien COELHO <coelho(at)cri(dot)ensmp(dot)fr>:

>
>
> Hello Pavel,
>
> you are talk about light session variables like MSSQL or MySQL (with same
>> syntax), I am talking about secure session variables like Oracle package
>> variables (with similar access syntax).
>>
>
> Hmmm. I do not know this Oracle stuff... After looking at the online
> documentation, my understanding of "Oracle package variables" refers to
> full fledged database objects, in particular they are not session limited.
> The example I found is about a variable holding the total number of
> employees, with functions hire & fire (well, they call it remove_emp) to
> update them when inserting or deleting an employee.
>
> AFAICS they are shared between backends, subjects to transactions and
> permissions, constraints and so on. So they look more like the first
> category I outlined, and probably they cost as any persistent database
> object, which make sense. They constitute a consistent design.
>

no
http://stackoverflow.com/questions/2383061/scope-of-oracle-package-level-variables

>
>
> This is * not * what you are proposing.
>
>
> [...] I have two important reasons why I insist on pg_class base.
>>
>> 1. security .. it is really fundamental part
>> 2. possibility to static check by plpgsql_check - without entry in
>> pg_class
>> (or other catalogue table) I have not any valid information about type,
>> existence of any variable.
>>
>
> Hmmm. I'm not quite convinced that putting session variables in pg_class
> is a good idea, because it adds significant costs for the use case of
> "standard" simple session variables, which is quite more probable than
> session-but-with-permissions variables.
>
> As far as security is concerned, ISTM that a limited but still useful
> access control can be implemented for a key-value store with simple session
> variables, see below.
>
> As far as typing is concerned, ISTM that it can be done as well for
> session variables by going through text and using casts when setting and
> getting values, or through some other simple ad-hoc checking.
>
> Although I am not supporter (due possible issues with plpgsql_checks) of
>> MySQL or MSSQL style variables I am not strongly against this
>> implementation with same syntax. But it is different feature, with
>> different benefits and costs.
>>
>
> I didn't proposed the packages (and package variables) due issues in
>> multilingual PostgreSQL environment and because it is redundant to
>> PostgreSQL schemas.
>>
>
> Instead I proposed >>secure global session variables<< (global like global
>> temporary tables).
>>
>
> That's where I'm leaving you and start disagreeing, because it is not
> consistent: you are proposing session variables that do not look like
> session variable and are somehow costly.
>
> I could agree with real "secure global variables" as in Oracle packages, a
> consistent kind of database object which stores a persistent value safely
> and securely. That would cost, but that is life in a database, you have
> great things for a price. Probably that could be implemented as a row in
> some special table, or as a one-row table, or whatever.
>
> I could also agree with à la MS or MY-SQL session variables that look like
> session variables, with limited ambition, light-weight and inexpensive.
>
> I disagree with having a half-backed stuff, where something looks like a
> database object (i.e. CREATE/ALTER/DROP/GRANT/REVOKE) but is really a
> session object with strange properties. I also disagree to the pg_class
> approach as it creates in effect an expensive session object while a simple
> session object would cost much less and would be much more useful.
>
> To summarize, I still think that your design is not consistent, even if it
> makes sense for some degree wrt the implementation.
>
> A possible compromise I have proposed is to have some declared access
> restrictions on simple session variables, so that say only the owner can
> access it, but they should stay and look like light-weight session
> variables nevertheless. 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

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

> SET ROLE Admin;
> SELECT @secure_variable; 3
>

I am sorry, I don't see benefit in your proposal. Probably there will be
only one agreement, so there are not agreement between us :(

Regards

Pavel

> ...
>
> Currently light session variables can be implemented as not big extension.
>>
>
> Sure. I would review that as well.
>
> --
> Fabien.