oushuDB之pgcrypto加密解密

[toc]

參考

PGCRYPTO

固若金湯 - PostgreSQL pgcrypto加密插件

Postgresql數(shù)據(jù)加密函數(shù)介紹

pgcrypto安裝

## 安裝
psql -d <DBNAME> -c 'create schema crypto'

sed 's/SET search_path = public/SET search_path = crypto/g' $GPHOME/share/postgresql/contrib/pgcrypto.sql  | psql  <DBNAME>

## 簡(jiǎn)單驗(yàn)證
### 加密
select crypto.pgp_sym_encrypt('This is HAWQ', 'password');

### 解密
select crypto.pgp_sym_decrypt(
      crypto.pgp_sym_encrypt('This is HAWQ', 'password'),
      'password'
);

功能介紹

digest()

根據(jù)給定的算法獲取給定數(shù)據(jù)的hash值。<br /> 標(biāo)準(zhǔn)算法支持有md5、sha1、sha224、sha256、sha384和sha512<br />e.g.

select crypto.digest('This is HAWQ', 'md5');

hmac()

用key計(jì)算hash值，type和digest一樣，hmac和digest類似，但是只有知道key的情況下才能計(jì)算出哈希值，
這樣可以預(yù)防更改數(shù)據(jù)以及更改哈希匹配的情況，如果key大于hash block size，那么先計(jì)算哈希值，哈希值作為key使用<br />e.g.

select crypto.hmac('This is HAWQ', 'This is key','md5');

密碼哈希函數(shù)

crypt()用來計(jì)算hash值.<br />gen_salt()隨機(jī)產(chǎn)生一個(gè)值作為crypt()的算法參數(shù).<br />gen_salt()的type參數(shù)為des, xdes, md5, bf. <br />gen_salt()的iter_count指迭代次數(shù), 數(shù)字越大加密時(shí)間越長, 被破解需要的時(shí)間也越長.<br />crypt()和gen_salt()的組合主要是提高了逆向破解的難度, 增強(qiáng)了數(shù)據(jù)的安全性

crypt()支持的算法

算法 最大密碼長度 適應(yīng)? Salt bits 描述
bf 72 Yes 128 Blowfish-based, variant 2a
md5 unlimited No 48 MD5-based crypt
xdes 8 Yes 24 Extended DES
des 8 No 12 Original UNIX crypt

crypt()迭代次數(shù)

算法 默認(rèn) 最小值 最大值
xdes 725 1 16777215
bf 8 4 31
  • xdes額外限制，只能是奇數(shù)

hash算法速度

算法 Hashes/sec For [a-z] For [A-Za-z0-9]
crypt-bf/8 28 246 years 251322 years
crypt-bf/7 57 121 years 123457 years
crypt-bf/6 112 62 years 62831 years
crypt-bf/5 211 33 years 33351 years
crypt-md5 2681 2.6 years 2625 years
crypt-des 362837 7 days 19 years
sha1 590223 4 days 12 years
md5 2345086 1 day 3 years

crypt和gen_salt是以犧牲hash速度為代價(jià)來換取安全性的

e.g.

## 根據(jù)salt獲取password對(duì)應(yīng)的hash值
dw=# select crypto.crypt('password', 'salt');
     crypt
---------------
 sa3tHJ3/KuYvI
(1 row)

## password + hash獲取對(duì)應(yīng)的hash值
dw=# select crypto.crypt('password', 'sa3tHJ3/KuYvI');
     crypt
---------------
 sa3tHJ3/KuYvI
(1 row)

--------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------

# 例子
## 原始數(shù)據(jù)
-- 原始表
dw=# create table s_test(id int,name text);
dw=# insert into s_test values(1,'This is HAWQ');
dw=# select * from s_test;
 id |     name
----+--------------
  1 | This is HAWQ
(1 row)

insert into s_test (id,name) values (2, crypto.crypt('password', crypto.gen_salt('bf',10)));
INSERT 0 1

## 加密后的數(shù)據(jù)
dw=# select * from s_test;
 id |                             name
----+--------------------------------------------------------------
  1 | This is HAWQ
  2 | $2a$10$qVsnbCuy2z102e9vKa/bfugyDmEUzt5AUzoRNVApQf31iOySx7mgu
(2 rows)

## error password <> password 返回false
dw=# select crypto.crypt('error password', name)=name from s_test where id = 2;
 ?column?
----------
 f
(1 row)

## password == password 返回true
dw=# select crypto.crypt('password', name)=name from s_test where id = 2;
 ?column?
----------
 t
(1 row)
dw=#

PGP 加密函數(shù)

該功能實(shí)現(xiàn)了部分OpenPGP (RFC 4880)標(biāo)準(zhǔn)的加密。支持對(duì)稱秘鑰和公共秘鑰的加密。

一條加密的PGP消息包含2個(gè)部分，或數(shù)據(jù)包：

  • 數(shù)據(jù)包包含一個(gè)會(huì)話秘鑰—加密了的對(duì)稱秘鑰或者是公共秘鑰。
  • 數(shù)據(jù)包包含帶有會(huì)話秘鑰的加密數(shù)據(jù)。

公共秘鑰

pgp_key_id()

pgp_key_id抽取一個(gè) PGP 公鑰或私鑰的密鑰 ID?；蛘呷绻o定了一個(gè)加密過的消息，它給出一個(gè)用來加密數(shù)據(jù)的密鑰 ID。

它能夠返回 2 個(gè)特殊密鑰 ID：

  • SYMKEY

    該消息是用一個(gè)對(duì)稱密鑰加密的。

  • ANYKEY

    該消息是用公鑰加密的，但是密鑰 ID 已經(jīng)被移除。這意味著你將需要嘗試你所有的密鑰來看看哪個(gè)能解密該消息。pgcrypto本身不產(chǎn)生這樣的消息。

注意不同的密鑰可能具有相同的 ID。這很少見但是是一種正常事件?？蛻舳藨?yīng)用則應(yīng)該嘗試用每一個(gè)去解密，看看哪個(gè)合適 — 像處理ANYKEY一樣

armor(), dearmor()

這些函數(shù)把二進(jìn)制數(shù)據(jù)包裝/解包成 PGP ASCII-armored 格式，其基本上是帶有 CRC 和額外格式化的 Base64。

pgp_pub_encrypt()

用一個(gè)公共 PGP 密鑰 key加密data。給這個(gè)函數(shù)一個(gè)私鑰會(huì)產(chǎn)生一個(gè)錯(cuò)誤。<br />options參數(shù)可以包含下文所述的選項(xiàng)設(shè)置

pgp_pub_decrypt()

解密一個(gè)公共密鑰加密的消息。key必須是對(duì)應(yīng)于用來加密的公鑰的私鑰。如果私鑰是用口令保護(hù)的，你必須在psw中給出該口令。如果沒有口令，但你想要指定選項(xiàng)，你需要給出一個(gè)空口令。

不允許使用pgp_pub_decrypt解密bytea數(shù)據(jù)。這是為了避免輸出非法的字符數(shù)據(jù)。使用pgp_pub_decrypt_bytea解密原始文本數(shù)據(jù)是好的。

options參數(shù)可以包含下文所述的選項(xiàng)設(shè)置。

使用舉例

## 生成公鑰和密鑰
[xx]# gpg --gen-key
gpg (GnuPG) 2.0.22; Copyright (C) 2013 Free Software Foundation, Inc.
This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law.

請(qǐng)選擇您要使用的密鑰種類：
   (1) RSA and RSA (default)
   (2) DSA and Elgamal
   (3) DSA (僅用于簽名)
   (4) RSA (僅用于簽名)
您的選擇？ 1
RSA 密鑰長度應(yīng)在 1024 位與 4096 位之間。
您想要用多大的密鑰尺寸？(2048)
您所要求的密鑰尺寸是 2048 位
請(qǐng)?jiān)O(shè)定這把密鑰的有效期限。
         0 = 密鑰永不過期
      <n>  = 密鑰在 n 天后過期
      <n>w = 密鑰在 n 周后過期
      <n>m = 密鑰在 n 月后過期
      <n>y = 密鑰在 n 年后過期
密鑰的有效期限是？(0)
密鑰永遠(yuǎn)不會(huì)過期
以上正確嗎？(y/n)y

You need a user ID to identify your key; the software constructs the user ID
from the Real Name, Comment and Email Address in this form:
    "Heinrich Heine (Der Dichter) <heinrichh@duesseldorf.de>"

真實(shí)姓名：feifeifei
電子郵件地址：
注釋：feifeifei
您選定了這個(gè)用戶標(biāo)識(shí)：
    “feifeifei (feifeifei)”

更改姓名(N)、注釋(C)、電子郵件地址(E)或確定(O)/退出(Q)？O
您需要一個(gè)密碼來保護(hù)您的私鑰。

我們需要生成大量的隨機(jī)字節(jié)。這個(gè)時(shí)候您可以多做些瑣事(像是敲打鍵盤、移動(dòng)
鼠標(biāo)、讀寫硬盤之類的)，這會(huì)讓隨機(jī)數(shù)字發(fā)生器有更好的機(jī)會(huì)獲得足夠的熵?cái)?shù)。
我們需要生成大量的隨機(jī)字節(jié)。這個(gè)時(shí)候您可以多做些瑣事(像是敲打鍵盤、移動(dòng)
鼠標(biāo)、讀寫硬盤之類的)，這會(huì)讓隨機(jī)數(shù)字發(fā)生器有更好的機(jī)會(huì)獲得足夠的熵?cái)?shù)。
gpg: 密鑰 512675A3 被標(biāo)記為絕對(duì)信任
公鑰和私鑰已經(jīng)生成并經(jīng)簽名。

gpg: 正在檢查信任度數(shù)據(jù)庫
gpg: 需要 3 份勉強(qiáng)信任和 1 份完全信任，PGP 信任模型
gpg: 深度：0 有效性：  2 已簽名：  0 信任度：0-，0q，0n，0m，0f，2u
pub   2048R/512675A3 2021-03-29
密鑰指紋 = 34AE 3E3D C0FE 99CA EA3D  4448 F5DD 1206 5126 75A3
uid                  feifeifei (feifeifei)
sub   2048R/1A6C562B 2021-03-29

## 剔除密碼
[xx]# gpg  --passwd feifeifei
gpg (GnuPG) 2.0.22; Copyright (C) 2013 Free Software Foundation, Inc.
This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law.

密鑰受保護(hù)。

您需要輸入密碼，才能解開這個(gè)用戶的私鑰：“feifeifei (feifeifei)”
2048 位的 RSA 密鑰，鑰匙號(hào) 512675A3，建立于 2021-03-29

輸入要給這把私鑰用的新密碼。

您不想要用密碼――這大概是個(gè)壞主意！

您真的想要這么做嗎？(y/N)y

## 查看鑰匙串
[xx]# gpg --list-secret-keys
/root/.gnupg/secring.gpg
------------------------
sec   2048R/512675A3 2021-03-29
uid                  feifeifei (feifeifei)
ssb   2048R/1A6C562B 2021-03-29


## 導(dǎo)出公鑰
[xx]# gpg -a --export feifeifei > public.key

## 導(dǎo)出私鑰
[xx]# gpg -a --export-secret-keys feifeifei > secret.key
[xx]#


## 測(cè)試
### ID=1存放公鑰；ID=2存放私鑰
create table keys(id int,name text,pkey bytea);

### 將鑰匙串導(dǎo)入TABLE( keys )
insert into keys
select 1,'公鑰',crypto.dearmor('-----BEGIN PGP PUBLIC KEY BLOCK-----
Version: GnuPG v2.0.22 (GNU/Linux)

mQENBGBiAL8BCAC9GRWDzTyIxEgEgxY0UtgiuxwA4w3dlV5O/iGZ71zzAkpAs7fY
ZfTattp/5KwoAkhR3WRzZS89gQhDkR67Orqw67t45giH65M4f294urOTguyonXtc
yFTigsHQ5aV5vTOuP/1EarAxb+LV7GUGN4BDDS7nWmpqiPwQ/y8CmgocEf8a6ilc
+6/tedUWX0RGylkMhMGmjXsEG171KYsMYDHZEJYVEAF+3HsarYO/05BcBOaW4pbg
MOiJredSrR9FIKpf4xAlyWITn1tP9m8qQd7JmHMXUUPB4Jq6tK6gEqZX8LZLl4np
SAe0MFSwZFPnND5rOghcReJQwqgMflhQ6KCvABEBAAG0FWZlaWZlaWZlaSAoZmVp
ZmVpZmVpKYkBOQQTAQIAIwUCYGIAvwIbAwcLCQgHAwIBBhUIAgkKCwQWAgMBAh4B
AheAAAoJEPXdEgZRJnWjvyYH/ik4oUs/Mc0M188ELxvbsQkhFUN0NiZHWtNrGnKR
NjsP/vnZERYV7ZncdMXjTBzEdPv6hrB+nl/v3C3wQm9kLi3PoI2gpE6Xr9F2/KTJ
qyXPuKUKso3CyHYRt26dFziUSK3qzeLjHtVGmavavhhuuV1bMUhbvyUxa9UbhyzC
oGxoMKViPhV91zp4bUJSvA8+arjNJuSMAKcqN8o15kQS5HTACunpAFRlhJHqRM0s
wE3AXiov2ffx7oGklD/P12K0kbd2RbBa2YjYkV3E7+IB1O1+JxaTNxjNv1dQRh8G
pnYB9GYUpqpKFoHiEefQ0npmXjfWrOFZuMAxCi1cbzgurB+5AQ0EYGIAvwEIALaY
y+Bk+lfB5+UFnWDGJPY9SUP+3WS5l21Gsyvd2QLF9AxpTQKkCxMHB3CnzBGQLX73
d+ycemVx5tm1CoY1NfxeOwBi8cs1QPJD2IyvdpcZtdDXMZ4qxXKm5IpnDWsoJdjg
9XLt9tu06NR3DyinpV0PxKSw3w/rFGj/96vJ57VWsb11yub4sGAy7QAyIpCTsbIg
muhcaejTecUTDFOV7YlA2DhGhqoYQUtk2Jyi68qSWIW+NVCg0jLuEc6t9NYmlbnY
m3XLHK+/TaFOxoSYssk9gXVWOMdhKEifRUWFwL20rDpYchFNLdr1xP311BIYZBYV
aqd7SIUCwLubjpNxcokAEQEAAYkBHwQYAQIACQUCYGIAvwIbDAAKCRD13RIGUSZ1
owxRCACqPEa1L9zetLwW/yuDuR/h+76qEmQwHmPOo8xUromtiDcZaOAHYCRkJ5wz
4CnHazYZBrGfcMiuFVwwguvHYi0mb29rh3ZvmF+NGTw6PkNbt9Rh7bjanxLj4Bu0
GeYPMI3I+0SLnTfh47v1HLjZ6ozM4lsPRFN6/zkZetltm5sZTW2pOTX4Mx+Gg328
QIDZd8q7I7DNjGRS3tqnZxa2SS4RHXKtM5iMJQmOej2X9MgFCwnFjiklLcZ7QR6z
xB7ccZwZu8lq5l9G3/A4Sg5jrAgzOyKFq4bM7GZboyYTTHhi6VT1zkfVurBb6HXm
tFX82YVb20ybYYOxDGag6qzE7QJX
=NES5
-----END PGP PUBLIC KEY BLOCK-----');

insert into keys
select 2,'私鑰',crypto.dearmor('-----BEGIN PGP PRIVATE KEY BLOCK-----
Version: GnuPG v2.0.22 (GNU/Linux)

lQOYBGBiAL8BCAC9GRWDzTyIxEgEgxY0UtgiuxwA4w3dlV5O/iGZ71zzAkpAs7fY
ZfTattp/5KwoAkhR3WRzZS89gQhDkR67Orqw67t45giH65M4f294urOTguyonXtc
yFTigsHQ5aV5vTOuP/1EarAxb+LV7GUGN4BDDS7nWmpqiPwQ/y8CmgocEf8a6ilc
+6/tedUWX0RGylkMhMGmjXsEG171KYsMYDHZEJYVEAF+3HsarYO/05BcBOaW4pbg
MOiJredSrR9FIKpf4xAlyWITn1tP9m8qQd7JmHMXUUPB4Jq6tK6gEqZX8LZLl4np
SAe0MFSwZFPnND5rOghcReJQwqgMflhQ6KCvABEBAAEAB/wNSMHWLIjgIsncZ0kc
C+XbKsHg3hKPSnsBmaDKq6IgAD0vJnD35tG4u7fF3E6r0N07ww3XfXhAHdxywrMh
/BI5c5YL/D0FL2t8QJeYJ6WN61isz8Nm1TwBXaY4AqoJT11eFGi6cbRHBNEurhi6
wxNjon11C0kGivEKUKMAz8l+Bzau0B9JIB+JoaMczsUgVG6PFJUQrmUbLPhxMYal
7e+R5WYIx/lAYEW/mKwKLL9KgVWY885lNB4uGKP8IsH0j1mh90NsBVVEogeKEn5S
z28ZCmAXrKknHAn/mGTqh5Z85OzXutzi+Z2TaTToX0B8p3sOrwpQJwLJOKYlff+M
Kw/hBADSVxzCkCfLrxwlCzTk4V9gBmj2GGcK/Loi6yvIEiObqK+BorAucnKWwMU5
DFFuWc/ROm7QUHVYPkaNVNppV58MxliHuUz4NHpEjvFRdIRW9Ezk+5AEnYGKtR70
U7lGl/L47IKBDcVwtpaSQWUOZZHQhoXtErWO6HCj37b8+VjNRQQA5iWCVLcQWgay
YvO1DV/+RgG1QOuYNwDQdqLnTnZP2nrVoR1Mzlu7LYh64jrqXTZwr14nTREJ41rQ
4hEFThkG8WCNkc4VlgXiWs6yWwYoLxAnPQkHtNACeeltcS72V7dZgCu4eb4DHX6r
UAY7Da90LtRZwCRYx/CxPsYDy8OCs2MD/3r0s6SvFtixLwncoC2T++D438+J31qm
NTssVfQIxQLGbcT6cas8rZI0F6ALJzFUVlXE1kzv3fde8eZwh8q1Gp5fb6GJq6kL
Gzb0jFAOtuJ1sg+FnK0of+ttU2oTv9dhl3uwRE79PKy/t35WgqGPZZGCeseLhAW4
fuF4qeIPRSH/OP20FWZlaWZlaWZlaSAoZmVpZmVpZmVpKYkBOQQTAQIAIwUCYGIA
vwIbAwcLCQgHAwIBBhUIAgkKCwQWAgMBAh4BAheAAAoJEPXdEgZRJnWjvyYH/ik4
oUs/Mc0M188ELxvbsQkhFUN0NiZHWtNrGnKRNjsP/vnZERYV7ZncdMXjTBzEdPv6
hrB+nl/v3C3wQm9kLi3PoI2gpE6Xr9F2/KTJqyXPuKUKso3CyHYRt26dFziUSK3q
zeLjHtVGmavavhhuuV1bMUhbvyUxa9UbhyzCoGxoMKViPhV91zp4bUJSvA8+arjN
JuSMAKcqN8o15kQS5HTACunpAFRlhJHqRM0swE3AXiov2ffx7oGklD/P12K0kbd2
RbBa2YjYkV3E7+IB1O1+JxaTNxjNv1dQRh8GpnYB9GYUpqpKFoHiEefQ0npmXjfW
rOFZuMAxCi1cbzgurB+dA5gEYGIAvwEIALaYy+Bk+lfB5+UFnWDGJPY9SUP+3WS5
l21Gsyvd2QLF9AxpTQKkCxMHB3CnzBGQLX73d+ycemVx5tm1CoY1NfxeOwBi8cs1
QPJD2IyvdpcZtdDXMZ4qxXKm5IpnDWsoJdjg9XLt9tu06NR3DyinpV0PxKSw3w/r
FGj/96vJ57VWsb11yub4sGAy7QAyIpCTsbIgmuhcaejTecUTDFOV7YlA2DhGhqoY
QUtk2Jyi68qSWIW+NVCg0jLuEc6t9NYmlbnYm3XLHK+/TaFOxoSYssk9gXVWOMdh
KEifRUWFwL20rDpYchFNLdr1xP311BIYZBYVaqd7SIUCwLubjpNxcokAEQEAAQAH
/iwlN9E6CI5T+oETwO9HKGOCAKcYRKVX+2S3fZTi71bszep8QNqdF3JTtjhF0SAD
6AP0x4EkebDx/zpuOyukTLeFfqVCxqt25gtMXNvyhezVEfB6Pmw7cf4cnE9bx9I6
5B+Rj7XoE1Ed5VaMrFULZv7fU47vlhZOouIZ1GKrj0H3WXEbPFTW4SYS1p+mRydw
YqXIP1iY0u7Xjye5MGJKgH5lZ9X/rR3iwZw7yEFnVMBjVFTyLgMhcsSCn0mzWlpX
Am3Sb0psYkx3dsoZa3kCaW0ebIcHy2Vh0FuYnwVEdL43LX1OAzO8IOK676/UOoaE
deSKO73+pAl7RGThpaW4T6kEAMjiszxkdIufC66VbccKduUFR9ALnlG/89HT/ew5
vgBX7/bKCgWI//otzwH9HTibGbLP4OIhTndzseYpuxQ+jYNMyQysf+DOco1sahNs
UGScToXeN8o3un4ja77BnEH5Wxb5xhD0zLhjda94FLRxD+if844DSjgass7bEXOk
jQrVBADosZS4Xxi5SWv/Md8kFerfdo2H6wv+t74GdK0KQ9qD3+0yMWvNX49Y2RsB
N9da5E63XZcn2MDD4aa6tacfza1/iYHmMrc1LWUo8YoW/rELWX0rmgWdqgIBJwDM
b8XR/0SPNbTHDNL6mlyrUYxTZGVi+bicRrNvRW3QJ62eLci65QQAg7bPxR+dto6g
lFpfErGx64tjlET5KBpB7pF8e1k4CTx9QrEaNCwDLaOIG7GbGln/+MyPGxAbYuOU
Kkz9tw+EfMdQRQU0O/8C1VZapTSb2yjNxIg4lUewalx1pY0eZq8UCzL+j0UUnVbM
Ncx8MouvE7C/9Y/WqeD7cvqIzpjfKthBYIkBHwQYAQIACQUCYGIAvwIbDAAKCRD1
3RIGUSZ1owxRCACqPEa1L9zetLwW/yuDuR/h+76qEmQwHmPOo8xUromtiDcZaOAH
YCRkJ5wz4CnHazYZBrGfcMiuFVwwguvHYi0mb29rh3ZvmF+NGTw6PkNbt9Rh7bja
nxLj4Bu0GeYPMI3I+0SLnTfh47v1HLjZ6ozM4lsPRFN6/zkZetltm5sZTW2pOTX4
Mx+Gg328QIDZd8q7I7DNjGRS3tqnZxa2SS4RHXKtM5iMJQmOej2X9MgFCwnFjikl
LcZ7QR6zxB7ccZwZu8lq5l9G3/A4Sg5jrAgzOyKFq4bM7GZboyYTTHhi6VT1zkfV
urBb6HXmtFX82YVb20ybYYOxDGag6qzE7QJX
=395H
-----END PGP PRIVATE KEY BLOCK-----');


### 查看原始的鑰匙串的值
select id,name,crypto.armor(pkey) from keys;

### 獲取鑰匙串的key id
select id,name,crypto.pgp_key_id(pkey) from keys;
 id | name |    pgp_key_id
----+------+------------------
  2 | 私鑰 | 086812A41A6C562B
  1 | 公鑰 | 086812A41A6C562B
(2 rows)

### 公鑰加密
select crypto.pgp_pub_encrypt('This is HAWQ',pkey) from keys where id=1;

### 私鑰解密
with t_msg as (
select crypto.pgp_pub_encrypt('This is HAWQ',pkey) as msg from keys where id=1
)
select crypto.pgp_pub_decrypt(msg,pkey) from t_msg join keys on keys.id=2;

對(duì)稱秘鑰

pgp_sym_encrypt()

帶有一個(gè)對(duì)稱的PGP秘鑰psw加密data。 options參數(shù)可以包含選項(xiàng)設(shè)置。

e.g.

select crypto.pgp_sym_encrypt(
                                                        'This is HAWQ',   -- 數(shù)據(jù)
                              'password'        -- 密碼
                             );

pgp_sym_decrypt()

解密一個(gè)對(duì)稱秘鑰加密的PGP消息。<br />用pgp_sym_decrypt解密bytea數(shù)據(jù)是不允許的。 <br />這是為了避免輸出不合法的字符數(shù)據(jù)。<br />用pgp_sym_decrypt_bytea 解密原始的文本數(shù)據(jù)是可以的。<br />options參數(shù)可以包含選項(xiàng)設(shè)置。

e.g.

select crypto.pgp_sym_decrypt(
                              crypto.pgp_sym_encrypt('This is HAWQ', 'password'),  -- 加密后的數(shù)據(jù)
                              'password'                                           -- 密碼 
                             ) ;

PGP功能的選項(xiàng)

compress-algo

只有PostgreSQL編譯的時(shí)候帶有zlib選項(xiàng)時(shí)才可以使用下來該選項(xiàng)的壓縮算法

Values:
      0 - no compression
      1 - ZIP compression
      2 - ZLIB compression (= ZIP plus meta-data and block CRCs)
    Default: 0
    Applies to: pgp_sym_encrypt, pgp_pub_encrypt

e.g.

-- 加密
select crypto.pgp_sym_encrypt(
                                                        'This is HAWQ',   -- 數(shù)據(jù)
                              'password' ,      -- 密碼
                              'compress-algo=2'
                             );

-- 解密
select crypto.pgp_sym_decrypt(
                         crypto.pgp_sym_encrypt('This is HAWQ', 'password','compress-algo=2'),  -- 加密后的數(shù)據(jù)
                         'password'      -- 密碼
                             ) ;

unicode-mode

Whether to convert textual data from database internal encoding to UTF-8 and back. If your database already is UTF-8, no conversion will be done, but the message will be tagged as UTF-8. Without this option it will not be.

Values: 0, 1
    Default: 0
    Applies to: pgp_sym_encrypt, pgp_pub_encrypt

e.g.

-- 加密
select crypto.pgp_sym_encrypt(
                                                        'This is HAWQ',   -- 數(shù)據(jù)
                              'password' ,      -- 密碼
                              'unicode-mode=1'
                             );

-- 解密
select crypto.pgp_sym_decrypt(
                         crypto.pgp_sym_encrypt('This is HAWQ', 'password','unicode-mode=1'),  -- 加密后的數(shù)據(jù)
                         'password'      -- 密碼
                             ) ;

compress-level

How much to compress. Higher levels compress smaller but are slower. 0 disables compression.

    Values: 0, 1-9
    Default: 6
    Applies to: pgp_sym_encrypt, pgp_pub_encrypt

e.g.

-- 加密
select crypto.pgp_sym_encrypt(
                                                        'This is HAWQ',   -- 數(shù)據(jù)
                              'password' ,      -- 密碼
                              'compress-level=9'
                             );

-- 解密
select crypto.pgp_sym_decrypt(
                         crypto.pgp_sym_encrypt('This is HAWQ', 'password','compress-level=9'),  -- 加密后的數(shù)據(jù)
                         'password'      -- 密碼
                             ) ;

cipher-algo

Which cipher algorithm to use.

    Values: bf, aes128, aes192, aes256 (OpenSSL-only: 3des, cast5)
    Default: aes128
    Applies to: pgp_sym_encrypt, pgp_pub_encrypt

e.g.

-- 加密
select crypto.pgp_sym_encrypt(
                                                        'This is HAWQ',   -- 數(shù)據(jù)
                              'password' ,      -- 密碼
                              'cipher-algo=aes256'
                             );
 
-- 解密 
select crypto.pgp_sym_decrypt(
                         crypto.pgp_sym_encrypt('This is HAWQ', 'password','cipher-algo=aes256'),  -- 加密后的數(shù)據(jù)
                         'password'      -- 密碼
                             ) ;

convert-crlf

Whether to convert \n into \r\n when encrypting and \r\n to \n when decrypting. RFC 4880 specifies that text data should be stored using \r\n line-feeds. Use this to get fully RFC-compliant behavior.

    Values: 0, 1
    Default: 0
    Applies to: pgp_sym_encrypt, pgp_pub_encrypt, pgp_sym_decrypt, pgp_pub_decrypt

e.g.

-- 加密
select crypto.pgp_sym_encrypt(
                                                        'This is HAWQ',   -- 數(shù)據(jù)
                              'password' ,      -- 密碼
                              'convert-crlf=1'
                             );

-- 解密
select crypto.pgp_sym_decrypt(
                         crypto.pgp_sym_encrypt('This is HAWQ', 'password','convert-crlf=1'),  -- 加密后的數(shù)據(jù)
                         'password'      -- 密碼
                             ) ;

disable-mdc

Do not protect data with SHA-1. The only good reason to use this option is to achieve compatibility with ancient PGP products, predating the addition of SHA-1 protected packets to RFC 4880. Recent gnupg.org and pgp.com software supports it fine.

    Values: 0, 1
    Default: 0
    Applies to: pgp_sym_encrypt, pgp_pub_encrypt

e.g.

-- 加密
select crypto.pgp_sym_encrypt(
                                                        'This is HAWQ',   -- 數(shù)據(jù)
                              'password' ,      -- 密碼
                              'disable-mdc=1'
                             );

-- 解密
select crypto.pgp_sym_decrypt(
                         crypto.pgp_sym_encrypt('This is HAWQ', 'password','disable-mdc=1'),  -- 加密后的數(shù)據(jù)
                         'password'      -- 密碼
                             ) ;

s2k-mode

Which S2K algorithm to use.

    Values:
      0 - Without salt.  Dangerous!
      1 - With salt but with fixed iteration count.
      3 - Variable iteration count.
    Default: 3
    Applies to: pgp_sym_encrypt

e.g.

-- 加密
select crypto.pgp_sym_encrypt(
                                                        'This is HAWQ',   -- 數(shù)據(jù)
                              'password' ,      -- 密碼
                              's2k-mode=1'
                             );

-- 解密
select crypto.pgp_sym_decrypt(
                      crypto.pgp_sym_encrypt('This is HAWQ', 'password','s2k-mode=1'),  -- 加密后的數(shù)據(jù)
                         'password'      -- 密碼
                             ) ;

s2k-digest-algo

Which digest algorithm to use in S2K calculation.

    Values: md5, sha1
    Default: sha1
    Applies to: pgp_sym_encrypt

e.g.

-- 加密
select crypto.pgp_sym_encrypt(
                                                        'This is HAWQ',   -- 數(shù)據(jù)
                              'password' ,      -- 密碼
                              's2k-digest-algo=md5'
                             );

-- 解密
select crypto.pgp_sym_decrypt(
                      crypto.pgp_sym_encrypt('This is HAWQ', 'password','s2k-digest-algo=md5'),  -- 加密后的數(shù)據(jù)
                         'password'      -- 密碼
                             ) ;

s2k-cipher-algo

Which cipher to use for encrypting separate session key.

    Values: bf, aes, aes128, aes192, aes256
    Default: use cipher-algo
    Applies to: pgp_sym_encrypt

e.g.

-- 加密
select crypto.pgp_sym_encrypt(
                                                        'This is HAWQ',   -- 數(shù)據(jù)
                              'password' ,      -- 密碼
                              's2k-cipher-algo=aes256'
                             );

-- 解密
select crypto.pgp_sym_decrypt(
                     crypto.pgp_sym_encrypt('This is HAWQ', 'password','s2k-cipher-algo=aes256'),  -- 加密后的數(shù)據(jù)
                         'password'      -- 密碼
                             ) ;

enable-session-key

Use separate session key. Public-key encryption always uses a separate session key; this is for symmetric-key encryption, which by default uses the S2K key directly.

    Values: 0, 1
    Default: 0
    Applies to: pgp_sym_encrypt

e.g.

PGP功能的選項(xiàng)的復(fù)合選項(xiàng)

-- 加密
select crypto.pgp_sym_encrypt(
                                                        'This is HAWQ',   -- 數(shù)據(jù)
                              'password' ,      -- 密碼
                              'compress-algo=2,unicode-mode=1,compress-level=9,convert-crlf=1,disable-mdc=1,s2k-mode=1,s2k-digest-algo=md5,cipher-algo=bf,s2k-cipher-algo=bf'
                             );
 
 
-- 解密 
select crypto.pgp_sym_decrypt(
                     crypto.pgp_sym_encrypt(
                                                        'This is HAWQ',   -- 數(shù)據(jù)
                              'password' ,      -- 密碼
                              'compress-algo=2,unicode-mode=1,compress-level=9,convert-crlf=1,disable-mdc=1,s2k-mode=1,s2k-digest-algo=md5,cipher-algo=bf,s2k-cipher-algo=bf'
                             ),  -- 加密后的數(shù)據(jù)
  
                         'password'      -- 密碼
                             ) ; 
                             
-- cipher-algo 與 s2k-cipher-algo 必須需要一致
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請(qǐng)結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡(jiǎn)書系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

  • 環(huán)境：PostgreSQL 13PostgreSQL中，pgcrypto是contrib下的一個(gè)插件，它提供了一些...
    Jerry_1116閱讀 10,314評(píng)論 0贊 2
  • 說明 針對(duì)上行參數(shù) value，做加密處理，基本思路每個(gè)項(xiàng)目擁有有不同的私鑰因子（通過定義后臺(tái)配置），最終的私鑰=...
    SuperGu閱讀 481評(píng)論 0贊 0
  • 一、對(duì)稱加密算法 1.DES 可以存在獲取秘鑰算法和加解密算法不同情況 創(chuàng)建秘鑰： 注入BouncyCas...
    白字書生_30a2閱讀 1,159評(píng)論 0贊 0
  • 數(shù)字摘要 MD5 Message Digest Algorithm MD5（消息摘要算法第五版），用于確保信息的完...
    龐哈哈哈12138閱讀 678評(píng)論 0贊 3
  • 表情是什么，我認(rèn)為表情就是表現(xiàn)出來的情緒。表情可以傳達(dá)很多信息。高興了當(dāng)然就笑了，難過就哭了。兩者是相互影響密不可...
    Persistenc_6aea閱讀 129,567評(píng)論 2贊 7

友情鏈接更多精彩內(nèi)容