Re: proposal: session server side variables

From: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
To: Craig Ringer <craig(at)2ndquadrant(dot)com>
Cc: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: proposal: session server side variables
Date: 2016-11-28 07:42:40
Message-ID: CAFj8pRCEyUHFednUXmsW3PnQmXSwNbDHvkHn1E5iKkeL1P6WMw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi

2016-11-25 15:32 GMT+01:00 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:

> Hi
>
> 2016-10-14 9:56 GMT+02:00 Craig Ringer <craig(at)2ndquadrant(dot)com>:
>
>> On 14 October 2016 at 13:30, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
>> wrote:
>> > Hi,
>> >
>> > long time I working on this topic. Session server side variables are one
>> > major missing feature in PLpgSQL. Now I hope, I can summarize requests
>> for
>> > implementation in Postgres:
>>
>> +1
>>
>> > 2. accessed with respecting access rights:
>> >
>> > GRANT SELECT|UPDATE|ALL ON VARIABLE variable TO role
>> > REVOKE SELECT|UPDATE|ALL ON VARIABLE variable FROM role
>>
>> This bit is important.
>>
>> For those wondering "why the hell would you want these, just (ab)use
>> GUCs"... this is why.
>>
>> Think RLS. Especially when we eventually have session start / at login
>> triggers, but even before then, you can initialise some expensive
>> state once at the start of the session, transfer it from the app, or
>> whatever. You initialise it via a SECURITY DEFINER procedure so the
>> session user does not have the rights to write to the variable, and it
>> can only be set via arbitration from the database security logic. From
>> then on your RLS policies, your triggers, etc, can all simply inspect
>> the session variable.
>>
>> People use package variables in another major database with a feature
>> called virtual private database for something similar. So this will
>> interest anyone who wants to make porting those users easier, too.
>>
>> > 4. non transactional - the metadata are transactional, but the content
>> is
>> > not.
>>
>> but only within the session, right? You're not proposing some kind of
>> inter-backend IPC where one backend sets a session var and another
>> backend accesses it and sees the value set by the first session?
>>
>> Speaking of which: parallel query. How do you envision this working in
>> parallel query, where the workers are different backends? Especially
>> since things like RLS are where it'd be quite desirable.
>>
>
> In first stage the session variables should be marked as parallel unsafe -
> but in future - there can be used similar technique like shared hashjoin.
>
> I am sending proof concept - it doesn't support access to fields of
> composite variables, but any other functionality is done.
>
> Most important features:
>
> 1. the values are stored in native types
> 2. access to content is protected by ACL - like the content of tables
> 3. the content is not MVCC based - no any cost of UPDATE
> 4. simple API allows access to content of variables from any supported
> environment.
>

next update - setattr, getattr functions are working now

notes, comments?

Regards

Pavel

>
> Regards
>
> Pavel
>
>
>> --
>> Craig Ringer http://www.2ndQuadrant.com/
>> PostgreSQL Development, 24x7 Support, Training & Services
>>
>
>

Attachment Content-Type Size
secure-typed-session-variables-concept-02.patch text/x-patch 81.2 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Gilles Darold 2016-11-28 08:16:31 Re: [patch] psql tab completion for ALTER DEFAULT PRIVILEGES
Previous Message Michael Paquier 2016-11-28 07:12:05 Re: [COMMITTERS] pgsql: libpq: Allow connection strings and URIs to specify multiple hos