EJBCA – Creating the Database

The following covers setting up your database and creating database indexes.

To maximize performance EJBCA runs with Optimistic Locking turned on, which sacrifices concurrent transactions to the same database row in place of shorter database roundtrips. If connecting multiple instances of EJBCA to the same database schema, ensure that these will not update the same end entities concurrently, or this will result in attempting to write data onto dirty table rows and thus failed transactions.

Database Setup #

Create a database and database user according to the following examples for MariaDB/MySQL, Oracle XE, and PostgreSQL.

MariaDB/MySQL #

In MariaDB, use the following commands to create the database, matching the DataSource in the next step, and add privileges to connect to the database:

$ mysql -u root -p
mysql> CREATE DATABASE ejbca CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
mysql> GRANT ALL PRIVILEGES ON ejbca.* TO 'ejbca'@'localhost' IDENTIFIED BY 'ejbca';

You should define secure passwords for your database in production. You can also improve security by limiting access to tables. For more information, see Database Privileges.

If you do not set utf8 as character set, then EJBCA may not start because of index limitations if using the default charset utf8mb4 in some configurations of MariaDB/MySQL. It will result in the following error: Specified key was too long; max key length is 767 bytes

Thus, you can use a command like the following instead:

CREATE DATABASE ejbca CHARACTER SET utf8 COLLATE utf8_unicode_ci;

In some configurations for InnoDB the binlog_format defaults to statement. Running EJBCA requires is to be set to row. For example: binlog_format=row

MySQL version 8 or later

As of MySQL version 8, users cannot be implicitly created using the GRANT command. If running MySQL version 8 or later, create the ejbca user first according to the following example:

mysql> CREATE USER 'ejbca'@'localhost' IDENTIFIED BY 'ejbca';
mysql> GRANT ALL PRIVILEGES ON ejbca.* TO 'ejbca'@'localhost';

Oracle XE version 11g Release 2 #

Connect to Oracle database using sqlplus (or similar tool) as sysdba:

sqlplus sys/<your password>@//localhost:1521/XE as sysdba

And create an ejbca user and grant it the following permissions:

create user ejbca identified by ejbca;

# Permissions which must be granted to ejbca user:

GRANT create session TO ejbca;
GRANT create table TO ejbca;
GRANT create view TO ejbca;
GRANT create any trigger TO ejbca;
GRANT create any procedure TO ejbca;
GRANT create sequence TO ejbca;
GRANT create synonym TO ejbca;

GRANT RECOVERY_CATALOG_OWNER TO EJBCA WITH ADMIN OPTION;
GRANT GATHER_SYSTEM_STATISTICS TO EJBCA WITH ADMIN OPTION;
GRANT ADM_PARALLEL_EXECUTE_TASK TO EJBCA WITH ADMIN OPTION;
GRANT DBA TO EJBCA WITH ADMIN OPTION;
GRANT AQ_ADMINISTRATOR_ROLE TO EJBCA WITH ADMIN OPTION;
GRANT CONNECT TO EJBCA WITH ADMIN OPTION;
GRANT DELETE_CATALOG_ROLE TO EJBCA WITH ADMIN OPTION;
GRANT OEM_MONITOR TO EJBCA WITH ADMIN OPTION;
GRANT HS_ADMIN_SELECT_ROLE TO EJBCA WITH ADMIN OPTION;
GRANT HS_ADMIN_EXECUTE_ROLE TO EJBCA WITH ADMIN OPTION;
GRANT LOGSTDBY_ADMINISTRATOR TO EJBCA WITH ADMIN OPTION;
GRANT EXECUTE_CATALOG_ROLE TO EJBCA WITH ADMIN OPTION;
GRANT SCHEDULER_ADMIN TO EJBCA WITH ADMIN OPTION;
GRANT IMP_FULL_DATABASE TO EJBCA WITH ADMIN OPTION;
GRANT EXP_FULL_DATABASE TO EJBCA WITH ADMIN OPTION;
GRANT DATAPUMP_IMP_FULL_DATABASE TO EJBCA WITH ADMIN OPTION;
GRANT HS_ADMIN_ROLE TO EJBCA WITH ADMIN OPTION;
GRANT RESOURCE TO EJBCA WITH ADMIN OPTION;
GRANT AQ_USER_ROLE TO EJBCA WITH ADMIN OPTION;
GRANT DATAPUMP_EXP_FULL_DATABASE TO EJBCA WITH ADMIN OPTION;
GRANT SELECT_CATALOG_ROLE TO EJBCA WITH ADMIN OPTION;
GRANT OEM_ADVISOR TO EJBCA WITH ADMIN OPTION;
GRANT DBFS_ROLE TO EJBCA WITH ADMIN OPTION;

On the EJBCA side in the database.properties file, set the following parameters:

database.name=oracle
database.url=jdbc:oracle:thin:@oracledb:1521:XE
database.driver=oracle.jdbc.driver.OracleDriver
database.username=ejbca
database.password=ejbca

PostgreSQL #

1. After installing the PostgreSQL database, set the client authentication for local UNIX domain socket connections from peer to md5:

   $ sudo vi /etc/postgresql/10/main/pg_hba.conf
   
   # "local" is for Unix domain socket connections only
   local all all md5
   
   

    If the pg_hba.conf file is not located under the path /etc/postgresql/10/main/pg_hba.conf, log in to the PostgreSQL CLI and use the following to find the location of the file:

   SHOW hba_file; 

2. Restart the PostgreSQL service:

   $ sudo systemctl restart postgresql
   

3. Login to the user postgres and assign a password:

   $ sudo -i -u postgres
   $ createuser ejbca_user -P

4. Create a new database and username:

   $ createdb ejbcadb -O ejbca_user
   $ logout

5. Create EJBCA tables and indexes:

   $ psql -U ejbca_user -W ejbcadb
   > psql -hlocalhost -U ejbca_user -d ejbcadb < /opt/ejbca/doc/sql-scripts/create-tables-ejbca-postgres.sql
   > psql -hlocalhost -U ejbca_user -d ejbcadb < /opt/ejbca/doc/sql-scripts/create-index-ejbca.sql
   > \q

Create Database Indexes #

As the database grows, it is important to have correct database indexes to maintain good performance.

The following SQL file that is ready to run on your database contains a set of recommended database indexes that can be applied to your database:

• doc/sql-scripts/create-index-ejbca.sql.

If you are confident in what you are doing, you may also apply partitions and compression. The following SQL example file, provided for MariaDB / MySQL, should be analyzed for your specific workload and database maintenance processes and not applied blindly:

• doc/sql-scripts/optimize-ejbca-mysql.sql

Applying partitions and compression may cause adverse effects if used incorrectly.

EJBCA will run well without this optimization and it can be ignored for most installations.