Encryption in postgres using pgcrypto module and Decryption in java

Question

I have created a trigger and a trigger function which invokes on every update operation on a table and encrypts a specific value as below:

create trigger project_trigger
before update
on projects
for each row
execute procedure project_function();

create or replace function project_function()
returns trigger as
$BODY$
begin
IF (TG_OP = 'UPDATE') THEN
NEW.title = armor(pgp_sym_encrypt(NEW.title, 'cipher-algo=aes256' ));
return NEW;
END IF;
end;
$BODY$
language plpgsql;

The above approach for encryption is working fine and an armored PGP encrypted value gets saved as below:

-----BEGIN PGP MESSAGE-----

ww0EBwMCneBsNZw1gYFq0jYB9y58EoTaRXWmDFqvQArWU5tZ+wS+7yAm9ycVUpkH1EzvYLbfRoDj
rqR83I0nGErHcLSLlAs=
=IYg8
-----END PGP MESSAGE-----

Decryption needs to be done at the application level for which I followed the following 2 steps:

• Added bcpg-jdk15on and bcprov-jdk15on dependencies. (v1.47)
• Implementation:

String key = "aes_key";
File file = new File("D:\\file.txt.asc"); //this file contains the PGP encrypted value as shown above
InputStream input = new FileInputStream(file);
byte[] byt = new byte[input.available()];
input.read(byt);
input.close();
Security.addProvider(new BouncyCastleProvider());
System.out.println(new String(ByteArrayHandler.decrypt(byt, 
                    key.toCharArray())));

I keep getting the following exception while using the above approach to decrypt the value:

Exception in thread "main" org.bouncycastle.openpgp.PGPDataValidationException: data check failed. at org.bouncycastle.openpgp.PGPPBEEncryptedData.getDataStream(Unknown Source) at org.bouncycastle.openpgp.examples.ByteArrayHandler.decrypt(Unknown Source) at abc.demo.encryption.SymmetricDecyption.main(SymmetricDecyption.java:59)

So can someone guide me to the appropriate approach to achieve decryption at the application level (not in the queries).

Answer 1

There are two problems. The PGPDataValidationException is caused by using a different pass-phrase for encryption and decryption. If you had used the correct pass-phrase, then you would have found that the Bouncy Castle example code is not fully functional.

The trigger is probably not what you intended. The call to pgp_sym_encrypt should look more like this:

create or replace function project_function()
returns trigger as
$BODY$
begin
IF (TG_OP = 'UPDATE') THEN
NEW.title = armor(pgp_sym_encrypt(NEW.title, 'my-secret-passphrase', 'cipher-algo=aes256, compress-algo=2' ));
return NEW;
END IF;
end;
$BODY$
language plpgsql;

The three input parameters to pgp_sym_encrypt are the text to be encrypted, the pass phrase from which the cipher key will be derived, and options. In your question, you omitted the pass phrase.

Second the BouncyCastle example code assumes that the plain text has been compressed. I have added RFC1950 compression (ZLIB) to the pgp_sym_encrypt.

With those changes to the trigger I get:

postgres=# update projects set title = 'My secret compressed title.';
UPDATE 1
postgres=# \t off
postgres=# select * from projects;
title
-----BEGIN PGP MESSAGE-----

ww0ECQMCuN3MyfrWhBt50lcBGbUtjOlTBxGpAFCl7aYEybhhXRJodDsikWxdLmOsXnE6vWr9mwd7
dGy7N1eE5VFmwI5N29eCNhEvG5U4YmVC7fV1A1sBeoJMtsO/nz2mi2jbFiZHlzo=
=s6uI
-----END PGP MESSAGE-----

(1 row)
postgres=#

Feeding that into a Java program:

    String value = "-----BEGIN PGP MESSAGE-----\n"
        + "\n"
        + "ww0ECQMCuN3MyfrWhBt50lcBGbUtjOlTBxGpAFCl7aYEybhhXRJodDsikWxdLmOsXnE6vWr9mwd7\n"
        + "dGy7N1eE5VFmwI5N29eCNhEvG5U4YmVC7fV1A1sBeoJMtsO/nz2mi2jbFiZHlzo=\n"
        + "=s6uI\n"
        + "-----END PGP MESSAGE-----\n";

    String key = "my-secret-passphrase";
    byte[] byt = value.getBytes(StandardCharsets.UTF_8);
    Security.addProvider(new BouncyCastleProvider());
    System.out.println(new String(ByteArrayHandler.decrypt(byt, key.toCharArray()), StandardCharsets.UTF_8));

Produces the output:

My secret compressed title.

Exactly as desired.

If you want to not compress the plain text before encrypting it, then you can look at the example PBEFileProcessor as this handles both compressed and uncompressed data, or you can just use this code:

  public static byte[] decrypt(
      byte[] encrypted,
      char[] passPhrase
  ) throws IOException, PGPException {
    JcaPGPObjectFactory pgpF = new JcaPGPObjectFactory(PGPUtil.getDecoderStream(new ByteArrayInputStream(encrypted)));

    // Find the encrypted data list. The first object might be a PGP marker packet, or the actual data list
    PGPEncryptedDataList enc;
    Object o = pgpF.nextObject();
    if (o instanceof PGPEncryptedDataList) {
      enc = (PGPEncryptedDataList) o;
    } else {
      enc = (PGPEncryptedDataList) pgpF.nextObject();
    }

    // Do the decryption
    PGPPBEEncryptedData pbe = (PGPPBEEncryptedData) enc.get(0);
    InputStream clear = pbe.getDataStream(new JcePBEDataDecryptorFactoryBuilder(
        new JcaPGPDigestCalculatorProviderBuilder().setProvider("BC").build()).setProvider("BC").build(passPhrase)
    );

    // Process the decrypted data. It may be compressed, or it may be literal
    JcaPGPObjectFactory pgpFact = new JcaPGPObjectFactory(clear);
    o = pgpFact.nextObject();
    if (o instanceof PGPCompressedData) {
      // Need to decompress the data
      PGPCompressedData cData = (PGPCompressedData) o;
      pgpFact = new JcaPGPObjectFactory(cData.getDataStream());
      o = pgpFact.nextObject();
    }

    // We should have the literal data now, so convert it into bytes
    PGPLiteralData ld = (PGPLiteralData) o;
    return Streams.readAll(ld.getInputStream());
  }

Finally, when decrypting in postgresql you do not need to specify whether the plain text was compressed, nor how it was encrypted, as the PGP data specifies this, so you can do:

select pgp_sym_decrypt(dearmor(title), 'my-secret-passphrase') from projects;