iDempiere Encrypted Database Fields

🔐 Encryption Protection

To comply with security and privacy requirements, certain data stored in the database must be encrypted at rest.

While ISO certification ensures proper operating procedures, it doesn't guarantee data-level security.

In iDempiere, encryption is considered from two perspectives:

Storage Encryption – Is the data stored in encrypted form?
Presentation Encryption – Is the data decrypted or masked on the user interface?

These form the following matrix:

Storage Encrypted	Storage Not Encrypted
✅ Decrypted on UI: Database stores ciphertext, UI shows plaintext ✅ Masked on UI: Database stores ciphertext, UI shows masked data	❌ Plaintext on UI: Both database and UI show unencrypted data ✅ Masked on UI: Database stores plaintext, UI masks sensitive data

🎯 Key Points

IMPORTANT SECURITY NOTE: By default the encryption key is hardcoded in java and public, so is not safe, with this default key all tenants share the same key

So, the recommended safer approach is to generate a keystore for encryption idempiere.ks and describe the algorithm and password in idempiere-ks.properties

With this safer method:

The encryption key is stored on the iDempiere Application Server.
In multi-server environments, keystore files must be manually synchronized across servers.
Each tenant has its own unique encryption key.
If the keystore is lost and not restored, encrypted fields cannot be recovered.
Regular backups of the keystore are critical.

✅ Can Users See Decrypted Data?

Yes. Encrypted data stored in the database is automatically decrypted and displayed on the user interface.

To verify whether a field is encrypted at the database level, run the following SQL:

SELECT
  ad_column_id,
  columnname,
  isencrypted
FROM ad_column
WHERE ad_column_id = 2174;

If `isencrypted = 'Y'`, then the field is encrypted.

🔐 How Encryption and Key Management Works

Encryption logic is implemented in:

org.compiere.process.ColumnEncryption

Key encryption logic:

private int encryptColumnContents(String columnName, String tableName) throws Exception {
    ...
    for (ResultSet row : resultSet) {
        String value = rs.getString(2);
        int AD_Client_ID = rs.getInt(3);
        value = SecureEngine.encrypt(value, AD_Client_ID);
        ...
        updateStmt.setString(1, value);
        updateStmt.setInt(2, id);
        updateStmt.executeUpdate();
    }
    ...
}

Steps:

SELECT rows to encrypt (id, value, AD_Client_ID)
Encrypt the value using `SecureEngine.encrypt()`
UPDATE the encrypted value back into the table

🔁 Syncing Across Multiple Application Servers

In multi-server environments, encryption won’t work properly unless the keystore is available on all servers.

1. Locate Keystore Files on Primary Server

Example location:

$env_IDEMPIERE_HOME/idempiere.ks
$env_IDEMPIERE_HOME/idempiere-ks.properties

Sample `idempiere-ks.properties`:

password=ZnVja0V0aGFu
algorithm=AES

2. Copy to Local

scp TA:$env_IDEMPIERE_HOME/idempiere.ks ./
scp TA:$env_IDEMPIERE_HOME/idempiere-ks.properties ./

3. Distribute to Other Application Servers

scp idempiere.ks TA:$env_IDEMPIERE_HOME
scp idempiere-ks.properties TA:$env_IDEMPIERE_HOME

4. Restart iDempiere

After copying the keystore files, restart the Application Server so encrypted fields can be decrypted.

✅ Summary

Encryption is done per tenant, using a key stored on the Application Server.
Database fields store ciphertext, decrypted in the UI.
Copy the keystore to all application servers in clustered environments.
Always back up both:

 * `idempiere.ks`
 * `idempiere-ks.properties`

Without the correct keystore, encrypted fields will be permanently unreadable.

Encrypt column

Contents