With the Greenplum Database pgcrypto
extension, you can use the PostgreSQL module pgcrypto
encryption/decryption functions. The pgcrypto
functions allow database administrators to store certain columns of data in encrypted form. This adds an extra layer of protection for sensitive data, as data stored in Greenplum Database in encrypted form cannot be read by anyone who does not have the encryption key, nor can it be read directly from the disks.
See pgcrypto in the PostgreSQL documentation for more information about individual functions.
Note: 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 optimal security, consider also using SSL connections between the client and the Greenplum master server.
For Greenplum Database 5.21.5 and earlier 5.x releases, you enable pgcrypto
functions as a module using an SQL script. For Greenplum Database 5.22.0 and later 5.x releases, you enable pgcrypto
functions as an extension.
To enable the pgcrypto
extension for Greenplum Database 5.22.0 and later, follow these steps.
If needed, uninstall the existing pgcrypto
module with the SQL script uninstall_pgcrypto.sql
.
The uninstall_pgcrypto.sql
script is in the share/postgresql/contrib/
directory of the Greenplum Database 5.x installation that you used to install pgcrypto
. This example psql
command runs the SQL script to uninstall pgcrypto
from the database testdb
.
$ psql -d testdb -f <old-gp-install-dir>/share/postgresql/contrib/uninstall_pgcrypto.sql
For each database that uses the pgcrypto
functions, register the pgcrypto
extension if necessary. This example psql
command registers the pgcrypto
extension in the database testdb
.
$ psql -d testdb -c 'CREATE EXTENSION pgcrypto'
When you remove pgcrypto
extension support from a database, user-defined functions in the database that use pgcrypto
functions will no longer work.
To disable the pgcrypto
extension for Greenplum Database 5.22.0 and later, use the DROP EXTENSION
command. This example psql
command drops the pgcrypto
extension in the database testdb
.
$ psql -d testdb -c 'DROP EXTENSION pgcrypto'
Note: If you enabled the pgcrypto.fips server configuration parameter, you must disable the parameter.
Parent topic: Installing Optional Extensions