Set up a database for EJBCA in JBoss ==================================== The following databases have in some stage been tested with EJBCA: * Hypersoniq (hsqldb) (default in JBoss) * PostegreSQL 8.x and 9.x (http://www.postgresql.org/) * MySQL and MariaDB 5.x (http://www.mysql.com/) * Oracle 10g and 11g (http://www.oracle.com/) * DB2 10.5 * MS-SQL2000, 2003, 2008 * Sybase * Informix 9.2, 11.50 * Derby * Ingres 10.1 Community Edition Unless you are adventurous and know what you're doing, it is recommended to define another datasource for ejbca and not reconfigure DefaultDS with another database as you run the risk to jump into mapping bugs in the JBoss configuration and mess up your server default configuration. If all is well the automatic deployment script of EJBCA will configure all that for you and you will be up and running in 30 seconds. Configuration is done in conf/database.properties. In a production environment you should use something else than the default Hypersonic database that comes with JBoss for the following reasons: 1. Hypersonic database is in-memory, which means that over time it will consume more memory. If a large number of certificates is issued, it will become an issue after a while. 2. Hypersonic does not support full SQL, in particular ALTER statements. When a new version of EJBCA is released we can not create scripts that updates the database if some tables changed. This will make upgrades much much harder. If you will be running a huuuge system you will probably need to run some analysis tools to perfect indexes etc in the database, at least in the long run. Examples of such tools are: http://sourceforge.net/projects/mysqa/ NOW ON TO THE HOWTO! Troubleshooting database problems: --------------------------------- * Have you configured a database correctly in JBoss? Configuration is done in conf/database.properties to set EJBCA to the correct database. * Do you have the correct username/password configured for your database? Configuration is done in JBoss (taken from conf/database.properties if you have EJBCA do it for you). * Is the JDBC driver installed in JBoss? * Does you database accept incoming connection from the network, not only localhost or vice versa? * Do you have a firewall blocking connections to the database? * Can you connect to the database from another machine? * Use ip-address instead of hostname in data source configuration in JBoss (DNS problems?) Configuring EJBCA ----------------- 0. Use 'ant clean' to clean up your repository if not already done. 1. Edit 'conf/database.properties' and follow the instructions to change the default values. Specify the 'datasource.jndi-name' to the one you chose. For example: 'EjbcaDS' There is a database.properties.sample that is heavily commented. 2. On JBoss: Use 'ant deploy-datasource', it will configure automatically all deployment descriptors according to the database/jndi-name you chose before. If something is wrong, you should see it in the logs of your application server The database mapping for EJBCA is defined in modules/ejbca-entity/resources/orm-{mode}-{database}.xml. A database table create script can be created for your database by issuing "ant generate-dbscripts". Configuring JBoss ----------------- Instructions how to configure JBoss are in the Installation guide. Start there. 1. Install and setup the database your database. 2. Create a database for EJBCA (that is obviously the one you defined in conf/database.properties) Check your database documentation for more info (I know it is boring). 3. Configure JBoss with the correct JDBC driver. 4. Install EJBCA (see Installation Guide) Use your favorite database graphic editor to look at the beautiful database tables. Hardening --------- When using logging you probably do not want an attacker to be able to remove log posts, therefore you should limit the table rights on AuditRecordData. MySQL and MariaDB specifics --------------- EJBCA have been tested with MySQL 5.x and MariaDB 5.x. It is NOT recommended to use the MyISAM storage engine, since it only supports table locks and not row-locking. For InnoDB in some configurations the "binlog_format" defaults to "statement". Running EJBCA requires is to be set to "row". binlog_format=row It is recommended to configure my.cnf with at least the following: [mysqld] default-storage-engine = INNODB transaction_isolation = REPEATABLE-READ sync_binlog = 1 innodb_file_per_table = 1 innodb_file_format = Barracuda innodb_flush_log_at_trx_commit = 1 character-set-server = utf8 default-collation = utf8_general_ci default-character-set = utf8 The JDBC driver (e.g. mysql-connector or mariadb-java-client) can be downloaded from http://www.mysql.com/ or http://www.mariadb.org/. Sometimes there can be problems with MySQL related to case sensitivity of database tables. To use UTF-8 encoding use the following option to the MySQL connect string: jdbc:mysql://127.0.0.1:3306/ejbca?characterEncoding=UTF-8 Additionally you might want to specify a socket timeout for fail-over setups: jdbc:mysql://127.0.0.1:3306/ejbca?characterEncoding=UTF-8&socketTimeout=300000 Basic database, user, table and index creation: $ mysql -u root -p mysql> CREATE DATABASE ejbca CHARACTER SET utf8 COLLATE utf8_general_ci; mysql> GRANT ALL PRIVILEGES ON ejbca.* TO 'ejbca'@'' IDENTIFIED BY ''; mysql> exit $ mysql -u root -p ejbca < doc/sql-scripts/create-tables-ejbca-mysql.sql $ mysql -u root -p ejbca < doc/sql-scripts/create-index-ejbca.sql $ mysql -u root -p ejbca < doc/sql-scripts/optimize-ejbca-mysql.sql List your tables, indexes and partitions: $ mysql -u root -p ejbca mysql> SHOW TABLES; mysql> SELECT * FROM information_schema.tables WHERE TABLE_SCHEMA='ejbca'; mysql> SELECT * FROM information_schema.statistics WHERE TABLE_SCHEMA='ejbca'; mysql> SELECT * FROM information_schema.partitions WHERE TABLE_SCHEMA='ejbca'; mysql> exit To limit the table rights to AuditRecordData configure and run doc/howto/mysql-privileges.sh to generate an .sql file with the new database privileges. Then run: $ mysql -u root -p ejbca < $ mysql -u root -p mysql> FLUSH PRIVILEGES; mysql> exit Restart you application server after this. PostgreSQL specifics -------------------- EJBCA have been tested with PostgreSQL 8.x and 9.x. JDBC driver: For v8.x and later the 42.2.1 driver works fine (tested feb 2018). Download JDBC driver for PostgreSQL from https://jdbc.postgresql.org/. Sample setup: admin@host:~$ sudo su postgres postgres@host:~$ createuser -SDRP ejbca postgres@host:~$ createdb -E UTF8 ejbca Typically usage: 'createdb ejbca "database for ejbca"' will create a database named 'ejbca' with description "database for ejbca" and with user postgres (add '-E UTF8' for utf8 encoding if not default). To create a new user for the ejbca database 'createuser -P ejbca' Then you may have to mess around a lot to be able to connect to the database as well... for example change in pg_hba.conf to not use ident for logins to the local database, so you can actually connect locally as the ejbca user. Create a backup with 'pg_dump -U ejbca ejbca > ejbca.sql' Restore with 'psql ejbca ejbca < ejbca.sql' Usage example, log in and list tables: psql ejbca ejbca \dt Check columns of a specific table: \d RoleData Drop and re-create database >sudo su postgres >psql #\l - list all databases #exit >dropdb ejbca >createdb -E UTF8 ejbca Dump and restore >pg_dump ejbca > ejbca.sql >dropdb ejbca >createdb -E UTF8 ejbca >psql ejbca < ejbca.sql Oracle specifics ---------------- JDBC driver for Java 6: ojdbc6.zip Use latest driver from at least Oracle 11 that can be downloaded from http://www.oracle.com/. From EJBCA 4.0 hibernate is used as the JPA (Java Persistence) provider. If startup is slow, when hibernate is loading/updating the schema you should add the following to modules/ejbca-entity/resources/persistense-ds-template.xml, and then do a "ant clean bootstrap" ----- YOUR_SCHEMANAME_IN_UPPERCASE ----- The SQL command to show tables in Oracle is: select table_name from user_tables; DB2 specifics ---------------- DB2 Express 10.5 and DB2 Enterprise has been tested. IBMs JDBC driver for DB2 is named "db2jcc.jar" or "db2jcc_license_c.jar". 1. DB2 does not manage users, so DB2 users have to be local's or ldap's. We create a system user called "ejbca" to be used by DB2. 2. Login as db2inst1 ("su - db2inst1"). Type db2cc, it will launch the administration control center of DB2. 3. Choose your DB2 server and your instance db2inst1 and the Databases directory 4. Create a new database named "EJBCA" and the rest default. 5. Create a new Schema for the EJBCA database with "EJBCA" as "Schema Name" and "EJBCA" as "Authorization name". 6. Create a new BufferPool "BP16K" with SIZE 2500 and PAGESIZE 16K. 7. Create a new Tablespace "EJBCADB_DATA_01" with PAGESIZE 16K, EXTENTSIZE 32 and PREFETCHSIZE 32. 8. Verify that that your temporary tablespace (e.g. TEMPSPACE1) uses a bufferpool (e.g. IMBDEFAULTBP or BP16K) with a page size of 16K. 9. Configure EJBCA_HOME/conf/database.properties and install EJBCA. DB2 through installation creates the instance db2inst1 (user) with the group db2iadm1. Let's give him the password foo123. His home directory is /home/db2inst1 and when we create new database (ex. custom) then it is located in: */home/db2inst1/ NODE0000/ CUSTOM/ JBOSS-EAP-6.1 configuration: We have to create the following directories ($APPSRV_HOME/modules/system/layers/base pre-exist) $APPSRV_HOME/modules/system/layers/base/com/ibm/db2/main/ Then put the following there: - db2jcc4.jar (DB2 driver located in /opt/ibm/db2/V10.5/java/ if is installed as root) - module.xml (which its contents are the following) Then we have to register the driver through jboss-cli: $ /subsystem=datasources/jdbc-driver=com.ibm.db2.jcc.DB2Driver:add(driver-name=com.ibm.db2.jcc.DB2Driver,driver-module-name=com.ibm.db2,driver-xa-datasource-class-name=com.ibm.db2.jcc.DB2XADataSource) $ :reload The database can be registered again through jboss-cli: $ data-source add --name=customds --driver-name="com.ibm.db2.jcc.DB2Driver" --connection-url="jdbc:db2://127.0.0.1:50000/custom" --jndi-name="java:/CustomDS" --use-ccm=true --driver-class="com.ibm.db2.jcc.DB2Driver" --user-name="db2inst1" --password="foo123" --validate-on-match=false --background-validation=false --prepared-statements-cache-size=50 --share-prepared-statements=true --min-pool-size=5 --max-pool-size=20 --pool-prefill=true --transaction-isolation=TRANSACTION_READ_COMMITTED --jta=false --check-valid-connection-sql=select 1;" EJBCA configuration: in database.properties: database.name=db2 database.url=jdbc:db2://127.0.0.1:50000/ejbca database.driver=com.ibm.db2.jcc.DB2Driver database.username=db2inst1 database.password=foo123 Table space: Using "Select Tools→Command Editor", 6 & 7 would look something like this: CREATE BUFFERPOOL "BP16K" SIZE 2500 PAGESIZE 16384 NOT EXTENDED STORAGE; CONNECT RESET; CONNECT TO EJBCA; CREATE REGULAR TABLESPACE EJBCADB_DATA_01 IN DATABASE PARTITION GROUP IBMDEFAULTGROUP PAGESIZE 16384 MANAGED BY DATABASE USING (FILE '/home/db2inst1/db2inst1/EJBCA/ejbcadb_data_01.dbf'512000) EXTENTSIZE 32 PREFETCHSIZE 32 BUFFERPOOL BP16K OVERHEAD 7.500000 TRANSFERRATE 0.060000 FILE SYSTEM CACHING DROPPED TABLE RECOVERY ON; '512000' here is not the size of the table space in pages, so 512000*16384 equal 7.9 Gb. You can add more space for your database by selecting the tablespace "EJBCA_DATA_01", click on "Alter Tablespace", then click on "Containers", then select the only container and click on "Change..." and now change the value of the container size. On DB2 you have a database, a schema id and a table name. The default database properties works, because when you don't specify a schema id, the userid is used. But you can specify the schema name in the connection string: database.url=jdbc:db2://bigfatiron.foo.com:5021/DB2T:currentSchema=EJBCA; MS-SQL specifics ---------------- You can either use Microsoft's JDBC driver or the open source JDBC driver from http://jtds.sourceforge.net/ . This driver is not tested by us (yet), but it has received very good reviews as being much better than Microsoft's driver, so I suggest you take it for a spin. Using the MS-SQL jdbc driver you should only use sqljdbc4.jar. In Sql Server Configuration Manager under SQL Server Network Configuration make sure that the TCP/IP is enabled and that everything else is disabled. When configuring the TCP/IP protocol (the properties view), make sure that: 1. All IP addresses are activated. 2. The IP Address 127.0.0.1 is active and enabled. 3. TCP Dynamic Ports is empty 4. TCP Port is set to 1433 Under SQL Native Client Configuration, make sure that TCP/IP is enabled and that everything else is disabled. When configuring the TCP/IP (the properties view), make sure that 'Default Port' is set to 1433 Informix specifics ------------------ EJBCA have been tested Informix Dynamic Server 11.50.UC6DE. JDBC Driver: IBM Informix JDBC Driver 3.50.JC6 Old doc (might no longer be true): Some Informix database modifications need to be done to be able to insert BLOB and TEXT data (which is required by EJBCA): You must generate a dbspace of the SmartBlobSpace kind for storing the TEXT data type attributes. This require the generation of a Unix RAW filesystem and then create the sbspace. After that, change the database configuration to recognize the new data space. Derby specifics --------------- JavaDB has a command-line utility called "ij" (ij is actually generic and may be used with any compliant JDBC driver), and you may use general open source tools like SQuirreL SQL (http://squirrel-sql.sourceforge.net/). When creating tables in Derby, they get case sensitiva if you quote them, CREATE TABLE "UserData". Ij started like this: java -jar db-derby-10.2.1.3-bin/lib/derbyrun.jar ij in JavaDB 10.2, and like this java -cp db-derby-10.1.3.1-bin/lib/derby.jar:db-derby-10.1.3.1-b in/lib/derbytools.jar org.apache.derby.tools.ij in JavaDB 10.1 You can also run directly from glassfish like this: > export DERBY_HOME=/home/sun/glassfish/javadb > cd /home/sun/glassfish/bin > ../javadb/bin/ij Sample run of ij: ij version 10.1 ij> connect 'jdbc:derby://localhost:1527/ejbca;create=true'; ij> run '/home/tomas/dev/workspace/ejbca/doc/howto/create-tables-ejbca3-derby.sql'; ij> select tablename from sys.systables; TABLENAME -------------------------------------------------------------------------------------------------------------------------------- AccessRulesData AdminEntityData AdminGroupData AdminPreferencesData ApprovalData AuthorizationTreeUpdateData CAData CRLData CertReqHistoryData CertificateData CertificateProfileData EndEntityProfileData GlobalConfigurationData HardTokenCertificateMap HardTokenData HardTokenIssuerData HardTokenProfileData HardTokenPropertyData KeyRecoveryData LogConfigurationData AuditRecordData PublisherData ServiceData UserData UserDataSourceData ... some SYS tables ... 42 rows selected ij> exit; Ingres specifics ------------------ EJBCA has been tested with JBoss 5.1.0.GA together with Ingres Community Edition 10.1 x64. Since earlier versions of Ingres did not support booleans, we will map these fields as INT4 in the database, so you have a good chanse of making EJBCA work with earlier versions. Look in http://community.ingres.com/wiki/Using_JBoss_with_Ingres for a good guide on how to configure Ingres with JBoss. It is very important that you specify the lock level and isolation level. $ export TERM_INGRES='konsole'; cbf DBMS Server -> connect_limit = 64 qsf_guideline = large table_auto_structure = on system_isolation = read_committed system_lock_level = row We also recommend setting DBMS cache on 16k pages to on. DBMS Server -> Configure -> Cache -> DMF Cache 16k = on To be able to handle huge CRLs, you need a larger transaction log than the 256MiB default: "Transaction Log |II_LOG_FILE" -> Configure -> Destroy -> Create (512MiB) Also make 16k pages default before creating the ejbca database or set it manually when creating the database by using the command: $ createdb ejbca -page_size=16384 Indexes for higher performance ============================== Database indexes will improve your performance when the database grows large (at least 100.000 certificates). (you can start messing with this when you notice some slowdown, for normal installations it's rarely needed) Indexes are listed as a script in create-index-ejbca3.sql. You can run this script to boost your database performance, if you have many record. Indexes are in MySQL syntax, it should be general enough though. On external OCSP responders (_NOT_ on CAs) you should create the following index: create index certificatedata_idx1 on CertificateData (issuerDN,serialNumber); Indexes for extra security ========================== In a X.509 PKI every issuerDN, serialNumber pair must be unique. That means that a CA can not issue two certificates with the same serialNumber. The default installation has a very, very, very small chance that that can occur. SerialNumber are 64 bit random numbers, so the chance of a collision should be virtually nil, unless you issue billions of certificates. In any case, a unique index over (issuerDN, serialNumber) on CertificateData assures that even this slim chance will never occur. Note that this is the same index as for performance, and that it is unique.