Andrew's PostgreSQL blog

My many thanks to Radu Radutiu who has found the solution to my SSL problem. It turns out that if you use a CRL file it needs to contain a CRL (even if it's only empty) for every CA in the chain of CAs. We need to document this.

I'm always having trouble with things like uploads in Serendipity. Maybe I need to switch to a blogger host. Anyway, if anyone had troubles downloading the test certificates from my post about SSL, I have put them somewhere else and fixed the blog entry.

Here's a problem I and some others have been wrestling with. The problem was presented by a PostgreSQL Experts client. If you want to play along, here are the test files I have been using. Included is a root Certificate Authority certificate, an Intermedfiate Certificate Authority certificate signed by the root CA, a server certificate and key and two client certificates and associated keys, and a revocation certificate which revokes the second client certificate. The server certificate, client certificates and revocation certificate are all signed by the Intermediate CA. The client certificates are for a user named "andrew"

Our test platform is PostgreSQL 9.1 built with openssl, and a config setting of 'ssl = on', a user and database both named "andrew" and the following pg_hba.conf line:

hostssl   all  all 127.0.0.1/32 cert

All the binaries are under $INSTALL and so is the data directory.

To install the server certificates (but not the CRL just yet) we do:

cat root.crt > $INSTALL/data/root.crt
cat server.crt ra.crt > $INSTALL/data/server.crt
cat server.key > $INSTALL/data/server.key
$INSTALL/bin/pg_ctl -D $INSTALL/data -l $INSTALL/logfile -w start

Next we test both client certificates are working:

$ $INSTALL/bin/psql 'host=localhost sslmode=verify-ca sslcert=client.crt sslkey=client.key sslrootcert=root.crt'
psql (9.1.3)
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.

andrew=# \q
$ $INSTALL/bin/psql 'host=localhost sslmode=verify-ca sslcert=client2.crt sslkey=client2.key sslrootcert=root.crt'
psql (9.1.3)
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.

andrew=# \q
$ 

So far, so good. Now let's install the revocation certificate

cat cli2.crl > $INSTALL/data/root.crl
$INSTALL/bin/pg_ctl -D $INSTALL/data -l $INSTALL/logfile -w restart

Is the revocation effective?

$ $INSTALL/bin/psql 'host=localhost sslmode=verify-ca sslcert=client2.crt sslkey=client2.key sslrootcert=root.crt'
psql: SSL error: sslv3 alert certificate revoked
$

Yes, it sure is. So let's make sure we can still use the unrevoked certificate:

$ $INSTALL/bin/psql 'host=localhost sslmode=verify-ca sslcert=client.crt sslkey=client.key sslrootcert=root.crt'
psql: SSL error: tlsv1 alert unknown ca
$

Oops! That's not supposed to happen!

Anyone who can shed some light on what's going on here would earn at least some gratitude from me. I don't think I'm doing anything wrong, but I could certainly be missing something. This looks like a nasty bug, but I'm not sure if it's a bug in Postgres or in OpenSSL.

Yesterday on IRC Andrew Gierth suggested it would be good to have a count_if aggregate that would count the rows where it's argument is true. This seems so obviously useful that I cooked up a version quickly in two tiny bits of SQL:

andrew=# create or replace function countif_trans(bigint, bool)
andrew-# returns bigint language sql as
andrew-# $$ select case when $2 then $1 + 1 else $1 end $$;
CREATE FUNCTION
andrew=# create aggregate count_if (bool)
andrew-# (sfunc = countif_trans,
andrew(# stype = bigint, initcond = 0);
CREATE AGGREGATE

Now you can easily do repeated counts of different conditions in one pass over a set of rows:

andrew=# select count_if(relkind in ('r','v','S'))as "r,v,S",
andrew-# count_if(relkind in ('i', 't')) as "i,t" 
andrew-# from pg_class;
 r,v,S | i,t 
-------+-----
   165 | 134
(1 row)

For speed the transition function should probably be written in C, but for many purposes this is good enough.

One of PostgreSQL's nice new features in the 9.2 release is index-only scans. However, it's only likely to be used when the table's relallvisible setting in pg_class has been set, and unfortunately the ANALYSE command doesn't do this - only VACUUM does. So, if you want index-only scans to work right after the upgrade (whether this is done by importing a dump or by running pg_upgrade), you should probably run a database-wide VACUUM ANALYSE as soon as your new database is available. Otherwise you'll have to wait until autovacuum kicks in, assuming you have it enabled, or your next vacuum cycle.

I often find Richard Stallman annoying, or just plain wrong, sometimes hilariously so, as in his defence of the MySQL two-licence business model. But sometimes he's right, too. I think his criticism of Ubuntu's spyware is one such case.

This is not a great breakthrough, but I had to wrestle slightly with it yesterday in the context of writing an extension with a variadic Postgres function in C.

In C, you can pass zero variadic arguments to a variadic function like printf(), but Postgres is a bit less forgiving. It requires there to be a value supplied for the variadic array. so you get this happening:

andrew=# create function v1(x text, variadic y int[]) 
returns text language plpgsql as 
$$ begin return x ||y::text; end; $$;
CREATE FUNCTION
andrew=# select v1('a',1,2,3);
    v1    
----------
 a{1,2,3}
(1 row)
andrew=# select v1('a');
ERROR:  function v1(unknown) does not exist
LINE 1: select v1('a');
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

Fortunately, Postgres also allows you to supply the variadic parameter explicitly as an array, so we can achieve the wanted effect via overloading:

andrew=# create function v1(text) 
returns text language sql as 
$$ select v1($1,variadic array[]::int[]) $$;
CREATE FUNCTION
andrew=# select v1('a');
 v1  
-----
 a{}
(1 row)

And we can now refine the original function to do something nicer in the zero variadic arguments case:

andrew=# create or replace function v1(x text, variadic y int[]) 
returns text language plpgsql as 
$$ begin return x || case when array_length(y,1) > 0 then y::text else '' end; end; $$;
CREATE FUNCTION
andrew=# select v1('a');
 v1 
----
 a
(1 row)

IVC have contracted me to update the Redis Foreign Data Wrapper, and Dave Page has been good enough to make a collaborator on the repo. Today I comitted the first bunch of updates, which was basically to provide a separate branch for building with each Postgres branch, just as was done not long ago with the File Text Array Foreign Data Wrapper. The REL9_2_STABLE and master branches were updated to reflect the FDW API changes that took place in the 9.2 branch.

There are one or two bits of the FDW I'm not quite happy about that I'll be digging into as I get time, so this isn't really finished work. I've also seen reports of the FDW inducing a crash, but I haven't been able to reproduce that.

There will be some more non-FDW Redis work coming along, too, but this is a good start. I hear of a number of companies building high performance web applications using hybrid PostgreSQL+Redis persistence layers, so the more we can make them play nicely together, the better off we'll be.

Tom Lane complained the other day that we needed some more buildfarm animals building with the -DCLOBBER_CACHE_ALWAYS setting, so I set one up yesterday. It's the same machine as nightjar, but this runs at a different time of the day. It's easy enough to do, but the tests take a very long time to run. Compare the times for the new machine friarbird and nightjar, which does substantially more work in about 4% of the time. In fact, the ratio of time would be even higher but for the presence of some constant factors (e.g. build time). So I set this to run around midnight my time every day, and only for the main development branch. That way it should not stress either the VM it runs on or the host machine.