10. Databases

10.1. PostgreSQL

This is only a short introduction to PostgreSQL. This section should not replace the reading of the PostgreSQL Administration Guide but after the lecture of this section you should be able to setup OpenCA very easily with a real datbase and not only with DBM files. This is important because PostgreSQL has transaction support which is essential for serious database applications.

If we talk about real world databases then it is a good idea to mention pgadmin III. The old versions pgadmin I and II only support windows. The third version supports Unix too. So there is now a GUI like for MySQL available which can be used to manage the database. There are people who think that GUIs are not necessary but they enhance the management of several things a lot because not everybody has the time and skill to optimize it's PostgreSQL database perfectly.

Another note about security, please never trust database security mechanisms fully. Use at every time a small IP-firewall in front of your database server. The default installation of OpenCA is a datbase server on localhost. If you want to install a database server on a different machines than the OpenCA components then always install these servers at minimum in a DMZ. Databases like PostgreSQL has today it's own strong security mechanisms but they have from time to time some big bugs. OS-based IP-filters like ipf or netfilter are usually more robust.

10.1.1. Basic Setup

After the installation of PostgreSQL there is usually a use postgres. Please use su - postgres to use this identity. After this step you should login into the database with psql. This is possible because the database template1 is always present on new databaseservers. The first real step is the creation of the user openca with create user. After this you logout and login again as the newly created user. Now you create the database openca. After this the database itself is ready for use but you should make the database a little bit more secure. Sometimes the database is already secure then you have problems to issue the psql commands. Please edit the pg_hba.conf first to get the access permissions for the openca user. You can restrict the database via PosgrSQL's own configuration. The directory /etc/postgres contains usually a file pg_hba.conf which controls the access to the database. There should only be one entry which looks like follows: This configuration restricts the access to the PostgreSQL database to the database openca via users from localhost. Nobodyelse can access the database now. Please remove all the other access rights - especially the lines which use IP-based trust settings. After the configuration you must restart PostgreSQL. The tables can be created with OpenCA's web interfaces. Please ensure that the PostgreSQL server is connected to the network. Usually the TCP/IP socket is deactivated. Sometimes the command of PostgreSQL changes. So please try to run the command \h in psql to see a list of the actual available commands.

10.1.2. Backup

PostgreSQL maintains three programs to dump a database - pg_dump, pg_dumpall and pg_dumplo. We describe pg_dump here because we don't like to backup all other databases and we don't like ot backup large objects only. The following options are used:
  • large object are included

  • create the database newly on recovery

  • export all data as insert commands to support on the fly database mogration

  • columnname based inserts to support rearrangement of column ordering

10.1.3. Recovery

If we have different backup methods then we have different recovery procedures too. The main difference is that we can use the plain text export directly with psql to import the database. This is possible because this file only includes pure SQL statement. The PostgreSQL specific exports must be processed with pg_restore because they include support for BLOBs too. There is only one command for both PostgreSQL formats because pg_restore detects the used format automatically.

10.2. MySQL

Until now nobody created a howto for MySQL.

Note

If you use a version of MySQL prior 4.1 then you can see perhaps an error message which reports a syntax error because of an unkown internal variable NAMES. You can ignore this error message or better you should use a newer version of MySQL.

The background is the internationalization of OpenCA. We support several different character encodings. MySQL can only handle by default the character encoding which is specified for the table during configuration. The solution is the internal variable NAMES which was introduced in the SQL92 standard. This variable can be used to set the actual character encoding. MySQL supports this variable beginning with MySQL 4.1. It is recommended to use at minimum 4.1.1 because of a rewrite of this stuff in MySQL.

10.3. DBM Files

DBM file are much easier to handle than real SQL databases. If you want to use these database then you must only ensure that the directory which should contain the database files is fully writeable, readable and accessible by the webserver user. This will be handled by OpenCA's installation routine automatically. Sometimes in the past the users choose the wrong webserver user. The result is a message in the logs that the function configError doesn't exist. This happens because the OpenCA script cannot load the library files. The new versions of OpenCA (0.9.1.4+) display correct errormessages in this case. Extra actions by the installing administrator are not necessary.

Please never forget that DBM files don't support transactions. If you implement a real world PKI then it is strongly recommend to use SQL databases to have a consistent state of the PKI even in the case of a system crash. DBM has also problems with multi user access for example on web servers with high loads.

10.3.1. Backup and Recovery

The advantage of DBM files is the use of plain files. You can simply use tar to backup and recover this database.