Lots of activitiy

Lots of activity in the PostgreSQL tree and on the lists lately affecting me directly or indirectly.

Seems we'll have GIST WAL Logging for 8.1. Hopefully we'll also have GIST concurrency. This is great - it'll bring tsearch2 to a whole new level /mha/templates/default/img/emoticons/smile.png alt=":-)" style="display: inline; vertical-align: bottom;" class="emoticon" / Yay!

It also seems the old autovaccuum-in-backend discussion is back up. Let's hope it gets in before the feature freeze this time. It's going to make life a lot easier for a lot of people - especially in the win32 camp where installation was previously a bit of a hassle. If it's done, we'll get it working with the MSI installer out of the box.

Andreas, of pgAdmin fame, is working on getting the backend instrumentation functions in this time - they were rejected for 8.0 because they were submitted too late. While a bit controversial to some people, let's hope they get in this time! Having to rely on contrib modules can be a pain sometimes.

I seem to be all out of pending patches. Both the pending timezone patches are in, and the kerberos work has been in for a while. Now perhaps I have time to look over that signaling patch, and to start digging into Slony.

Also seems the win32 build has been broken for a while. The fact that I didn't notice shows I haven't built on win32 enough lately.

PostgreSQL web ftp browser symlink fix

I've just committed a fix to the PostgreSQL website ftp browser to fix handling of symbolic links in the ftp tree.

Previously, symbolic links were only read on creation of the browse pages. This made links to for example /ftp/win32 (which is a symbolic link to /ftp/binary/v8.0.3/win32) fail, since only the /ftp/index.html page was actually updated. Unfortunatly, just this link is present in the release notes for 8.0.3 - and was broken. Not good!

The new code generates Apache rewrite rules that does proper redirection of anything inside a symlinked directory.

Timezone code finished?

I just finished off the docs part of my latest time zone patch. As Tom appeared at least reasonably positive, I'm hoping for this to go into the codebase shortly.

This patch in itself is fairly simple, and builds on the previous patch that was applied a couple of months back that removed the gobal timezone from the internal API.

Datatyping with PL/Perl

PostgreSQL supports custom datatypes through either "full datatypes" or "DOMAIN"s. Doing full datatypes requires C-language coding that is loaded into the backend, whereas DOMAINs are SQL level. This makes DOMAINs a lot easier.

Next topic, procedural languages. PostgreSQL supports multiple procedural languages in the same installations - pl/pgsql which is "almost oracle pl/sql", pl/tcl, pl/python and... pl/perl. I like pl/perl mainly because it gives me the power of perl regexps :-)

Final topic before summary, Swedish SSNs (that's social security numbers, not nuclear attack submarines - we don't have those). All citizens in Sweden are given a "personnummer" (which is about the same as a SSN in the US) at birth, on the form YYMMDD-PPKX. Year, month, day should be self-explaining. PPK is a serial number, in which PP represents which area of the country the person is born, and K is even for females and odd for males. Finally, X is a checksum. You need a personnummer for almost everything. And of course, we need to store this in databases all the time.

Which brings me to the point of this post. Using a combination of DOMAIN and custom functions, you can create a simple datatype for personnummer that will automatically validate that the entered number is correct. Sure, if you have access you can validate it against a table of all valid personnummer, but that table is very restricted in availability. So we'll settle for validating the checksum. Here's how to do it:

CREATE OR REPLACE FUNCTION validate_pnr( text) RETURNS bool AS
$BODY$

# Basic validation
return 'f' unless ($_[0] =~ /^(\d{2})([01]\d)([0123]\d)-(\d|T)(\d|F)\d{2}$/);

return 'f' if ($2>12);

return 'f' if ($3>31);

# No checksum check for temporary numbers

return 't' if ($4 eq 'T');

# Checksum

my $s = substr($_[0],0,6) . substr($_[0],7,3);
my $m = 2;
my $c = 0;

while (length($s)) {

  my $d = chop($s) * $m;
  $c += ($d%2510)+int($d/10);
  $m = ($m>1)?1:2;

}

$c = 10-($c %25 10);



return ($c == int(chop($_[0])))?'t':'f';

$BODY$

LANGUAGE 'plperl' IMMUTABLE STRICT;



CREATE DOMAIN persnr

  AS varchar(11)
  NOT NULL
    CONSTRAINT persnr_check CHECK validate_pnr((VALUE)::text);

Simple as that. You will notice there is an exception for personnummer with the final four numbers as TFxx. These are used for temporary numbers for people who are not Swedish citizen but need to be registered in official databases that require a personnummer. Now we can use this like:

CREATE TABLE people (personnr persnr PRIMARY KEY, name varchar(128));

Or whatever. And the database will prevent us from putting in an invalid personnummer.

One could of course use the CHECK constraint on the table, but then we'd have to write that one once for each table. Using a DOMAINs we can re-use this information in multiple tables.

I'm sure this can be written in more efficient perl. If anybody wants to do it for me, please let me know and I'll happily update my installations /mha/templates/default/img/emoticons/smile.png alt=":-)" style="display: inline; vertical-align: bottom;" class="emoticon" /

(Sidenote to anybody blogging on worldpress (possibly only worldpress-pg? I'm not really sure): The thing eats backslashes in the editor. If you save multiple times, one level of backslashes is lost every time.. Also, the preview box is broken with this.)

Success with OLEDB

I can happily report that the PostgreSQL OLEDB driver works perfectly with Kerberos integrated login with Active Directory! Didn't even need any hackings at all.

Yay!

DOSing PostgreSQL :-(

During my PostgreSQL coding last weekend I found a fairly horrible DOS in a library that PostgreSQL can be compiled with. It's totally exploitable to a DOS (double-free) in a PostgreSQL environment, and the user doesn't need to be authenticated to do it. The crashed backend will cause the postmaster to kick out all other backends and restart then - not good!

The good news is that it's not compiled-in by default, and even if you compile it in, you also have to explicitly enable it to become vulnerable.

Also, the guys with the library responded really fast (hours) and I expect a complete advisory along with a patched version to be out sometime the end of this week or beginning of next. At which times full details will be available...

In summary, there is nothing wrong with PostgreSQL here - it's just a vector to exploit the vulnerability in the library. So there will be no PostgreSQL security patch...

The evil that is RPM

I just returned from yet another really bad experience with RPM. Scenario: RedHat AS 3.0, freshly installed this morning. Not a single package installed from anywhere other than the RHAS CDs. Needed to change the smarthost in sendmail. This required some 10-15 new RPMs to be installed. One of which was "glibc-2.3.2-95.30-i386". So I install it, and bang, RPM stops working with broken libraries. Turns out I should have installed the -i686 version, even though the system told me to install -i386.

The solution? Copy the i686 version over to a slackware machine, run rpm2tgz, copy it back and just untar it in the root. System back alive. Then run some rpm -i --force to make it understand it was there, and now pray it keeps working. It seems good.

No offense to the pg RPM people, but I would never risk using RPM to install my database. PostgreSQL goes in from source every time. (On Unix that is - on Win32 I use the installer. Because frankly, I've never seen this level of problems on a Windows machine as long as you don't install "weird software")

Now, I wouldn't run RedHat on the box if it was my choice either - slackware is my distro of choice for servers. But for some reason people who say "we support Linux" really only mean "we support RedHat". Which sucks really bad, but that's a different story...

Oh, and in about 1 hour of doing simple "rpm -i" on all the required packages, the RPM database was corrupted four times. Perhaps it's time for these people to put their data in a PostgreSQL database...

Finally, thanks to Devrim for attempting to help me solve this in a less hackish way.

Open source and support

So people keep telling me that you can't get good support for open source. At least, you can't get it unless you pay for it. At least not if you need actual code changes. At least not.. etc etc.

Yesterday I opened a bug with Mono, because it crashed (segfault) whenever I tried to execute anything on the Graphics class. In about 30 minutes I had my first response, asking for some more information. Another 20 minutes later and I had a solution - I was missing a scalable sserif font on my system (it's a server after all). And 5 more minutes confirming that a better error message will appear in a later version.

This is the second time I had to file a bug with Mono. The first time, it was XML-related, and an actual code bug. In this case I had a patch to apply to my installation within 48 hours, and it was included in the next release (in the meantime, I had a workaround).

In contrast, I've had an ipsec issue open with Microsoft since last week, without an actual solution in sight. Granted, this is a more complex issue than the ones above. And don't get me wrong, the guy(s?) working on it from the MS are doing a good job. But it generally takes more time. And more than once I've had cases closed with "no resolution, issue will not be fixed".

There are of course cases when things aren't fixed, but a (good or bad) workaround is provided in the open source world as well. But the argument that support is worse there just doesn't stand. In my experience, it's usually about as good.

Unless you are in a position where you can hire someone (or have someoen on your staff) that can actually fix the broken code. In this case, open source is a winner because that is possible to do this. That's just not possible in closed source - I can have an army of good coders, they still can't fix a bug in Windows.

Nice article about the installer

Seems at least somebody think we did a good job with the MSI installer for PostgreSQL on Win32 (and the db, but the installer was the focus). PostgreSQL vs MySQL vs Oracle on Win32, and PostgreSQL won the total score. And in the point specific about the installer, it was 10/10 for PostgreSQL, 5/10 for MySQL and 7/10 for Oracle.

Oh. The article.

Postgresql vs Active Directory

My project to integrate PostgreSQL with Active Directory is progressing nicely.

(Yes, this is a good thing. MS SQL Server has "Integrated Security" which means it leverages the exsting Windows login to automatically access the database without a separate password. In a domain environment (read corporate environment), this is usually a very good thing. Having similar functionality in PostgreSQL helps make migration easier.)

Once the patches that were included in 8.0.2 got in, the remaining job wasn't very hard. Getting basic kerberos interoperability working was a lot easier now than last time I tried it, lots of progress made on the kerberos distributions there.

So far it only works if your server runs on Linux (or any unix should work - the point is that the win32 native server currently does not work). The clients can run either Windows or Unix. It requires the clients to use libpq (which means perhaps it works with the OLE DB driver since it's based on libpq - I need to test that).

It also requires the server and libpq to be recompiled with a different compile option. I'm going to be working on a patch for 8.1 to solve that.

A HOWTO document will be written once I've ironed out the last parts of the process. What I have now is enough for me to deploy to a set of about 30 users for 10dbs, but the build instructions are not exactly clear ATM.

Oh, and big thanks to Dave and the pgAdmin team for putting out 1.2.1 so quickly which had a fix required for Kerberos authentication to work.

Conferences

I speak at and organize conferences around Open Source in general and PostgreSQL in particular.

Upcoming

SCALE 17x + PGDay
Mar 7-10, 2019
Pasadena, California, USA
pgDay.Paris 2019
Mar 12, 2019
Paris, France
Nordic PGDay 2019
Mar 19, 2019
Copenhagen, Denmark
PGConf.DE 2019
May 10, 2019
Leipzig, Germany
PGDay.IT 2019
May 17, 2019
Bologna, Italy
PGCon 2019
May 27-31, 2019
Ottawa, Canada

Past

FOSDEM+PGDay 2019
Feb 1-3, 2019
Brussels, Belgium
PGConf.Asia 2018
Dec 10-12, 2018
Tokyo, Japan
DC PostgreSQL Users Group
Nov 14, 2018
Washington DC, USA
New York City PostgreSQL User Group
Nov 13, 2018
New York City, NY, USA
Driving IT 2018
Nov 2, 2018
Copenhagen, Denmark
More past conferences