pgcrypto
The pgcrypto module provides cryptographic functions: general hashing, password hashing, PGP encryption, raw encryption, and random data generation. With these functions, you can store certain columns of data in encrypted form. Data that is stored encrypted cannot be read by anyone who does not have the encryption key, and it cannot be read directly from the disks.
The pgcrypto functions run inside the database server, which means that all the data and passwords move between pgcrypto and the client application in clear text. For better security, also use SSL connections between the client and the Apache Cloudberry coordinator.
Install and register the module
pgcrypto is built and installed by a default Apache Cloudberry build. Register the extension in each database in which you want to use it:
CREATE EXTENSION pgcrypto;
Functions
| Group | Functions |
|---|---|
| General hashing | digest(), hmac() |
| Password hashing | crypt(), gen_salt() |
| PGP encryption | pgp_sym_encrypt(), pgp_sym_encrypt_bytea(), pgp_sym_decrypt(), pgp_sym_decrypt_bytea(), pgp_pub_encrypt(), pgp_pub_encrypt_bytea(), pgp_pub_decrypt(), pgp_pub_decrypt_bytea(), pgp_key_id(), armor(), dearmor(), pgp_armor_headers() |
| Raw encryption | encrypt(), decrypt(), encrypt_iv(), decrypt_iv() |
| Random data | gen_random_bytes(), gen_random_uuid() |
Example:
SELECT digest('santa claus', 'sha256');
SELECT pgp_sym_decrypt(pgp_sym_encrypt('santa clause', 'mypass', 'cipher-algo=aes256'), 'mypass');
In addition to the ciphers of the PostgreSQL module, the raw encryption functions accept the SM4 ciphers sm4-ecb and sm4-cbc, and digest() and hmac() accept the sm3 digest. In a build that uses OpenSSL, the SM4 ciphers are available only if Apache Cloudberry was configured with --enable-openssl-redirect. Otherwise, they fail with Cannot use "sm4-ecb": No such cipher algorithm. The sm3 digest is available if the OpenSSL library provides it.
FIPS mode
The module defines the configuration parameter pgcrypto.fips, which restricts pgcrypto to a limited set of functionality that is suitable for FIPS 140-2 mode. The parameter is a Boolean, its default value is off, and only superusers can change it.
FIPS mode requires an OpenSSL library that provides the FIPS module, on all hosts of the cluster. If the OpenSSL version that Apache Cloudberry was compiled against has no FIPS support, turning the parameter on fails with the error FIPS enabled OpenSSL is required for strict FIPS mode. A build without OpenSSL does not support FIPS mode.
When pgcrypto.fips is on:
- FIPS mode is turned on in the OpenSSL library.
crypt()andgen_salt()are not allowed.- The raw encryption functions (
encrypt(),decrypt(),encrypt_iv(), anddecrypt_iv()) are not allowed. digest()andhmac()reject digests that are not FIPS-approved, such asmd5, with an error that starts withCannot use "md5".- The PGP functions are not usable either. They recognize only the
3des,aes128(aes),aes192, andaes256cipher names and thesha1,sha256,sha384, andsha512digest names, and reject other names, such asbf, with the errorUnsupported cipher algorithm. Encryption and decryption with the recognized ciphers then fail withrequested functionality not allowed in FIPS mode, becausepgcryptoreaches these ciphers through an OpenSSL interface that is not permitted in FIPS mode.
A call to crypt(), gen_salt(), or a raw encryption function fails with the error requested functionality not allowed in FIPS mode.
Enable FIPS mode
-
Add the
pgcryptolibrary toshared_preload_libraries, and restart the cluster. This makes thepgcrypto.fipsparameter known to the server before anypgcryptofunction is called.gpconfig -c shared_preload_libraries -v 'pgcrypto.so'gpstop -arIf
shared_preload_librariesalready lists other libraries, keep them in the value. -
Set the parameter for each database that must use FIPS mode. For example, for the database
testdb:ALTER DATABASE testdb SET pgcrypto.fips TO on; -
Reconnect to the database, and check the setting:
SHOW pgcrypto.fips;
To turn FIPS mode off, run ALTER DATABASE testdb RESET pgcrypto.fips;, and reconnect.
Module documentation
See pgcrypto in the PostgreSQL documentation for details about the individual functions in this module.