Version control unmaddened

PostgreSQL is the only project I'm involved in that uses CVS. Most other places now use Subversion, Bazaar, or something else. That's fine, but it's really annoying to continously type in the wrong tool when you switch back and forth.

So today I fixed this problem. I wrote this little shell script I call vcs:

#!/bin/sh



if [ -d CVS ]; then

       cvs "$@"

       exit $?

fi



if [ -d .svn ]; then

       svn "$@"

       exit $?

fi



dir=$(pwd)



while [ -n "$dir" ] && [ "$dir" != / ]; do

       if [ -d "$dir/.bzr" ]; then

               bzr "$@"

               exit $?

       fi



       dir=$(dirname "$dir")

done



exit 77

This can be extended in obvious ways.

Put this in your path, and then you can use vcs for everything.

But somehow, cvs is really easy to type, or maybe your fingers are wired to svn or something else. So add to this a couple of shell functions like this:

bzr() { vcs "$@" || { [ $? -eq 77 ] && command bzr "$@"; }; }

cvs() { vcs "$@" || { [ $? -eq 77 ] && command cvs "$@"; }; }

svn() { vcs "$@" || { [ $? -eq 77 ] && command svn "$@"; }; }

Now you can type cvs update or whatever your brain or fingers prefer and it will do the right thing.

EnterpriseDB and the Professional Grade

I was naturally confused when I read about EnterpriseDB announcing the First-Ever Professional-Grade PostgreSQL Distribution for Linux. I would have thought that at least Great Bridge, Red Hat, and Pervasive would have prior claim to that title, and there are surely others who might fit this category but don't make so much noise about it.

So that needed to be cleared up. I went to download their "EnterpriseDB Postgres" package, versioned 824-linux-x32-b1. (Is that beta 1?) This unpacks to a setup_x86.bin file. I recalled my bumpy experience with the installer of EnterpriseDB proper, but this one seems to work quite a bit better. It's the same Java-type installer, for what it's worth, but this one actually ran to the end without error at the first attempt. Somehow it even installed all the software with the proper library dependencies for my Debian system, including the dreaded libssl issue, so that was really a relief.

The installation contains PostgreSQL, Slony-I, PostGIS, pgAdmin, phpPgAdmin, JDBC and ODBC drivers, Apache, and PHP, each in current versions. The default installation goes to /opt/edb-postgres/8.2/, but you can change that. The entire installation is owned by the postgres user, so no points for security.

The installation layout looks like this:

$ ls -l /opt/edb-postgres/8.2/8.2.4
total 56
drwxrwxr-x 15 postgres root 4096 2007-08-08 11:13 apache/
drwxrwxr-x 2 postgres root 4096 2007-08-08 11:14 bin/
drwxrwxr-x 3 postgres root 4096 2007-08-08 11:13 doc/
-rwxrwxr-x 1 postgres root 369 2007-08-08 11:13 env.55434*
drwxrwxr-x 2 postgres root 4096 2007-08-08 11:13 etc/
drwxrwxr-x 6 postgres root 4096 2007-08-08 11:13 include/
drwxrwxr-x 2 postgres root 4096 2007-08-08 11:14 install_logs/
drwxrwxr-x 2 postgres root 4096 2007-08-08 11:13 jdbc/
drwxrwxr-x 3 postgres root 4096 2007-08-08 11:14 lib/
drwxrwxr-x 7 postgres root 4096 2007-08-08 11:13 pgadmin3/
drwxrwxr-x 6 postgres root 4096 2007-08-08 11:13 php/
-rwxrwxr-x 1 postgres root 214 2007-08-08 11:13 postgres.env*
drwxrwxr-x 5 postgres root 4096 2007-08-08 11:13 share/
drwxrwxr-x 6 postgres root 4096 2007-08-08 11:13 utils/

This was clearly designed by a Windows engineer who never heard of a Linux file system hierarchy standard.

How is this actually works out is that there is a bin/psql which is a shell script that sets PATH and LD_LIBRARY_PATH and then calls bin/psql.bin which is the actual binary. Unfortunately, this shell wrapper neglects to set the right TCP port (which I configured in the installer as 55434, the default offer was 5433), so just calling psql without options doesn't connect. I'm not sure whether it is intentional that way.

But all the programs seem to generally work. Even pgAdmin has no problems with libraries or whatnot. phpPgAdmin is available at http://localhost:8080/. The default database contains a few sample tables that I seem to recall to have seen in the EnterpriseDB installer as well. All in all it's a normal PostgreSQL installation with a crazy installation layout. There is also an init script based on the one you find in the PostgreSQL source code distribution. A vacuum cron job or autovacuum is not configured, and the postgresql.conf file isn't tuned.

Now what makes this "professional grade"? The installation uses insecure permissions, nobody knows how to upgrade these things, nobody knows whether there will be security updates for the integrated Apache and PHP components, there is no integration into log rotation, log checking, or other system services, the setup isn't tuned or configured, no vacuum, nobody has access to the source code of the installer, so you don't know what's in there and can't fix it yourself.

It remains to be explained why this is any more useful than whatever your local apt or yum installs.

EnterpriseDB Trial continued

I have received a few helpful tips yesterday after my failed attempt to install EnterpriseDB Advanced Server on Debian lenny. The most useful was to run

sudo ./edb-linux-x86_82xx.bin -console -extractall

to extract the archive without additional setup. I was a bit disturbed to notice that this installed the entire software under /opt/EnterpriseDB/8.2/ world-writable. I was able to "ldd initdb" enough until I had set the required symlinks libssl.so.4 and libcrypto.so.4 and installed the missing packages related to termcap from Debian sarge (oldstable).

After that I removed the directory /opt/EnterpriseDB/ and restarted the installer, but the installer was convinced I still had EDB installed and aborted. I had no idea what was wrong, because the old installation directory was surely gone. After a bunch of experimenting, stracing, and file searching I found a new directory /root/InstallShield/ that apparently contained an hsqldb database that stores the ultimate truth about what is really installed. Once you blow that away, you can start the installation again, and this time it actually ran to the end.

The installation layout looks like this from the top:

$ ls -l /opt/EnterpriseDB/8.2/
insgesamt 88
-rwxr--r-- 1 enterprisedb edb 2467 2007-07-18 14:09 BrowserLauncher.class*
drwxr-xr-x 5 enterprisedb edb 4096 2007-07-18 14:10 connectors/
drwx------ 13 enterprisedb edb 4096 2007-07-18 14:10 data/
drwxr-xr-x 7 enterprisedb edb 4096 2007-07-18 14:09 dbserver/
drwxr-xr-x 5 enterprisedb edb 4096 2007-07-18 14:10 devstudio/
-rwxr-xr-x 1 enterprisedb edb 101 2007-07-18 14:10 devstudio_desktop_launcher.sh*
drwxr-xr-x 2 enterprisedb edb 4096 2007-07-18 14:10 doc/
-rwxr-xr-x 1 enterprisedb edb 3116 2007-04-17 20:19 edbDynatune_multiOS.sh*
drwxr-xr-x 2 enterprisedb edb 4096 2007-07-18 14:09 icons/
drwxr-xr-x 2 enterprisedb edb 4096 2007-07-18 14:10 install_logs/
drwxr-xr-x 6 enterprisedb edb 4096 2007-07-18 14:09 jre1.5/
drwxr-xr-x 5 enterprisedb edb 4096 2007-07-18 14:09 mgmtsvr/
-rwxr-xr-x 1 enterprisedb edb 157 2007-07-18 14:09 mgmtsvr_desktop_launcher.sh*
drwxr-xr-x 4 enterprisedb edb 4096 2007-07-18 14:10 rpconsole/
-rwxr-xr-x 1 enterprisedb edb 95 2007-07-18 14:10 rpconsole_desktop_launcher.sh*
drwxr-xr-x 2 enterprisedb edb 4096 2007-07-18 14:09 samples/
-rwxr-xr-x 1 enterprisedb edb 4114 2007-07-18 14:10 serviceControl.sh*
drwxr-xr-x 2 enterprisedb edb 4096 2007-07-18 14:10 snmpd/
drwxr-xr-x 2 root root 4096 2007-07-18 14:10 _uninst/
drwxr-xr-x 3 enterprisedb edb 4096 2007-07-18 14:09 updateagent/
drwxr-xr-x 2 enterprisedb edb 4096 2007-07-18 14:10 updates/

Fortunately, the files are no longer world writable, but I can't help but suspect that there might be a window during the installation process where they are. But the installer sets up all files executable, even if they are just README files. Actually, some of the ones you might want to execute are set to owner-only executable. It's quite a mess. Careful observers will also note in the above output that the program files are owned by user enterprisedb, which also owns the server process – a blatant security violation.

In the directory dbserver/ you will find the usual PostgreSQL installation with some binaries renamed to have an edb- prefix (e.g., edb-psql). The directory devstudio/ contains the "Developer Studio", a frontend application that looks a bit like pgAdmin but is evidently written in Java and includes remnants of Red Hat's old Visual Explain tool. This application is under the GPL and you can get the source code on the EnterpriseDB web site. The mgmtsvr/ is the "DBA Management Server", which opens a browser to a local server and complains about an invalid certificate. I couldn't log in with any of the obvious accounts, so I didn't look further. Is one supposed to add all these directories to the PATH by the way?

The directory doc/ contains the documentation, which is a 800-some page PDF that is obviously based on the PostgreSQL and Slony-I documentations but does not contain the required copyright notice and license statements. In fact, I couldn't find any license statements about the included open-source products in the installation. (Maybe I didn't look right, but "rgrep CALIFORNIA ." ought to return something, I think.)

The installation also installs an init script in /etc/init.d/ to manage starting and stopping the server. Unfortunately, this is set up to stop the service in run level 2 by default, which is the default on Debian, so you need some manual fixup there. Logging from the server goes to syslog by default.

A few people asked me yesterday about what the dynatune setting does. Well, actually it sets a parameter edb_dynatune in postgresql.conf to a value between 0 and 100 that tells how many percent of your resources you want to dedicate to the database system. After that it supposedly takes care of the rest. There is a mysterious "postgres: edb dynatune" process hanging around, which probably has something to do with it. I could see that the shared buffers are apparently adjusted depending on the dynatune setting, but the EDB license prevents me from telling more, so you will have to check that yourself.

The recent thread on pgsql-advocacy raised a few questions about how PostgreSQL compliance can be maintained while having Oracle compatibility, in particular regarding the pecular null vs. empty string handling that Oracle famously has. Well, that is not present at all; it works exactly like PostgreSQL and exactly not like Oracle. Further quick checking found a number of obvious Oracle-compatibility extensions such as additional available functions, a "dual" table, varchar2, switches for date styles, and what not. But now that I have checked the null handling, my doubts that this is really not that compatible after all were confirmed.

Now, how to uninstall this ...

On Oracle Compatibility

I attempted to try out EnterpriseDB today. I got the EnterpriseDB Advanced Server 8.2 download, which turns out to be 115 MB. The test machine is running Debian lenny.

A few minutes after my registration I got a welcome email from "EnterpriseDB" , although en25.com does not seem to be associated with EnterpriseDB. I don't know what that is about.

What you get is an InstallShield installer written in Java. This brings back pleasant memories about the JVM/library/64-bit mess last time I installed Oracle. The EDB installer supposedly has a console mode, a notable improvement over Oracle. Why they can't just offer an RPM for installation is beyond me.

Somewhere during the installation the praised Dynatune feature pops up and asks me in MySQL-style whether I'm installing a) on a development machine, b) on a mixed machine, or c) a dedicated machine. I tried to trick the thing by claiming it's a dedicated machine, even though it's obviously a development machine. I don't know what the effect of this is at the moment.

A minute later the installation aborts with an unspecified error and asks me to check /opt/EnterpriseDB/8.2/log.txt for details. That file contains a Java stack trace (any Oracle administrator would feel right at home). The real information turns out to be in the file /opt/EnterpriseDB/8.2/install_logs/initdb_stderr.txt, which I had to find on my own. This file informs me that libssl.so.4 could not be found. I don't find a file by that name in either Debian or Ubuntu in any release (even though Ubuntu is supposedly supported).

At this juncture I had to abort the experiment. Regarding the installation process, I can give EnterpriseDB full points for the near-equivalent Oracle experience, but none for the claimed PostgreSQL compliance.

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.