Pivotal Knowledge Base

Follow

How to encrypt data loaded from external table with GPDB pgcrypto?

Environment

Product Version
Pivotal Greenplum (GPDB) 4.3.x
OS RHEL 6.x
Others  

Purpose

Greenplum Database (GPDB) database provides GPDB pgcrypto extension package and the gpg encryption utility for a user to encrypt data stored in the database. Refer to this white paper for details about 'how to install and use pgcrypto package'.

Sometimes there is a need to encrypt data which is loaded from an external table. This article gives an example on how to encrypt data from an external table:

Procedure

1. Create a heap table

gpadmin=# create table t3(note bytea) distributed by (note);
CREATE TABLE

2. Create another external table for loading data from

gpadmin=# create external table ext_t3 (note text) location ('gpfdist://mdw:8082/note.txt') format 'text' (delimiter '|');
CREATE EXTERNAL TABLE

3. Make sure data in the external table is able to be selected

gpadmin=# select * from ext_t3;
note
----------------------
12345dlfjsald;fasd
12903230slkdfcmsdlf
ncvl,zxnkvl32-098389
(3 rows)

4. Load and encrypt data from external table into the heap table

gpadmin=# insert into t3 select pgp_pub_encrypt(note, dearmor('-----BEGIN PGP PUBLIC KEY BLOCK-----
<public key>
-----END PGP PUBLIC KEY BLOCK-----')) from ext_t3;
INSERT 0 3

5. Verify the data was loaded and encrypted 

gpadmin=# select note from t3 limit 1;

note
-------------------------------------------------------------------------------------------------------------------------------------------------

\301\301N\003:2dmT,c\224\020\007\375\030\347\352\034g\307\371|\273\222\2220o\352\275\220\365\277a\002x#\260\305l\277\376\017+T\334|\346H\033\221
\207@\262\201\350fj\214\363\320\346\277\363Vb\325\343$\362\201\363\03482\221<\225]\215\272\345v+\237h\376\253;\033T%Qw\363\266|\224\343\213\245\2
57\374\037y\206U\233\376\354\235R\023\253d\275\204b\371\021O\006\241\027C\276\275\351\262\177d\311g\035\211oa\231\233\033C\3047\203\246\306\324\0
22\027p\201\360\204C\202}|vD\033\263D\242\232\014\005\340\243\315H\334g\211\326\353K$R\370\324\005z\274D.\352\330B\342v`c\320(\002l\263\333\206\0
27\003\327.\024\323\034\355\3719\334\375\323Xu\357\206\266%\203\355n\270\277\030\0210\376\306\362\030\223\332\376\337\321y\306r\002?L\345\211\363
\011l\336_\222\332\016\2054\266\263\241<pa\346#1\011\031&\346}V\202\314\355\007\376#\225\376\215D,\037\016kW\333\030.\250f\3106\206\357\303\353\3
14\017\321\252\305S\302\3220\032\303U=\260E\032)\354\311\035\261\337\220\261$3\\\225\207\215T\026\23404X5\247\201(\034MJ\016\263\220~c7H`\350\357
\002\260\323\364\205"|\257\2419\264\011D\247\3202T4\330{\241\376r-\013\034\324\032{k\342\354\033`\0168\200\205\253\270ct\250bW\015r?]\346~\205\25
6a\372\300\224\\7Wr!\000\301\034\344\251)\331\362\371>\253O\353\327f\2454\001\212\243\250\261\223W\334\241\015\231\015\362\236\037\010\267\005Q\2
47\330\303\204\0062\251\215\342\222q\235\341\334\313L\343.\323\361\247i\2369\315"\027\217\011\307\357\3711\362J=\007EJ\234\223\221\020\365\346z=\
353\367\362\002mn\326\367.\353\207\3401\363\362la\215b\011\006P\366\360\012\346S8\342\362\333\010NOd%\213\322E\001$\030W\326\022g\031l\232\264\35
5\305\304\324\346\033\360\005\364\031b"\314\327\\\330\352\235+X0\35251\337\326\267}\315\202\246\357\206\331z\3369i\006\305\273\000\020\272\343{[\
273\217\205\356\2411\360\331\240\036\024
(1 row)

6. Decrypt the data to verify it's been loaded correctly

gpadmin=# select pgp_pub_decrypt(note, dearmor('-----BEGIN PGP PRIVATE KEY BLOCK-----
< private key>
-----END PGP PRIVATE KEY BLOCK-----')) as dec_note from t3;
dec_note
----------------------
12903230slkdfcmsdlf
ncvl,zxnkvl32-098389
12345dlfjsald;fasd
(3 rows)

Note: replace< private key> and <public key> block with the public key generated in advance. Refer to the White Paper.

Additional Information

 

Comments

Powered by Zendesk