PostgreSQL中pgp_sym_encrypt函数的加密类型及修改方式咨询

Hey there! Let's dive into your questions about PostgreSQL's pgp_sym_encrypt function—super common stuff when dealing with column-level encryption, so great call asking for clarity.

By default, pgp_sym_encrypt relies on the CAST5 (Blowfish) symmetric encryption algorithm, which is part of the OpenPGP standard. Under the hood, it uses GnuPG's OpenPGP implementation, so it also includes additional security layers like key derivation (via S2K, String-to-Key) and integrity checking to ensure your encrypted data stays intact and secure.

Absolutely! You're not stuck with the default. When calling pgp_sym_encrypt, you can specify a different cipher algorithm using the third parameter (options string). PostgreSQL supports several modern, secure options here, including:

• AES-128
• AES-192
• AES-256 (highly recommended for strong security)
• CAST5 (default)
• 3DES (avoid this one—it's no longer considered secure for most use cases)

Here's a quick example of how to use AES-256 instead of the default:

SELECT pgp_sym_encrypt('sensitive_column_data', 'your_very_strong_passphrase', 'cipher-algo=aes256');

A quick note: When decrypting with pgp_sym_decrypt, you don't need to explicitly specify the cipher—PostgreSQL will automatically detect which algorithm was used during encryption, as long as you provide the correct passphrase.

If you want to go deeper, you can also tweak other encryption-related parameters like the key derivation mode (s2k-mode) or digest algorithm (s2k-digest-algo), but adjusting the cipher is the most common customization.

内容的提问来源于stack exchange，提问作者Punter Vicky