Re: proposal: schema PL session variables

>>
>> The schema variables are private by design. It can be enhanced in
>> future, but now it is out my scope. If you need public access to these
>> variables, you can use a functions. The access to functions can be
>> controlled by a rights. We can introduce a private (schema limited)
>> function too, but again it is out scope of this proposal.
>>
>
> So it's not possible for function schema_a.blah to access variables in
> schema_b? If it is then variables are NOT private.
>

the scope is schema. It is private for schema objects. The access to
content is limited from objects from same schema. I know so this concept is
new in Postgres, but I hope so it is useful. Anybody can expect different
behave related to some technical terms - so maybe "private" keyword isn't
best in this moment. I just variables with possible safe (limited) access.
Anything else I can do with functionality that we have already.

I got off list mail with little bit different syntax proposal

CREATE VARIABLE xxx DEFAULT [ PRIVATE ]

I am thinking so more SQL natural is form:

CREATE [ PRIVATE ] VARIABLE xxx ...

There should not be only variables, there can be tables, views, functions,
...

The "PRIVATE" in this context means - only accessible from current schema.
The syntax is different, than I propose, but the idea is same.

>
> When it comes to variables, I think it's a mistake to discuss this
>> patch while pretending that packages don't exist. For example all we
>> wanted were session variables, there's no reason they need to be
>> tied to schemas. The only reason to tie them to schemas is to try
>> and fake package support via schemas. I think it'd be a mistake to
>> have non-schema variables, but lets not fool ourselves as to why
>> that would be a mistake.
>>
>>
>> I am happy, so you are opened the question about that package.
>> Originally the Oracle package is a Ada language feature, but if you
>> compare Oracle schemas and Postgresql schemas, you should to see a
>> significant differences. Our schemas are much more similar to Oracle
>> packages than Oracle schemas. So introduction of packages to Postgres is
>> contra productive - will be pretty messy to have the packages and the
>> schemas together. We don't need packages, because we have schemas, but
>> we have not any safe (and simply used) schema scope tools. I implemented
>> Orafce and the main problems there are not missing packages, but
>> different default casting rules and missing procedures.
>>
>
> I'm not saying we have to implement packages the same way oracle did. Or
> at all.
>
> My point is that there are MAJOR features that packages offer that we
> don't have at all, with or without schemas. One of those features is the
> idea of private objects. You CAN NOT do the same thing with permissions
> either, because public vs private doesn't care one iota about what role is
> executing something. They only care about what's in the call stack.
>

I don't understand well, and probably I don't explain my ideas well. But
this exactly what I would to implement. The security based on locality, not
based on roles.

>
> Another problem I have with this is it completely ignores
>> public/private session variables. The current claim is that's not a
>> big deal because you can only access the variables from a PL, but I
>> give it 2 days of this being released before people are asking for a
>> way to access the variables directly from SQL. Now you have a
>> problem because if you want private variables (which I think is
>> pretty important) you're only choice is to use SECDEF functions,
>> which is awkward at best.
>>
>
> While this patch doesn't need to implement SQL access to variables, I
> think the design needs to address it.

SQL access to variables needs a) change in SQL parser (with difficult
discussion about syntax) or b) generic get/set functions. @b can be used in
other PL in first iteration.

I afraid to open pandora box and I would to hold the scope of this patch
too small what is possible - to be possible implement it in one release
cycle.

Regards

Pavel

>
> --
> Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
> Experts in Analytics, Data Architecture and PostgreSQL
> Data in Trouble? Get it in Treble! http://BlueTreble.com
>