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.
Create a database and database user according to the following examples for MariaDB/MySQL, Oracle XE, and PostgreSQL.
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';
Connect to the Database
Connect to the Oracle database using sqlplus (or a similar tool) as sysdba.
# 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
lsnrctl status" or
"lsnrctl service"to get more information.
Create ejbca user
To create an ejbca user:
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:
# 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;
# 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:
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
After installing the PostgreSQL database, set the client authentication for local UNIX domain socket connections from
$ sudo vi /etc/postgresql/10/main/pg_hba.conf # "local" is for Unix domain socket connections only local all all md5CODE
pg_hba.conffile 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:
Restart the PostgreSQL service:
$ sudo systemctl restart postgresqlCODE
Login to the user postgres and assign a password:
$ sudo -i -u postgres $ createuser ejbca_user -PCODE
Create a new database and username:
$ createdb ejbcadb -O ejbca_user $ logoutCODE
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 > \qCODE
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:
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:
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.
Next Step: Application Server Setup
For more information on configuring the application server, see Application Servers.