2016-12-26 13:08 GMT+01:00 Fabien COELHO <coe...@cri.ensmp.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.
