PGCon Day 4

So, black nail polish actually comes off in the shower with a bit of manual labor ...

Nikolay and I gave our talk in the morning. Everything went well, I thought. I got good feedback afterwards, plus a dozen people who were supposedly sorry to have missed it. :-)

Then I saw Neil Conway's talk "Stream Processing with PostgreSQL", which is pretty much the story of my life over the past half year, because their system wasn't yet available and we had to build our own. I hope they finish their product soon, so we all have something to play with.

Then I went to the PGCluster-II talk by Atsushi Mitani. This thing is not ready for production, because the write performance is terrible. I'm not sure I'm optimistic, but I think it's good that this architecture is at least tried out. In fact, I've heard other people being interested in similar things, so if you think shared storage clusters are worthwhile, check this out and help.

Finally, we had lightning talks, which showed a bunch of new little pieces of software, including an accounting package, a package manager for Windows, air traffic control using PostgreSQL, odbclink, pgAdmin III on a Windows CE mobile phone, and — shock — a new multimaster replication system. I think I forgot one.

After that we had a wrap-up session with a charity auction and lots of laugh. We made a group photo which should be available somewhere sometime soon.

Thanks to Dan and associates for organizing this. Thanks to all the sponsors for helping the conference and funding various meals. And thanks to everyone who came and said hi to me.

PGCon Day 3

It was actually my first day at the events yesterday, but the schedule calls it day 3 because there had already been two days of tutorials beforehand.

So, yesterday I went to these talks:

Great Steps in PostgreSQL History (Bruce Momjian)

A reconstruction of the first moonlanding using PostgreSQL. Never mind that. You had to have been there.

PostgreSQL-IE (Denise Guliato)

This is not Internet Explorer interfacing with PostgreSQL, but an image-handling extension to support content-based image retrieval, useful for example in medical applications to analyze images. Some of the details were over my head, but it seemed well thought out and appears to match and exceed in some regards what the commercial RDBMS vendors offer. With luck, this could be the next PostGIS-type success story

Execution plan optimization techniques (Tomas Kovarik and Julius Stroffek)

These guys showed up new ways to handle the search space in large joins, to replace (or work alongside) the current GEQO system. Such as using simulated annealing, if that rings a bell with anyone. What I took out of it is that we pretty much would need pluggable optimizers. It's interesting that in hallway conversations afterwards everyone I talked to pretty much spontaneously came up with a subset of the ideas presented in this talk. What we need now is some code.

PostgreSQL replication strategies (Emmanuel Cecchet)

Emmanuel is the author of Sequoia, so honestly I came here to see some Sequoia bashing from the audience. But really, this was a useful and pretty unbiased summary of available replication techniques. Unfortunately, we hear similar talks every hear — heck, I gave one two years ago — but make little progress on the actual code.

GIN in practice (Teodor Sigaev)

A very technical and insightful talk. Now I know what hstore is about. Where would we be without the indexing dream team?

There seemed to be a scheduling issue later in the day in that the dinner sponsored by EnterpriseDB already started while sessions were still in progress, which shortened the palaver and key-signing session considerably, but everyone got to voice their concerns about their non-favorite version control system or bug tracker once more, but I think most of it came down to needing more people to do the various work ahead of us.

The dinner itself didn't actually start nearly as early as announced. We got there about two hours late and nobody had gotten food yet. Bizarrely, about a dozen fellows including myself came home with their fingernails painted black. How do you get this stuff off anyway?

PGCon Arrival

So I've been told that I must do some conference blogging. I got to Ottawa yesterday afternoon. The bus from the airport was only $1.90, not $2.60 as announced. Score. When I got to the pub where the registration was supposed to happen, there was nothing set up there. But luckily I recognized a face, so that I didn't have to feel completely lost. The conference bags, which arrived eventually, contained a handy container of screen cleaning solutions with a cloth — very good idea. As I left, someone had already paid for my beer. Score again. Whoever that was, thanks.

I must have hit the hay at around 19:00 EDT, after having been up for 22 hours, with 3 hours of sleep the night before. Of course the way to battle jetlag — I should know this — is not to go to sleep when you're tired but when the local bed time is. That would explain why I'm up at 5 in the morning writing blog now.

I managed to commit the final major XPath/XML-related patch before I left. And sure enough, the build farm is still green. I'd figured that Nikolay and Bruce wouldn't have let me stay if I had forgotten that. And after all we need to have something to talk about tomorrow.

Advisory on possibly insecure security definer functions

It has come to the attention of the core team of the PostgreSQL project that insecure programming practice is widespread in SECURITY DEFINER functions. Many of these functions are exploitable in that they allow users that have the privilege to execute such a function to execute arbitrary code with the privileges of the owner of the function.

The SECURITY DEFINER property of functions is a special non-default property that causes such functions to be executed with the privileges of their owner rather than with the privileges of the user invoking the function (the default mode, SECURITY INVOKER). Thus, this mechanism is very similar to the "setuid" mechanism in Unix operating systems.

Because SQL object references in function code are resolved at run time, any references to SQL objects that are not schema qualified are resolved using the schema search path of the session at run time, which is under the control of the calling user. By installing functions or operators with appropriate signatures in other schemas, users can then redirect any function or operator call in the function code to implementations of their choice, which, in case of SECURITY DEFINER functions, will still be executed with the function owner privileges. Note that even seemingly innocent invocations of arithmetic operators are affected by this issue, so it is likely that a large fraction of all existing functions are exploitable.

The proper fix for this problem is to insert explicit SET search_path commands into each affected function to produce a known safe schema search path. Note that using the default search path, which includes a 
reference to the "$user" schema, is not safe when unqualified references are intended to be found in the "public" schema and "$user" schemas exist or can be created by other users. It is also not recommended to rely on rigorously schema-qualifying all function and operator invocations in function source texts, as such measures are likely to induce mistakes and will furthermore make the source code harder to read and maintain.

This problem affects all existing PostgreSQL releases since version 7.3. Because this situation is a case of poor programming practice in combination with a design mistake and inadequate documentation, no security releases of PostgreSQL will be made to address this problem at this time. Instead, all users are urged to hastily correct their code as described above. Appropriate technological fixes for this problem are being investigated for inclusion with PostgreSQL 8.3.

Literarisches Code-Quartett

In Germany, there is a television show called "Literarisches Quartett" where four important people discuss books. Here at the Chaos Communication Congress, there is a session called "Literarisches Code-Quartett" where four people discuss source code, mostly bad code. Last year, they dwelt on MySQL for quite a while, leaving with a promise of dealing with PostgreSQL this year. So what they came up with is a supposed buffer overflow in the precompiler part of ECPG (looks like using array bounds longer than 11 characters might be a problem in certain places) and a supposed flaw in the German translation that causes an SQL syntax error (so it was probably in psql), but I could not actually find the place or the revision where this was ever the case. (If anyone knows details, tell me.) In any case, they pointed out that the PostgreSQL code was much better for one's stomach than last year's alternative...

Thoughts on the Open Source Database Conference

With the Open Source Database Conference over, I now have my doubts that this conference model is very viable. Here's the math: The PHP conference going on in parallel seems to have settled at around 350 attendees. The open source database market is, in my extremely rough estimation, about a quarter of the PHP market. (Almost every PHP site uses a database, but not all PHP developers are interested in open source databases.) Assuming again that PostgreSQL has about a third of the open source database market, that gives us a potential attendance of 30 people for a PostgreSQL track. The actual group of people following the virtual PostgreSQL track was about 20, but the growth potential does not seem too big.

Now what's the problem? Of course, the conference was advertized poorly and late. So perhaps improving that would give us the 50% growth predicted by my model.

The major fallacy, however, seems to be assuming that there is, indeed, an open source database community. There isn't. There is a PostgreSQL community, a MySQL community, etc. Throwing them all together is quite enjoyable for me as a developer, giving me the opportunity to meet the other groups, but for a regular conference attendee , it doesn't make too much sense. You see, there wasn't even a recognizable PostgreSQL track. You had to guess as to which talks related to which database system. A related problem is that there is not a lot of crossover between the PHP conference and the PostgreSQL track, something which is perhaps less of an issue for SQLite or MySQL.

But the biggest problem of it all is: It's just too expensive. The conference fee is about 800 euros; add to that the rather expensive hotel and travel, you're easily above 1000 euros for the two days. To see six talks about PostgreSQL (one of which you get to see for free again next week at LinuxWorld Expo Frankfurt). For that price, you can hire an expert on any database system to come to your site and train your staff for a full day or more. In fact, a three-day PostgreSQL training with room and board seems to go for 1368.80 euros.

That said, the technical education I received from this conference was excellent, including from the two PHP talks I saw. But perhaps it is indeed time to think about a PostgreSQL-only event with more affordable terms.