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. Oracle

Oracle is supported by OpenCA via the standard Perl DBD Driver. However, there are some special considerations concerning system setup, configuration and security.

10.3.1. Perl database driver and Oracle OCI client libraries

Oracle databases are accessed via the Perl DBD::Oracle module which uses Oracle OCI client libraries to access database services. Both must be installed on the system.

If the location of the Oracle OCI client shared library is incorrectly configured the OpenCA daemon will not start up properly.

As a consequence, the OpenCA application must be able to locate the Oracle shared library libclntsh.so. Because the client library is usually not installed in a standard library path but rather below the Oracle home directory, the explicit location of this library must usually be configured on the system:

  • Explicitly set the directory location of libclntsh.so in environment variable LD_LIBRARY_PATH for the OpenCA daemon. To do so you could for example write a wrapper script that calls etc/openca_start or include the environment setting in the openca_start script itself.

  • Change the dynamic linker configuration by adding the required directory location to the config file (/etc/ld.so.conf on a Linux system). You will have to ensure that the dynamic linker configuration is updated (Linux: run the ldconfig command).

10.3.2. OpenCA Oracle database configuration

Note

The parameters db_host and db_port are completely ignored for Oracle databases, make sure to leave them empty in order to avoid confusion.

In config.xml set the db_type to 'Oracle'. The db_name, db_user, db_passwd and db_namespace variables may or may not be set according to your configuration (see below).

The Oracle client library requires some environment variables that must be set. As a minimum, the ORACLE_HOME variable must be set. This can be done via the etc/database/DBI.conf configuration file or by sourcing the oraenv file that should have been set up for your Oracle instance by your Oracle DBA.

Depending on your setup it may be useful to source the ora_env file in the OpenCA rc start script like in the following example. In this case, usually no environment variables need to be set up in the DBI.conf file.

10.3.3. Internal Authentication

Internal authentication is the classic authentication method and is also the easiest to configure. It is similiar to the methods used for the other database types and requires the explicit configuration of a database user, a password and a database name. The disadvantage, however, is the need to configure the database password in the configuration file which is not always desired. If this is an issue, consider the external authentication method discussed below.

The db_user and db_passwd parameters must be set to the correct username and password values for the OpenCA database. These are identical to the parameters specified in the CREATE USER user IDENTIFIED BY "password"; command that is used to create the user in the Oracle database.

The db_name parameter must be set to the database name configured in the TNSNAMES.ORA configuration file. As it is possible to specify aliases in this file, this may also point to an alias name of the database. In order to verify if your db_name is set correctly run the command tnsping with the db_name parameter as its only argument. The command must be run with the same environment variables that are set for the OpenCA daemon.

If the tnsping command above fails then the database setup is incorrect and must be fixed. Debugging this error is beyond the scope of this document, as there are lots of possible error causes.

If privilege separation should be used (see below) then the db_namespace parameter must be set to the name of the database user owning the OpenCA schema.

10.3.4. External Authentication

Internal authentication is easy to configure but has the significant drawback of requiring a cleartext password for the database user in the configuration file.

External authentiation grants a Unix user access to the database without the need of a password.

This authentication type usually only works for databases running on the local machine. Although it is possible to configure external authentication for remote machines this is not recommended due to security reasons.

In order to set up an Oracle user for external authentication for the Unix user 'wwwdata' use the following Oracle command (the string 'OPS$' must be prefixed to the Unix user name): CREATE USER OPS$WWWDATA IDENTIFIED EXTERNALLY; After the required permissions (e. g. CREATE SESSION) are granted to the user you can test this by trying to access the database using sqlplus / as the user wwwdata. Make sure that the environment (in particular the ORACLE_SID environment variable) is set up properly for this user. The database monitor should now start without a password prompt.

To make this work with your OpenCA setup you must use the Unix user name that is configured as httpd_user in your etc/openca_start startup file.

Assuming the 'OPS$...' user has been created and the database schema is already set up properly, the database configuration for OpenCA must be completed.

Note

For external authentication the following configuration settings are required: db_user MUST be set to / (a single slash), db_passwd MUST be empty and db_name MUST be empty. In addition, the environment variable ORACLE_SID MUST be set properly.

10.3.5. Database privilege separation for the OpenCA application

The db_namespace parameter allows to specify tables that are owned by another database user. This makes it possible to create the OpenCA database scheme with one user (let's call it the schema owner) and let the OpenCA application access the tables owned by this user as another user (called the schema user). In this case the db_user parameter must be set to the unprivileged schema user and the db_namespace parameter must contain the schema owner name.

10.3.6. Sample Oracle setup

This example describes an example setup using Oracle external authentication, privilege separation and script based database setup. When used this way, the database is prepared by the setup script and NOT via the web frontend (CA/Initialization...).

10.4. 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.4.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.