Re: proof concept - access to session variables on client side

Lists: pgsql-hackers
From: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: proof concept - access to session variables on client side
Date: 2012-06-26 05:06:06
Message-ID: CAFj8pRD+C8oAWEzX999pQg1itbQmfT9X4jjhZdJy2QigWzVMAg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Hello

I worked on simple patch, that enable access from server side to
client side data. It add two new hooks to libpq - one for returning of
local context, second for setting of local context.

A motivation is integration of possibilities of psql console together
with stronger language - plpgsql. Second target is enabling
possibility to save a result of some server side process in psql. It
improve vars feature in psql.

pavel ~/src/postgresql/src $ cat test.sql
\echo value of external paremeter is :"myvar"

do $$
begin
-- we can take any session variable on client side
-- it is safe against to SQL injection
raise notice 'external parameter accessed from plpgsql is "%"',
hgetvar('myvar');

-- we can change this session variable and finish transaction
perform hsetvar('myvar', 'Hello, World');
end;
$$ language plpgsql;

\echo new value of session variable is :"myvar"

cat test.sql | psql postgres -v myvar=Hello
value of external paremeter is "Hello"
NOTICE: external parameter accessed from plpgsql is "Hello"
DO
new value of session variable is "Hello, World"

This is just proof concept - there should be better integration with
pl languages, using cache for read on server side, ...

Notices?

Regards

Pavel Stehule

Attachment Content-Type Size
client-session-vars.diff application/octet-stream 8.0 KB

From: Magnus Hagander <magnus(at)hagander(dot)net>
To: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
Cc: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: proof concept - access to session variables on client side
Date: 2012-06-26 07:20:38
Message-ID: CABUevEwXHKss6evz4MLesmjCC5mTv+qbf9orWOTButbZXECJbw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

On Tue, Jun 26, 2012 at 7:06 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
> Hello
>
> I worked on simple patch, that enable access from server side to
> client side data. It add two new hooks to libpq - one for returning of
> local context, second for setting of local context.
>
> A motivation is integration of possibilities of psql console together
> with stronger language - plpgsql. Second target is enabling
> possibility to save a result of some server side process in psql. It
> improve vars feature in psql.
>
> pavel ~/src/postgresql/src $ cat test.sql
> \echo value of external paremeter is :"myvar"
>
> do $$
> begin
>  -- we can take any session variable on client side
>  -- it is safe against to SQL injection
>  raise notice 'external parameter accessed from plpgsql is "%"',
> hgetvar('myvar');
>
>  -- we can change this session variable and finish transaction
>  perform hsetvar('myvar', 'Hello, World');
> end;
> $$ language plpgsql;
>
> \echo new value of session variable is :"myvar"
>
> cat test.sql | psql postgres -v myvar=Hello
> value of external paremeter is "Hello"
> NOTICE:  external parameter accessed from plpgsql is "Hello"
> DO
> new value of session variable is "Hello, World"
>
> This is just proof concept - there should be better integration with
> pl languages, using cache for read on server side, ...
>
> Notices?

Why not just use a custom GUC variable instead? E.g. you could have
psql SET "psql.myvar='Hello, World'", and then you'd need no changes
at all in the backend? Maybe have a "shorthand interface" for
accessing GUCs in psql would help in making it easier, but do we
really need a whole new variable concept?

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/


From: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
To: Magnus Hagander <magnus(at)hagander(dot)net>
Cc: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: proof concept - access to session variables on client side
Date: 2012-06-26 07:50:28
Message-ID: CAFj8pRDfkxVLjGqg2Z44m2q+B2Qp2Zu7fy_s7VzsYnWfnjCjZQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

2012/6/26 Magnus Hagander <magnus(at)hagander(dot)net>:
> On Tue, Jun 26, 2012 at 7:06 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
>> Hello
>>
>> I worked on simple patch, that enable access from server side to
>> client side data. It add two new hooks to libpq - one for returning of
>> local context, second for setting of local context.
>>
>> A motivation is integration of possibilities of psql console together
>> with stronger language - plpgsql. Second target is enabling
>> possibility to save a result of some server side process in psql. It
>> improve vars feature in psql.
>>
>> pavel ~/src/postgresql/src $ cat test.sql
>> \echo value of external paremeter is :"myvar"
>>
>> do $$
>> begin
>>  -- we can take any session variable on client side
>>  -- it is safe against to SQL injection
>>  raise notice 'external parameter accessed from plpgsql is "%"',
>> hgetvar('myvar');
>>
>>  -- we can change this session variable and finish transaction
>>  perform hsetvar('myvar', 'Hello, World');
>> end;
>> $$ language plpgsql;
>>
>> \echo new value of session variable is :"myvar"
>>
>> cat test.sql | psql postgres -v myvar=Hello
>> value of external paremeter is "Hello"
>> NOTICE:  external parameter accessed from plpgsql is "Hello"
>> DO
>> new value of session variable is "Hello, World"
>>
>> This is just proof concept - there should be better integration with
>> pl languages, using cache for read on server side, ...
>>
>> Notices?
>
> Why not just use a custom GUC variable instead? E.g. you could have
> psql SET "psql.myvar='Hello, World'", and then you'd need no changes
> at all in the backend? Maybe have a "shorthand interface" for
> accessing GUCs in psql would help in making it easier, but do we
> really need a whole new variable concept?

GUC variables doesn't help with access to psql's command line
parameters from DO PL code.

Regards

Pavel

>
> --
>  Magnus Hagander
>  Me: http://www.hagander.net/
>  Work: http://www.redpill-linpro.com/


From: Magnus Hagander <magnus(at)hagander(dot)net>
To: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
Cc: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: proof concept - access to session variables on client side
Date: 2012-06-26 08:05:50
Message-ID: CABUevEz-L2474eUcwRsf4JL2Ts4=Kpn1pWCOF0XJmpZfzEmuPw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

On Tue, Jun 26, 2012 at 9:50 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
> 2012/6/26 Magnus Hagander <magnus(at)hagander(dot)net>:
>> On Tue, Jun 26, 2012 at 7:06 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
>>> Hello
>>>
>>> I worked on simple patch, that enable access from server side to
>>> client side data. It add two new hooks to libpq - one for returning of
>>> local context, second for setting of local context.
>>>
>>> A motivation is integration of possibilities of psql console together
>>> with stronger language - plpgsql. Second target is enabling
>>> possibility to save a result of some server side process in psql. It
>>> improve vars feature in psql.
>>>
>>> pavel ~/src/postgresql/src $ cat test.sql
>>> \echo value of external paremeter is :"myvar"
>>>
>>> do $$
>>> begin
>>>  -- we can take any session variable on client side
>>>  -- it is safe against to SQL injection
>>>  raise notice 'external parameter accessed from plpgsql is "%"',
>>> hgetvar('myvar');
>>>
>>>  -- we can change this session variable and finish transaction
>>>  perform hsetvar('myvar', 'Hello, World');
>>> end;
>>> $$ language plpgsql;
>>>
>>> \echo new value of session variable is :"myvar"
>>>
>>> cat test.sql | psql postgres -v myvar=Hello
>>> value of external paremeter is "Hello"
>>> NOTICE:  external parameter accessed from plpgsql is "Hello"
>>> DO
>>> new value of session variable is "Hello, World"
>>>
>>> This is just proof concept - there should be better integration with
>>> pl languages, using cache for read on server side, ...
>>>
>>> Notices?
>>
>> Why not just use a custom GUC variable instead? E.g. you could have
>> psql SET "psql.myvar='Hello, World'", and then you'd need no changes
>> at all in the backend? Maybe have a "shorthand interface" for
>> accessing GUCs in psql would help in making it easier, but do we
>> really need a whole new variable concept?
>
> GUC variables doesn't help with access to psql's command line
> parameters from DO PL code.

But with a small change to psql they could, without the need for a
whole new type of variable. For example, psql could set all those
variable as "psql.<commandlinevarname>", which could then be accessed
from the DO PL code just fine.

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/


From: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
To: Magnus Hagander <magnus(at)hagander(dot)net>
Cc: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: proof concept - access to session variables on client side
Date: 2012-06-26 08:12:52
Message-ID: CAFj8pRAhwDUdboce92SChzFeNnQXDvjXb92fkEVh-=irxvpAqQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

2012/6/26 Magnus Hagander <magnus(at)hagander(dot)net>:
> On Tue, Jun 26, 2012 at 9:50 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
>> 2012/6/26 Magnus Hagander <magnus(at)hagander(dot)net>:
>>> On Tue, Jun 26, 2012 at 7:06 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
>>>> Hello
>>>>
>>>> I worked on simple patch, that enable access from server side to
>>>> client side data. It add two new hooks to libpq - one for returning of
>>>> local context, second for setting of local context.
>>>>
>>>> A motivation is integration of possibilities of psql console together
>>>> with stronger language - plpgsql. Second target is enabling
>>>> possibility to save a result of some server side process in psql. It
>>>> improve vars feature in psql.
>>>>
>>>> pavel ~/src/postgresql/src $ cat test.sql
>>>> \echo value of external paremeter is :"myvar"
>>>>
>>>> do $$
>>>> begin
>>>>  -- we can take any session variable on client side
>>>>  -- it is safe against to SQL injection
>>>>  raise notice 'external parameter accessed from plpgsql is "%"',
>>>> hgetvar('myvar');
>>>>
>>>>  -- we can change this session variable and finish transaction
>>>>  perform hsetvar('myvar', 'Hello, World');
>>>> end;
>>>> $$ language plpgsql;
>>>>
>>>> \echo new value of session variable is :"myvar"
>>>>
>>>> cat test.sql | psql postgres -v myvar=Hello
>>>> value of external paremeter is "Hello"
>>>> NOTICE:  external parameter accessed from plpgsql is "Hello"
>>>> DO
>>>> new value of session variable is "Hello, World"
>>>>
>>>> This is just proof concept - there should be better integration with
>>>> pl languages, using cache for read on server side, ...
>>>>
>>>> Notices?
>>>
>>> Why not just use a custom GUC variable instead? E.g. you could have
>>> psql SET "psql.myvar='Hello, World'", and then you'd need no changes
>>> at all in the backend? Maybe have a "shorthand interface" for
>>> accessing GUCs in psql would help in making it easier, but do we
>>> really need a whole new variable concept?
>>
>> GUC variables doesn't help with access to psql's command line
>> parameters from DO PL code.
>
> But with a small change to psql they could, without the need for a
> whole new type of variable. For example, psql could set all those
> variable as "psql.<commandlinevarname>", which could then be accessed
> from the DO PL code just fine.

yes, it is possibility too. It has different issues - it can send
unwanted variables - maybe some compromise is optimum.

>
> --
>  Magnus Hagander
>  Me: http://www.hagander.net/
>  Work: http://www.redpill-linpro.com/


From: David Fetter <david(at)fetter(dot)org>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Re: proof concept - access to session variables on client side
Date: 2012-06-26 17:56:15
Message-ID: 20120626175615.GA19560@fetter.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

On Tue, Jun 26, 2012 at 10:12:52AM +0200, Pavel Stehule wrote:
> 2012/6/26 Magnus Hagander <magnus(at)hagander(dot)net>:
> > On Tue, Jun 26, 2012 at 9:50 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
> >> 2012/6/26 Magnus Hagander <magnus(at)hagander(dot)net>:
> >>> On Tue, Jun 26, 2012 at 7:06 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
> >>>> Hello
> >>>>
> >>>> I worked on simple patch, that enable access from server side to
> >>>> client side data. It add two new hooks to libpq - one for returning of
> >>>> local context, second for setting of local context.
> >>>>
> >>>> A motivation is integration of possibilities of psql console together
> >>>> with stronger language - plpgsql. Second target is enabling
> >>>> possibility to save a result of some server side process in psql. It
> >>>> improve vars feature in psql.
> >>>>
> >>>> pavel ~/src/postgresql/src $ cat test.sql
> >>>> \echo value of external paremeter is :"myvar"
> >>>>
> >>>> do $$
> >>>> begin
> >>>>  -- we can take any session variable on client side
> >>>>  -- it is safe against to SQL injection
> >>>>  raise notice 'external parameter accessed from plpgsql is "%"',
> >>>> hgetvar('myvar');
> >>>>
> >>>>  -- we can change this session variable and finish transaction
> >>>>  perform hsetvar('myvar', 'Hello, World');
> >>>> end;
> >>>> $$ language plpgsql;
> >>>>
> >>>> \echo new value of session variable is :"myvar"
> >>>>
> >>>> cat test.sql | psql postgres -v myvar=Hello
> >>>> value of external paremeter is "Hello"
> >>>> NOTICE:  external parameter accessed from plpgsql is "Hello"
> >>>> DO
> >>>> new value of session variable is "Hello, World"
> >>>>
> >>>> This is just proof concept - there should be better integration with
> >>>> pl languages, using cache for read on server side, ...
> >>>>
> >>>> Notices?
> >>>
> >>> Why not just use a custom GUC variable instead? E.g. you could have
> >>> psql SET "psql.myvar='Hello, World'", and then you'd need no changes
> >>> at all in the backend? Maybe have a "shorthand interface" for
> >>> accessing GUCs in psql would help in making it easier, but do we
> >>> really need a whole new variable concept?
> >>
> >> GUC variables doesn't help with access to psql's command line
> >> parameters from DO PL code.
> >
> > But with a small change to psql they could, without the need for a
> > whole new type of variable. For example, psql could set all those
> > variable as "psql.<commandlinevarname>", which could then be accessed
> > from the DO PL code just fine.
>
> yes, it is possibility too. It has different issues - it can send
> unwanted variables -

Could you expand on this just a bit? Are you picturing something an
attacker could somehow use, or...?

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate


From: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
To: David Fetter <david(at)fetter(dot)org>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: proof concept - access to session variables on client side
Date: 2012-06-26 18:47:55
Message-ID: CAFj8pRBeu5mQh0OZzy1erQV3_=P5M7_WWULcOt=crXjfC5-Mtg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

2012/6/26 David Fetter <david(at)fetter(dot)org>:
> On Tue, Jun 26, 2012 at 10:12:52AM +0200, Pavel Stehule wrote:
>> 2012/6/26 Magnus Hagander <magnus(at)hagander(dot)net>:
>> > On Tue, Jun 26, 2012 at 9:50 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
>> >> 2012/6/26 Magnus Hagander <magnus(at)hagander(dot)net>:
>> >>> On Tue, Jun 26, 2012 at 7:06 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
>> >>>> Hello
>> >>>>
>> >>>> I worked on simple patch, that enable access from server side to
>> >>>> client side data. It add two new hooks to libpq - one for returning of
>> >>>> local context, second for setting of local context.
>> >>>>
>> >>>> A motivation is integration of possibilities of psql console together
>> >>>> with stronger language - plpgsql. Second target is enabling
>> >>>> possibility to save a result of some server side process in psql. It
>> >>>> improve vars feature in psql.
>> >>>>
>> >>>> pavel ~/src/postgresql/src $ cat test.sql
>> >>>> \echo value of external paremeter is :"myvar"
>> >>>>
>> >>>> do $$
>> >>>> begin
>> >>>>  -- we can take any session variable on client side
>> >>>>  -- it is safe against to SQL injection
>> >>>>  raise notice 'external parameter accessed from plpgsql is "%"',
>> >>>> hgetvar('myvar');
>> >>>>
>> >>>>  -- we can change this session variable and finish transaction
>> >>>>  perform hsetvar('myvar', 'Hello, World');
>> >>>> end;
>> >>>> $$ language plpgsql;
>> >>>>
>> >>>> \echo new value of session variable is :"myvar"
>> >>>>
>> >>>> cat test.sql | psql postgres -v myvar=Hello
>> >>>> value of external paremeter is "Hello"
>> >>>> NOTICE:  external parameter accessed from plpgsql is "Hello"
>> >>>> DO
>> >>>> new value of session variable is "Hello, World"
>> >>>>
>> >>>> This is just proof concept - there should be better integration with
>> >>>> pl languages, using cache for read on server side, ...
>> >>>>
>> >>>> Notices?
>> >>>
>> >>> Why not just use a custom GUC variable instead? E.g. you could have
>> >>> psql SET "psql.myvar='Hello, World'", and then you'd need no changes
>> >>> at all in the backend? Maybe have a "shorthand interface" for
>> >>> accessing GUCs in psql would help in making it easier, but do we
>> >>> really need a whole new variable concept?
>> >>
>> >> GUC variables doesn't help with access to psql's command line
>> >> parameters from DO PL code.
>> >
>> > But with a small change to psql they could, without the need for a
>> > whole new type of variable. For example, psql could set all those
>> > variable as "psql.<commandlinevarname>", which could then be accessed
>> > from the DO PL code just fine.
>>
>> yes, it is possibility too.  It has different issues - it can send
>> unwanted variables -
>
> Could you expand on this just a bit?  Are you picturing something an
> attacker could somehow use, or...?

it is not security issue - just I dislike sending complete stack, when
just only one variable should be used.

Regards

Pavel

>
> Cheers,
> David.
> --
> David Fetter <david(at)fetter(dot)org> http://fetter.org/
> Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
> Skype: davidfetter      XMPP: david(dot)fetter(at)gmail(dot)com
> iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics
>
> Remember to vote!
> Consider donating to Postgres: http://www.postgresql.org/about/donate
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers


From: Merlin Moncure <mmoncure(at)gmail(dot)com>
To: Magnus Hagander <magnus(at)hagander(dot)net>
Cc: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: proof concept - access to session variables on client side
Date: 2012-06-26 19:33:33
Message-ID: CAHyXU0x0bz8CXHwqo29z35LA7T3Ts3u0a0BMu8g=M7etM7hcxw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

On Tue, Jun 26, 2012 at 3:05 AM, Magnus Hagander <magnus(at)hagander(dot)net> wrote:
> But with a small change to psql they could, without the need for a
> whole new type of variable. For example, psql could set all those
> variable as "psql.<commandlinevarname>", which could then be accessed
> from the DO PL code just fine.

That's a really neat idea.

merlin


From: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
To: Merlin Moncure <mmoncure(at)gmail(dot)com>
Cc: Magnus Hagander <magnus(at)hagander(dot)net>, PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: proof concept - access to session variables on client side
Date: 2012-06-26 19:42:38
Message-ID: CAFj8pRDLebfuXQsDH2Av8-=65o+J8p3g3o7LsuM4Cr+KKVviXw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

2012/6/26 Merlin Moncure <mmoncure(at)gmail(dot)com>:
> On Tue, Jun 26, 2012 at 3:05 AM, Magnus Hagander <magnus(at)hagander(dot)net> wrote:
>> But with a small change to psql they could, without the need for a
>> whole new type of variable. For example, psql could set all those
>> variable as "psql.<commandlinevarname>", which could then be accessed
>> from the DO PL code just fine.
>
> That's a really neat idea.

yes, it can be good idea - psql sends some status variables on start,
so it should be small patch

Pavel

>
> merlin


From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
Cc: David Fetter <david(at)fetter(dot)org>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: proof concept - access to session variables on client side
Date: 2012-06-26 21:17:12
Message-ID: 13361.1340745432@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
> it is not security issue - just I dislike sending complete stack, when
> just only one variable should be used.

That's a pretty darn weak argument. If I read the patch correctly, what
you're proposing involves a dynamic fetch from the client at runtime,
which is going to be disastrous for performance. Quite aside from the
network round trip involved, the fetch function would have to be marked
volatile (since it has client-visible side-effects, not to mention that
we don't know when the client might change the variable value); which
would really hurt any query involving it, and probably lead to yet more
round trips.

Pushing over the known values once at session start (and individual
values after updates) is likely to be vastly better-performant than
this. Matters could be improved further by requiring variables to be
sent to the server to be explicitly marked, which seems like a good
idea anyway in case anybody has security concerns that they're not
going to let you airily dismiss.

Another thing I don't care for is the unannounced protocol extension.
This feature is just not interesting enough to justify breaking
client compatibility, but that's what it would do as proposed.
Clients that haven't heard of this 'v' message would probably
think they'd lost sync and drop the connection.

(BTW, the patch doesn't seem to include the added backend source file?)

regards, tom lane


From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Merlin Moncure <mmoncure(at)gmail(dot)com>
Cc: Magnus Hagander <magnus(at)hagander(dot)net>, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: proof concept - access to session variables on client side
Date: 2012-06-26 21:29:33
Message-ID: 13588.1340746173@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Merlin Moncure <mmoncure(at)gmail(dot)com> writes:
> On Tue, Jun 26, 2012 at 3:05 AM, Magnus Hagander <magnus(at)hagander(dot)net> wrote:
>> But with a small change to psql they could, without the need for a
>> whole new type of variable. For example, psql could set all those
>> variable as "psql.<commandlinevarname>", which could then be accessed
>> from the DO PL code just fine.

> That's a really neat idea.

I do see a problem with this client-push idea, which is what happens if
psql sends a SET and later the active transaction gets rolled back.
psql does not have enough knowledge to be sure whether it lost the SET
or not. It could hack things by always resending all variables after
any rollback, but ugh.

We could address that by inventing a non-transactional variant of SET,
perhaps. Not sure it's worth the complication though --- I don't think
I want to have to define how that would interact with other variants
of SET in the same transaction ...

Another approach would be to define such variables as being truly
shared, in the spirit of last-update-wins multi master replication.
The backend sends over its values using the existing GUC_REPORT
mechanism. So a rollback would cause the psql-side variable to revert
as well. Not actually sure if that behavior would be more or less
useful than a simpler definition, but it's worth thinking about.

In this connection, there was some recent discussion in the jdbc list
of wanting clients to be able to set the GUC_REPORT flag on any GUC
variable, because the jdbc driver would like to track some settings we
have not seen fit to mark that way. Not sure if anybody mentioned that
on -hackers yet, but it's coming. If we had that ability then a
shared-variable behavior like this could be built entirely on the psql
side: the push part is just SET, and the pull part is GUC_REPORT.

regards, tom lane


From: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: David Fetter <david(at)fetter(dot)org>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: proof concept - access to session variables on client side
Date: 2012-06-27 05:15:06
Message-ID: CAFj8pRBJP00vSF_jH3eCVbdwVCwSToy=EEzRdwhTeX7KHaeU9Q@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

2012/6/26 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
>> it is not security issue - just I dislike sending complete stack, when
>> just only one variable should be used.
>
> That's a pretty darn weak argument.  If I read the patch correctly, what
> you're proposing involves a dynamic fetch from the client at runtime,
> which is going to be disastrous for performance.  Quite aside from the
> network round trip involved, the fetch function would have to be marked
> volatile (since it has client-visible side-effects, not to mention that
> we don't know when the client might change the variable value); which
> would really hurt any query involving it, and probably lead to yet more
> round trips.

I didn't implement any optimization, because it is just concept, but
server side caching is possible. Then only "first read" and any
"write" can do some network communication.

>
> Pushing over the known values once at session start (and individual
> values after updates) is likely to be vastly better-performant than
> this.  Matters could be improved further by requiring variables to be
> sent to the server to be explicitly marked, which seems like a good
> idea anyway in case anybody has security concerns that they're not
> going to let you airily dismiss.
>

this is decision between push and pull model. Both variants has own
issues and benefits. Probably pull model has more complex changes in
protocol implementation. Push model needs more code on client side.
Propagation psql variables should be enabled some command line option
and can be disabled by default.

> Another thing I don't care for is the unannounced protocol extension.
> This feature is just not interesting enough to justify breaking
> client compatibility, but that's what it would do as proposed.
> Clients that haven't heard of this 'v' message would probably
> think they'd lost sync and drop the connection.
>

yes, it needs protocol extension and increasing version too. But I
don't afraid about dissynchronisation - server doesn't send 'v'
message when client doesn't support it.

> (BTW, the patch doesn't seem to include the added backend source file?)

The goal of this patch is showing requested functionality and checking
how hard is implementation

Regards

Pavel

>
>                        regards, tom lane


From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
Cc: David Fetter <david(at)fetter(dot)org>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: proof concept - access to session variables on client side
Date: 2012-06-27 05:37:04
Message-ID: 22183.1340775424@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
>> Another thing I don't care for is the unannounced protocol extension.

> yes, it needs protocol extension and increasing version too. But I
> don't afraid about dissynchronisation - server doesn't send 'v'
> message when client doesn't support it.

And you would know that how, exactly?

regards, tom lane


From: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: David Fetter <david(at)fetter(dot)org>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: proof concept - access to session variables on client side
Date: 2012-06-27 11:37:35
Message-ID: CAFj8pRByFwxN3rhWf4tr3TS=MfPPUuFkuU-M7=x6D0UtxY0r-Q@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

2012/6/27 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
>>> Another thing I don't care for is the unannounced protocol extension.
>
>> yes, it needs protocol extension and increasing version too. But I
>> don't afraid about dissynchronisation - server doesn't send 'v'
>> message when client doesn't support it.
>
> And you would know that how, exactly?

minor version of protocol can be used

http://archives.postgresql.org/pgsql-hackers/2011-12/msg00025.php

I don't know if this topic is done, I only remember this thread

Regards

Pavel Stehule

>
>                        regards, tom lane


From: Peter Eisentraut <peter_e(at)gmx(dot)net>
To: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
Cc: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: proof concept - access to session variables on client side
Date: 2012-07-03 17:53:41
Message-ID: 1341338021.21530.21.camel@vanquo.pezone.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

On tis, 2012-06-26 at 07:06 +0200, Pavel Stehule wrote:
> A motivation is integration of possibilities of psql console together
> with stronger language - plpgsql. Second target is enabling
> possibility to save a result of some server side process in psql. It
> improve vars feature in psql.

I think it would be better if DO could be extended into some kind of
"lambda", taking parameters and returning a value. Then you can use
existing infrastructure for passing values and saving the return. It
would also extend better to other languages.


From: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
To: Peter Eisentraut <peter_e(at)gmx(dot)net>
Cc: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: proof concept - access to session variables on client side
Date: 2012-07-03 18:01:38
Message-ID: CAFj8pRCjFgqJFCvqt4CZh3d66u=nH1h5GRP48-XLscMpHmADbQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

2012/7/3 Peter Eisentraut <peter_e(at)gmx(dot)net>:
> On tis, 2012-06-26 at 07:06 +0200, Pavel Stehule wrote:
>> A motivation is integration of possibilities of psql console together
>> with stronger language - plpgsql. Second target is enabling
>> possibility to save a result of some server side process in psql. It
>> improve vars feature in psql.
>
> I think it would be better if DO could be extended into some kind of
> "lambda", taking parameters and returning a value.  Then you can use
> existing infrastructure for passing values and saving the return.  It
> would also extend better to other languages.

I did it
http://archives.postgresql.org/pgsql-hackers/2010-07/msg00118.php

it is other approach. I think so callback from server to client is
more general solution - access to client system variables is possible,
but I know so this is very obscure and risk idea.

but any form of parametrization of PL block can be nice.

Regards

Pavel

>


From: Dimitri Fontaine <dfontaine(at)hi-media(dot)com>
To: Peter Eisentraut <peter_e(at)gmx(dot)net>
Cc: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: proof concept - access to session variables on client side
Date: 2012-07-04 07:05:10
Message-ID: B6B07E7A-9FA2-4547-9F5A-7F56800A135F@hi-media.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Le 3 juil. 2012 à 19:53, Peter Eisentraut <peter_e(at)gmx(dot)net> a écrit :
> I think it would be better if DO could be extended into some kind of
> "lambda", taking parameters and returning a value. Then you can use
> existing infrastructure for passing values and saving the return. It
> would also extend better to other languages.

+1

--
dim