Using pgcrypto in Managed Service for Greenplum®

The pgcrypto extension provides cryptographic functions that allow database administrators to store certain columns of data in encrypted form.

Installing the pgcrypto extension in a Greenplum® clusterInstalling the pgcrypto extension in a Greenplum® cluster

1. Connect to a database as owner or user with the CREATE permission to the database and run the query below:

   CREATE EXTENSION pgcrypto;
   

2. Make sure that the extension is installed:

   SELECT extname FROM pg_extension;
   

For more information about the pgcrypto extension, see the official documentation.

Use casesUse cases

One-way encryptionOne-way encryption

1. Create a table:

   CREATE TABLE tbl_one_way_crypt(username varchar(100) PRIMARY KEY, cryptpwd text);
   

2. Insert the hash text data in the cryptpwd column:

   INSERT INTO tbl_one_way_crypt(username, cryptpwd)
       VALUES ('bob', crypt('Password123', gen_salt('md5'))),
              ('alice', crypt('Password123', gen_salt('md5')));
   

3. Make sure that the data in the cryptpwd column is encrypted:

   SELECT * FROM tbl_one_way_crypt;
   

4. See if Password123 is stored in the encrypted data:

   SELECT username FROM tbl_one_way_crypt
   WHERE cryptpwd = crypt('Password123', cryptpwd);
   

Symmetric encryptionSymmetric encryption

1. Create a table:

   CREATE TABLE tbl_sym_crypt (username varchar(100) PRIMARY KEY, crypttext text);
   

2. Insert the encrypted text data in the crypttext column:

   INSERT INTO tbl_sym_crypt (username, crypttext)
       VALUES ('bob', pgp_sym_encrypt('Text to encrypt','!qazSymKeyXsw2')),
              ('alice', pgp_sym_encrypt('Secret Data','!qazSymKeyXsw2'));
   

3. Make sure that the data in the crypttext column is encrypted:

   SELECT * FROM tbl_sym_crypt;
   

4. Retrieve the decrypted data by explicitly specifying bytea as the data type:

   SELECT username, pgp_sym_decrypt(crypttext::bytea, '!qazSymKeyXsw2')
   FROM tbl_sym_crypt;
   

Public-key encryptionPublic-key encryption

1. Use the GnuPG utility to generate a pair of keys:

   1. Create a pair of keys:

      gpg --gen-key
      

      Specify the key USER-ID (Real name and Email address) and the Passphrase.

   2. Export the keys to files:

      gpg -a --export <key_Real_name> > public.key && \
      gpg -a --export-secret-keys <key_Real_name> > private.key
      

2. Create a table:

   CREATE TABLE tbl_asym_crypt(ssn_id SERIAL PRIMARY KEY,
       username varchar(100), ssn bytea);
   

3. Insert the encrypted text data in the ssn column using the public key:

   INSERT INTO tbl_asym_crypt (username, ssn)
   SELECT tmp.username, pgp_pub_encrypt(tmp.ssn, keys.pubkey) AS tbl
   FROM (
       VALUES ('Alice', '123-45-6788'), ('Bob', '123-45-6799'))
       AS tmp(username, ssn)
   CROSS JOIN (SELECT dearmor('<contents_of_public.key_file>') AS pubkey) AS keys;
   

4. Make sure that the data in the ssn column is encrypted:

   SELECT * FROM tbl_asym_crypt;
   

5. Decrypt the data in the ssn column using the private key:

   SELECT username, pgp_pub_decrypt(ssn, keys.privkey, '<key_Passphrase>')
       AS decrypted_ssn FROM tbl_asym_crypt
   CROSS JOIN (SELECT dearmor('<contents_of_private.key_file>') AS privkey) AS keys;
   

Greenplum® and Greenplum Database® are registered trademarks or trademarks of VMware, Inc. in the United States and/or other countries.