pgcrypto有效化 1 create extension pgcrypto;
function test create test table
1 2 3 4 CREATE TABLE test ( "name" varchar NULL, value varchar NULL );
digest
digest(data text, type text) returns bytea
1 insert into test (name,value) VALUES (digest('a','md5'),'b');
hmac
hmac(data text, key text, type text) returns bytea
1 insert into test (name,value) VALUES (hmac('a','hello','md5'),'b');
crypt
1 2 3 insert into test (name,value) VALUES (crypt('a11',gen_salt('md5')),'b'); // $1$gYXMyHsI$lshn/UVmnZSGtOzydymk1/ select * from test where name=crypt('a11', '$1$gYXMyHsI$lshn/UVmnZSGtOzydymk1/');
pgp_sym_encrypt
1 2 insert into test (name,value) VALUES (pgp_sym_encrypt('8888','toxin','compress-algo=1, cipher-algo=aes256'),'b333'); select pgp_sym_decrypt(name::bytea, 'toxin'::text) from test where value='b333';
pgp_pub_encrypt
1 2 3 4 5 6 // generate gpg key pair gpg --gen-key // list your keys gpg -k // get armor string gpg --armor --export
usage
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 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 insert into test values (pgp_pub_encrypt('666',dearmor('-----BEGIN PGP PUBLIC KEY BLOCK----- mQGNBF5BiLwBDADX83XGTSqXul1gi3ODzjG8vb7T4II4hEwnjxxg/Rr/dmtZSKkJ Uvdwk3A4ZVKwCyRa5tr5XDVaYMvapwRtsyRrqB5DLazWqBaYItBAkIsmLsFyL2tD XaIOS2KrTMXP1pkiZcA5tFR9EBLE7LliNzjaqqPJ27imydoCdnficYpmb4wzTfnM Nd+u7OTFGsqfcXzXPiburhBS+NLz5g5Cc8ZEzsSaS4B2hi1K18MFdlINYP13+y9W FHewSx+aHvzvzC6iGko3Ztlj7df6poT37YHkcmbFLRxOEavJPc1mzOV2M3umCQcN JB6muBqx8XAioWEplBDzVM3tl9e1djliSUELn2R/SY5qBk5J9ZdNPE5nSEVl+HAX qqZR6GaDm25Jy1MeD/l79FpW+lFxU1+nnH0sIWScIjpAlaRjdjfKnG5OqUmeSdVP 9UD2g2hLIVeZYUx2bLSwFtzluzd0aJPsnQIf8oF6J45PkyugSkfHeXRc9P/fNbrj JZG023GqxAr0r+kAEQEAAbQZZm9yLXRlc3QgPHRlc3RAZ21haWwuY29tPokB1AQT AQoAPhYhBLDusavVVp+HCMDVCCui8EBE0ypBBQJeQYi8AhsDBQkDwmcABQsJCAcC BhUKCQgLAgQWAgMBAh4BAheAAAoJECui8EBE0ypBvJEL+wefXhdeTSKfOnpG/DAw FRRQvtmElIE4scE89ZLhkAz2StRmXWNI315C2sspU2AYxuCyqsoNW0xH5H7cVAzQ 8iPZx8CteSTHrfTQCwGAJzeF07xjewau2WnYSP2INmQuEevwNk0ZNShYiH6qQkyT Ql783d7HPbLTaF2Nfm96R2bVeHRhDDJE8FcwA0KeX7zXvMvtyeyy8JSlsl6ewOpi Ol6wCjay70T2VY2ZvQSVOgFMMjm6ulZA265IsT669H4yX1iA4FGFq5RQt4gpUQDY NaZLOfuBtRyo3SR4T1UnUSS2pCZG7G12j5yzKCwE6OHPrk4hbHkQv1D5ArAskKlk /NAafZhykgu1tpPn+cSI5s8gDyzpQ0QItn7628jljrNdOgOvRvZtrgYad7FYumE3 3/pMNzJympaiGwL9xbMYHBPad3uCpvsQVLzZK9HE8SuQTARRlZhCvqqLY5ZLpOni oJ2QDqEUdliIfg2wnZZLcFT+bA5PUoSX1JvEPyA3FIB+ZLkBjQReQYi8AQwAx9Cr dgXm0IwYe+t5QbVYG6qDDLmuOT9X/3v+h92rIzIKiiSLsvlAfdM8k50Ua9cnXtMo EHFQpQQmxu/IjmZ5Nwt5xV/b2x0EXSmlzpfmF/431USShgBh4I0LId3D2zLLRiid X0FeRMUNKPsqdyhFfs6pTf9WJfwxlRYE9xiEsZd5Dqqb9KdOMspQUhaqfoLz9DKv xpJfgG0ceogg/tfRisfaQAo/yDBgj5jzCswTP+PrIT9gaiRkA4LW1JzHgGCIwGAZ hWcdkFvfKTUXkrgATX1zR03HYXhFCETTKQxpI4Z09SduI8MynfrwgSb2/WHVqF99 y9kctNe3cIPrI6Uv5UH5PN/YZzgDIvuAdMD1+ooYIdYmkfU6RdY8RAmWgcPtIzU4 TpWh6GhsrkGtEQ+MRT+tiNQqoP/gAfQbD91MbhIJdK+f5TfMRaWPhXMlPenR+8Hc My5Zwttit04WiC9SF2jqlNYxHP4wvNz+icfH12IM6ldtYwJ9AfFuTu/2Eq/lABEB AAGJAbwEGAEKACYWIQSw7rGr1VafhwjA1QgrovBARNMqQQUCXkGIvAIbDAUJA8Jn AAAKCRArovBARNMqQTOqC/963hDErKNncF3/2Z7vxbOCoconABrMpzIcTcEILiM8 CqJir9cC9rpuLzyNe4/VwD5MXsGX31y5YhtlW066a6YfrOitu41CwZZopcPAD1xH 3LnTdHIFdy80ioFTo3zGH2UVH3SCaVZS+6g8+mREseEILDlCPl+SQGy1RnSCt+jQ X98vRJ+qKw1AfpeVGR3HX4b3Lj9P7wUOpwzQSXU+qoqCcvkmuOwT1Z8QE2k7X8W9 D7qwSR+Ydn2X51q3fr0VuCYjJl6Pb7Hf52QnXjiRDtqtapMkxVRMopfWaYRS1VZX PVY7vJXy6AZ1RdHBgyMMdjdglm0q/6m3oBuGAwzHrskq97FWkKpO5TzZLWL49b2X YuVIPJD5H5MRMoQJO286V4o6cpXjWSePNrYkYj7JRcxbBAP+2jlGSXfaLhSSCwvd 0F2ONWF/mw+95lptuEL8706RqlkEcmPlCU83halekS4sZqQh2eaBB85xeQj0MIS+ 7T6c3QiG9a4gaRGJvd4Dq3U= =pZyZ -----END PGP PUBLIC KEY BLOCK-----'),'cipher-algo=aes256, compress-algo=2'), '666'); select pgp_pub_decrypt(name::bytea, dearmor('-----BEGIN PGP PRIVATE KEY BLOCK----- lQWGBF5BiLwBDADX83XGTSqXul1gi3ODzjG8vb7T4II4hEwnjxxg/Rr/dmtZSKkJ Uvdwk3A4ZVKwCyRa5tr5XDVaYMvapwRtsyRrqB5DLazWqBaYItBAkIsmLsFyL2tD XaIOS2KrTMXP1pkiZcA5tFR9EBLE7LliNzjaqqPJ27imydoCdnficYpmb4wzTfnM Nd+u7OTFGsqfcXzXPiburhBS+NLz5g5Cc8ZEzsSaS4B2hi1K18MFdlINYP13+y9W FHewSx+aHvzvzC6iGko3Ztlj7df6poT37YHkcmbFLRxOEavJPc1mzOV2M3umCQcN JB6muBqx8XAioWEplBDzVM3tl9e1djliSUELn2R/SY5qBk5J9ZdNPE5nSEVl+HAX qqZR6GaDm25Jy1MeD/l79FpW+lFxU1+nnH0sIWScIjpAlaRjdjfKnG5OqUmeSdVP 9UD2g2hLIVeZYUx2bLSwFtzluzd0aJPsnQIf8oF6J45PkyugSkfHeXRc9P/fNbrj JZG023GqxAr0r+kAEQEAAf4HAwLMTw7af9GuN/8Lrw62EeqOInWVJVlqZhZV+iiV FSSzk7F/YlJnngPCOZxw+qHhwXCMbu/bALlWWbbNr8wCQzxIEv8/ZlvLYlhXeIeW 9c01TyIoRZ8bxwMCSubdccwXSF9ztSIW45o/xpyO2eWV9OtWh+ve7Wd9bn6D0Q3d kz+Y+jUesdnKB51GveF7tm8WWw9/mZXcZRqZ6VX2v0UuXlH8+YyDbB29cPGO91ED QgPPlO8i6VaYYygNqETqQSkGMEYXezy7OKJf2PBpJGu2C7I+2Z1TvnnMEktBfEMt 0uum/6OUfjM3jaso8q0YHn3TkIdd1sVxq794ttVj1FoyW8LKb1WOn89Vcj83vEMv +XTey+AvCAyzF8+RiqfDF93NvaBRm3zL460PgRz7xVa5Ku06NZsabLNJgicKvZF5 1lB0F2LLsOXCkV/At3GLAETpprJiifupWM2JjQTOqO0XorytYZoroGNIqJ9UqomH N6ARBM5JBWIO0fetK6DuHQ2Y+Ki2Ws0tejWOiZQV6lSMfT0tTpasRO48wOoCOyDd r6uwspUvy2WEgr27bR24qkVHdmARexl17Vd1v6Rn0qXZRLH+QSbgPaeE8mBPCPoM peD5wNyeebjjHm1h6Bhhi3LneZq8C4HRBOG9eaFDiXJZ8XdhKKKG+m98wX63b4j+ SssE8MXC850aYjcl5VzqpWTgMEpQ9uR9WlU/xteB/91JGLSzd5fd/OVGQi3na++0 IH7H9MU6KzWnYImHLOzfAnqykPSp5SUwvtYCSalVh2+v24cc7t2V4uxFSs8Djq8v kpPFA0hWIVthHtFau6JNUeU0dyYp+ZATULQUWzDyQ7I3ym724DDmUjDv8jH+n0xg ZZ//HV98h81vrtKP7oqVixhfZ0Uq2+yMiMGgrYv29zO7C6rYzhEArJV7Q9mdTGU8 1I8s2dStG3GhYJSiXI/XraVy8dS9hdsAjxjpN0lI34jasbBurDMaLgrjyDLBXuVS crZINXVFpyKCe50SV+q2r2v4ilY7yXZ7pZ7ueuII+0IyGwRn/Ky1SF8Wp0bToH+D EeVGnpH80jEKy/WClqeJxa6bn14rDehd4+EEQMwJEjJ6YWiqkZldHo0OqXCKNZJg qMahqS5BSgi3b31zJdqS+78sqTzjzhT62s5g2Xdf+kyJCkqRPB60W9So5scQwVAb 3mcQmB2Mh+gvtdMPp81i1neZjWx4rPa7039i17qRf8pAaRP3L5iMKZ60jITdPll3 ThiKVD4sJBtkgcJLF0rMmFwh/sYhHOIwm5X1BLO4mAldFiF5n+jfkqi9BSAflehc b6Pq+WNfbyrgcvFpw76OVGSNSXbZ5UJiULQZZm9yLXRlc3QgPHRlc3RAZ21haWwu Y29tPokB1AQTAQoAPhYhBLDusavVVp+HCMDVCCui8EBE0ypBBQJeQYi8AhsDBQkD wmcABQsJCAcCBhUKCQgLAgQWAgMBAh4BAheAAAoJECui8EBE0ypBvJEL+wefXhde TSKfOnpG/DAwFRRQvtmElIE4scE89ZLhkAz2StRmXWNI315C2sspU2AYxuCyqsoN W0xH5H7cVAzQ8iPZx8CteSTHrfTQCwGAJzeF07xjewau2WnYSP2INmQuEevwNk0Z NShYiH6qQkyTQl783d7HPbLTaF2Nfm96R2bVeHRhDDJE8FcwA0KeX7zXvMvtyeyy 8JSlsl6ewOpiOl6wCjay70T2VY2ZvQSVOgFMMjm6ulZA265IsT669H4yX1iA4FGF q5RQt4gpUQDYNaZLOfuBtRyo3SR4T1UnUSS2pCZG7G12j5yzKCwE6OHPrk4hbHkQ v1D5ArAskKlk/NAafZhykgu1tpPn+cSI5s8gDyzpQ0QItn7628jljrNdOgOvRvZt rgYad7FYumE33/pMNzJympaiGwL9xbMYHBPad3uCpvsQVLzZK9HE8SuQTARRlZhC vqqLY5ZLpOnioJ2QDqEUdliIfg2wnZZLcFT+bA5PUoSX1JvEPyA3FIB+ZJ0FhgRe QYi8AQwAx9CrdgXm0IwYe+t5QbVYG6qDDLmuOT9X/3v+h92rIzIKiiSLsvlAfdM8 k50Ua9cnXtMoEHFQpQQmxu/IjmZ5Nwt5xV/b2x0EXSmlzpfmF/431USShgBh4I0L Id3D2zLLRiidX0FeRMUNKPsqdyhFfs6pTf9WJfwxlRYE9xiEsZd5Dqqb9KdOMspQ UhaqfoLz9DKvxpJfgG0ceogg/tfRisfaQAo/yDBgj5jzCswTP+PrIT9gaiRkA4LW 1JzHgGCIwGAZhWcdkFvfKTUXkrgATX1zR03HYXhFCETTKQxpI4Z09SduI8Mynfrw gSb2/WHVqF99y9kctNe3cIPrI6Uv5UH5PN/YZzgDIvuAdMD1+ooYIdYmkfU6RdY8 RAmWgcPtIzU4TpWh6GhsrkGtEQ+MRT+tiNQqoP/gAfQbD91MbhIJdK+f5TfMRaWP hXMlPenR+8HcMy5Zwttit04WiC9SF2jqlNYxHP4wvNz+icfH12IM6ldtYwJ9AfFu Tu/2Eq/lABEBAAH+BwMCEifba0QDLiv/bV39zG979wk5i19OeEtoJTSYYhYUV0uk wpIG/W9ZQ/UPxsbK14t6yUPWWhTaB98GEUAyOvqPuUpOoXL2aZfnC6yWD5fCEkbV omdFr8ATKxSAXp7Khp20v/ZNptWXM+DJoOGikrLMdYxO8wFQfweIDWnbWj5vUyCo OPMOQV+KEQ61+nqM55LYX0JkKmmpL3TGb0MvwkqZB+SA0ejqZKMuCyD26uURoV7+ pVeXmSFCZyuxXl2Fl+X3M7QNISYouE4L1mU9OakJQS3iKK92CNP3b0Xp4nQzp+i5 HxC2gfELveR34hn4ZKeyB8OnblcNM76UJboaJFQOVn51wq/nhg8Mghu+bSnLqqzk a2RxChQyzEZHZR+T7ejYzsIhhOLmP5yi/CL50Qri9rJWkSN242uaZPBq5put4Z2y 12XUuNGlT4chpLv92oBtJzGVSXW2/v132XMpHCeWB1x5B8N30moeRdlmgyD9qaoA Axs+AWrrJNnf3DZmMbuRTKVtUdKcaph/sO96QYCe6InR0cc8Qjg10DVCLGoB8uh4 9l7OqkcRNpgjTAD3IWrDrknL6c+X9N5SdNf4qBhBPn0s4i0AflYGUy4uDj69g5v8 jD45S4G6SI94KFg+RlEvw5+fNyzlvRgSrWVzPIcvbhjQdZnQGnaYXeWDuTC2K2qm J94539q60fTHAXT6MFegCXXSM7MTrPIH54OJaTsIjQpElw7erZSuuMKB6US8EXIN X4HChFpzOOyQUtOrpGAu64NEvg0xMOkntBO/ZXiJIypM7VkFc0Hl1YfET2krXNJz Y6twCvvKfqCsMiHRpJkyyVFqECwQD0EwNRSYNq86vg9Ykuc6aPFSrHsBwcpR1d9I XpFGT5pfQMbTJlEVD4vBRrWda+8x1Ct4gUSVeWJDAdRv7kJELXW0YXMH9FEoK/vF M8UIgHDoIe3Ra5SxQ4XXb4IBzuaqgACjQEZShozzMpcrZe1iSCQbnvx5+7fhVnxX dYXcSiTfR/CY0O06J3h0qZqg6QEFskdPa6EYwBCld1JDO+04x6hKgTcoYxSV1ZdI UtDT9A0oLVQaSSIjtcEd+snCkAamGzdAn3pVvBOaueyOsQ982MxNq72OGppq2MKd vU8y4y4mZEtHK2EMWTRbVDVPz9fTGOZQzLi3WFwKu+lufmN2/TysiA11bOaHawEL LCPnfy4Vkj0HspINepX5XefuPsvsfBaHWzl5vXieBPwcWJyvRh1amZpSHjufTd0F xg5nKhovfqvys5DHKPb5ubdjwqU6+FoEn64d+85OMHuuLQDRjxY6OyNreR3HkhFi GVZ370wZ6tigYVHRTxbaSZF9ZBuJAbwEGAEKACYWIQSw7rGr1VafhwjA1QgrovBA RNMqQQUCXkGIvAIbDAUJA8JnAAAKCRArovBARNMqQTOqC/963hDErKNncF3/2Z7v xbOCoconABrMpzIcTcEILiM8CqJir9cC9rpuLzyNe4/VwD5MXsGX31y5YhtlW066 a6YfrOitu41CwZZopcPAD1xH3LnTdHIFdy80ioFTo3zGH2UVH3SCaVZS+6g8+mRE seEILDlCPl+SQGy1RnSCt+jQX98vRJ+qKw1AfpeVGR3HX4b3Lj9P7wUOpwzQSXU+ qoqCcvkmuOwT1Z8QE2k7X8W9D7qwSR+Ydn2X51q3fr0VuCYjJl6Pb7Hf52QnXjiR DtqtapMkxVRMopfWaYRS1VZXPVY7vJXy6AZ1RdHBgyMMdjdglm0q/6m3oBuGAwzH rskq97FWkKpO5TzZLWL49b2XYuVIPJD5H5MRMoQJO286V4o6cpXjWSePNrYkYj7J RcxbBAP+2jlGSXfaLhSSCwvd0F2ONWF/mw+95lptuEL8706RqlkEcmPlCU83hale kS4sZqQh2eaBB85xeQj0MIS+7T6c3QiG9a4gaRGJvd4Dq3U= =bcBL -----END PGP PRIVATE KEY BLOCK----- '), '12345678') from test where value='666'
