pgcrypto

Greenplum Database is installed with an optional module of encryption/decryption functions called pgcrypto. 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.

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 coordinator server.

Installing and Registering the Module

The pgcrypto module is installed when you install Greenplum Database. Before you can use any of the functions defined in the module, you must register the pgcrypto extension in each database in which you want to use the functions. Refer to Installing Additional Supplied Modules for more information.

Configuring FIPS Encryption

Starting with Greenplum 6.22, the pgcrypto extension provides a module-specific configuration parameter, pgcrypto.fips. This parameter configures Greenplum Database support for a limited set of FIPS encryption functionality (Federal Information Processing Standard (FIPS) 140-2). For information about FIPS, see https://www.nist.gov/itl/popular-links/federal-information-processing-standards-fips. The default setting is off, FIPS encryption is not enabled.

Before enabling this parameter, ensure that FIPS is enabled on all Greenplum Database system hosts.

When this parameter is enabled, these changes occur:

FIPS mode is initialized in the OpenSSL library
The functions digest() and hmac() allow only the SHA encryption algorithm (MD5 is not allowed)
The functions for the crypt and gen_salt algorithms are disabled
PGP encryption and decryption functions support only AES and 3DES encryption algorithms (other algorithms such as blowfish are not allowed)
RAW encryption and decryption functions support only AES and 3DES (other algorithms such as blowfish are not allowed)

To enable pgcrypto.fips

Enable the pgcrypto functions as an extension if it is not enabled. See Installing Additional Supplied Modules. This example psql command creates the pgcrypto extension in the database testdb.
```
psql -d testdb -c 'CREATE EXTENSION pgcrypto'
```
Configure the Greenplum Database server configuration parameter shared_preload_libraries to load the pgcrypto library. This example uses the gpconfig utility to update the parameter in the Greenplum Database postgresql.conf files.
```
gpconfig -c shared_preload_libraries -v '$libdir/pgcrypto'
```
This command displays the value of shared_preload_libraries.
```
gpconfig -s shared_preload_libraries
```
Restart the Greenplum Database system.
```
gpstop -ra 
```
Set the pgcrypto.fips server configuration parameter to on for each database that uses FIPS encryption. For example, these commands set the parameter to on for the database testdb.
```
psql -d postgres
```
```
ALTER DATABASE testdb SET pgcrypto.fips TO on;
```
Important
You must use the ALTER DATABASE command to set the parameter. You cannot use the SET command that updates the parameter for a session, or use the gpconfig utility that updates postgresql.conf files.
After setting the parameter, reconnect to the database to enable encryption support for a session. This example uses the psql meta command \c to connect to the testdb database.
```
\c testdb
```

To disable pgcrypto.fips

If the database does not use pgcrypto functions, disable the pgcrypto extension. This example psql command drops the pgcrypto extension in the database testdb.
```
psql -d testdb -c 'DROP EXTENSION pgcrypto'
```
Remove \$libdir/pgcrypto from the shared_preload_libraries parameter, and restart Greenplum Database. This gpconfig command displays the value of the parameter from the Greenplum Database postgresql.conf files.
```
gpconfig -s shared_preload_libraries
```
Use the gpconfig utility with the -c and -v options to change the value of the parameter. Use the -r option to remove the parameter.
Restart the Greenplum Database system.
```
gpstop -ra 
```

Additional Module Documentation

Refer to pgcrypto in the PostgreSQL documentation for more information about the individual functions in this module.