pgcrypto Cryptographic Functions

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.

Enable pgcrypto Extension

To enable the pgcrypto extension for Greenplum Database 5.22.0 and later, follow these steps.

  1. 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.

    1. $ psql -d testdb -f <old-gp-install-dir>/share/postgresql/contrib/uninstall_pgcrypto.sql

  2. 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.

    1. $ psql -d testdb -c 'CREATE EXTENSION pgcrypto'

Disable pgcrypto Extension

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.

  1. $ 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