Thread: proof concept - access to session variables on client side
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
On Tue, Jun 26, 2012 at 7:06 AM, Pavel Stehule <pavel.stehule@gmail.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/
2012/6/26 Magnus Hagander <magnus@hagander.net>:
> On Tue, Jun 26, 2012 at 7:06 AM, Pavel Stehule <pavel.stehule@gmail.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/
On Tue, Jun 26, 2012 at 9:50 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
> 2012/6/26 Magnus Hagander <magnus@hagander.net>:
>> On Tue, Jun 26, 2012 at 7:06 AM, Pavel Stehule <pavel.stehule@gmail.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/
2012/6/26 Magnus Hagander <magnus@hagander.net>:
> On Tue, Jun 26, 2012 at 9:50 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
>> 2012/6/26 Magnus Hagander <magnus@hagander.net>:
>>> On Tue, Jun 26, 2012 at 7:06 AM, Pavel Stehule <pavel.stehule@gmail.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/
On Tue, Jun 26, 2012 at 10:12:52AM +0200, Pavel Stehule wrote:
> 2012/6/26 Magnus Hagander <magnus@hagander.net>:
> > On Tue, Jun 26, 2012 at 9:50 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
> >> 2012/6/26 Magnus Hagander <magnus@hagander.net>:
> >>> On Tue, Jun 26, 2012 at 7:06 AM, Pavel Stehule <pavel.stehule@gmail.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@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.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
2012/6/26 David Fetter <david@fetter.org>:
> On Tue, Jun 26, 2012 at 10:12:52AM +0200, Pavel Stehule wrote:
>> 2012/6/26 Magnus Hagander <magnus@hagander.net>:
>> > On Tue, Jun 26, 2012 at 9:50 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
>> >> 2012/6/26 Magnus Hagander <magnus@hagander.net>:
>> >>> On Tue, Jun 26, 2012 at 7:06 AM, Pavel Stehule <pavel.stehule@gmail.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@fetter.org> http://fetter.org/
> Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
> Skype: davidfetter XMPP: david.fetter@gmail.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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
On Tue, Jun 26, 2012 at 3:05 AM, Magnus Hagander <magnus@hagander.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
2012/6/26 Merlin Moncure <mmoncure@gmail.com>: > On Tue, Jun 26, 2012 at 3:05 AM, Magnus Hagander <magnus@hagander.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
Pavel Stehule <pavel.stehule@gmail.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
Merlin Moncure <mmoncure@gmail.com> writes:
> On Tue, Jun 26, 2012 at 3:05 AM, Magnus Hagander <magnus@hagander.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
2012/6/26 Tom Lane <tgl@sss.pgh.pa.us>: > Pavel Stehule <pavel.stehule@gmail.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
Pavel Stehule <pavel.stehule@gmail.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
2012/6/27 Tom Lane <tgl@sss.pgh.pa.us>: > Pavel Stehule <pavel.stehule@gmail.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
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.
2012/7/3 Peter Eisentraut <peter_e@gmx.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 >
Le 3 juil. 2012 à 19:53, Peter Eisentraut <peter_e@gmx.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