Using pgcrypto in Managed Service for Greenplum®
The pgcrypto
Installing the pgcrypto extension in a Greenplum® cluster
-
Connect to a database as owner or user with the
CREATE
permission to the database and run the query below:CREATE EXTENSION pgcrypto;
-
Make sure that the extension is installed:
SELECT extname FROM pg_extension;
For more information about the pgcrypto
extension, see the official documentation
Use cases
One-way encryption
-
Create a table:
CREATE TABLE tbl_one_way_crypt(username varchar(100) PRIMARY KEY, cryptpwd text);
-
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')));
-
Make sure that the data in the
cryptpwd
column is encrypted:SELECT * FROM tbl_one_way_crypt;
-
See if
Password123
is stored in the encrypted data:SELECT username FROM tbl_one_way_crypt WHERE cryptpwd = crypt('Password123', cryptpwd);
Symmetric encryption
-
Create a table:
CREATE TABLE tbl_sym_crypt (username varchar(100) PRIMARY KEY, crypttext text);
-
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'));
-
Make sure that the data in the
crypttext
column is encrypted:SELECT * FROM tbl_sym_crypt;
-
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 encryption
-
Use the GnuPG
utility to generate a pair of keys:-
Create a pair of keys:
gpg --gen-key
Specify the key
USER-ID
(Real name
andEmail address
) and thePassphrase
. -
Export the keys to files:
gpg -a --export <key_Real_name> > public.key && \ gpg -a --export-secret-keys <key_Real_name> > private.key
-
-
Create a table:
CREATE TABLE tbl_asym_crypt(ssn_id SERIAL PRIMARY KEY, username varchar(100), ssn bytea);
-
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;
-
Make sure that the data in the
ssn
column is encrypted:SELECT * FROM tbl_asym_crypt;
-
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.