Обсуждение: Plans for 9.1, Grouping Sets, disabling multiqueries, contrib module for string, plpgpsm, preload dictionaries
Hello, * Now I am working on migration of plpgpsm to plpgsql 9.0 base. I hope so I understand SQL/PSM well so I am able to write production quality implementation. If you like, I can integrate it to core. It can share about 40-50% code with plpgpsm. The behave of plpgpsm is same as plpgsql - without some plpgsql's historical issues (about FOUND, about NULL and record type). SQL/PSM is litlle bit richer language. Now we have not any wide used runtime so I don't thinking about rewriting. Maybe we can rewrite these PL language for parrot or lua runtime in future. But this step isn't necessary - people hasn't performance problems with PL based on PL runtime. * Month ago there was discussion about integration sprintf function to core. I don't think it is good idea. sprintf implementation isn't simple. I prefer more simple "format" function based on RAISE NOTICE ... behave and code. It is significantly shorter and simpler. But for people who like sprintf we can prepare some contrib module with basic string functions - sprintf, left, right, reverse. This module can be based on cleaned pstcollection - http://pgfoundry.org/frs/download.php/2556/pstcoll-100127.tar.gz. * Last two months I spent some time with preparing workshops about SQL injection. PostgreSQL has only one issue related to this topic. It allows multi queries. With this feature any successful injection can have much more destructive impact. Now we have a GUC per user. I know, we cannot break multiqueries without breaking basic functionality. But we can break multiple queries on top level for some selected users - (web application roles). Then we are able to configure database for "secure web access". >>It isn't protection against SQL injection<<. But it can down up possible risk about successful SQL injection. + downgrade rights on system tables for web application roles. * I would to like solve problem with fulltext reported by Czech users. I will try implement preloaded TSearch dictionaries. * Still I thinking about GROUPING SETS feature. Regards Pavel Stehule
Pavel Stehule <pavel.stehule@gmail.com> writes:
> * Last two months I spent some time with preparing workshops about SQL
> injection. PostgreSQL has only one issue related to this topic. It
> allows multi queries. With this feature any successful injection can
> have much more destructive impact. Now we have a GUC per user. I know,
> we cannot break multiqueries without breaking basic functionality. But
> we can break multiple queries on top level for some selected users -
> (web application roles). Then we are able to configure database for
> "secure web access". >>It isn't protection against SQL injection<<.
This seems like a waste of effort. It is already the case that multi
queries are forbidden when submitting through the extended query
protocol. All that an app has to do is not use simple protocol ---
which, if it's trying to be secure, it's already not using because
it needs out-of-line parameters.
There's no need for yet another GUC.
regards, tom lane
Pavel Stehule escribió: > Hello, > > * Now I am working on migration of plpgpsm to plpgsql 9.0 base. I hope > so I understand SQL/PSM well so I am able to write production quality > implementation. If you like, I can integrate it to core. It can share > about 40-50% code with plpgpsm. The behave of plpgpsm is same as > plpgsql - without some plpgsql's historical issues (about FOUND, about > NULL and record type). SQL/PSM is litlle bit richer language. Now we > have not any wide used runtime so I don't thinking about rewriting. > Maybe we can rewrite these PL language for parrot or lua runtime in > future. But this step isn't necessary - people hasn't performance > problems with PL based on PL runtime. How do you plan to go about code sharing? I'm wondering if we're going to have src/pl/common or something like that. Since there's a huge amount of common code it doesn't make any sense to keep it duplicate. Also, AFAIR that was the main rejection point for the plpgpsm patch last time around, so it would be good to discuss this thoroughly. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
2010/2/22 Alvaro Herrera <alvherre@commandprompt.com>: > Pavel Stehule escribió: >> Hello, >> >> * Now I am working on migration of plpgpsm to plpgsql 9.0 base. I hope >> so I understand SQL/PSM well so I am able to write production quality >> implementation. If you like, I can integrate it to core. It can share >> about 40-50% code with plpgpsm. The behave of plpgpsm is same as >> plpgsql - without some plpgsql's historical issues (about FOUND, about >> NULL and record type). SQL/PSM is litlle bit richer language. Now we >> have not any wide used runtime so I don't thinking about rewriting. >> Maybe we can rewrite these PL language for parrot or lua runtime in >> future. But this step isn't necessary - people hasn't performance >> problems with PL based on PL runtime. > > How do you plan to go about code sharing? I'm wondering if we're going > to have src/pl/common or something like that. Since there's a huge > amount of common code it doesn't make any sense to keep it duplicate. sure - a there a a few parts - simple query diagnostic, namespace support. Now I would to migrate to 9.0 and have initial version. This version and regress tests can be used as "etalon". Next stage can be code cleaning and migration to shared code. Pavel > > Also, AFAIR that was the main rejection point for the plpgpsm patch last > time around, so it would be good to discuss this thoroughly. > > -- > Alvaro Herrera http://www.CommandPrompt.com/ > The PostgreSQL Company - Command Prompt, Inc. >
On sön, 2010-02-21 at 11:00 +0100, Pavel Stehule wrote: > * Now I am working on migration of plpgpsm to plpgsql 9.0 base. I hope > so I understand SQL/PSM well so I am able to write production quality > implementation. If you like, I can integrate it to core. It can share > about 40-50% code with plpgpsm. The behave of plpgpsm is same as > plpgsql - without some plpgsql's historical issues (about FOUND, about > NULL and record type). SQL/PSM is litlle bit richer language. Now we > have not any wide used runtime so I don't thinking about rewriting. > Maybe we can rewrite these PL language for parrot or lua runtime in > future. But this step isn't necessary - people hasn't performance > problems with PL based on PL runtime. While having a "cleaner" variant of PL/pgSQL available might be desirable for some (but compare discussion on plpython3), given that you label this SQL/PSM, I suppose you are also working on this from a standards-compliance perspective. According to my reading, the part of the SQL standard that is named SQL/PSM does not, however, describe a procedural language in the PostgreSQL sense of the term. It describes server-side modules and an extension to the SQL language (that is, it is activated by CREATE FUNCTION ... LANGUAGE SQL). It remains to be decided which parts of these are ultimately useful and desirable, but I suggest that there be some discussion on the exact strategy in this area first, lest we end up with a "plpgsql3".
2010/3/1 Peter Eisentraut <peter_e@gmx.net>: > On sön, 2010-02-21 at 11:00 +0100, Pavel Stehule wrote: >> * Now I am working on migration of plpgpsm to plpgsql 9.0 base. I hope >> so I understand SQL/PSM well so I am able to write production quality >> implementation. If you like, I can integrate it to core. It can share >> about 40-50% code with plpgpsm. The behave of plpgpsm is same as >> plpgsql - without some plpgsql's historical issues (about FOUND, about >> NULL and record type). SQL/PSM is litlle bit richer language. Now we >> have not any wide used runtime so I don't thinking about rewriting. >> Maybe we can rewrite these PL language for parrot or lua runtime in >> future. But this step isn't necessary - people hasn't performance >> problems with PL based on PL runtime. > > While having a "cleaner" variant of PL/pgSQL available might be > desirable for some (but compare discussion on plpython3), given that you > label this SQL/PSM, I suppose you are also working on this from a > standards-compliance perspective. According to my reading, the part of > the SQL standard that is named SQL/PSM does not, however, describe a > procedural language in the PostgreSQL sense of the term. It describes > server-side modules and an extension to the SQL language (that is, it is > activated by CREATE FUNCTION ... LANGUAGE SQL). It remains to be > decided which parts of these are ultimately useful and desirable, but I > suggest that there be some discussion on the exact strategy in this area > first, lest we end up with a "plpgsql3". > I invite any discussion. I hope so my implementation will be clean and fast. Still I am learning this language and have to understand to core. I afraid so implementation SQL/PSM will be little bit modified - postgresql use little bit different concept of warnings, and mainly we have not "procedures". So there will be some new limits. My goal is creating some prototype now. I am sure so we can better integrate PL to main parser - but it hasn't be a first step. Pavel >