ODBC and Kerberos

Finally got around to testing the new ODBC version for Kerberos support, and I'm happy to report it works perfectly and requires no hacks to install.

To use it, make sure you have a libpq with Kerberos support compiled in (yeah, this means manual build - we're looking into this for 8.1, but no promises yet). Then just set up your data source as you normally would, and skip specifying the password (you must specify a username though). As long as your pg_hba.conf is properly set up, that's all you need.

Synchronizing Apache passwords

I have an application with the need to synchronize apache passwords for basic auth (using DBM files) from a postgresql table. Using mod_auth_pgsql simply not an option - there may be other cases where it's an option but not wanted, in this case there it was out from the beginning.

So. pl/perl once again to the rescue, along with the CPAN module Apache::Htpasswd. Pretty easy to do with a trigger. Hit the link to see the code.

CREATE TABLE users (
  userid character varying(32) NOT NULL PRIMARY KEY,
  "password" character varying(32) DEFAULT randpwd() NOT NULL
);

CREATE FUNCTION sync_user_list() RETURNS "trigger"
  AS $_$

use Apache::Htpasswd;
use strict;

my %25userlist;

open(HT,"/data/webusers/users") || elog(ERROR,"Could not read password file: $!");

while (<HT>) {
  my ($uid,$pwd) = split /:/;
  $userlist{$uid}=1;
}

close(HT);

my $ht = new Apache::Htpasswd("/data/webusers/users") || elog(ERROR,"Cannot open password file: $!");

my $r = spi_exec_query("SELECT userid,password FROM users");

my %25users;

for (my $i = 0; $i < $r->{processed}; $i++) {
  my $u = $r->{rows}[$i]->{userid};
  my $p = $r->{rows}[$i]->{password};
  $users{$u} = 1;
  if (!$ht->fetchPass($u)) {
    $ht->htpasswd($u,$p) || elog(ERROR,"Could not add user $u: " . $ht->error());
  }
  elsif (!$ht->htCheckPassword($u,$p)) {
    $ht->htpasswd($u,$p,1) || elog(ERROR,"Could not add/set password for $u: " . $ht->error());
  }

}

foreach my $u (keys %25userlist) {
  if ($users{$u} != 1) {
    $ht->htDelete($u);
  }

}

return;
$_$
   LANGUAGE plperlu;



CREATE TRIGGER trg_sync_user_list
  AFTER INSERT OR DELETE OR UPDATE ON users
  FOR EACH STATEMENT
  EXECUTE PROCEDURE sync_user_list();

pginstaller progress

Code has now been committed to pginstaller to move user creation to the execute sequence. This means it can be used from silent installs, and also that the user won't be created until you have filled in all dialogs - in 8.0 we create it as soon as you leave the service config screen.

If you need this, or really anybody else as well, please grab latest cvs, test and let us know how it works. If you can't compile yourself, wait for the next beta which hopefully won't be too far off. But as this is a fairly major change, we'd appreciate a lot of testing.

A couple of minor fixes have also gone in, as we're nearing release. Not a lot of bug/feature-requests still pending now.

Hectic autumn

Wow, this autumn is turning out really hectic. Which can also be read as very interesting /mha/templates/default/img/emoticons/smile.png alt=":-)" style="display: inline; vertical-align: bottom;" class="emoticon" /

Apart from helping out with the final touches of PostgreSQL 8.1 (now in beta! Download and test!) and finishing off the 8.1 installer, I'm also involved in the Slony on win32 project Dave has been referring to.

Apart from coding up the win32 port of Slony, this also means I'll be going to Tokyo to deliver a talk on it /mha/templates/default/img/emoticons/laugh.png alt=":-D" style="display: inline; vertical-align: bottom;" class="emoticon" / Which is going to be really interesting - a big thanks to Hiroshi for setting this up!

And finally, I'm also a co-speaker with Simon Riggs on Euro OSCON in Amsterdam, on the topic of PostgreSQL in the Enterprise. My part of the talk will deal with win32 issues, such as Active Directory integration. If you're a PostgreSQL user or developer, I'll look forward to meeting you there!

Oh, and on top of this, all the normal stuff at work of course /mha/templates/default/img/emoticons/smile.png alt=":-)" style="display: inline; vertical-align: bottom;" class="emoticon" / So there will certainly not be many boring moments.

dblink-ldap on win32

Just a quick note that I now have dblink-ldap running fine on win32. I have some performance issues in my lab environment, but I think I've tracked them down to a broken DNS setup. Will need to try it again in a better environment in the next couple of days.

Let me know if you want to test it and I can send you a binary of what I have now.

Not caught up

I'm back for my around-two-weeks of vacation - much needed, and turned out very well.

Meanwhile, Dave has done a lot of work on the installer, and even put out a beta release.

Still leaves the break-out-account-creation part for me to do, but a lot of the other TODO items have been ticked off. Great work Dave!

Dave Page joins core

Dave Page has been added to PostgreSQL core, so now the win32 team has a representative there! Not to mention the website team. For these reasons and others I believe Dave is a great choice for the core team.

Congratulations, Dave!

pginstaller for 8.1

Got together with Dave this monday as I was visiting the UK. Didn't have much time to do anything productive, but we did get some discussion going on what to do about pginstaller for 8.1. Some highlights:

  • User creation has to be moved to the execute sequence. This probably shouldn't be too hard now. It was when we first added it, but things have changed since. This would also mean it could easily be exposed to silent installs, which is a much requested feature.
  • We need to move libpq.dll out of SYSTEM32. The current working model for this is to just remove the ability to move binaries between different directories, and force them into the same directory. Then we just stick libpq.dll there, and the problem is solved. We still need to do some testing on how this will effect the ODBC driver (the new one uses libpq), especially if you have 8.0 installed with libpq.dll in SYSTEM32.
  • Slony will definitly be included, assuming the win32 version is stable and released. But that's the hope.

Of course, we're going to try to tick off anything else that is on the feature tracker on pgfoundry. If anybody wants to help out, now's a great time to start feeding patches :-)

And Dave - nice meeting you! Looking forward to the next time - most likely in Tokyo for the Slony talk!

Slony-win32 service

I have submitted a patch that runs Slony as a service on Win32. It supports multiple parallell engines (if you need to replicate more than one database) if needed.

Still needs a few things polished up (installation/uninstallation, registration of multiple engines etc) but I'd say win32 Slony is progressing very nicely. (Much of these things will probably be available in the installer and pgAdmin as well, but we'll need commandline interfaces as well)

Unicode on win32

As many PostgreSQL users are aware, PostgreSQL 8.0 does not fully support Unicode on win32 due to limitations in the API. Hopefully this will be fixed in 8.1, as we now have two different patches that solves this problem in different ways. The patches were posted a little over a week ago, but due to me sending it to the wrong list it didn't go through because of size. It has now been sent to -patches as well, and is available.

People interested in this are encouraged to apply one or both patches (the native patch is a whole lot easier than the ICU one, as it has much fewer dependencies) and test, test, test! You can grab the patches from the list archive. Let us know your results by a mail to -hackers-win32 or plain -hackers.

Conferences

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

Upcoming

Past

Warsaw User Group
Jun 29, 2020
Virtual, Virtual
Postgres Vision
Jun 23-24, 2020
Online, Virtual
PGCon 2020
May 26-29, 2020
Online, Virtual
pgDay.paris 2020
Mar 26, 2020
Paris, France
Nordic PGDay 2020
Mar 24, 2020
Helsinki, Finland
More past conferences