Summary of feedback from FOSDEM devroom

I forgot completely to close up the feedback system for our talks at FOSDEM and post the feedback to the speakers. My apologies to all speakers and others who were interested! Thankfully, Marc Balmer reminded me today and the individual ratings for different speakers have been sent off to them.

When it comes to the aggregated statistics, they look fairly similar to what we saw at PGDay. The number of speakers were much lower, and unfortunately so were the number of people giving feedback. But in the end, the results were fairly clear. I am especially happy with the level of knowledge in the speakers we attract. Let's start with the graphs:

Breaking down the details, I think this shows a great quality of our speakers:

Name | Speaker Quality | Votes | Deviation Simon Riggs | 4.8 | 6 | 0.4 Magnus Hagander | 4.6 | 5 | 0.5 David Fetter | 4.5 | 4 | 1 Heikki Linnakangas | 4.3 | 9 | 0.9 Dave Page | 4.2 | 10 | 1.0 Marc Balmer | 3.4 | 5 | 1.7 Peter Eisentraut | 3.3 | 7 | 1.0

And the level of their knowledge is even higher:

Name | Speaker Knowledge | Votes | Deviation Simon Riggs | 5 | 6 | 0 Magnus Hagander | 5 | 5 | 0 Heikki Linnakangas | 5 | 9 | 0 David Fetter | 4.8 | 4 | 0.5 Dave Page | 4.3 | 10 | 1.1 Peter Eisentraut | 4.3 | 7 | 1.1 || Marc Balmer || 3.6 || 5 || 1.7 ||

Even at the bottom of this list, the numbers are very good! Unfortunately, the fairly low number of votes give a high deviation at the bottom of the chart.

For next time, we'd really like to get more people to leave their feedback. Does anybody have a good idea on how to make that happen?

Important updates for PostgreSQL Europe

Those of you who were at FOSDEM, heard my bold announcement that we will finally get off the ground and hold proper elections fort he board of PostgreSQL Europe, per a decision made the day before. Now, when we got home and read up on the statutes that govern the organization, it turns out we actually can't do that. The way forward that we've found around this, is to actually change the statutes to make these things possible.

To change the statutes, we need a majority of the General Assembly, made out of the active members, to agree on a change. It turns out that given how the statues are formed, there are currently only four members in PostgreSQL Europe, and these four members are the original board. Because of this, it was pretty easy to change the statutes (we only needed 3 of these people to agree, which we all did). And this we have done. The reason for changing them is to make sure we can get a working system going where we have more people involved. But in order to make that work, we first had to make this small-group decision.

The changes have just been published to www.postgresql.eu, and should show up any minute there. The changes that have been made are:

• We have removed the definition of "Benefactor members", to keep things simple. We are keeping Active and Honorary members.
• Created a membership fee, to control who is a member.
• Change so that any resignations are handled by the Board, not the President, to make things more flexible.
• Add that a membership is terminated if the fee isn't paid.
• Added mailinglist as an approved way to do meetings. Since this is how we do most of our communicating, it made no sense not to have it on there.
• Moved the budget out of the GA, it's just a tool for the executive board. Keeps down the paperwork.
• Add a defined way for the GA to vote on issues, using a web based system (to be developed).
• Since we will be voting over the internet, remove the ability to vote by proxy.

As you can see, the main change is the creation of a membership fee. This is the most controversial change. The idea here is that it will be a nominal fee (€10 for two years of membership - though that exact amount isn't part of the statutes, to keep flexibility). This will make it possible for us to have an actual membership list, which will be the grounds for who can vote.

The current method for handling membership requires the signing and sending around of forms on paper (yes! Actual dead trees!). Given how our organization works, this simply does not work. That is the main driver behind us changing this.

We did evaluate several other ways to deal with membership, ranging from things like requiring physical meetings (not really a good idea for a pan-European organization) to fancy PGP signing schemes (which really is way too complex for a lot of people). Our conclusion was that this was at least the least bad way to do it.

We are going to build on this shortly with more information about exactly how the elections are going to be held. The plan is still to have completed this years election before the summer, and we are well on track to make that goal.

The changes to the English statutes are of course available in our git repository. The binding french changes will be made shortly.

If you have any changes or comments, please direct them at the pgeu-general@postgresql.org mailinglist, or directly contact the board.

Time management by (somebody elses) press-releases

A while back I submitted a couple of talks for PG-East 2010 in Philly, and over the past couple of weeks I've been nagging the organizers semi-frequently to get some pre-info on whether I've been accepted or not, since flight prices started to climb fairly rapidly. The site clearly says information that the information will be available on Feb 15th, so I can't really complain that the answer kept being "don't know yet".

A couple of days ago, I got a note from Dave pinged me with a message asking if I was approved. Turns out this press-release had been posted (by his company, no less). Which explicitly names me as a speaker at the conference.

Took me two more days of chasing down JD, but I now have confirmation I'll be there. I don't actually know what I'll be speaking about, but it's a pretty safe bet it will be PostgreSQL related.

I call this Time management by press releases. If I could only get it to apply to all meetings, I would no longer need to keep my own calendar up to date.

So, I'll see you in Philly!

Integrating django authentication with PostgreSQL

If you've been to any of my pgcrypto talks, you know I'm a fan of exposing a login interface in the database. It's a great way to allow multiple systems to use the same authentication system, without being limited to a single framework. However, it can often be nice and easy to be able to use the account management system in said framework, because it's already there.

When I go to frameworks, django is my current favorite, and I do like the ability to use it's integrated admin system to edit users. For this reason, I wanted to add the ability to use a database function to authenticate other systems using the django user tables. Thanks to the fact that django choose a nicely readable text format for their hashes, and that PostgreSQL has the awesome pgcrypto library, this is pretty simple. Here's a function that will simply return true if authentication was successful, and false if not.

CREATE OR REPLACE FUNCTION django_login (_username text, _password text)
RETURNS boolean
AS $$
BEGIN
   IF EXISTS (SELECT * FROM auth_user WHERE username=_username) THEN
      RETURN encode(pgcrypto.digest(split_part(password, '$', 2) || _password, 'sha1'), 'hex') = 
             split_part(password, '$', 3) FROM auth_user WHERE username=_username;
   ELSE
      RETURN 'f';
   END IF;
END;
$$
LANGUAGE 'plpgsql';

This assumes you have installed pgcrypto in it's own schema, something I always recommend. If you haven't, just remove the schema specifier in the query.

Finally, if you use this type of authentication, remember to use SSL. And don't enable query logging...

64-bit Windows in PostgreSQL

Let me first say that I still stand by my previous comments that PostgreSQL is in much less need of a native 64-bit version on Windows than other databases - and less than PostgreSQL on other platforms. (Both this and the previous post talk about server-side support, having support client-side in libpq and ODBC is obviously important)

That said, I am now happy to say that with the effort of several people, it looks like we now have basic 64-bit support for PostgreSQL on Windows. During the past couple of weeks I have cleaned up and extended the patch from Tsutomu Yamada, which in itself builds on the work of several others, and applied it to cvs. As of today, you can build and run PostgreSQL on 64-bit Windows from the official repository.

This is obviously just the beginning. Unfortunately, Windows in general seems to be lagging behind quite severely on the 64-bit front, and this appears to be particularly true of the open source libraries. I've so far been unable to find 64-bit builds of many of the libraries we rely on for full functionality (gettext, zlib, libxml, libxslt, libiconv, tcl, uuid). While in theory support for these things should show up as soon as they are available, that's obviously not tested. If you know where I can download complete 64-bit binaries (including .lib and .h files for development) of any of these projects, please let me know.

Also, this is not very well tested yet. It passes all the PostgreSQL regression tests. That's pretty much all the tests I've been running on it. This is obviously a point where a lot of people can help - so please do. I have made a set of pre-alpha binaries available here. This is a bare-bones build of cvs head earlier today - things like OpenSSL that do have 64-bit binaries available is also disabled. It comes with no installer - just unzip into a directory, and use the initdb and pg_ctl commands directly. So if you have a 64-bit environment, please download and test this against your application!

PostgreSQL security releases out

As you can see, PostgreSQL has just released new updated versions, which include security fixes. They also contain other critical bug fixes, so even if you are not directly affected by the security issues, plan an upgrade as soon as possible.

One of the security issues that have been patched deal with NULL prefixes in SSL certificate names, a vulnerability that is basically the same one that have surfaced in a lot of different products this autumn, for example in the Mozilla suite of products. There is not really space enough to properly discuss the implications this has in a PostgreSQL environment in the release notes, so I'll try to elaborate some here - given that I wrote the fix for it.

First of all, a quick explanation of what the problem is. PostgreSQL uses OpenSSL to deal with certificates. Prior to the fixed version, we just asked OpenSSL for the name of the certificate, got back a string, and used this one. Now, if you know C coding, you know that a string is terminated by a NULL character. The bug in PostgreSQL is that we did not check the return value from this function, and make sure it returned the same value as the length of the returned string. This means that somebody could embed a NULL value in the certificate, and we would incorrectly parse and validate only the part that was before the NULL value. For example, if someone managed to get a certificate with the common name set to "postgresql.bank.com\0attacker.com", PostgreSQL would match this certificate against "postgresql.bank.com" (or ".bank.com"), which is not correct. With the fix, the certificate will be rejected* completely.

It is important to know that in order to make use of this vulnerability, the attacker needs to convince a trusted CA to sign such a certificate - which is quite obviously malicious. If the attacker cannot get the CA to hand this out, PostgreSQL will reject the certificate before we even get this far. It is arguably also a bug in the CA handling (technical or procedural) to even hand out such a certificate, and that bug need to be exploited before the one in PostgreSQL can be.

In the vast majority of cases, if not all, where PostgreSQL is deployed and actually using certificate validation, the certificates will be handed out by a trusted local CA. In which case, exploiting this vulnerability becomes much harder. This scenario is significantly different from the original scenario this bug was discovered in, which is the web browser. In the web browser case, the browser already trusts a large number of external CAs by default. PostgreSQL will trust no CAs by default (unless you are doing a debian install, in which case they put some default CAs in there - this is another reason why this is a really bad idea from a security perspective). PostgreSQL also does not prompt the user with a potentially incorrect name field on the certificate asking if this is ok or not - it will just reject the certificate if it doesn't match (correctly or incorrectly), closing another attack venue. So the bug is really only significant if you can't trust your CA - but the whole point of the CA is that it is a trusted entity...

PostgreSQL 8.4 is the first version to properly support certificate name validation, and also the first version to support client certificate authentication, both of which are vulnerable to this bug, neither of which is enabled by default. However, previous versions are also indirectly vulnerable, because they exposed the CN field of the certificate to the application for further validation. So you could have a stored procedure checking the client certificate, or just the libpq application checking the server certificate, even in earlier versions. And given the API structure, there was no way for these outside processes to know if they were being fooled or not. So if you are using an application that makes use of this on previous versions of PostgreSQL, you still need the patch - there is no way to fix the bug from the application.

The summary of this post is that this vulnerability is a lot less serious in PostgreSQL than in many other systems that had the issue. That doesn't mean it's not there, and that it should be (and have been) fixed. But it means that this vulnerability alone is likely not reason enough to rush an upgrade on your production systems - most likely you're not affected by it. On the PostgreSQL security page it is tagged with classification A, which is the highest. This is more an indication that the system we're using for classification really doesn't take these things into consideration - something we will look into for the future.

Feedback from pgday.eu

I've finally had the time to summarize the feedback we received from pgday.eu.

We received feedback from about 35 people, which is obviously way less than we were hoping for. Ideas for how to improve this for next time are very welcome! This also means that the figures we have are not very exact - but they should give a general hint about what our attendees thought.

I just sent out the individual session feedback summaries to each individual speaker. These will not be published - it's of course fine for each speaker to publish his own feedback if he wants to, but the conference organizers will not publish the detailed per-session data.

The statistics we do have show that most of our speakers did a very good job, and that the attendees were in general very happy with the sessions. We have also received a fairly large amount of comments - both to the conference and the speakers - which will help us improve specific points for next year!

I'll show a couple of graphs here with the total across all sessions and speakers. In these graphs, 5 is the highest score and 1 is the lowest.

The attendees also seemed to be very happy with our speakers, which is something I'm very happy to hear about. It's also good to see that almost nobody felt the speakers didn't know very well what they were talking about - always a worry with a conference that has so many experienced community people attending.

Actually trying to figure out which speaker is best using this data is very difficult. But here's a list of the top speakers based on speaker quality, who had more than 5 ratings on their talks. The list includes all speakers with an average score of at least 3.5. There are a lot more hovering around that line, but there has to be a cutoff somewhere... Again note that there are still not that many ratings to consider, so values are pretty unstable. I've included the standard deviation as well to make sure this is visible.

Place | Speaker | Score | Stddev | Num 1 | Gavin M. Roy | 4.9 | 0.5 | 18 2 | Guillaume Lelarge | 4.9 | 0.4 | 7 3 | Robert Hodges | 4.8 | 0.4 | 13 4 | Magnus Hagander | 4.8 | 0.4 | 20 5 | Jean-Paul Argudo | 4.8 | 0.5 | 8 6 | Joshua D. Drake | 4.6 | 0.7 | 9 7 | Simon Riggs | 4.6 | 0.6 | 17 8 | Dimitri Fontaine | 4.5 | 0.5 | 14 9 | Greg Stark | 4.3 | 0.5 | 8 10 | Vincent Moreau | 4.1 | 0.6 | 8 11 | Mark Cave-Ayland | 4.0 | 0.6 | 11 12 | David Fetter | 3.9 | 1.1 | 9 13 | Gabriele Bartolini | 3.7 | 1.0 | 15 14 | Heikki Linnakangas | 3.6 | 0.7 | 9

All of these are clearly very good numbers.

So once again, a big thanks to our speakers for their good work. And also a very big thanks to those who did fill out the session feedback forms - your input is very valuable!

Update: Yes, these graphs were made with a python script calling the Google Charts API. Does anybody know of a native python library that will generate goodlooking charts without having to call a remote web service?

My pgday.eu pictures are up

I've finally gotten around to uploading my pictures from PGDay.EU 2009 to [ my smugmug gallery].

Clearly the conference was tiring, and we all needed a rest... (yes, this was during JD's talk)

And as the picture says, don't forget to submit your feedback - the site is still open for that!

PGDay.EU 2009 - it's a wrap

I'm currently sitting on my flight home from Paris CDG, after a couple of very hectic days. It's going to be a couple of days (which in reality is going to drag out into a couple of weeks due to other work engagements and then travel for the JPUG conference) before it'll be possible to completely evaluate the conference and things around it, but here's what I have so far.

I'm going to leave the evaluation of the talks themselves to somebody else. There were many others of the "regular PostgreSQL bloggers" present at the conference and we've already seen some posts around it. Hopefully there will be more, both in French and English. If you are blogging about this and your blog isn't already up on Planet PostgreSQL, please consider adding it so that the community at large gets notification of your posts.

Continue reading

PGDay.EU day one

align="right" So, day one is almost finished, when it comes to the conference itself - and then it's off to the EnterpriseDB evening party. A quick summary of the day is: awesome.

Going into a little more detail, the day started with us actually getting up painfully early. Got to ParisTech in the morning, right as they opened up our room. I have to say the facilities at ParisTech have been great - the rooms are in great shape, perfect size, and all the A/V equipment is working perfectly. (Yes, there is a slight flicker on one of the projectors, but it's not bad).

I did the intro section with Jean-Paul, and there's really not much to say about that. We (well, I) forgot to add a slide about the feedback - oops. I bet that's one reason we don't have as much feedback entered yet as we'd like.

Simon took over with a keynote, which was very good. Simon is a very good speaker, and he found a good balance between technical and non-technical talks. It was a nice way to kick off the conference. At this time, we had somewhere between 125-150 people had shown up, which is definitely not bad.

I half-followed the English track after that, with talks about PostGIS and Data Warehousing, which were both very good talks. Also spent some time in the organization, which has really worked pretty smoothly. We've had a few minor issues, but they were all solved quickly.

Lunch was fantastic. Many thanks to our great caterers who served us an amazing lunch, with good organization, and more than enough food. Couldn't be better!

align="left" Watched Gavin's talk on scalability after lunch, which is always a good one. After that I had my own talk which went at least Ok, though I did finish a bit early. After that it was off to a pgadmin developer meeting, which is where I am now.

So I'd better go now, so I don't miss out on the activities.