Setting up a MySQL database

This page documents how to set up a MySQL database for use with the qmail rules interface.

Installing MySQL

MySQL is usually included in the operating system's repositories. If this is the case, you should install it from there, so that the server can be upgraded easily when security issues are found.

For example, under the RedHat-flavoured distributions (i.e. RedHat Enterprise, Fedora, CentOS, etc.) you can run a command like this to install the server:

# yum install mysql-server

The specific package name may vary, but the procedure should be very similar.

If your system doesn't have a package repository from which to install the software, you can always install it from the source code.

After installing the software, make sure the server is running, and that your system is configured to automatically start the server when the machine boots up.

Creating the database and users

Note that you should already have downloaded the files and run the ./fix-permissions.sh script.

To set up the database using MySQL, do the following:

• Configure the server to not allow connections from the network. This will make it impossible for people outside the machine itself to connect to your mysql server. You may not want or need to do this, however if you don't have a specific reason to allow remote network connections, you should do it.

  Edit the file /etc/my.cnf. Within the [mysqld] section, search for a line saying "skip-networking". If it's there, make sure it's not commented out. If it's not there, add it.

  [mysqld]
  ...
  skip-networking

  If you changed the /etc/my.cnf file, restart the mysqld server.

  Verify that it worked by looking at the open network connections to the mysqld process, using a netstat command like so:

  # netstat -anp | grep mysqld unix 2 [ ACC ] STREAM LISTENING 386734 23313/mysqld /var/lib/mysql/mysql.sock

  You should see the "unix" connection (as shown here), but you should NOT see any "tcp" connections.

• Make sure you have a mysql "root" password. This "root" is totally different from the "root" login on your server. If this is not a new installation of mysql, this will probably have already been done. If not, you can set one like so:

  # mysqladmin password 'new.p@ssw3rd'

• Create an empty database and two database users. The database will hold all of the data, and the users will be used by the web site and the email processing script. I call my database "rules", and the users "g4web" and "g4mail". You can call them whatever you like, just be sure to substitute your names where shown in the examples below. (You will probably also want to choose more secure passwords than these.)

  # mysql -p Enter password: (Enter your mysql root password) ... mysql> CREATE DATABASE rules ; Query OK, 1 row affected (0.00 sec) mysql> CREATE USER g4web IDENTIFIED BY 'web' ; Query OK, 0 rows affected (0.00 sec) mysql> CREATE USER g4mail IDENTIFIED BY 'mail' ; Query OK, 0 rows affected (0.00 sec) mysql> FLUSH PRIVILEGES ; Query OK, 0 rows affected (0.00 sec) mysql> \q

• Create the empty tables within the database. The download comes with a file called "lib/create-tables.mysql", which creates the empty tables and runs the GRANT commands needed to set the permissions correctly. If you are using different usernames for the web and mail users, you should edit this file before running it. Simply change the usernames at the end of each GRANT command to match the ones you are using.

  For example...

  GRANT SELECT ON phases TO g4mail@localhost ;
  GRANT SELECT,INSERT,UPDATE,DELETE ON phases TO g4web@localhost ;

  When you have edited all of the GRANT lines in the file, run the script as the MySQL super-user, within the database, like so:

  # cd /www/secure.domain.xyz/rules/lib (your web site's DocumentRoot)
  # mysql -p rules < create-tables.mysql
  Enter password: (Enter your mysql root password)

  Other than the password prompt, the command should not produce any output. If you see any error messages, figure out what the problem is and try again.

• Insert one or more phases. When you first create the empty tables (above) the "phases" table will be empty. This will prevent the system from working. You must insert at least one phase before the system will work.

  The download comes with a file called "lib/insert-standard-phases.sql". This will install the phases I use on my own server, which are listed above. If you plan to change the phases in some way, you will want to edit this file before running it.

  To create the phases, run this script as the MySQL super-user.

  # cd /www/secure.domain.xyz/rules/lib (your web site's DocumentRoot)
  # mysql -p rules < insert-standard-phases.sql
  Enter password: (Enter your mysql root password)

  Again, other than the password prompt, the command should not produce any output. If you see any error messages, figure out what the problem is and try again.

• Verify that the users are able to access to the database. I ran into some problems when I wrote these directions, so I found that it's a good idea to manually test each of the new userids and make sure they are able to access the database.

  $ mysql -p -u g4web rules Enter password: (Enter the "g4web" user's password.) ... mysql> SHOW GRANTS ; +-------------------------------------------------------------------------------------+ | Grants for g4web@localhost | +-------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'g4web'@'localhost' IDENTIFIED BY PASSWORD '686029a02981ef9c' | | GRANT SELECT, INSERT, UPDATE, DELETE ON `rules`.`phases` TO 'g4web'@'localhost' | | GRANT SELECT, INSERT, UPDATE ON `rules`.`sessions` TO 'g4web'@'localhost' | | GRANT SELECT, INSERT, UPDATE, DELETE ON `rules`.`rules` TO 'g4web'@'localhost' | +-------------------------------------------------------------------------------------+ 4 rows in set (0.00 sec) mysql> \q $ mysql -p -u g4mail rules Enter password: (Enter the "g4mail" user's password.) ... mysql> SHOW GRANTS ; +--------------------------------------------------------------------------------------+ | Grants for g4mail@localhost | +--------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'g4mail'@'localhost' IDENTIFIED BY PASSWORD '64d94ec17fb953a4' | | GRANT SELECT, INSERT, UPDATE ON `rules`.`greylist` TO 'g4mail'@'localhost' | | GRANT INSERT ON `rules`.`log` TO 'g4mail'@'localhost' | | GRANT SELECT ON `rules`.`phases` TO 'g4mail'@'localhost' | | GRANT SELECT, UPDATE ON `rules`.`rules` TO 'g4mail'@'localhost' | +--------------------------------------------------------------------------------------+ 5 rows in set (0.00 sec) mysql> \q

Once this is done, the database will be ready for the web interface to be used to start creating rules.

When configuring the web interface and the mail script, you will need to set the value of the G4DBCONN environment variable. For a MySQL database, the value needs to look like this:

SetEnv G4DBCONN "dbi:mysql:database=rules"

If the database is physically stored on a different machine, you may need to include parameters which specify the hostname and/or port number. If so, the value will look like this:

SetEnv G4DBCONN "dbi:mysql:database=rules;host=HOST;port=PORT"

Copyright 1995-2024 John M. Simpson KG4ZOW (email) (PGP key)
Last updated 2012-07-24 07:17:12 +0000