An experiment in searching for an interoperable encryption scheme to permit encrypted content to reside in a database (PostgreSQL) and encrypted/decrypted by both application code (Java) and raw database routines.
Integration test uses the excellent TestContainers library to spin up ephemeral PostgreSQL databases using docker just for integration tests
| import java.nio.charset.StandardCharsets; | |
| import java.sql.Connection; | |
| import java.sql.PreparedStatement; | |
| import java.sql.ResultSet; | |
| import org.bouncycastle.bcpg.SymmetricKeyAlgorithmTags; | |
| import org.bouncycastle.openpgp.examples.ByteArrayHandler; | |
| /** | |
| * Crypto utilities using both pure-Java (from bouncycastle's OpenPGP implementation) and | |
| * calling the crypto routines in the pgcrypto extension in PostgreSQL. | |
| * | |
| * The database calls assume "CREATE EXTENSION pgcrypto" has already been called. (see integration tests) | |
| * | |
| * Why: To enable encrypted database column content without application codebase lock-in. | |
| * I.e. By using the postgreSQL routine documented herein the database can still be accessed by another application. | |
| * | |
| * @author rob | |
| * | |
| */ | |
| public class PGPCrypto | |
| { | |
| /** | |
| * Encrypt using pure Java (bouncycastle, OpenPGP, AES128) | |
| * @param text | |
| * @param symmetricKey | |
| * @return a PGP-Armoured encrypted message. | |
| */ | |
| public static String encrypt(String text, String symmetricKey) | |
| { | |
| try { | |
| return new String(ByteArrayHandler.encrypt( text.getBytes(StandardCharsets.UTF_8), | |
| symmetricKey.toCharArray(), | |
| null, | |
| SymmetricKeyAlgorithmTags.AES_128, | |
| true)); | |
| } | |
| catch (Exception e) { | |
| throw new IllegalStateException("Error Encrypting (bcpgp): " + e.getMessage(), e); | |
| } | |
| } | |
| /** | |
| * Decrypt using pure Java (bouncycastle) | |
| * @param pgpArmoredMsg | |
| * @param symmetricKey | |
| * @return plain text | |
| */ | |
| public static String decrypt(String pgpArmoredMsg, String symmetricKey) | |
| { | |
| try | |
| { | |
| return new String(ByteArrayHandler.decrypt(pgpArmoredMsg.getBytes(), | |
| symmetricKey.toCharArray())); | |
| } | |
| catch (Exception e) { | |
| throw new IllegalStateException("Error Decrypting (bcpgp): " + e.getMessage(), e); | |
| } | |
| } | |
| /** | |
| * Encrypt by calling the PGP-based crypto routines in a PostgreSQL database with the pgcrypto extension enabled. | |
| * (OpenPGP, AES128) | |
| * @param conn live database Connection | |
| * @param text | |
| * @param symmetricKey | |
| * @return a PGP-Armoured encrypted message. | |
| */ | |
| public static String encrypt(Connection conn, String text, String symmetricKey) | |
| { | |
| try (PreparedStatement statement = conn.prepareStatement("SELECT armor(pgp_sym_encrypt_bytea(convert_to(?, 'UTF8'), ?::text, 'compress-algo=1, cipher-algo=aes128' ))")) | |
| { | |
| statement.setString(1, text ); | |
| statement.setString(2, symmetricKey); | |
| try(ResultSet rs = statement.executeQuery()) | |
| { | |
| rs.next(); | |
| return rs.getString(1); | |
| } | |
| } | |
| catch(Exception e) | |
| { | |
| throw new RuntimeException("Error Encrypting (pgcrypto): " + e.getMessage(), e); | |
| } | |
| } | |
| /** | |
| * Decrypt by calling the PGP-based crypto routines in a PostgreSQL database with the pgcrypto extension enabled. | |
| * @param conn | |
| * @param pgpArmoredMsg | |
| * @param symmetricKey | |
| * @return | |
| */ | |
| public static String decrypt(Connection conn, String pgpArmoredMsg, String symmetricKey) | |
| { | |
| try (PreparedStatement statementDec = conn.prepareStatement("SELECT convert_from( pgp_sym_decrypt_bytea(dearmor(?), ?::text), 'UTF8' )")) | |
| { | |
| statementDec.setString(1, pgpArmoredMsg); | |
| statementDec.setString(2, symmetricKey); | |
| try(ResultSet rs = statementDec.executeQuery()) | |
| { | |
| rs.next(); | |
| return rs.getString(1); | |
| } | |
| } | |
| catch(Exception e) | |
| { | |
| throw new RuntimeException("Error Decrypting (pgcrypto): " + e.getMessage(), e); | |
| } | |
| } | |
| } |
| import static org.assertj.core.api.Assertions.assertThat; | |
| import java.security.Security; | |
| import java.sql.Connection; | |
| import java.sql.DriverManager; | |
| import java.sql.SQLException; | |
| import org.junit.After; | |
| import org.junit.AfterClass; | |
| import org.junit.Before; | |
| import org.junit.BeforeClass; | |
| import org.junit.Test; | |
| import org.testcontainers.containers.PostgreSQLContainer; | |
| /** | |
| * Test PGPCrypto interoperability of BouncyCastle's OpenPGP implementation and the PostgreSQL pgcrypto module. | |
| * | |
| * @author rob | |
| * | |
| */ | |
| public class PGPCryptoTests | |
| { | |
| private static final String Postgres_Docker_Image = "postgres:9.6"; | |
| private static final String MESSAGE = "Rob Sian Nova"; | |
| private static final String PASS = "si vous reussissez, vous serez bientôt couvert de gloire"; | |
| Connection conn; // @Rule managed | |
| static PostgreSQLContainer<?> pg; // @Rule managed | |
| @BeforeClass | |
| public static void startDocker() throws SQLException | |
| { | |
| pg = new PostgreSQLContainer(Postgres_Docker_Image); | |
| pg.start(); | |
| createConnection().prepareStatement("CREATE EXTENSION pgcrypto;").execute(); | |
| } | |
| @AfterClass | |
| public static void stopDocker() | |
| { | |
| pg.stop(); | |
| } | |
| private static Connection createConnection() throws SQLException | |
| { | |
| return DriverManager.getConnection("jdbc:postgresql://" + pg.getContainerIpAddress() + ":" + pg.getMappedPort(PostgreSQLContainer.POSTGRESQL_PORT) + "/test", "test", "test"); | |
| } | |
| @Before | |
| public void setConnection() throws SQLException | |
| { | |
| conn = createConnection(); | |
| } | |
| @After | |
| public void closeConnection() | |
| { | |
| try { conn.close(); } | |
| catch (SQLException e) {} | |
| } | |
| @Test | |
| public void testPGCryptoForwardReverse() | |
| { | |
| String pg_encrypted_armoured = PGPCrypto.encrypt(conn, MESSAGE, PASS); | |
| System.out.println(pg_encrypted_armoured); | |
| String plain = PGPCrypto.decrypt(conn, pg_encrypted_armoured, PASS); | |
| System.out.println(plain); | |
| assertThat(plain).isEqualTo(MESSAGE); | |
| } | |
| @Test | |
| public void testBCCryptoForwardReverse() | |
| { | |
| String encrypted_armoured = PGPCrypto.encrypt(MESSAGE, PASS); | |
| System.out.println(encrypted_armoured); | |
| String plain = PGPCrypto.decrypt(encrypted_armoured, PASS); | |
| System.out.println(plain); | |
| assertThat(plain).isEqualTo(MESSAGE); | |
| } | |
| @Test | |
| public void testPGencBCdec() | |
| { | |
| String encrypted_armoured = PGPCrypto.encrypt(conn, MESSAGE, PASS); | |
| System.out.println(encrypted_armoured); | |
| String plain = PGPCrypto.decrypt(encrypted_armoured, PASS); | |
| System.out.println(plain); | |
| assertThat(plain).isEqualTo(MESSAGE); | |
| } | |
| @Test | |
| public void testBCencPGdec() | |
| { | |
| String encrypted_armoured = PGPCrypto.encrypt(MESSAGE, PASS); | |
| System.out.println(encrypted_armoured); | |
| String plain = PGPCrypto.decrypt(conn, encrypted_armoured, PASS); | |
| System.out.println(plain); | |
| assertThat(plain).isEqualTo(MESSAGE); | |
| } | |
| @Test | |
| public void testUTF8Samples() | |
| { | |
| for(String msg : new String[]{ | |
| "მინას ვჭამ და არა მტკივა." // georgian | |
| ,"ᚠᛇᚻ᛫ᛒᛦᚦ᛫ᚠᚱᚩᚠᚢᚱ᛫ᚠᛁᚱᚪ᛫ᚷᛖᚻᚹᛦᛚᚳᚢᛗ" // old english rune | |
| ,"На берегу пустынных волн" // russian cyrillic | |
| ,"நான் கண்ணாடி சாப்பிடுவேன், அதனால் எனக்கு ஒரு கேடும் வராது." // tamil | |
| ,"ᐊᓕᒍᖅ ᓂᕆᔭᕌᖓᒃᑯ ᓱᕋᙱᑦᑐᓐᓇᖅᑐᖓ" // Inuktitut | |
| ,"나는 유리를 먹을 수 있어요. 그래도 아프지 않아요" // korean | |
| ,"私はガラスを食べられます。それは私を傷つけません。" // jp | |
| }) | |
| { | |
| assertThat(PGPCrypto.decrypt(conn, PGPCrypto.encrypt(msg, PASS), PASS)).isEqualTo(msg); | |
| assertThat(PGPCrypto.decrypt(PGPCrypto.encrypt(conn, msg, PASS), PASS)).isEqualTo(msg); | |
| } | |
| } | |
| static { | |
| Security.addProvider(new org.bouncycastle.jce.provider.BouncyCastleProvider()); | |
| } | |
| } |