首页
学习
活动
专区
工具
TVP
发布
社区首页 >问答首页 >pgcrypto pgp_pub_encrypt的问题

pgcrypto pgp_pub_encrypt的问题
EN

Stack Overflow用户
提问于 2015-03-22 07:16:13
回答 1查看 2.4K关注 0票数 0

我一直在尝试在Postgres中测试pgcrypto扩展,但遇到了一些困难。当我尝试加密数据时,我总是得到一个

代码语言:javascript
复制
ERROR:  No encryption key found

********** Error **********

ERROR: No encryption key found
SQL state: 39000

我使用Kleopatra创建了一个pub/private密钥,但它似乎不起作用。

测试表:

代码语言:javascript
复制
CREATE TABLE notes
(
  id serial NOT NULL,
  note bytea,
  description text,
  CONSTRAINT pk_notes PRIMARY KEY (id )
)
WITH (
  OIDS=FALSE
);

任何建议都是很棒的。

下面是我的插入代码:

代码语言:javascript
复制
INSERT INTO "notes"("note")
VALUES(pgp_pub_encrypt('This test data', dearmor(
'-----BEGIN PGP PUBLIC KEY BLOCK-----
Version: GnuPG v2

mQENBFUNhxMBCADXMrvdx/UmVdH0ABZdmUIjCe3/lh7s7G6AFRjryFfuZbcj62//
LBxRq2QsONxuzNq8HIwssuLQxOtU9yqv5U49TGl4wuu+2jGorKnbDIKCIrM7tFpi
JSOeClsDJer9I+UrXbYQLSoN/nyYUxl5ColxnvmWnQagZH71L5eLKke/W4DJXcgW
N3Yt6vfi4wOFQ3IdVQeMP8b6ehZjbTU2+eDShvsLhkUYD5//OGjpshsBocnIvZBe
...
...
...
...
B/0SD4A2b6buRnwyDtJ+eTBjQTVT3ISSgTtwtmtLQvy4/3r95G/3UdB/+ZtB1ygB
H/1h9gh2NbonRS0uoIvhsy81/G+qXngnvuIL2K3SeDV5rF9AMEX1NWeoX73GhdD2
X6Er82DfC8cowqWge4yM55FRMo3k2Upjo004ofDmpj/wbJDspU/iDIUHWYv+upEJ
0yeVukQLaRe3/IMqd0lSHW0piDLP6FvG98suZOPZS6ivYCE1Y7cFUPxxPAZRvxVO
dXbPRUw8vBHBDHoJ8h+zcBz4kinj4I28gXNIzaMLZU6+Ju+GicAMwh3iS2TBSM8y
fZn+OwxJfeAUq9sw94A3hmPU
=2/+P
-----END PGP PUBLIC KEY BLOCK-----')));
EN

回答 1

Stack Overflow用户

发布于 2019-06-05 03:35:35

我也遇到了一些麻烦,我不能用一个新的密钥重复,所以我想我会分享工作的东西:

自上而下

代码语言:javascript
复制
gpg --full-generate-key
# Real-Name: testname
# Email:
# Comment:
# Okay
# Passphrase: foobar
代码语言:javascript
复制
gpg --armor --export 'testname'
代码语言:javascript
复制
CREATE TEMPORARY TABLE foo_data (data BYTEA);
INSERT INTO foo_data
SELECT pgp_pub_encrypt('x', dearmor(
'-----BEGIN PGP PUBLIC KEY BLOCK-----

mQENBFz2w6IBCAC08MpF5wQlcNiuY3YYZx/c82cuIS09yIrw12bHGMOuCCL68l8+
czjCQi9fM8k1uGDKn8ttDuz+rtapweBqjNTrQGnoKrhShxM3pRwfUb/ieuDtjc84
vJU+P5FEV9Yh66ymzolBbY+r+8h9Zjc2fpawFlash7lfXXyvkGs0sorjsHETSyi8
0UyOsgbOxcHSHYeAqbazCL5QU5HTxrt1x5FI/0CWxsoFinMaiMEJrbYYMqfBC4TE
Hz0vwkd7drCk8tOMnJFxZNcGz5zRaJjloMCdt4NObQ048wfGJ7SKP0mNAb4oPnrn
dUsZAKfzQkUmHICzb7XEk/QJTrxsqSjN3U6PABEBAAG0CHRlc3RuYW1liQFOBBMB
CAA4FiEEAYV6IBG8fWXf/RvVwSQuiBgl2dwFAlz2w6ICGwMFCwkIBwIGFQoJCAsC
BBYCAwECHgECF4AACgkQwSQuiBgl2dwKcwgAsEA3rcKUncKR/P/vl3PD8XrV0der
f1M0XPuc8zaQqrPpIvdOpVfQ8uFkoDacr5uCWdH+qjaK7uxO+Tp/29TKHLxD4Ycx
sPFstKxjyE+dG4j57ddQJRei0O8mM7VvPUXx0LjqAwYb7PrGIBtXOB7m3DbvVVww
QeSLO49Z+wTLcXJiRw38wT/jSWAlNV3aPbfW0gTRoDLDUfOqjhCcULDfW08m/tvX
++Zgp4csXc3c4y3jpIpG82nkqFj6tYujab7lLUTcb+DOHJQecChG/JFTYlH3A0bY
InRB/JFFpvVd6dsz6ePB/rjTxz0lj2Fm0vnz1o9XCzOGMFwgwQhVuw9OArkBDQRc
9sOiAQgAq87Eh5oRnPXqzSKDhyH1yaBvGtnjM+Dk1PIn9SV4EC6oVoyPF4sMxx3o
c9kFmBucvhXWdLB6AJBIXWyx0jX3Ieopxis43FCzUVvxyyqo9ESD5ZCbRRz8Ftxe
W3GIlTD4+q/mvBVp8K0SHlDDbn/uevjwJcn1laKKO08e5FBtozjRwnTLys8Ybplu
32a7GWZq8KsknxJFo45MHmuHAysxlvhgsJJICAnRgVJ/jEoxb/Odbq3Vu6cOIfRS
j2yJVJcZM57txZiMmihEboTICeJNWooTUCuiWYtg2dNjniue38PHZImPe1MsDWuQ
52awPqjw2UBQKTgxjH8hDPYm7jjXIQARAQABiQE2BBgBCAAgFiEEAYV6IBG8fWXf
/RvVwSQuiBgl2dwFAlz2w6ICGwwACgkQwSQuiBgl2dwCHwgAhrwu7EFb52pMiHFn
fYmAB70varpA8ONX9o/OyrqcR1WR8Ew9wGZ6+uXK2+KTdz1Em0OsuK24DZjHJgPc
IADtymz/IQiclH6WWvpkZlkecITBV/OSXAK9bERcv0CjtHDW+B0piGf+ipdMy7kQ
+HbExKxKki9eiiO5iaH7QA/FRqoq7K3Ab4fAO36RuB2vKdNBPkDlwgIUBqjtQw2a
96wwwb50ra0eWH0ElGi3OhL5A3S06Wc0RmVAF+9XvT4pPH9t6fcQiGHQxKr6iFwF
PaF99Jtk5oR9JUg0DDK+nyY5NZ1OPMNakY4qThMV0fGpp3lLyBIW7RlQCXxj6j63
DgiBWQ==
=3n/6
-----END PGP PUBLIC KEY BLOCK-----'
)) AS data;
代码语言:javascript
复制
gpg --armor --export-secret-key 'testname'
代码语言:javascript
复制
SELECT pgp_pub_decrypt(data, dearmor(
'-----BEGIN PGP PRIVATE KEY BLOCK-----

lQPGBFz2w6IBCAC08MpF5wQlcNiuY3YYZx/c82cuIS09yIrw12bHGMOuCCL68l8+
czjCQi9fM8k1uGDKn8ttDuz+rtapweBqjNTrQGnoKrhShxM3pRwfUb/ieuDtjc84
vJU+P5FEV9Yh66ymzolBbY+r+8h9Zjc2fpawFlash7lfXXyvkGs0sorjsHETSyi8
0UyOsgbOxcHSHYeAqbazCL5QU5HTxrt1x5FI/0CWxsoFinMaiMEJrbYYMqfBC4TE
Hz0vwkd7drCk8tOMnJFxZNcGz5zRaJjloMCdt4NObQ048wfGJ7SKP0mNAb4oPnrn
dUsZAKfzQkUmHICzb7XEk/QJTrxsqSjN3U6PABEBAAH+BwMCYtlZ+LeRuR/mx49N
axGJOKVDZbtgPBMHzekzfZ9MpVPyG/xYe8AWAL0fdoygkbCbOOlz1uvbZULlmNrK
nmkNajeyk/+gD5jGYeu/ijpTv9aH+0TOQ8ESxICgZZ4ojq6B4F60x8/dsdcJfI9B
s+W6dobmSG++Td+aXLi/mzxpilwa8GfZsUdCD70DbPKqGutYrXOrst5lXlaHMh8u
wcsth8e6dxGhJYh4AnKBPAFc3B5DuAkioGkXKKD8wWtvapwphLu3WREtRQLVByaE
ospSUCVl0hXgqYr1Es9PWBfuGP+90TU37BVv5uaCygRm+9loHIM1wkiz5Hn5oUcr
PH+QFV3xrYHjlouGtjzqE503SGRB82Mvl03X3fdglPlwJ7AI/8yDZheq4VDDfGcS
HXj0mqWAekkb4u5+mE6vg+I4cYIi3QU2AYjSRQRODvgeQmFk2vGD2+LM1SpHAhon
YmibNqlYv4GrQMpkPmUOEWRSrUnobXzDHtquIxFQBly8NkLIX3xfCRviSDGnmcs/
EUP5vUsbuOyyffAsrBYWMI+joeNw0w2P01UNcfoF8Gf0xmDy9K2RYK6vpW/NJ5lV
3d6cJSqa9+fhBLVk/ZUZ+FPk+atswT6YIdv6Cssx8e4dSnKsPApwHbtd6jqFP0wI
onkyzD8tMHkt0484Z/waip4EO5Y/wq4A1xFVQUUVIADWLtm4tWlPN3aGFsP9sAx2
vO3lO4MOw9/mZumLorwfQFq6W9e0WUDwosaMPIpIT6GnwipJw/NlS1Ye7h+wjHnv
exBLmMquQYkEUWexhWw4/igvYzYdCZN1KSbjKM2ROFNGT5LaNuf06qI+kYkc0pdc
q3MnuZmVs1gO5UpQTFuy1lpPnDC96UUMmrqkF15zGTlMclicwIsjwNQ4PucUkWXn
RdAmfQcu0AbdtAh0ZXN0bmFtZYkBTgQTAQgAOBYhBAGFeiARvH1l3/0b1cEkLogY
JdncBQJc9sOiAhsDBQsJCAcCBhUKCQgLAgQWAgMBAh4BAheAAAoJEMEkLogYJdnc
CnMIALBAN63ClJ3Ckfz/75dzw/F61dHXq39TNFz7nPM2kKqz6SL3TqVX0PLhZKA2
nK+bglnR/qo2iu7sTvk6f9vUyhy8Q+GHMbDxbLSsY8hPnRuI+e3XUCUXotDvJjO1
bz1F8dC46gMGG+z6xiAbVzge5tw271VcMEHkizuPWfsEy3FyYkcN/ME/40lgJTVd
2j231tIE0aAyw1Hzqo4QnFCw31tPJv7b1/vmYKeHLF3N3OMt46SKRvNp5KhY+rWL
o2m+5S1E3G/gzhyUHnAoRvyRU2JR9wNG2CJ0QfyRRab1XenbM+njwf6408c9JY9h
ZtL589aPVwszhjBcIMEIVbsPTgKdA8UEXPbDogEIAKvOxIeaEZz16s0ig4ch9cmg
bxrZ4zPg5NTyJ/UleBAuqFaMjxeLDMcd6HPZBZgbnL4V1nSwegCQSF1ssdI19yHq
KcYrONxQs1Fb8csqqPREg+WQm0Uc/BbcXltxiJUw+Pqv5rwVafCtEh5Qw25/7nr4
8CXJ9ZWiijtPHuRQbaM40cJ0y8rPGG6Zbt9muxlmavCrJJ8SRaOOTB5rhwMrMZb4
YLCSSAgJ0YFSf4xKMW/znW6t1bunDiH0Uo9siVSXGTOe7cWYjJooRG6EyAniTVqK
E1ArolmLYNnTY54rnt/Dx2SJj3tTLA1rkOdmsD6o8NlAUCk4MYx/IQz2Ju441yEA
EQEAAf4HAwJ4hakCLp3MEOZrTxPBslcHivS2s2rdo2Nz39U1mhPydqsVCrByaPrD
b853JSh5p8jsNRkHIlEY69TMb47g9WaFXYq3adm0DT0WeY29AX9Ie6t3ib0t/iw7
FP23VvUck195lGZTeXQbU/d6hrfC1ic1GAaxAIuMltlYezmTrI11mcToE27lPaEy
UbUAAuMLwqq1Sr+SB5STXFsSVMZSvotq+9rgFZnBVIKIa0xpYB9DlAHs0BQJn2A4
ElG4zI6r4hbOEjYk5mEx08AKhm9MgfjXXYvzMcTLiXSSod1pEbwBAzeKgDoCc9HA
fMIgP8PlXZCC/ATTjykkBtL2/cWWx7hjA0Vy53sjt2A8Ba4EadrQZFu97Uvq5693
5AcDLdFWXMdV/tBVesZVNuOvbql2AVUeR9pr5mlnRI3izXebbibU8DcPqo0aP/9o
GnbSDSibUZTcFno0qQF2hANjPRBEm9xqRBpoNWTDLDuPOiQsUA7QIR00WrPXMNvp
86MFFxtSbiniHglJA8zzfHe0/rntSQEMH2rUuAnLpUUEeDc3h+1h4MDIDml8yNEC
G5SCzfIJG0H/F8tbqurS/mY0oa9qhWpf3rJ+/3nDctOhE57fX6Ae+7ZHRZmB+bgG
gvlYRy6AAcqdOBGXVZns++/uPg1oqNNCu26y0XhmiEcIm6et9it2MH2c2Qcanvw9
IARB3NKxRtTmis9OVusIDONmtbqwFK+5AvPnLyE8psI0kqYA0EJxbca/TJxghoDh
kx0j+Y9MIid1tuiDxMRIc7ZmzPipCRz2w9Br4r/6CMdb4QPnsNpQjg6Uu3J1+hN+
uwGEkPRe+uIzMUuI6k04/TKUEi6Se8gXbpFPKG5laK7Hsd6uXnpKtLeC1AjLcvp1
MwimBNMC9Kik9/wsRTcZEBW7q1wckpqgCHWlfYkBNgQYAQgAIBYhBAGFeiARvH1l
3/0b1cEkLogYJdncBQJc9sOiAhsMAAoJEMEkLogYJdncAh8IAIa8LuxBW+dqTIhx
Z32JgAe9L2q6QPDjV/aPzsq6nEdVkfBMPcBmevrlytvik3c9RJtDrLituA2YxyYD
3CAA7cps/yEInJR+llr6ZGZZHnCEwVfzklwCvWxEXL9Ao7Rw1vgdKYhn/oqXTMu5
EPh2xMSsSpIvXoojuYmh+0APxUaqKuytwG+HwDt+kbgdrynTQT5A5cICFAao7UMN
mvesMMG+dK2tHlh9BJRotzoS+QN0tOlnNEZlQBfvV70+KTx/ben3EIhh0MSq+ohc
BT2hffSbZOaEfSVINAwyvp8mOTWdTjzDWpGOKk4TFdHxqad5S8gSFu0ZUAl8Y+o+
tw4IgVk=
=pUwd
-----END PGP PRIVATE KEY BLOCK-----'
), 'foobar') AS data FROM foo_data;

额外好处:使用环境和postgres变量

代码语言:javascript
复制
\set pgp_public_key `gpg --armor --export 'Test User'`
SELECT :'pgp_public_key';

\set pgp_private_key `echo "$PGP_PRIVATE_KEY"`
SELECT :'pgp_private_key';
票数 1
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/29189154

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档