Running PostgreSQL as admin on Win32

Being able to run PostgreSQL from an accoun that is a local administrator is a much requested feature. There are times when this makes sense - say when you're test-running things from your own account, and you haven't yet taken the step to use a non-admin account. There are other cases when it doesn't, such as when you're creating a new account for the service anyway - there's just no point in making it an administrator then.

PostgreSQL on Unix has a check that prevents it from starting from the root account, and the same check exists on Windows in the form of checking for membership in Administrators or Power Users. This is a good thing, because it keeps the attack surface on the system way down. There are no plans to change this.

However, there is now a patch available (that will hopefully make it into 8.2, but we don't know that yet. It's been pending on -patches since jan 14th, but there are plenty of patches that have been waiting longer than that) that will let you start PostgreSQL from an adminstrative account. The code (which lives in pg_ctl) will then create and use what's known as a restricted token to launch the backend. This token will explicitly disable the Administrators and Power Users groups, as well as a whole lot of privileges that may be on by default. There is absolutely no way for anything executing in the database context (such as a stored procedure, or code injected through a security bug somewhere) to gain back these privileges. Since we take out the privileges as well, this actually makes the system slightly more secure than before.

The patch is available here, if you need it now. Should apply cleanly to 8.1 (and should work in 8.0, but may not apply cleanly). It only works on Windows 2000 and up, not NT4. But relly, you shouldn't be using NT4 any longer, for a lot of other reasons more important than this.

As a bonus, this patch wraps the database in a job object (when available, and will only work when running as a service) which makes it possible to do performance monitoring on the group of processes - such as total CPU used by all backends. Just use Performance Monitor, open up job objects, and it should list the PostgreSQL job object if it's running.

Hacking on pgAdmin

This weekend I finally took some time to dive into pgAdmin hacking. Previously, I've just nagged at Dave to get issues fixed (those few there were), but that doesn't work forever :-)

Turns out it was a lot easier than I thought. At least, that's what I think now. I've sent in several patches to the guys, and I guess I'll know if it was actually easy once I hear back from them. I might get them all back with a "yuck, this is ugly code" or something. But it does appear to at least work, which is a good first step.

In summary I have to say that it was really easy to understand the layout of the system in pgAdmin. Even though it has been many years since I did any serious C++ing, and I have never done any wx before...

The things I've worked on so far are:

  • Couple of tiny patches for building with VS2003 and for the unicode radiobuttons on the export screen
  • Ability to select all rows in the query output window, for copying, with a single keypress (Ctrl-A)
  • Ability to control quoting and separation when copying from the query results to the clipboard
  • Ability to auto-connect to a database and open a query window on startup using commandline parameters
  • Simple cut at autocompletion ("tab completion" / "intellisense" / whatever you might want to call it) for the SQL editor

Not bad for a first weekend, I think. Assuming, that is, it's actually accepted in the end...

Wow for contrib/intarray

In my ongoing quest to migrate some stuff from MSSQL to PostgreSQL, I've done my first implementation using contrib/intarray. And all I can say in this case is wow. It took some query times from 3+ seconds down to 0.4 milliseconds!! (Granted, that is on a faster machine as well - but not that much faster. And no longer dedicated. And the new machine actually has less memory than the old one).

Now, the old design was pretty darn bad. It basically had a varchar field that held a lot of numbers in the format "4,6,7,9,15". The objective was to find which rows contained say the number 9. The only way to do that in MSSQL is, AFAIK, to do a WHERE field LIKE '%259,%25', which is obviously dead slow.

For PostgreSQL, with the data stored in an intarray instead, the query is WHERE field @ '{9}'. You can even create a GIST index on it, for even faster lookups. And it's even more correct (the previous one will fail if the list has for example 19 in it. It had to be special-cased every time).

Finally, it makes it a lot easier to match for multiple values. Such as WHERE field @ '{9,14}'. For every step, the MSSQL solution just gets uglier and uglier, whereas PostgreSQL is nice and beautiful :-)

So. Arrays can be a bit of a hurdle to get into, and it will tie you down to PostgreSQL, but if you're doing something that deals with arrays or lists of integer, you should definitly check out contrib/intarray. Once again, a great job by Oleg and Teodor.

Small things making a big difference

I ported a fairly small webapp from Microsoft SQL Server to PostgreSQL today. Fairly small, but it still went pretty darn fast - took me just about an hour!

One thing that helped a lot compared to previous times I've done this is the new lastval() function in version 8.1. It takes away having to rewrite all those pieces of code that rely on the @@IDENTITY variable or SCOPE_IDENTITY() function in MSSQL. Thank you Dennis!

Apart from that there were two small things to do - get rid of the datediff() calls (and replace them with much much nicer PostgreSQL equivalents) and change JOINs to FROM on multi-table updates. Finally, I replaced the Full Text Indexes with tsearch2. Using the snowball stemmer in Swedish that also gives a lot better result than the old one, which was a nice bonus :-)

Planet upgraded

The Serendipity blog software on people.planetpostgresql.org has been upgraded to 0.9.1 for all registered blogs.

It took a lot more time and effort than it should have, because somebody seems to think it's a good idea to require the webserver to have write permissions on the php files and the directories where they live in order to do an upgrade, which is obviously a horrible idea from any kind of security perspective. It also had some bad conflicts with the safe mode in PHP, which I still don't really know what it was - took a lot of hacking before it finally worked. I'm just hoping it's a long time until the next major upgrade...

For now - enjoy your new version!

Japan Summary

Ok, so better late than never. Or hey, at least it's late. But since Dave beat me to it anyway, I got kind of sidetracked. So to make it easy, start by reading Daves post, and I won't repeat, but just add a couple of quick notes instead.

  • Plane from London to Tokyo passes almost directly over my house. About 7 hours later! Kind of feels like a waste of time, but the tickets from London were 1/3rd the price of going over Copenhagen, and Finnair through Helsinki didn't have many departures for Tokyo at all.
  • Andreas brought a Japanese to German dictionary. He still refused to translate everything for us. Can't see why.
  • Italian food in Japan is not necessarily what you'd get in Italy. But it was good...
  • Seems what we get for Japanese food here in Sweden is fairly similar to what they actually give you in Japan. Not a perfect match, but cloose.
  • Bingo. Yeah. Haven't played that for many years. That may be how I managed to win. Twice. And Andreas once. (Ok, I have on idea how long it's been since he last played it). At least it's an easy game, and they held up cards so you could read the numbers if you didn't understand japanese.
  • People told me before I went that PostgreSQL is big over in Japan. Still, it was even bigger than I thought. It seems to be used in a lot of big businesses, and it's definitly not something people hide. And the JPUG - a lot of people, and a lot of work happening in that group.

So to summarise, I'll have to start repeating Dave anyway. It was a great trip, and a fantastic experience. Many thanks to Hiroshi, SKC and the JPUG. And of course also to Dave and Andreas!

Another round of pg win32 speedups

This is actually a bit old news, but I've been out of town.

About a week ago, we got a patch from Qingqing Zhou (and changed around in several different steps my me and Tom Lane) applied to PostgreSQL 8.1 that changes the way win32 polls for the "fake signals" used to emulate Unix behaviour and eliminates a kernel call in a very active codepath. The end result of this: Very nice speedups on Windows. In some isolated test-cases up to 40%25!!! In general cases, it's going to be much less than that of course, but it is still a significan speedup. It appears especially efficient in scenarios with lots of concurrent access, bringing win32 performance much closer to what we have on Linux.

Way to go Qingqing, and thanks also to Merlin and Andrew for quick testing work.

My own update from the Japan trip coming up in a bit...

OSCON Europe - Summary

The first thing to notice is that I certainly didn't have time to write any blog posts during the event :-) I didn't have internet access from my hotel, just from the convention area, which also contributed to that.

In general, OSCON Europe was good. A few sessions were of "a bit lesser quality", but in most were very good. Most sessions were technically oriented, which is what I was looking for, but a couple were a bit more on the marketing side. And it's hard to get into deep technical stuff if you only have a 50 minute slot.

From a PostgreSQL perspective, it was very good. I got the definite feel that PostgreSQL was a lot better represented than MySQL when you looked at what people were interested in. We had a BOF, which they didn't. It wasn't a huge number of people, but there were some 10-12 of us at least, and some interesting discussions.

In general there were a whole lot of interesting people to talk to. Bruce and Simon from the PostgreSQL project of course, but a lot of others as well - both PostgreSQL users and people doing completely other things.

The most asked question from db users: What will happen with MySQL after the Innobase thing?

The most made-fun-of-in-talks-incident: Oracle buys Innobase.

Nobody really knows what will happen, and the MySQL guys didn't answer any questions about it. At least not in public. The MySQL guy (David) also managed to insult their community pretty good in his talk. I don't think it was intentional, but this was actually the second time I hear things along that line from them. The quote was basically "we get contributions from the community, but they are never good enough. We may take the idea and rewrite it from scratch". Ironically, his collegue Kaj had a talk the next day titled "taking the community to the next level"...

Finally, about my own talk delivered together with Simon Riggs. I think it went reasonably well. If you were there and want to let me know what you thought, please do so. I'll put the presentation slides up in a couple of days.

OSCON Europe day one - first impressions

So I got here, so some quick first impressions:

  • It's a bit smaller than I expected. But it's probably going to pick up tomorrow.
  • Not surprisingly, a lot of familiar faces - if only familiar from webpages...
  • So far, good quality talks!
  • Oh yeah, "they" deliver talks about LISP...

No PostgreSQL talks scheduled until wednesday, but there's at least Bruce to chat about pg stuff with. And if there are others around, do show yourselves if you can figure out where we are :-)

/mha/uploads/oscon-dead-languages.jpg alt="" align="right" Now for the mandatory first day picture. Daiman Conway delivering his talk on dead languages, and actually showing LISP code (and that wasn't the worst!) I thought I'd never have to live through watching a presentation about LISP again... (pardon the layout - the wireless connection here (hotel) is very flaky, so I have to click things quickly before it goes offline)

Batch encoding conversion

Today I found out (the hard way - customer call!) that I had created a whole bunch of databases on a server in UNICODE encoding, when the locale picked only supported LATIN1. There were also a bunch of other dbs that were actually in LATIN1 that had no problems.

To solve this, I wrote a quick-n-dirty-n-hacky script to batch-convert all the databases from UNICODE to LATIN1. Luckily none of them were very large :-) I'll post it here in the hope that it'll help someone - but consider it mainly something to build from. Specifically the errorchecking should perhaps be better than just an echo of the exitcode.

It solved my problem. YMMV.

#!/bin/sh

/usr/local/pgsql/bin/psql template1 postgres -A -t -c "SELECT datname FROM pg_database WHERE datallowconn AND encoding=6 AND NOT datname ~ '.*_s'" | while read D ; do

  echo "Converting $D"
  echo "-- start" > $D.dump
  echo "ALTER DATABASE $D RENAME TO ${D}_s;" >> $D.dump
  echo "CREATE DATABASE $D ENCODING='LATIN1' TEMPLATE=template0;" >> $D.dump
  echo "\\connect $D" >> $D.dump
  /usr/local/pgsql/bin/pg_dump -U postgres $D >> $D.dump
  /usr/local/pgsql/bin/psql -v ON_ERROR_STOP=1 -U postgres template1 -f $D.dump
  echo Exitcode $?
done

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