Login | Register
My pages Projects Community openCollabNet
Project highlights: Stable Version: 1.6.1, Development Version: 1.7.6

Setting up your database

The database backend is now a critical part of a functioning Current server. Setting up the database is not difficult, but care must be taken to not only ensure it's done correctly, but that it's done in a way which won't compromise security. In any and all cases please consult the documentation for the database that you choose to use to make sure that you are running it in a secure fashion.

A common question is what database to use. This very much depends on how much work you are willing to do to set up and maintain your Current server and what your specific needs are. Each database has pros and cons. The PostgreSQL database is the recommeded database for a large number of clients. It handles transactions and locking very well. The SQLite database is easy to set up and works well for a small number of clients and can be quite fast. MySQL is supported as well but a 4.x or 5.x version is recommended.

Now that you have decided what database to use it is important to tell Current to use that database. Edit the Current configuration file which, in the default setup, is /etc/current/current.conf. Set the “db_type” to the proper database. Valid values are “postgres”, “pysqlite”, and “mysql”. The “db_user”, “db_pass”, “db_name”, and the “db_host” values also need to be configured for your database.

The PostgreSQL Backend

There are two parts to setting up the PostgreSQL backend; first you have to set up the PostgreSQL server itself to accept connections properly, then you have to set up the actual database instance your Current server will use. We'll address them in that order.

In order to set up the daemon, you have to first have an established /var/lib/pgsql/data directory. This is done by simply starting the PostgreSQL daemon and immediately stopping it. Then, you have to configure the daemon to accept network (TCP/IP) connections. This is done by editing two files in the /var/lib/pgsql/data directory, pg_hba.conf and postmaster.opts.default.

The pg_hba.conf file configures the method(s) used to authenticate connections to the PostgreSQL service; for Current to work, we must allow host-based authentication, which is what is used for TCP/IP connections. The simplest way of doing this is to add a line to the end of this file that reads:

host    all     127.0.0.1   255.255.255.255     password

This allows host-based connections over TCP/IP from the local host only (the loopback adapter, specifically) to all databases with a valid password supplied.

The host-based authentication method is mainly for cases when the database server is not the same machine as the Current server. For security conserns you should only allow the IP address that need to connect to your database server. If you are running the database on the same machine as Current you can also set up local, socket based authentication. Connections are then handled through an on-disk socket rather than the network. Local authentication can be enabled by using a line that reads:

local   all         all           password

This allows socket based connections using a password. In any case, the default IDENT based authentication will not work without a local IDENT server. For more details about configuring these access methods and permissions, please see the PostgreSQL Documentation.

The next file, postmaster.opts.default, simply tells the startup script what options to pass to the postmaster daemon when the PostgreSQL service is started. Please be very careful to modify the postmaster.opts.default file, even if there isn't one in existence already - the postmaster.opts file simply records what options the daemon was started with the last time it was started, it does not control the options.

If you have any preexisting options in this file, you should keep them. You will have to add a “-i” to this file if it's not already there if you decide to allow TCP/IP connections to the server.

Once you've modified those files, you can re-start the PosrgreSQL service and create the user that the Current server will use to connect to the database. This user and password must be the same username and password that appear in your current.conf file. The easiest way to create this user account is to run the createuser script with the “-P” option. Please note that you must use a capital "P" - the lowercase "p" is a different option and will cause unexpected behavior. For more information on creating PostgreSQL users, please see the PostgreSQL Documentation.

Now that we've allowed TCP/IP connections and created our user, we need to create the database and tables that Current will use. First, you need to create and empty database with the “createdb” command provided by the PostgreSQL package. Once the empty database has been created, you will create the actual tables that Current will use. This step has been simplified for you; all you need to do is run the cinstall initdb command and the tables will be created automatically. If you receive any error messages, they should be somewhat explicit about what went wrong. If you're unable to correct the error, please ask on the mailing list.

The SQLite Backend

The SQLite backend is the easiest to set up and run with Current. It supports transactions well but will not work well with more than a handful of clients. As of this writing SQLite 2.8 and Python-SQLite 0.5.0 are the versions used for testing. SQLite 3 should work better for handling more clients. Also, SQLite is what I do most of my development with.

Make sure that you have the RPMs for SQLite and Python-SQLite (the python bindings) installed. Links to RPMs can be found from the Current website. After they are installed set the Current configuration file to use the “pysqlite” database. The “db_type” value in the configuration file is the only database parameter used for SQLite. You may leave the rest blank. Finally, run the command as root cinstall initdb. You database should now be setup and ready to use. The actual file Current will keep at /export/current/db/current.sqlite.

That's it. Your are done setting up the SQLite database.

The MySQL Backend

The MySQL database will preform better the newer your version of the database code is. I'm testing with the versions that come with Fedora Core and Red Hat Enterprise Linux, currently 3.23.58. Although this version does work and should work for a large number of clients it does not support transactions. It is recommended that you run a version (4.x or greater) that supports transactions.

Install MySQL from the RPMs of the version of your choice and start up the database. Run the mysql as the MySQL root user. We will manually create a database and create a user that has read/write access. Please check out the MySQL documentation for security concerns.

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 157 to server version: 3.23.58

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create database up2date;
Query OK, 1 row affected (0.01 sec)

mysql> GRANT ALL PRIVILEGES  on *.* to 'bob'@'localhost' ;
Query OK, 0 rows affected (0.01 sec)

mysql>

You should now be able to configure Current to connect to this MySQL server with these parameters in your configuration file.

db_type = mysql
db_user = bob
db_pass =
db_name = up2date
db_host = localhost

If your MySQL server runs on a different host than your Current server you will have to update the GRANT statement to reflect the host that Current will be connecting from.