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

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.


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...
  SET ROLE Admin;  SELECT @secure_variable; 3  ...

> Currently light session variables can be implemented as not big 
> extension.

Sure. I would review that as well.

-- 
Fabien.