pgcrypto

Greenplum provides pgcrypto package (PostgreSQL package compiled in Greenplum env) . The pgcrypto package is not installed by default with Greenplum Database, however you can download a pgcrypto package from the EMC Download Center, then use the Greenplum Package Manager (gppkg) to install pgcrypto across your entire cluster.

1. The pgcrypto functions allow database administrators to store certain columns of data in encrypted form.

2. This adds an extra layer of protection for sensitive data, as data stored in Greenplum Database in encrypted form cannot be read by users who do not

have the encryption key, nor be read directly from the disks.

3. It is important to note that the pgcrypto functions run inside database server.

4. All the data and passwords move between pgcrypto and the client application in clear-text.

5. For optimal security, consider also using SSL connections between the client and the Greenplum master server.

6. PgCrypto has various levels of encryption ranging from basic to advanced built-in functions.

7. Encryption makes it difficult to read data but it also comes with a cost of consuming resources to encrypt and decrypt.

8. It is important to pick your encryption strategies based on the sensitivity of the data and performance needs.

9. For Greenplum Database version 4.2 and higher, pgcrypto is available as a package,

10. You can download from the EMC Download Center and install using the Greenplum Package Manager (gppkg).

==========================================================================================================

To check if pgcrypto libraries are installed on the server:

gppkg -q --all | grep pgcrypto

To Install:

Download the package in a tmp directory.

[gpadmin@mdw tmp]$ ls -ltr | grep pgcrypto

-rw-r----- 1 gpadmin gpadmin 186051 Oct 14 11:03 pgcrypto-ossv1.1_pv1.2_gpdb4.3orca-rhel5-x86_64.gppkg

[gpadmin@mdw tmp]

Source the GPDB environment.[gpadmin@mdw tmp]$ source /usr/local/greenplum-db/greenplum_path.sh

Install the gppkg package using [gpadmin@mdw tmp]$ gppkg -i pgcrypto-ossv1.1_pv1.2_gpdb4.3orca-rhel5-x86_64.gppkg

-----

gppkg:mdw:gpadmin-[INFO]:-pgcrypto-ossv1.1_pv1.2_gpdb4.3orca-rhel5-x86_64.gppkg successfully installed.

Once done you will then need to run the script $GPHOME/share/postgresql/contrib/pgcrypto.sql for the database you want to use pgcrypto on.

To do this run the following command:

psql -d <DBNAME> -f $GPHOME/share/postgresql/contrib/pgcrypto.sql

To remove the shared libraries:

gppkg -r <Package Name as found in the previous command>

To remove the database function(s):

psql -d <DBNAME> -f $GPHOME/share/postgresql/contrib/uninstall_pgcrypto.sql

==========================================================================================================

Note: The Greenplum Package Manager (gppkg) utility installs pgcrypto and other Greenplum Database extensions, along with any dependencies, on all hosts across a cluster. It will also automatically install extensions on new hosts in the case of system expansion and segment recovery.

Before you install the pgcrypto software package,

1. Make sure that your Greenplum database is running,

2. You have sourced greenplum_path.sh,

3. Following two env variables $MASTER_DATA_DIRECTORY and $GPHOME are properly set.

1. Download the PostGIS package from the EMC Download Center then copy it to the master host.

Pivotal Advanced Database Services, pgcrypto, Version 1.1.3 Last modified Nov 6, 2013 File Name pgcrypto-1.1.3.0-4609.x86_64.tar.gz

[gpadmin@sachi ~]$ ls -ltr

-rw-r--r--. 1 gpadmin gpadmin 501760 Nov 19 20:07 pgcrypto-1.1.3.0-4609.x86_64.tar

[gpadmin@sachi ~]$ echo $MASTER_DATA_DIRECTORY

/home/gpmaster/gpsne-1

[gpadmin@sachi ~]$ echo $GPHOME

/usr/local/greenplum-db/.

[gpadmin@sachi ~]$

[gpadmin@sachi ~]$ tar -xvf pgcrypto-1.1.3.0-4609.x86_64.tar

./share/

./share/postgresql/

./share/postgresql/contrib/

./share/postgresql/contrib/uninstall_pgcrypto.sql

./share/postgresql/contrib/pgcrypto.sql

./lib/

./lib/postgresql/

./lib/postgresql/pgcrypto.so

./pgcrypto_install.sh

[gpadmin@sachi ~]$

[gpadmin@sachi ~]$ ls -ltr

total 108164

-r--r--r--. 1 root root 7008 Oct 17 2012 README_INSTALL

-rwxr-xr-x. 1 root root 55684023 Oct 17 2012 greenplum-db-4.2.2.4-build-1-CE-RHEL5-x86_64.bin

-rw-r--r--. 1 root root 54377435 Apr 23 2013 greenplum-db-4.2.2.4-build-1-CE-RHEL5-x86_64.zip

-rwxr-xr-x. 1 gpadmin gpadmin 6 Apr 23 2013 hostlist_singlenode

-rw-rw-r--. 1 gpadmin gpadmin 4515 Jul 24 09:04 gpinitsystem_singlenode

drwxrwxr-x. 3 gpadmin gpadmin 4096 Oct 27 23:56 share

-rwxrwxr-x. 1 gpadmin gpadmin 1983 Oct 27 23:56 pgcrypto_install.sh

drwxrwxr-x. 3 gpadmin gpadmin 4096 Oct 27 23:56 lib

drwxrwxr-x. 2 gpadmin gpadmin 4096 Nov 17 16:29 sachi

-rw-rw-r--. 1 gpadmin gpadmin 144092 Nov 17 18:09 0

-rw-rw-r--. 1 gpadmin gpadmin 81 Nov 18 08:40 table_list.txt

drwxrwxr-x. 2 gpadmin gpadmin 4096 Nov 19 20:00 gpAdminLogs

-rw-r--r--. 1 gpadmin gpadmin 501760 Nov 19 20:07 pgcrypto-1.1.3.0-4609.x86_64.tar

2. Run the install script

[gpadmin@sachi ~]$ ./pgcrypto_install.sh

<hosts file> not specified.

./pgcrypto_install.sh -f <hosts file>

[gpadmin@sachi ~]$ cat hostlist_singlenode

sachi

=======================================================================================================

[gpadmin@sachi ~]$ cat pgcrypto_install.sh

#!/bin/sh

USAGE="$0 -f <hosts file>"

if [ ! -d $GPHOME ]; then

echo "GPHOME is either not set or is not a directory."

exit 1

which gpssh > /dev/null

if [ 0 -ne $? ]; then

echo "gpssh not found in PATH."

exit 1

which gpscp > /dev/null

if [ 0 -ne $? ]; then

echo "gpscp not found in PATH."

exit 1

hosts=""

while getopts f: opt; do

case $opt in

hosts=$OPTARG

;;

esac

done

if [ "$hosts" = "" ]; then

echo "<hosts file> not specified."

echo $USAGE

exit 1

# Validate GPHOME exists on all segments.

cmd="gpssh -f $hosts test -d $GPHOME"

output=$($cmd)

if [ 0 -ne $? ]; then

echo "Directory $GPHOME not found on one or more segments."

exit 1

if [[ $output == *ERROR* ]]; then

echo "Error running gpssh."

echo "Command: $cmd"

exit 1

echo "Copying artifacts to master and segments."

# Copy pgcrypto.so to master and segments.

cp lib/postgresql/pgcrypto.so $GPHOME/lib/postgresql

if [ 0 -ne $? ]; then

echo "Failed to copy artifacts on master."

exit 1

# SQL scripts are needed only on the master.

cp share/postgresql/contrib/pgcrypto.sql \

share/postgresql/contrib/uninstall_pgcrypto.sql \

$GPHOME/share/postgresql/contrib

if [ 0 -ne $? ]; then

echo "Failed to copy artifacts on master."

exit 1

cmd="gpscp -f $hosts lib/postgresql/pgcrypto.so =:$GPHOME/lib/postgresql"

output=$($cmd)

if [ 0 -ne $? ]; then

echo "Failed to copy artifacts to one or more segments."

# Clean up.

output=$(gpssh -f $hosts rm -f $GPHOME/lib/postgresql/pgcrypto.so)

exit 1

if [[ $output == *ERROR* ]]; then

echo "Error running gpscp."

echo "Command: $cmd"

exit 1

echo "Creating pgcrypto functions."

psql -d template1 -f share/postgresql/contrib/pgcrypto.sql

if [ 0 -ne $? ]; then

echo "Failed to create pgcrypto functions."

exit 1

=======================================================================================================

Run the

[gpadmin@sachi ~]$ ./pgcrypto_install.sh -f hostlist_singlenode

gpadmin@sachi's password:

[gpadmin@sachi ~]$ ./pgcrypto_install.sh

<hosts file> not specified.

./pgcrypto_install.sh -f <hosts file>

[gpadmin@sachi ~]$ ./pgcrypto_install.sh -f hostlist_singlenode

Copying artifacts to master and segments.

Creating pgcrypto functions.

SET

psql:share/postgresql/contrib/pgcrypto.sql:9: ERROR: incompatible library "/usr/local/greenplum-db-4.2.2.4/lib/postgresql/pgcrypto.so": version mismatch (dfmgr.c:360)