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 Creating the Database#MariaDB/MySQL, Creating the Database#Oracle XE, and Creating the Database#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:

BASH

$ 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:

CODE

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:

CODE

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

Oracle DB

Connect to the Database

Connect to the Oracle database using sqlplus (or a similar tool) as sysdba.

BASH

# Depending on your setup, you would either use SID or ServiceName for connection URL.
# url:port:SID or url:port/ServiceName

# For XE version 11g for example, you can login to the database using following command.
sqlplus sys/<your password>@//localhost:1521/XE as sysdba

If you are unsure about the available services and SID in your installation, you can use the lsnrctl (Listener Control) commands "lsnrctl status" or "lsnrctl service" to get more information.

Create ejbca user

To create an ejbca user:

BASH

CREATE USER ejbca IDENTIFIED BY ejbca;

Multi-tenant architecture was introduced as of Oracle Database 12c. If you are using Oracle 19c and having connection problems after creating the ejbca user, try the following:

BASH

# Use the following command to check connections and PDB's available:
SHOW con_name;
SHOW pdbs;

# Alternatively, create the user in container.
ALTER SESSION SET container = YOUR_CONTAINER_NAME;
CREATE USER ejbca IDENTIFIED BY ejbca;

Grant permissions

CODE

# 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 UNLIMITED TABLESPACE TO ejbca;

Set up EJBCA

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

BASH

database.name=oracle

# Use url:port:SID or url:port/ServiceName format mentioned above.
database.url=jdbc:oracle:thin:@oracledb:1521:XE

database.driver=oracle.jdbc.driver.OracleDriver
database.username=ejbca
database.password=ejbca

PostgreSQL

Due to differences between databases in how batches are read, PostgreSQL installations should have database.crlgenfetchordered set to 'true'. See cesecore.properties in Managing EJBCA Configurations.

After installing the PostgreSQL database, set the client authentication for local UNIX domain socket connections from peer to md5:
CODE
```
$ 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:
CODE
```
SHOW hba_file; 
```
Restart the PostgreSQL service:
CODE
```
$ sudo systemctl restart postgresql
```
Login to the user postgres and assign a password:
CODE
```
$ sudo -i -u postgres
$ createuser ejbca_user -P
```

Create a new database and username:

CODE

$ createdb ejbcadb -O ejbca_user
$ logout

MSSQL

Due to differences between databases in how batches are read, MSSQL installations should have database.crlgenfetchordered set to 'true'. See cesecore.properties in Managing EJBCA Configurations.

Create the Database

The database must be created with explicit character set / collation settings Latin1_General_100_CI_AI_SC_UTF8.

If you are using the Microsoft SQL Server Manager, select Collation Latin1_General_100_CI_AI_SC_UTF8 under Options when creating the database with the wizard.

The collation can be specified using the SQL command when creating the database as follows.

CODE

$ CREATE DATABASE <db-name> COLLATE Latin1_General_100_CI_AI_SC_UTF8;

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.

Create Database Tables Manually

Normally database tables are created automatically during the first deployment of EJBCA. This requires privileges to create tables in the database. If for some reason you need to create database tables manually you can do that with sql script located in the source under doc/sql-scripts.

Example for PostgreSQL to create tables and indexes:

CODE

$ 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

Next Step: Application Server Setup

For more information on configuring the application server, see Application Servers.