Why does PostgreSQL's pgp_sym_encrypt return different output although invoked for the same input?

Question

I am getting different results while executing PostgreSQL's pgp_sym_encrypt command with same plaintext and passphrase as input. When decrypting those different results with the same passphrase, I get the correct plain text back.

I would like to understand why the encrypt function always gives different result for same plain text with the same passphrase?

Same issue with plain text, unique key and session key also.

I have created unique constraints on same column where I am getting different encryption results, hence it allows to add new record for same text multiple times.

Example:

pgp_sym_encrypt('12345','key1')

If I execute the above command two times, then I will get different output and when I decrypt the differing outputs, I will get the same plaintext back.

Answer 1

OpenPGP Encryption

In OpenPGP, the message is not directly encrypted with the passphrase. Instead, a random session key is generated as key for the symmetric encryption of the message.

This session key now gets encrypted using the passphrase. By applying such a two-step approach, it is possible to encrypt the message once, but allow decryption using different private keys or passphrases by adding an encrypted copy of the session key once. For deeper understanding of how OpenPGP messages are constructed, have a look at RFC 4880, OpenPGP and the output of gpg --list-packets and pgpdump for the encrypted message (both of them print information on the OpenPGP packets).

Additionally, each message gets padded with some random bytes, also resulting in completely different message. Finally, the encrypted message stores the encryption timestamp, which will obviously be different unless you encrypt twice in the exactly same second.

Having different output when encrypting the same message twice can be very important: the information that two messages actually are the same message is often already an issue. By having random session keys and padding, an attacker cannot make any assumption on the message contents given he gets hold of multiple messages.

Unique Constraint on Encrypted Output

If you need to have a unique constraint on the message, calculate a cryptographic hash sum (for example, SHA-256) on the message and store this additionally (and calculate the unique constraint on the hash sum, instead of the encrypted message).