通过实现数据的加密，可以保证数据的安全性。在这里主要向大家介绍，如何在GREENPLUM中实现数据加密，提高数据安全性。
在GREENPLUM中，启用数据加密前，需要先安装一个加密扩展包，该扩展包可以从
http://www.kuaipan.cn/index.php?ac=file&oid=28721511460241463下载
扩展包的安装过程如下
unzip pgcrypto-1.0-GPDB-4.2-build-3-SuSE10-x86_64.zip
chown -R gpadmin pgcrypto-1.0-suse10-x86_64.gppkg
gpadmin@mdw:/stage> gppkg -i pgcrypto-1.0-suse10-x86_64.gppkg
20120207:19:34:49:gppkg:mdw:gpadmin-[INFO]:-Starting gppkg with args: -i pgcrypto-1.0-suse10-x86_64.gppkg
20120207:19:34:50:gppkg:mdw:gpadmin-[INFO]:-Installing package pgcrypto-1.0-suse10-x86_64.gppkg
20120207:19:34:50:gppkg:mdw:gpadmin-[INFO]:-Validating rpm installation cmdStr='rpm --test -i /usr/local/greenplum-db/./.tmp/pgcrypto-1.0-1.x86_64.rpm --dbpath /usr/local/greenplum-db/./share/packages/database --prefix /usr/local/greenplum-db/.'
20120207:19:34:51:gppkg:mdw:gpadmin-[INFO]:-Please run psql -d mydatabase -f $GPHOME/share/postgresql/contrib/pgcrypto.sql to enable the package.
20120207:19:34:51:gppkg:mdw:gpadmin-[INFO]:-pgcrypto-1.0-suse10-x86_64.gppkg successfully installed.
gpadmin@mdw:/stage> psql -d sh -f $GPHOME/share/postgresql/contrib/pgcrypto.sql
SET
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
完成扩展包的安装后，我们可以创建用来进行数据加密和解密的函数
select encode (encrypt_iv(decode($1,'ESCAPE'),
decode('1DD7067184F66EFF43732FE4CA0B40AE','HEX'),
decode('0CFD10360F24EA0F160C35172B38170C','HEX'),
'AES-CBC'),'BASE64')
$$
LANGUAGE SQL;
create or replace function decryptor (in myVal text)
returns bytea
as $$
select decrypt_iv(decode($1,'BASE64'),
decode('1DD7067184F66EFF43732FE4CA0B40AE','HEX'),
decode('0CFD10360F24EA0F160C35172B38170C','HEX'),
'AES-CBC')
$$
LANGUAGE SQL;
下面我们就可以对函数进行测试
sh=> select CUST_MAIN_PHONE_NUMBER,encryptor(CUST_MAIN_PHONE_NUMBER) encr,decryptor(encryptor(CUST_MAIN_PHONE_NUMBER)) decr from customers;
cust_main_phone_number | encr | decr
------------------------+--------------------------+----------------
288-613-9676 | 0X/va8ftSmE3SI4DpL6WEg== | 288-613-9676
183-207-2933 | SLJgyJxlskcEHG2WnkvV7A== | 183-207-2933
208-194-6025 | uIcJwj5q5HURa1x5Qdtm2Q== | 208-194-6025
258-692-7934 | j9/0rW/TYES5GPGl9j+wcQ== | 258-692-7934
103-711-9009 | 8PCukCOYbdFDeP8gmVIk5A== | 103-711-9009
454-716-5260 | jdmkTq8kRysyiYVTf/yujA== | 454-716-5260
155-204-1940 | aMqf8eSkIkA4ze7frjpdbQ== | 155-204-1940
630-692-5246 | kFFd5vdSYRO3L7LbyNlycg== | 630-692-5246
361-248-7857 | k+/FIdqxCZppubtJnHj/Ig== | 361-248-7857
570-248-9913 | 08BmduhHWmaurf2aiIEnfA== | 570-248-9913
166-716-4030 | z0Y5kA+BI8HLd51IaquoCw== | 166-716-4030
629-204-7324 | 8H1ClI76BS02oK0nJJNxnw== | 629-204-7324
173-226-3225 | wP7HNsm2CjnG2YMuyh3Zcg== | 173-226-3225
503-692-4047 | 1uVcmhzgHpJkDxTEmsOB6Q== | 503-692-4047
563-711-6988 | gz+rbkRRO2CQBTh4lhHgwg== | 563-711-6988
399-716-2429 | NT+592ex7OrJpGUnVqEKFg== | 399-716-2429
556-204-9439 | El4xUqv8BWkJS0PnQ0qgow== | 556-204-9439
444-226-8453 | QFOdjWiQNcAFOaUNWguN3w== | 444-226-8453
119-248-1644 | wc3OR+haruoV2kMapXT3xg== | 119-248-1644
498-711-5332 | Mrf0kst9RnYWvnKKWqiCag== | 498-711-5332
569-349-9468 | NCUY+r02sOYZV20I4QBp4A== | 569-349-9468
537-204-8513 | Kl7V+qLbjRWChqp7dIDCQw== | 537-204-8513
159-716-9696 | xTD11ack+juWaGl7PUVlpA== | 159-716-9696
593-349-9980 | Fkq17MWXJJl3dDdO8bO+ZA== | 593-349-9980
