BenV's notes

Another reason to avoid MySQL

Today MySQL has managed to piss me off enough to get rid of it for something I was working on.
The case:
I just created a brand new database. In that database I want to store items, and those items should be linked to users.
So as a first stab I had this CREATE TABLE statement:

CREATE TABLE user (user_id SERIAL, username varchar(255) UNIQUE NOT NULL, password char(32) NOT NULL, PRIMARY KEY (user_id));
CREATE TABLE item (item_id SERIAL, name varchar(255) NOT NULL UNIQUE, user_id bigint unsigned REFERENCES user(user_id),PRIMARY KEY (item_id)) ENGINE = InnoDB;

Guess what MySQL said? Of course, it said “Sure thing dude!”. And to make sure you believe me, here’s the verbatim:

mysql> CREATE TABLE user (user_id SERIAL, username varchar(255) UNIQUE NOT NULL, password char(32) NOT NULL, PRIMARY KEY (user_id));
Query OK, 0 rows affected (0.36 sec)
mysql> show warnings;
Empty set (0.00 sec)
mysql> CREATE TABLE item (item_id SERIAL, name varchar(255) NOT NULL UNIQUE, user_id bigint unsigned REFERENCES user(user_id),PRIMARY KEY (item_id)) ENGINE = InnoDB;
Query OK, 0 rows affected (0.32 sec)
mysql> show warnings;
Empty set (0.00 sec)

Well guess what….
IT LIED!
Those references to the user table? What references?

mysql> show create table item;
+-------+-----------------------+
| Table | Create Table |
+-------+-----------------------+
| item | CREATE TABLE `item` (
`item_id` bigint(20) unsigned NOT NULL auto_increment,
`name` varchar(255) NOT NULL,
`user_id` bigint(20) unsigned default NULL,
PRIMARY KEY (`item_id`),
UNIQUE KEY `item_id` (`item_id`),
UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |


Of course the guys over at MySQL (these days Oracle, enough said) seem to think this is perfectly normal behavior.
Idiots with their toy database.
“Let’s just execute a random part of the statement you just gave me, since I don’t feel like the rest“.
Any normal program would at least give you a warning or even syntax error, but those fuckers at MySQL Headquarters don’t care about your data integrity. They only care about money.

There is of course the SQL_MODE setting that you can give interesting values such as “ERROR_FOR_DIVISION_BY_ZERO” (what else is it going to do … but then again, this is MySQL so it’ll probably just insert 0 or something like that) and “NO_ZERO_DATE“. But even with the SQL_MODE set to “STRICT_ALL_TABLES” it did exactly the same.

Installing PostgreSQL

Now that you’re convinced that MySQL is not the way to go, the next obvious choice is PostgreSQL.
Pretty much everything I’ve ran into with PostgreSQL so far (and I use it on a daily basis) just feels a lot more mature and professional than MySQL. But that could be me 🙂
Since the machine I’m installing it on has been up for quite a while, it’s still running Slackware 11. But don’t let that fool you into thinking it’s obsolete or anything, installation on Slackware current is exactly the same. (I’ve tried)

Here goes:

root@pqsl:~$ cd /usr/src/
root@pqsl:/usr/src$ wget http://wwwmaster.postgresql.org/redir/110/h/source/v9.0.4/postgresql-9.0.4.tar.bz2
# Using slackbuild:
root@psql:/usr/src$ slackbuild.pl postgresql-9.0.4.tar.bz2
# prefix -> /usr, hit enter a few times

# Finally install the package
root@psql:/usr/src$ installpkg /usr/src/packages/postgresql-9.0.4-i386-1BnV.tgz

That installs the program itself. On one machine I had to install the readline, but other than that the installation was smooth.

Configuration

Personally I like running things in daemontools, so that’s what I have in mind for postgresql as well.
First we’ll have to create a user to run the postgres daemon as. I’ve picked user postgres for that.
We’ll also need some storage space for the database. Fortunately I had a brand new partition (that LVM just handed to me) which I formatted ext4
and mounted on /postgres. Obviously you might want to pick your own location for this, like /mnt or /var/lib/far/away/postgres.
Assuming you know how to create a user (and ignore the partition part if you like), here’s the daemontools part.

root@psql:~$ cd /etc/services-available
root@psql:/etc/services-available$ mkdir -p postgresql/log
root@psql:/etc/services-available$ echo -e "#!/bin/sh\nsleep 1\nexec setuidgid postgres /usr/bin/postgres -D /postgres/data -c config_file=/etc/postgres/postgresql.conf" > postgresql/run
root@psql:/etc/services-available$ echo -e '#!/bin/sh\nexec setuidgid multilog multilog t s5000000 n10 /var/log/services/postgres' > postgresql/log/run
root@psql:/etc/services-available$ chmod +t postgresql
root@psql:/etc/services-available$ chmod +x postgresql/run postgresql/log/run

But wait, we still need to initialize the database AND configure it a bit more.
To let postgres install the initial data simply issue this command:

# First make sure nobody gets entry to the data :)
root@psql:~$ chown postgres:postgres /postgres
root@psql:~$ chmod 770 /postgres
# Initialize!
root@psql:~$ setuidgid postgres initdb -D /postgres/data
# Move the new configuration files to their proper location
root@psql:~$ mv /postgres/data/*.conf /etc/postgres

It will make a directory structure for the data files and also create some initial configuration files. However, I don’t like them in that directory. So I moved them and made postgres look for the config file in /etc/postgres through a commandline option in the daemontools run script.
Also, I uncommented the options for the other configuration files in /etc/postgres/postgresql.conf and changed their paths. This is needed for both the hba_file and the ident_file directives so it can find the files that we just moved.
Now that our initial database is in place, let’s try starting the daemon!

root@psql:~$ cd /var/service ; ln -s /etc/services-available/postgres
root@psql:~$ sleep 1 ; tail -f /var/log/services/postgres/current
@400000004df61ede25823dd4 LOG: database system was shut down at 2011-06-13 15:37:16 CEST
@400000004df61ede33955c44 LOG: database system is ready to accept connections
@400000004df61ede3398bb8c LOG: autovacuum launcher started

Yay, it’s up and running. (If this isn’t the case for you, check what error message you get. Either it can’t find the configuration file or maybe you had a typo somewhere).
Next, change the postgres superuser password if you know what’s good for you.

benv@psql$ psql -U postgres
psql (9.0.4)
Type "help" for help.
postgres=# ALTER USER Postgres WITH PASSWORD '';
ALTER ROLE
postgres=# \q
# Now to make sure I don't have to enter it every time I need it
benv@psql$ echo 'localhost:5432:postgres:postgres:' > ~/.pgpass
benv@psql$ chmod 0600 ~/.pgpass

That last command makes sure you can simply start ‘psql’ without having to type the password all the time. See this for more information on that. Of course since the default is set to ‘trust’ local connections (which I don’t in this case) it will never ask for a password.
Let’s change that! The file pg_hba.conf is responsible for Host Based Authentication (check this for more info) and tells Postgres what it needs to check for what host. As you can see, ‘local’ is set to ‘trust’.
If you’re on a single user machine that only yourself touches it’s probably fine to leave it. But if you’re on a shared host with multiple users… change it! You don’t trust anyone! 🙂
This is what it could look like after you changed it:

local all all md5
host all all 127.0.0.1/32 md5
host all all ::1/128 md5
host all all 192.168.1.0/24 md5

This requires md5-hashed password authentication before anyone can access the database.
Of course if you don’t want external machines accessing it, simply firewall it off. Or don’t enable postgres to listen on your network, which is what I’m changing next.

Since I do want postgres to listen to the network, there’s a few last changes I need to make in the configuration file /etc/postgres/postgresql.conf.
These are the options to enable SSL connections and to listen to the network:

listen_addresses = '*'
ssl = on

If you restart postgres now, it’ll bitch about not being able to read server.crt and the corresponding key file. Weird enough the location and name of the SSL certificates can not be changed. Maybe in a future version. But for now you’ll have to copy your snakeoil certificates into the postgres data directory:

root@psql:~$ cd /postgres/data
root@psql:/postgres/data$ openssl genrsa -out server.key 2048
root@psql:/postgres/data$ openssl req -new -key server.key -out server.csr
root@psql:/postgres/data$ openssl x509 -req -days 365 -in server.csr -signkey server.key -out server.crt
root@psql:/postgres/data$ chown postgres:postgres server.*

Restart the server, and you’re ready! (well, maybe you still want to tweak some config variables like shared_buffers, but you can figure that out for yourself. If you have memory to burn you probably want to turn it up a bit 🙂

“But what about creating a database?”
Ghooh, fine, final question.
Creating a database and a corresponding user that can’t do anything he pleases is simple.
They made wrappers for it called “createuser” and “createdb”:

benv@psql:~$ createuser -U postgres -P
Enter password for new role:
Enter it again:
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) n
Shall the new role be allowed to create more new roles? (y/n) n
# New user created. Now give it a database to play with.
benv@psql:~$ createdb -U postgres -O
benv@psql:~$ psql -U
Password for user :
psql (9.0.4)
Type "help" for help.

# Deleting them is even easier ;)
benv@psql:~$ dropdb -U postgres
benv@psql:~$ dropuser -U postgres

Now go go have fun with it 🙂