Re: [GENERAL] Means to emulate global temporary table

On 01/11/2017 05:30 PM, Ian Lewis wrote:
Ccing list

> On Wed, Jan 11, 2017 at 4:38 PM, Adrian Klaver
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
>
>     So what makes them temporary as they seem to persist between sessions?
>
>
> They are temporary in the sense that the content of the table is
> per-session, just as a local temporary table would be. That is, each
> session has its own independent data set. But, the table is defined and
> accessible within the schema as a normal table would be.

So what is the relationship of clients to sessions?

>
> While efficiency is not an issue in our usage, on our current server,
> they are very efficient because they do not need to handle locking as a
> normal table would do because only one session can access the data.
>
>
>         That can be handled with SECURITY DEFINER:
>
>         https://www.postgresql.org/docs/9.6/static/sql-createfunction.html
>         <https://www.postgresql.org/docs/9.6/static/sql-createfunction.html>
>         "EXTERNAL] SECURITY INVOKER
>         [EXTERNAL] SECURITY DEFINER
>
>             SECURITY INVOKER indicates that the function is to be
>         executed with the privileges of the user that calls it. That is
>         the default. SECURITY DEFINER specifies that the function is to
>         be executed with the privileges of the user that created it.
>
>             The key word EXTERNAL is allowed for SQL conformance, but it
>         is optional since, unlike in SQL, this feature applies to all
>         functions not only external ones.
>
>
>         "
>
>
>
> I will look at this in more detail, but, on first reading, I do not
> quite see how it helps.

Well you where saying that having a client create a table would result
in it having the clients permissions instead of the servers. Doing the
table creation through a function with SECURITY INVOKER would allow you
to 'shape' the permissions.

>
> Ian Lewis (www.mstarlabs.com <http://www.mstarlabs.com>)


--
Adrian Klaver
adrian.klaver@aklaver.com