Column-level database encryption for an employee data microservice
- Role
- Back End Developer Intern
- Period
- Jul 2024 – Feb 2025 · thesis project
- Company
- PT Indonesia Indicator
- Stack
- Spring Boot · PostgreSQL · pgcrypto · GnuPG
Problem
Indonesia's Personal Data Protection Law (UU 27/2022) requires data such as employee ID numbers, tax IDs, family card numbers and addresses to be protected from unauthorised access. In a microservices architecture that data passes through many components, so the encryption had to be strong yet light.
Approach
Column-level encryption in PostgreSQL: a PL/pgSQL trigger encrypts 4 sensitive columns before INSERT/UPDATE using pgcrypto and PGP keys from GnuPG, and a decryption function is applied through LATERAL when data is read. Blowfish and AES-256 were tested in symmetric and asymmetric mode for 0–500 records.
Result
In symmetric mode Blowfish was about 45% faster than AES-256 for 500 records (INSERT ~3.0 vs ~5.4 s; GET ~2.2 vs ~4.0 s). Asymmetric decryption was far slower (~18 minutes for 500 records). Recommendation: Blowfish when speed comes first, AES-256 when security does.
Context
PT Indonesia Indicator's HR system runs on a microservices architecture: client requests go through an API Gateway to services such as ERM, HRIS, Attendance and Ticketing. The HRIS service uses PostgreSQL as its main database and stores employee data that counts as personal data under Indonesia's Personal Data Protection Law (UU 27/2022): ID number (NIK), tax ID (NPWP), family card number (No. KK) and address.
The goal: keep that data protected even if the database or its backups leak, without slowing the services down.
Architecture
Client → API Gateway → HRIS service (Spring Boot) → PostgreSQL + pgcrypto
Encryption and decryption happen at the database level, so the other services do not need their own cryptography code.
Implementation
A BEFORE INSERT OR UPDATE trigger on the employee table calls a function that encrypts the four sensitive columns with the public key:
CREATE OR REPLACE FUNCTION encrypt_data_table_employee()
RETURNS TRIGGER AS $$
DECLARE
pub_key BYTEA := dearmor(pg_read_file('keys/pub.key'));
options TEXT := 'cipher-algo=bf, unicode-mode=1';
BEGIN
NEW.nik := pgp_pub_encrypt(NEW.nik::TEXT, pub_key, options);
NEW.npwp := pgp_pub_encrypt(NEW.npwp::TEXT, pub_key, options);
NEW.alamat := pgp_pub_encrypt(NEW.alamat::TEXT, pub_key, options);
NEW.no_kk := pgp_pub_encrypt(NEW.no_kk::TEXT, pub_key, options);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
On reads, the decrypt_employee_data function (using pgp_pub_decrypt and the private key) is applied to each row through LATERAL, so the REST API receives decrypted data directly:
SELECT e.id, e.name, d.decrypted_nik, d.decrypted_npwp
FROM employee e,
LATERAL decrypt_employee_data(e.nik, e.npwp, e.alamat, e.no_kk) AS d;
Testing
Response times were measured from Spring Boot with MockMvc: POST /api/employee to store records (encryption) and GET /api/employees/all to read them all back (decryption), for 0 to 500 records, in symmetric (pgp_sym_*) and asymmetric (pgp_pub_*) mode.
| Mode | Operation (500 records) | AES-256 | Blowfish |
|---|---|---|---|
| Symmetric | INSERT | ~5.4 s | ~3.0 s |
| Symmetric | GET | ~4.0 s | ~2.2 s |
| Asymmetric | GET | ~18 min | ~18 min |
Values are read from the charts in the thesis and rounded.
Public/private-key decryption was by far the most expensive step: reading 500 records took well over ten minutes, while symmetric mode took a few seconds.
Algorithm comparison
| Algorithm | Type | Characteristics | Used for |
|---|---|---|---|
| AES-256 | Symmetric, 128-bit block, 14 rounds | Stronger cryptographic resistance, but slower in these tests. | The choice when security is the top priority. |
| Blowfish | Symmetric, 64-bit block, keys up to 448-bit | Fastest in the tests and scaled better as data grew. | The choice when speed is the top priority. |
| RSA | Asymmetric (public/private key) | Separates the encryption and decryption keys, but decryption is very slow. | PGP key pair (GnuPG) for pgp_pub_encrypt / pgp_pub_decrypt. |
Key management
The PGP key pair was generated with GnuPG. The public key is used by the encryption trigger; the private key and its passphrase are used only by the decryption function. Both keys are read from a directory on the database server with pg_read_file, so they never travel through the API.
Results & lessons
- Column-level encryption kept sensitive data confidential without changing the other services in the microservices architecture.
- Blowfish wins on speed; AES-256 remains the recommendation when resistance to modern cryptographic attacks matters most.
- Next steps: testing at much larger data volumes, regular key rotation, and routine performance and security monitoring.