-
Notifications
You must be signed in to change notification settings - Fork 0
/
pgbench_bf_vs_aes_alter.sql
64 lines (42 loc) · 10.3 KB
/
pgbench_bf_vs_aes_alter.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
--
-- BF
--
-- Check options http://www.postgresql.org/docs/9.4/static/pgcrypto.html
-- Symetic pgp_sym_encrypt_bytea(data bytea, psw text [, options text ]) returns bytea
-- Convert everything in the history
ALTER TABLE pgbench_history
ALTER delta TYPE bytea USING pgp_sym_encrypt_bytea(delta::text::bytea, $$secret$$::text,'compress-algo=1, compress-level=5, cipher-algo=bf'::text),
ALTER mtime TYPE bytea USING pgp_sym_encrypt_bytea(mtime::text::bytea, $$secret$$::text,'compress-algo=1, compress-level=5, cipher-algo=bf'::text),
ALTER filler TYPE bytea USING pgp_sym_encrypt_bytea(filler::text::bytea, $$secret$$::text,'compress-algo=1, compress-level=5, cipher-algo=bf'::text);
-- Test:
-- select pgp_pub_decrypt(mtime, $$\x9503530456c489fe1108009f62be739d0b30e215f73f1c7af6582739c4d9fa01aeef349344ff0351ea02f84f689c0c6a7f1c4efb5894ce91b3d330f472ae6969621767762edf0f804c1d5a306560daaeeb0f3b66a6229395e2e8a1fd8ab18798e50557b5ecc558992ca9a0ef209e9f62af438c04ea76c5b6fbe34d94e417dffd152c3752d559ef8969c1718e049e336ddb51e63c65a54e00abc9fede73d7e5f6e2302414d8ced6756ca45879bb22ab658f2b42c2232180ec17823ac41082144b87a4cf796ddad0808217b9369f1b0cd3c4de15ce90b730dff6d2214fc1804617a76ec37a9269563ccc59ed4419c6e0cde5bd3a9ea0e4e9e42a835704dd1d44d00eed1ecf5c7a275b70314b0100ad40ba1ff0c5b9e7731f8b99579203dd4210670b14c9fba1aeae8fcf8caf843707fb041ec34d981f9a88b78553ba6a059e30030f060b581168fda116ac0e79b4faea9986fb8739d4651ae407ba73f8da7ebd58080637fa07cda38ee24d020d3d5de0695179973e7d55c0eada48a9ba3990449eeb75a0019ab9ab8651c88711ab88a44b0a973c318f41a0fff8f7ede56500c43447277028c08001e889fc6df4be470df2d32cee80d153d228450a77094ef097e9a8e06904bcf5da9cd2f33f77cecc42521a9de4f5b4924caca54718e6808f8c680a08db51dab75780991883c5a3ecd3d4d598d4ea62fb66b24b5827f694d36deccdefbc82eafe5cdae0879bf0652960f72e0381c053273feef5c1f9409b416abbd2f8ac47bf54cb13fee9c022127fe407ff7317ed490f36b09d78c9e8d15c486058f66589c900b923fa986f5ac2322e690f0ec35d5d1d8084de2fdee1ae61cfe8fcf05fc0a62ed0166f1a0a55c18b8f73a3c2af6052a8d41a2a9fa2913ddb0005966285e9ff78ebeefabd9f30a2548e35de170d68fa445a6ba6785c82a3970a9943cc2ce704f1ba323cbf97fee914098f6e7fa07b44b81aa5d878ccc7d1a659015723ad3b5f406bc80ae427125be22c8c675eb2b14f1ab5f7778551b042ba186959ca7e60b5e6bcb6bc55225f3bdaeb93e4f5b82564227ef2b323ce99fe1eb4c55a1413e111d51a2ed7daed8e53988ea2525bb74307193ac9f50dceefeff88126b0f66bfb913d3bf6525df620f8fa4e80c00000ff49f1c60c5aa28856235229b270c337e2cd9471c26f9362c60297424313d9eb46107ab43f456d616e75656c20285468697320697320612074657374206b657920666f7220616e2061727469636c6529203c63616c766f407079746869616e2e636f6d3e887a041311080022050256c489fe021b03060b090807030206150802090a0b0416020301021e01021780000a0910e9ceecf3e65ff517fa7e00ff6fbc2a78a3dd7d1682e6af5b576e9aed556d67ef5ca66bdebcd551c91a49b9c9010084a69d54f8cf5fbf87f32e190d3859cf761db35b3c983a4f9aa8b5a5645a29529d023d0456c489fe100800c7e0dd9b3806f269745c6ae8ae0073450b825f9192e3838669a59a0cba44252dfc5dc0c9b9959ef933456bcf85dcf958ee5ebfb9d6dce9cd10c8d17797c739fc033425833e3a096cb3c35de0cbae50f7b71f563f7371ccf516d014d27c2ff070661fb7e5199946a1d2b5042708447abce70b4b0ac99cbda09f33fb243313ef911056175dbab4f937f96149e38a0349eee1ab3e62494cdc4bd91f62594c5acbd0a4f94bc869411f3da885f9392e3168fd70328be7e8f7df0abb7df08f83d73dcde3b2c44587994a5b0bda213e89f9eb434c14ee22cd91de4b0b5de4168900bef9fdc065083e58f6e2bf228195b08c77bcb1fad22484cde5664dff8dba2072e29700030507fd19d529288f45fd9cf6d2946b1f3207b5a88d9b1358725520f36ccad6c6b8d0568b4c7db39ba77c12c142a3c7e65cddece9bb1b5f9fd779eef5b70f4934b422dcfbacb683f0ab56537859cc51431287855b9bead02f46acff127b4096b7c0c3165f440dce9a9c4afd2eedfb13dda920d6cf05cce245e13456a7bb3cfb3f60b50ce7ca11689ea2874473636c969ecf87df3e377f0cc4e56158f5ee7ddaeaec4dbd1e7416d930f97203bf8cd2e348346eafdb5ecc2071f5b2d409b3787a95772d28d0c6d2ceedd3b7a79e658ca03c44b5c0f0168e69dbf5ff2b3e732167e6092a06b99c529f9cb104e0251bd4cc92a68800fed98a8fe3a656d9fbe7fcf5c9efb52300015101cddfb1b3683f74b367659209d34282da3a384e70d4c17e68b82f26e031a579f944ec6089e870c5ce54a216e38861041811080009050256c489fe021b0c000a0910e9ceecf3e65ff517f1f700fe36668ecf6f95d873d3874e1d1c1e43446a0ddd1863d04ca8391cd70ad23e0f9300ff422ba4950db06c6416e902955d113c32d66c9cec96ecebc459591fa61ccc0c68$$,'')::text from pgbench_history;
ALTER TABLE pgbench_branches
ALTER bbalance TYPE bytea USING pgp_sym_encrypt_bytea(bbalance::text::bytea, $$secret$$::text,'compress-algo=1, compress-level=5, cipher-algo=bf'::text),
ALTER filler TYPE bytea USING pgp_sym_encrypt_bytea(filler::text::bytea, $$secret$$::text,'compress-algo=1, compress-level=5, cipher-algo=bf'::text);
ALTER TABLE pgbench_accounts
ALTER abalance TYPE bytea USING pgp_sym_encrypt_bytea(abalance::text::bytea, $$secret$$::text,'compress-algo=1, compress-level=5, cipher-algo=bf'::text),
ALTER filler TYPE bytea USING pgp_sym_encrypt_bytea(filler::text::bytea, $$secret$$::text,'compress-algo=1, compress-level=5, cipher-algo=bf'::text);
-- testsym=# select oid from pg_class where relname = 'pgbench_accounts' and relkind = 'r';
ALTER TABLE pgbench_tellers
ALTER tbalance TYPE bytea USING pgp_sym_encrypt_bytea(tbalance::text::bytea, $$secret$$::text,'compress-algo=1, compress-level=5, cipher-algo=bf'::text),
ALTER filler TYPE bytea USING pgp_sym_encrypt_bytea(filler::text::bytea, $$secret$$::text,'compress-algo=1, compress-level=5, cipher-algo=bf'::text);
--
-- AES 128
--
ALTER TABLE pgbench_history
ALTER delta TYPE bytea USING pgp_sym_encrypt_bytea(delta::text::bytea, $$secret$$::text,'compress-algo=1, compress-level=5, cipher-algo=aes128'::text),
ALTER mtime TYPE bytea USING pgp_sym_encrypt_bytea(mtime::text::bytea, $$secret$$::text,'compress-algo=1, compress-level=5, cipher-algo=aes128'::text),
ALTER filler TYPE bytea USING pgp_sym_encrypt_bytea(filler::text::bytea, $$secret$$::text,'compress-algo=1, compress-level=5, cipher-algo=aes128'::text);
-- Test:
-- select pgp_pub_decrypt(mtime, $$\x9503530456c489fe1108009f62be739d0b30e215f73f1c7af6582739c4d9fa01aeef349344ff0351ea02f84f689c0c6a7f1c4efb5894ce91b3d330f472ae6969621767762edf0f804c1d5a306560daaeeb0f3b66a6229395e2e8a1fd8ab18798e50557b5ecc558992ca9a0ef209e9f62af438c04ea76c5b6fbe34d94e417dffd152c3752d559ef8969c1718e049e336ddb51e63c65a54e00abc9fede73d7e5f6e2302414d8ced6756ca45879bb22ab658f2b42c2232180ec17823ac41082144b87a4cf796ddad0808217b9369f1b0cd3c4de15ce90b730dff6d2214fc1804617a76ec37a9269563ccc59ed4419c6e0cde5bd3a9ea0e4e9e42a835704dd1d44d00eed1ecf5c7a275b70314b0100ad40ba1ff0c5b9e7731f8b99579203dd4210670b14c9fba1aeae8fcf8caf843707fb041ec34d981f9a88b78553ba6a059e30030f060b581168fda116ac0e79b4faea9986fb8739d4651ae407ba73f8da7ebd58080637fa07cda38ee24d020d3d5de0695179973e7d55c0eada48a9ba3990449eeb75a0019ab9ab8651c88711ab88a44b0a973c318f41a0fff8f7ede56500c43447277028c08001e889fc6df4be470df2d32cee80d153d228450a77094ef097e9a8e06904bcf5da9cd2f33f77cecc42521a9de4f5b4924caca54718e6808f8c680a08db51dab75780991883c5a3ecd3d4d598d4ea62fb66b24b5827f694d36deccdefbc82eafe5cdae0879bf0652960f72e0381c053273feef5c1f9409b416abbd2f8ac47bf54cb13fee9c022127fe407ff7317ed490f36b09d78c9e8d15c486058f66589c900b923fa986f5ac2322e690f0ec35d5d1d8084de2fdee1ae61cfe8fcf05fc0a62ed0166f1a0a55c18b8f73a3c2af6052a8d41a2a9fa2913ddb0005966285e9ff78ebeefabd9f30a2548e35de170d68fa445a6ba6785c82a3970a9943cc2ce704f1ba323cbf97fee914098f6e7fa07b44b81aa5d878ccc7d1a659015723ad3b5f406bc80ae427125be22c8c675eb2b14f1ab5f7778551b042ba186959ca7e60b5e6bcb6bc55225f3bdaeb93e4f5b82564227ef2b323ce99fe1eb4c55a1413e111d51a2ed7daed8e53988ea2525bb74307193ac9f50dceefeff88126b0f66bfb913d3bf6525df620f8fa4e80c00000ff49f1c60c5aa28856235229b270c337e2cd9471c26f9362c60297424313d9eb46107ab43f456d616e75656c20285468697320697320612074657374206b657920666f7220616e2061727469636c6529203c63616c766f407079746869616e2e636f6d3e887a041311080022050256c489fe021b03060b090807030206150802090a0b0416020301021e01021780000a0910e9ceecf3e65ff517fa7e00ff6fbc2a78a3dd7d1682e6af5b576e9aed556d67ef5ca66bdebcd551c91a49b9c9010084a69d54f8cf5fbf87f32e190d3859cf761db35b3c983a4f9aa8b5a5645a29529d023d0456c489fe100800c7e0dd9b3806f269745c6ae8ae0073450b825f9192e3838669a59a0cba44252dfc5dc0c9b9959ef933456bcf85dcf958ee5ebfb9d6dce9cd10c8d17797c739fc033425833e3a096cb3c35de0cbae50f7b71f563f7371ccf516d014d27c2ff070661fb7e5199946a1d2b5042708447abce70b4b0ac99cbda09f33fb243313ef911056175dbab4f937f96149e38a0349eee1ab3e62494cdc4bd91f62594c5acbd0a4f94bc869411f3da885f9392e3168fd70328be7e8f7df0abb7df08f83d73dcde3b2c44587994a5b0bda213e89f9eb434c14ee22cd91de4b0b5de4168900bef9fdc065083e58f6e2bf228195b08c77bcb1fad22484cde5664dff8dba2072e29700030507fd19d529288f45fd9cf6d2946b1f3207b5a88d9b1358725520f36ccad6c6b8d0568b4c7db39ba77c12c142a3c7e65cddece9bb1b5f9fd779eef5b70f4934b422dcfbacb683f0ab56537859cc51431287855b9bead02f46acff127b4096b7c0c3165f440dce9a9c4afd2eedfb13dda920d6cf05cce245e13456a7bb3cfb3f60b50ce7ca11689ea2874473636c969ecf87df3e377f0cc4e56158f5ee7ddaeaec4dbd1e7416d930f97203bf8cd2e348346eafdb5ecc2071f5b2d409b3787a95772d28d0c6d2ceedd3b7a79e658ca03c44b5c0f0168e69dbf5ff2b3e732167e6092a06b99c529f9cb104e0251bd4cc92a68800fed98a8fe3a656d9fbe7fcf5c9efb52300015101cddfb1b3683f74b367659209d34282da3a384e70d4c17e68b82f26e031a579f944ec6089e870c5ce54a216e38861041811080009050256c489fe021b0c000a0910e9ceecf3e65ff517f1f700fe36668ecf6f95d873d3874e1d1c1e43446a0ddd1863d04ca8391cd70ad23e0f9300ff422ba4950db06c6416e902955d113c32d66c9cec96ecebc459591fa61ccc0c68$$,'')::text from pgbench_history;
ALTER TABLE pgbench_branches
ALTER bbalance TYPE bytea USING pgp_sym_encrypt_bytea(bbalance::text::bytea, $$secret$$::text,'compress-algo=1, compress-level=5, cipher-algo=aes128'::text),
ALTER filler TYPE bytea USING pgp_sym_encrypt_bytea(filler::text::bytea, $$secret$$::text,'compress-algo=1, compress-level=5, cipher-algo=aes128'::text);
ALTER TABLE pgbench_accounts
ALTER abalance TYPE bytea USING pgp_sym_encrypt_bytea(abalance::text::bytea, $$secret$$::text,'compress-algo=1, compress-level=5, cipher-algo=aes128'::text),
ALTER filler TYPE bytea USING pgp_sym_encrypt_bytea(filler::text::bytea, $$secret$$::text,'compress-algo=1, compress-level=5, cipher-algo=aes128'::text);
-- testsym=# select oid from pg_class where relname = 'pgbench_accounts' and relkind = 'r';
ALTER TABLE pgbench_tellers
ALTER tbalance TYPE bytea USING pgp_sym_encrypt_bytea(tbalance::text::bytea, $$secret$$::text,'compress-algo=1, compress-level=5, cipher-algo=aes128'::text),
ALTER filler TYPE bytea USING pgp_sym_encrypt_bytea(filler::text::bytea, $$secret$$::text,'compress-algo=1, compress-level=5, cipher-algo=aes128'::text);
,'compress-algo=1, compress-level=5, cipher-algo=aes128'::text