使用PostgreSQL';解密时出现问题;s的gnupg扩展


Trouble decrypting using PostgreSQL's gnupg extension

当我尝试使用PostgreSQL的gnupg扩展和gpg_pub_decrypt函数进行解密时,遇到了一个错误。

这是我正在执行的查询,它应该对字符串Hello world!:进行往返加密/解密

select pgp_pub_decrypt(
           pgp_pub_encrypt('Hello world!', dearmor(:public_key)),
           dearmor(:private_key),
           ''
           );

相反,我得到了错误:

'SQLSTATE[39000]: External routine invocation exception: 7 ERROR:  Corrupt data'

我通过这个php脚本执行查询并提供公钥和私钥值。

<?php
function roundTrip() {
  $pubkey = file_get_contents("public_key");
  $privkey = file_get_contents("secret_key");
  $conn = new PDO("pgsql:dbname=*********", "*********", "*********");
  $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  $sql = "select pgp_pub_decrypt( pgp_pub_encrypt('Hello world!', dearmor(:public_key)), dearmor(:private_key), '')";
  $stmt = $conn->prepare($sql);
  $stmt->bindParam(':public_key', $pubkey);
  $stmt->bindParam(':private_key', $privkey);
  $stmt->execute();             // <------------------ Error encountered here
  $row = $stmt->fetch();
  file_put_contents('test.out', $row[0]);
}
roundTrip();
?>

我用gnupg生成了密钥对,它没有密码保护,所以pgp_pub_decrypt的最后一个参数是空字符串。

我已经将私钥和公钥都导出到ASCII文件中,供脚本读取。下面为任何想要验证其有效性的人提供了它们。

我可以在命令行上使用gpg,使用未导出的密钥进行成功的往返,所以我知道它们有效。如果gpg提供了一种方法,我会使用导出的,但我认为它没有。

有人能告诉我出了什么问题吗?

[mike@M5 ~]$ cat public_key
-----BEGIN PGP PUBLIC KEY BLOCK-----
Version: GnuPG v1.4.11 (GNU/Linux)
mQENBE86dZEBCADK0LCqLYUPWRCfiBt3qx7OKJZp1QTkwG+sFM5COw2XV96poCwS
7LtwdjiG6+uQ2Ikh6XmRkAoPqR3NHuoI1BXTAaHVJ1xuHXtEsaVdx1hk8yjtDO9Z
eAlWKc84Mq95ZMY7fvphY4CIOnWyMaUxSBNMSFJ6jyTUR712C+kz2oER1ZBeA0y8
bJ9mSii+yOO3ST+uws5M0G/wR+6t033iScbSNftvrMQ+O7wo3mEkAILMtr3ghF65
pRHzQkXPaUNDeBzgs43gBiwlt+vB7QRYM4hNxOvLe7YHHPINdYoQoMpvIIGDltaA
pe4rVkzeqczQYIQJ1UByePovTKwpZmJ4FZiHABEBAAG0Jk1pa2UgRXdpbmcgPG1l
d2luZ0BpbnRvdWNoaW5zaWdodC5jb20+iQE4BBMBAgAiBQJPOnWRAhsDBgsJCAcD
AgYVCAIJCgsEFgIDAQIeAQIXgAAKCRCamOUYvDsLQe1TB/98A5LAyBs1gVZXV9rs
friHNTS5QyJrgfjUjox3Pj45/JG7rfJzHH3As2pB5sMNEe/lP2VGTb+YhPab3x/w
8R+vIzGMOhc/dXypuFlsI6erw3/mJeWLiDQCbQ9qKl5bmFhGRpDko5KF7E1s1I+Z
z7QnmAnXgYz0+PQl3h6rf19TRcSzeWjujRVTgkTeUlELCaJ5p5meGv+Cs7GIplLE
Hp03qNy9MmuW3ZaRt2LSs6yDPJvMJot2g0ogAL6LON4svvpjsAMuFER8YuEMaK9I
DdJoi6LmAZeDyHm/vCJJBatL1iW4+F0S+amId4cu2KkcYcPPmQ0x8Kf+SEbQdhEM
Kc2puQENBE86dZEBCADH2quuZ41r884zgLCZ9UhSMUJ1L6ObQkJw0kZh4wnXRanV
oE+fpUoxChSNjCRw+2CoOwDRSXqY5dnMOVA/x02IFrml70/nhNWNjNbalarG/tn6
heWNHEKqL1nSSOns51dKXJ0k3cMjwnkrjrXmryqjpSwfn6VnljI5T4//uSR59CBI
J0BVkkx3p47Y+KfdeaF9KBDtOzNcFhJF7yB1l23mpgcl/dtHwXjExtZAvYmvpyBQ
+6P1DSHgWnJLo8Xp0TKkeATZ2eornlIwqsHfzfPDkvjWU0wqRb/j5XezsFvkwLrF
1jEyM7PzokoJ4GhktZR/HhdUH/cIjxYqdbZAyQxrABEBAAGJAR8EGAECAAkFAk86
dZECGwwACgkQmpjlGLw7C0ERnwf+NMNvZe3zv9Jd/ttXMcvYNg1ZnS070qDTUAP2
ex9IrYTcZ1MxtMka8yvioLgGTy4YUSyS/1BwNvBRfyQUl1oXJWr8ZeQyjYDHt3Aq
0VLcnxknCGtZbl1/3Kkbg7zrbQIwX1iilWhRbExeUfBquXbEYZx1l2F+GDa6Ni8k
tCad7SvvKg0g+M0chzmPnvviQm///3BjPB8NeIbksG3NvtesUmDiDawX2ymuh0cI
PfB+BlTYw4K4K+T0d971gNeMF1cetdHAN/vlsDmH0px05JBhRgDabELtMLB+SdMm
juxUmZmsRhZ4UK6lYMPtqYejWT2h5IeiwHJyMZc1XqcdipOPzw==
=aUlA
-----END PGP PUBLIC KEY BLOCK-----
[mike@M5 ~]$ cat secret_key
-----BEGIN PGP PRIVATE KEY BLOCK-----
Version: GnuPG v1.4.11 (GNU/Linux)
lQO+BE86dZEBCADK0LCqLYUPWRCfiBt3qx7OKJZp1QTkwG+sFM5COw2XV96poCwS
7LtwdjiG6+uQ2Ikh6XmRkAoPqR3NHuoI1BXTAaHVJ1xuHXtEsaVdx1hk8yjtDO9Z
eAlWKc84Mq95ZMY7fvphY4CIOnWyMaUxSBNMSFJ6jyTUR712C+kz2oER1ZBeA0y8
bJ9mSii+yOO3ST+uws5M0G/wR+6t033iScbSNftvrMQ+O7wo3mEkAILMtr3ghF65
pRHzQkXPaUNDeBzgs43gBiwlt+vB7QRYM4hNxOvLe7YHHPINdYoQoMpvIIGDltaA
pe4rVkzeqczQYIQJ1UByePovTKwpZmJ4FZiHABEBAAH+AwMCJxKTwYMjXPlgxdJV
EoTZ0dImTV/K6vh4cvyxyRwCF+zHDt0Oc79Ip0qsEkrFSvCqOfTyyltg4ZJvsh3F
/Z3vUpcX3LAGb0iUzWlLlDOjsrdtT/VBgkjIWw1f47ekmhJ9r89wiVpbG/eO1n31
TTA6MDILayNmtOuD6aNuuYCPuu0OZas/S9LDTbh+IfMlAsa9w6rLQe6kS06cHnlc
re0shxmLe5VmwNxSgJHstGcbCJHC8bak/7wXcAcz/h3w0KKdNkRL222bjJhr6DhO
Vq5ahBFyaznZTnPwm87Q+ms2021aRSy3sNw9KN68UtUZielGEQFFNepCHQgrD9SS
4M+YrSfAu16HhKB0R+XgqI/qZUba+aBgW70WbCWNMhQWljCIdxd5o37Y0Lw1i78W
xzKj7K8OjUhEpTQTnqEalEuX9PANUg8/pwRWVjG6RH8OxwH4OfvvROxVqM95snQk
hWIri57GnpYb1aX/G4RKR0c+oV6yC9gqTfKa8SzOe7LXzy7vjIz5xTTr2jc7ThUz
vTmbYZBhKn3/02WkpTSG253+7QVokbL/7u4IKU6BIA/5GTmG8DmTASc1OLO61T2R
5PEfxlSBgfq106w6YqNXU8vvKWayOAH/wqBeAtsFj7Ao/4FQj6uxmSZH+RnR11ce
ayjdKFsLtZ4nEXgHWc3h9cmMXVqgipZhHv47kkA6seAa8HaBKM8sEHrlKHAN3kn2
j9N/v/CETQMkjZUjp6Gv2WdioMSl04Fx6Rw5RYUNPEQTewy44jbhFUbMuyJOw4L7
meU9lNxUjCjaaKegRg+KDsWtXHWZ09enveZW65Zt12QJN6VxCelEO9dI8v5Xvb1S
Ks/D5ed8aCssYUPNhYGZeO8FTMMPmfmLZvhA1NDp0j8u8GWIZ4C+83+fIM7MDm3u
FLQmTWlrZSBFd2luZyA8bWV3aW5nQGludG91Y2hpbnNpZ2h0LmNvbT6JATgEEwEC
ACIFAk86dZECGwMGCwkIBwMCBhUIAgkKCwQWAgMBAh4BAheAAAoJEJqY5Ri8OwtB
7VMH/3wDksDIGzWBVldX2ux+uIc1NLlDImuB+NSOjHc+Pjn8kbut8nMcfcCzakHm
ww0R7+U/ZUZNv5iE9pvfH/DxH68jMYw6Fz91fKm4WWwjp6vDf+Yl5YuINAJtD2oq
XluYWEZGkOSjkoXsTWzUj5nPtCeYCdeBjPT49CXeHqt/X1NFxLN5aO6NFVOCRN5S
UQsJonmnmZ4a/4KzsYimUsQenTeo3L0ya5bdlpG3YtKzrIM8m8wmi3aDSiAAvos4
3iy++mOwAy4URHxi4Qxor0gN0miLouYBl4PIeb+8IkkFq0vWJbj4XRL5qYh3hy7Y
qRxhw8+ZDTHwp/5IRtB2EQwpzamdA74ETzp1kQEIAMfaq65njWvzzjOAsJn1SFIx
QnUvo5tCQnDSRmHjCddFqdWgT5+lSjEKFI2MJHD7YKg7ANFJepjl2cw5UD/HTYgW
uaXvT+eE1Y2M1tqVqsb+2fqF5Y0cQqovWdJI6eznV0pcnSTdwyPCeSuOteavKqOl
LB+fpWeWMjlPj/+5JHn0IEgnQFWSTHenjtj4p915oX0oEO07M1wWEkXvIHWXbeam
ByX920fBeMTG1kC9ia+nIFD7o/UNIeBackujxenRMqR4BNnZ6iueUjCqwd/N88OS
+NZTTCpFv+Pld7OwW+TAusXWMTIzs/OiSgngaGS1lH8eF1Qf9wiPFip1tkDJDGsA
EQEAAf4DAwInEpPBgyNc+WDCPS922nha3LxmP+uyUguADmNxzb3cPYc4RGKAuiAT
88PtE+iO/bJ+L5paBwcg27KoFfEwlKIK+hmRkQG7Myz9ET8NO3CgjGBcmEzX1ARB
xZc9VKE+aK4Ow4G6k6XM/f/jCbwWmwrZoi4HvkDV45d7pUYeixZv+h/DG90vCYM+
13JvwAuB/5n8m7eiYEq7TboWNqy8dFsF+OKlsaPgWg1rSY8k3T5XaX4mcUp6jyM8
1IQQTxigrPmCjgn08r+cacri+cXdeJxd2NI//3gngRFCKZvR6SLT29xVmQwWhfyF
9bm3g61/tQdMzrmL9fDm1CPYaJOdqhIwAozGb2XVyjkoa178dVkeGadYL2mPGqfc
OwDOgeUGDMlMX4mqmfTgD04QrVBj8am+pDhYEwwB3RHxxoRKYq+iLB2oitCnueWs
XDVMr4VAqpm/liRXepvSO15wT8XYyDDHXLwxcoQiEajzh7HaUomFycBEQDuMpDwg
bR+IK5vioV386kgcFZZbmkYk6UY4/HVUTBeZokFwsI+GPHeBdDvvfTeFew2VNS9L
b2V9LY2ODIuU4/8SL4Fp6zBAl/YWx1L/tpLe45G16Wvxeak6NcCTqxHeCUJvhNuk
WyeJCl5DIge8/wqie9u+FogeM4wTcfPwtLxIwdduO4b8nd2LFgxEOIgmvZsDlzC0
yanTNBDdzqNAEkVg5/9ZmmMB/nP1Ii2ALQxrmwNka+U9e9S5c0jxdTl6eh//Yz5R
rMXMj6+pIqitpyZlYP1LigROllhAYnc9RGp9R87fgsZBq3Own/NWymdoElGe/IZK
CnBtdEeo7FcXUgEdi0XsklNh5OGpMPbLRjosIf/6dn3LeIQvREshB0GegGMo7CP7
SEZuNGUz6H4rqYSpt0Yr8BVvU++UiQEfBBgBAgAJBQJPOnWRAhsMAAoJEJqY5Ri8
OwtBEZ8H/jTDb2Xt87/SXf7bVzHL2DYNWZ0tO9Kg01AD9nsfSK2E3GdTMbTJGvMr
4qC4Bk8uGFEskv9QcDbwUX8kFJdaFyVq/GXkMo2Ax7dwKtFS3J8ZJwhrWW5df9yp
G4O8620CMF9YopVoUWxMXlHwarl2xGGcdZdhfhg2ujYvJLQmne0r7yoNIPjNHIc5
j5774kJv//9wYzwfDXiG5LBtzb7XrFJg4g2sF9sprodHCD3wfgZU2MOCuCvk9Hfe
9YDXjBdXHrXRwDf75bA5h9KcdOSQYUYA2mxC7TCwfknTJo7sVJmZrEYWeFCupWDD
7amHo1k9oeSHosBycjGXNV6nHYqTj88=
=igkq
-----END PGP PRIVATE KEY BLOCK-----
[mike@M5 ~]$ 

根据这里的内容,当您提供密码时,会出现一个错误。尝试做:

pgp_pub_decrypt( pgp_pub_encrypt( 'Hello world', dearmor(:public_key) ), 
             dearmor(:private_key) ) `

并且不在CCD_ 3函数中提供第三字段。