Re: proposal: session server side variables

On 28 December 2016 at 22:04, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:

> For security the variable should be persistent.
>
> If you would to do statical analyse (what you usually would), then variable
> should be persistent.
>
> Currently the big issue of plpgsql_check is work with temporary tables.
> Local objects or dynamic sql is stop for static check.

Someone asked me off-list what use cases such a thing would have,
since it seems not to be spelled out very clearly in this discussion.
I think we're all assuming knowledge here.

So.

* Session starts
* app does SELECT setup_user('user-auth-key-data', 'some-other-blob')
** setup_user is SECURITY DEFINER to 'appadmin'
** 'appadmin' owns a variable IS_AUDITOR. Other roles have only read
access to it.
** setup_user(...) does whatever expensive/slow work it has to do
** setup_user sets USER_IS_AUDITOR var
* Later RLS policies simply reference USER_IS_AUDITOR var. They don't
need to know the 'user-auth-key-data', or do whatever expensive
processing that it does.
* Other later triggers, etc, also reference USER_IS_AUDITOR
* User cannot make themselves an auditor by SETting USER_IS_AUDITOR

That's the general idea.

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