Create the Database

This section describes how to set up the database for the EJBCA Software Stack, configure required users and privileges, and create database indexes.

EJBCA uses Optimistic Locking to reduce database roundtrips. This reduces the ability to run concurrent transactions on the same row. If multiple EJBCA instances share the same schema, ensure they do not update the same end entities concurrently. Concurrent writes to the same row will fail due to dirty-row conflicts.

Database Setup

Create a database and a database user according to the instructions for your chosen platform.

MariaDB/MySQL

In MariaDB, use the following commands to create the database, matching the DataSource name in the next configuration 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. Restricting table access can further improve security, see Database Privileges.

Character Set Warning

If you do not explicitly set utf8 as character set, some MariaDB/MySQL configurations using the default utf8mb4 may fail with:

Specified key was too long; max key length is 767 bytes

If this occurs, create the database with:

CODE

CREATE DATABASE ejbca CHARACTER SET utf8 COLLATE utf8_unicode_ci;

InnoDB Binary Log Format

Some installations default binlog_format to statement. EJBCA requires:

binlog_format=row

MySQL 8 and Later

MySQL 8 no longer creates users implicitly through GRANT. Create the ejbca user explicitly:

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 as sysdba using sqlplus:

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 log in to the database using following command.
sqlplus sys/<your password>@//localhost:1521/XE as sysdba

If you are unsure of the SID or available services, use:

CODE

lsnrctl status
lsnrctl service

Create the ejbca user

To create the ejbca user:

BASH

CREATE USER ejbca IDENTIFIED BY ejbca;

Oracle Multitenant (12c+)

If you are using Oracle 19c and encounter connection issues, verify your container and pluggable databases:

CODE

SHOW con_name;
SHOW pdbs;

To create the user inside a specific container:

CODE

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;

Configure EJBCA

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

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

PostgreSQL installations must enable ordered CRL fetching:

database.crlgenfetchordered=true

See cesecore.properties in Manage EJBCA Configurations.

Configure Client Authentication

After installing the PostgreSQL database, edit pg_hba.conf, changing local UNIX domain socket authentication to md5:

CODE

local   all   all   md5

If the file location is unknown, log in to the PostgreSQL CLI and use the following to find the location of the file:

CODE

SHOW hba_file;

Restart PostgreSQL:

CODE

$ sudo systemctl restart postgresql

Create User and Database

CODE

$ sudo -i -u postgres
$ createuser ejbca_user -P
$ createdb ejbcadb -O ejbca_user
$ logout

Microsoft SQL Server (MSSQL)

MSSQL installations must also set:

CODE

database.crlgenfetchordered=true

See cesecore.properties in Manage EJBCA Configurations.

Create the Database

Create the database with explicit collation:

Latin1_General_100_CI_AI_SC_UTF8

If using SQL Server Manager, select this collation under Options when creating the database.

Or create it via SQL:

CODE

CREATE DATABASE <db-name> COLLATE Latin1_General_100_CI_AI_SC_UTF8;

Create Database Indexes

As your EJBCA deployment grows, proper indexing is critical for performance.

A prepared SQL script containing recommended indexes is available:

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

For advanced users, an additional optimization script for MariaDB/MySQL provides example partitioning and compression strategies:

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

Review these optimizations carefully before applying them. Incorrect partitioning or compression may degrade performance. EJBCA operates well without them and most installations can omit this step.

Create Database Tables Manually

EJBCA typically creates required tables during the first deployment. This requires privileges to create tables. If you must create the schema manually, SQL scripts are available under:

doc/sql-scripts/

Example for PostgreSQL to create tables and indexes:

CODE

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

Next Step: Application Server Setup

Proceed to Application Servers for configuration instructions.