How to Encrypt Data Loaded from the External Table with Greenplum Database pgcrypto?
Article ID: 295494
Updated On:
Products
Issue/Introduction
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 pgcrypto documentation 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 of how to encrypt data from an external table:
Environment
Resolution
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.
Feedback
