Re: [HACKERS] proposal: session server side variables

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.