Re: [Postgres-xc-general] support of postgre's "Additional Supplied Modules" (hstore, pgcrypto, etc..)

[Koichi S. <koi...@gm...>]

pgcrypto looks to work fine.   Obviously, it is not recommended to
encrypt distribution key, where we don't have good optimization.

Here's my result.

Enjoy;
----
Koichi

----8<------------------------8<------------
postgres=# create extension pgcrypto;
CREATE EXTENSION
postgres=# create table test (id int, name bytea);
CREATE TABLE
postgres=# insert into test select 1, encrypt(convert_to('Suzuki',
'UTF8'), 'pass'::bytea, 'aes');
INSERT 0 1
postgres=# insert into test select 2, pgp_sym_encrypt('Sato', 'pass');
INSERT 0 1
postgres=# insert into test select 3, pgp_sym_encrypt('Sato', 'pass');
INSERT 0 1
postgres=# select * from test;
 id |
    name
----+------------------------------------------------------------------------------------------------------------------------------------------------
  1 | \x42a2c9e81526133e031336b8a09a3ae8
  2 | \xc30d0407030285360772296ea94e78d2350126b86572a89a4f054fb95eb0b8ab86b214e66571469460a469b740f26c3465e19f17500703e75e06378bc4b75b37f053befad44a
  3 | \xc30d040703024a9931a54cde6b6f60d235011bdbf887c8a0cb2e122ed034101ef64b3d5fb7d25cbff91dd82661de665651d92c0c1ee7d6672139ef7e59b767bb90612ab71bef
(3 rows)

postgres=# SELECT id, convert_from(decrypt(name, 'pass'::bytea,
'aes'), 'UTF8') FROM test where id = 1;
 id | convert_from
----+--------------
  1 | Suzuki
(1 row)

postgres=# SELECT id, pgp_sym_decrypt(name,'pass') FROM test WHERE id IN (2,3);
 id | pgp_sym_decrypt
----+-----------------
  3 | Sato
  2 | Sato
(2 rows)

postgres=# create table test1 (id varchar, name bytea);
CREATE TABLE
postgres=# insert into test1 values (pgp_sym_encrypt('1', 'pass'),
pgp_sym_encrypt('Suzuki', 'pass'));
INSERT 0 1
postgres=# select * from test1;
                                                                    id
                                                                   |

name
------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------
 \xc30d0407030233ffc6b915c7908d62d23201980e846bcfbf772c43ae7bb5c9476da716fe80dd2149e81aaa2353767bb5402af7fa54eb8b498ea21de58a58e8ca697229
| \xc30d040703020169a4462ef5f63d65d2370108b2e0dea578142f63e416989a95e19663605c7f035b54074d6f190e704e7bf75b651c6dfa6091667a8242fcd91ae60384bf0ec0c4c6
(1 row)

postgres=# select pgp_sym_decrypt(id::bytea, 'pass'),
pgp_sym_decrypt(name::bytea, 'pass') from test1;
 pgp_sym_decrypt | pgp_sym_decrypt
-----------------+-----------------
 1               | Suzuki
(1 row)

postgres=# select pgp_sym_decrypt(id::bytea, 'pass'),
pgp_sym_decrypt(name::bytea, 'pass') from test1 where
pgp_sym_decrypt(id::bytea, 'pass') = '1';
 pgp_sym_decrypt | pgp_sym_decrypt
-----------------+-----------------
 1               | Suzuki
(1 row)

postgres=#
---->8------------------------>8------------


2012/7/26 Aris Setyawan <ari...@gm...>:
> Hi All,
>
> Is "Additional Supplied Modules" supported in XC cluster (hstore,
> pgcrypto, etc..)?
>
> -thanks
>
> ------------------------------------------------------------------------------
> Live Security Virtual Conference
> Exclusive live event will cover all the ways today's security and
> threat landscape has changed and how IT managers can respond. Discussions
> will include endpoint security, mobile security and the latest in malware
> threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
> _______________________________________________
> Postgres-xc-general mailing list
> Pos...@li...
> https://lists.sourceforge.net/lists/listinfo/postgres-xc-general