暂无图片
暂无图片
7
暂无图片
暂无图片
暂无图片
首页 / 【PG安全】-PG的数据加密和解密实践

【PG安全】-PG的数据加密和解密实践

原创 闫伟 2021-09-07
4140

本文简单的测试官方手册等相关数据库和数据的加密解密实验步骤

Table of Contents

image-20210905000409285

1 Postgresql的加密技术

加密方式 说明
口令加密 数据库用户的口令都是以哈希(取决于password_encryption配置)的方式存储，主要是md5，scram-sha-256
口令安全 通过passwordcheck插件进行控制和管理
指定列加密 pgcrypto模块允许对特定域进行加密存储。这个功能只对某些敏感数据有用。 客户端提供解密的密钥，然后数据在服务器端解密并发送给客户端。
数据分区加密 数据分区加密技术是依赖文件系统加密技术，Linux 文件系统加密 选项包括 eCryptfs 和 EncFS
块加密技术FDE 加密解密是在读写文件时完成的，所以如果数据已经在shared buffer中了，对性能的影响就很小了。
跨网络加密数据 通过SSL网络加密协议传送安全数据
TED透明加密 针对整体数据库进行透明加密，对应用透明（对加密的数据读取不需要解密操作），对数据加密
PGP加密函数 双向加密使用
pgcrypto加密模块 pgcrypto 扩展模块可以用于 PostgreSQL 中实现加密和解密功能。从 PostgreSQL 13 版本开始 pgcrypto 属于“可信”模块；只要用户拥有当前数据库上的 CREATE 权限就可以安装该模块，不再需要超级用户权限，pgcrypto 提供了两类加密算法：单向加密和双向加密。：单向：通用函数digest()；密码hash函数：crypt() 和 gen_salt() 函数专用于密码加密，其中 crypt() 用于加密数据，gen_salt() 用于生成 salt（加盐）。

1 TDE 数据库加密

TDE，全称是Transparent data encryption, 数据透明加密。是数据库用于加密数据文件的一种技术。TDE提供了文件级别的加密。文件在硬盘上是密文，在内存中是明文。TDE解决了保护静止数据的问题，也称为静态数据加密。所谓透明是指加密对用户来说是透明的，用户无需更改原有的操作习惯,用户和应用程序都不需要关心密钥管理或者加密/解密。

提供的公司是：https://www.cybertec-postgresql.com/en/ 相关介绍在https://www.cybertec-postgresql.com/en/products/postgresql-transparent-data-encryption/

img

img

img

1.1 下载

image-20210903182813652

我们测试一下12.3的版本下载

1.2 上传到服务器，并解压


[postgres@PGserver2 ~]$ mkdir /data/pg12tde
[postgres@PGserver2 ~]$
[postgres@PGserver2 ~]$ ls
human_resource  pgbadger-master      postgresql-10.18                userlist
logfile         pgbadger-master.zip  postgresql-10.18.tar.gz
pack            postgres_dba         postgresql-12.3_TDE_1.0.tar.gz

gunzip postgresql-12.3_TDE_1.0.tar.gz
[postgres@PGserver2 ~]$ tar -xvf postgresql-12.3_TDE_1.0.tar


复制

1.3 编译和安装软件

具体的安装介绍：https://www.cybertec-postgresql.com/en/transparent-data-encryption-installation-guide/


[postgres@PGserver2 postgresql-12.3_TDE_1.0]$ ls
aclocal.m4  configure.in  doc             Makefile  README.git
config      contrib       GNUmakefile.in  NEWS.TDE  src
configure   COPYRIGHT     HISTORY         README
[postgres@PGserver2 postgresql-12.3_TDE_1.0]$

 ./configure --prefix=/data1/pg12tde --with-pgport=20001 --with-readline --with-python --with-perl --with-tcl --with-openssl --with-ldap 

make world -j2
make install-world -j2

复制

1.4 设置初始化变量

 [postgres@node01 ~]$ cat key.sh 
 #!/bin/bash
 echo d42a2912dc23cdf3fb78ed4a8a6d6d9c
 
 修改环境变量：这里因为安装了很多的版本的PG因此进行如下修改
 
 .bashrc 文件添加
 # User specific aliases and functions

export PGHOME=/data
read -p "请选择数据库版本: " version
if [ "${version}" == "14" ]; then
	. ${PGHOME}/pg14/postgresql.env
elif [ "${version}" == "13" ]; then
	. ${PGHOME}/pg13/postgresql.env
elif [ "${version}" == "12" ]; then
	. ${PGHOME}/pg12/postgresql.env
elif [ "${version}" == "11" ]; then
	. ${PGHOME}/pg11/postgresql.env
elif [ "${version}" == "10" ]; then
	. ${PGHOME}/pg10.18/postgresql.env
elif [ "${version}" == "9" ]; then
        . ${PGHOME}/pg96/postgresql.env
elif [ "${version}" == "12tde" ]; then
        . ${PGHOME}/pg12tde/postgresql.env
else
        echo "未选择版本将退出本次登录"
        exit 99
fi


cat >/data/pg12tde/postgresql.env<<EOF
export PGHOME=/data/pg12tde
export PGDATA=\${PGHOME}/pgdata
export LD_LIBRARY_PATH=\${PGHOME}/lib:/usr/lib:/usr/lib64:/lib:/lib64:/usr/local/lib:/usr/local/lib64
export PATH=\${PGHOME}/bin:\${PATH}
EOF

cat >/data/pg10.18/postgresql.env<<EOF
export PGHOME=/data/pg10.18
export PGDATA=\${PGHOME}/pgdata
export LD_LIBRARY_PATH=\${PGHOME}/lib:/usr/lib:/usr/lib64:/lib:/lib64:/usr/local/lib:/usr/local/lib64
export PATH=\${PGHOME}/bin:\${PATH}
EOF

重新登陆用户即可

[postgres@PGserver2 ~]$ more .psqlrc
\set dba '\\i /home/postgres/postgres_dba/start.psql'
\set PROMPT1 '%/@%M:%>%R%#'


复制

1.5 初始化安装

设置一个key

vi key.sh 
#!/bin/bash
echo d42a2912dc23cdf3fb78ed4a8a6d6d9c
chmod +x key.sh

初始化
[postgres@PGserver2 ~]$ initdb -D $PGDATA -K /home/postgres/key.sh
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.
Data encryption is enabled.

creating directory /data/pg12tde/pgdata ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Asia/Shanghai
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    pg_ctl -D /data/pg12tde/pgdata -l logfile start



复制

1.6 启动并测试加密效果

[postgres@PGserver2 ~]$   pg_ctl -D /data/pg12tde/pgdata -l logfile start
waiting for server to start... done
server started
[postgres@PGserver2 ~]$ psql
psql (12.3_TDE_1.0)
Type "help" for help.

尝试创建表找出对应文件
postgres@[local]:20001=#create table tab_test(id int,name varchar);
CREATE TABLE
postgres@[local]:20001=#select pg_relation_filepath('tab_test');
 pg_relation_filepath
----------------------
 base/13593/16384
(1 row)

postgres@[local]:20001=#insert into tab_test values(1,'PostgreSQL');


查看对应文件


-rw-------. 1 postgres postgres 8192 Sep  3 23:25 16384
  postgres=# \! strings $PGDATA/base/13593/16384
        postgres=# insert into tab_test values(1,'PostgreSQL');
        INSERT 0 1
        postgres=# \! strings $PGDATA/base/13593/16384
        postgres=# checkpoint;
        CHECKPOINT
        postgres=# \! strings $PGDATA/base/13593/16384
        N)jt
        #PO\
        <hhl
        *^rO
        .?x6
        
 说明已经进行了加密文件处理
 
 

复制

1.7 块级别查看

/home/postgres/postgresql-12.3_TDE_1.0/contrib
make
make install
pageinspect

postgres@[local]:20001=#create extension pageinspect;
CREATE EXTENSION
postgres@[local]:20001=#


\d pg_extension;

postgres@[local]:20001=#select extName from pg_extension;
   extname
-------------
 plpgsql
 pageinspect
(2 rows)



 SELECT * FROM page_header(get_raw_page('pg_class', 0));
 
 postgres@[local]:20001=#\df page_header
                                                                                                                  List of functions
 Schema |    Name     | Result data type |                                                                                         Argument data
types                                                                                         | Type
--------+-------------+------------------+-------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------+------
 public | page_header | record           | page bytea, OUT lsn pg_lsn, OUT checksum smallint, OUT flags smallint, OUT lower smallint, OUT upper s
mallint, OUT special smallint, OUT pagesize smallint, OUT version smallint, OUT prune_xid xid | func
(1 row)

postgres@[local]:20001=#\df get
get_bit                get_byte               get_current_ts_config  getdatabaseencoding    getpgusername          get_raw_page
postgres@[local]:20001=#\df get_raw_page
                           List of functions
 Schema |     Name     | Result data type | Argument data types | Type
--------+--------------+------------------+---------------------+------
 public | get_raw_page | bytea            | text, integer       | func
 public | get_raw_page | bytea            | text, text, integer | func
(2 rows)

postgres@[local]:20001=#



postgres@[local]:20001=#\df heap_page_items

               List of functions
 Schema |      Name       | Result data type |
                                 Argument data types
                                        | Type
--------+-----------------+------------------+---------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------+------
 public | heap_page_items | SETOF record     | page bytea, OUT lp smallint, OUT lp_off smallint, OUT lp_flags smallint, OUT lp_len smallint, OUT
t_xmin xid, OUT t_xmax xid, OUT t_field3 integer, OUT t_ctid tid, OUT t_infomask2 integer, OUT t_infomask integer, OUT t_hoff smallint, OUT t_bit
s text, OUT t_oid oid, OUT t_data bytea | func
(1 row)

postgres@[local]:20001=#


查看tab_test第一个块
postgres@[local]:20001=#select heap_page_items(get_raw_page('tab_test',0));
                                heap_page_items
-------------------------------------------------------------------------------
 (1,8152,1,39,487,0,0,"(0,1)",2,2050,24,,,"\\x0100000017506f737467726553514c")
(1 row)

postgres@[local]:20001=#select heap_page_items(get_raw_page('tab_test',1));
ERROR:  block number 1 is out of range for relation "tab_test"

postgres@[local]:20001=#select * from tab_test ;
 id |    name
----+------------
  1 | PostgreSQL
(1 row)

说明加密是透明的，但是#对于数据的读取和写入有一定的影响



复制

2 pgcrypto使用

部分内容参考于：https://blog.csdn.net/horses/article/details/109073000

img

相关说明在官方文档的附录F.额外提供的模块 F.25：

​ pgcrypto 扩展模块可以用于 PostgreSQL 中实现加密和解密功能。从 PostgreSQL 13 版本开始 pgcrypto 属于“可信”模块；只要用户拥有当前数据库上的 CREATE 权限就可以安装该模块，不再需要超级用户权限。

pgcrypto 提供了两类加密算法：单向加密和双向加密。

​ 单向加密属于不可逆加密，无法根据密文解密出明文，适用于数据的验证，例如登录密码验证。常用的单向加密算法有 MD5、SHA、HMAC 等。

​ 双向加密属于可逆加密，根据密文和密钥可解密出明文，适用于数据的安全传输，例如电子支付、数字签名等。常用的双向加密算法有 AES、DES、RSA、ECC 等。

​

2.1 单行加密

2.2.1通用哈希函数

digest()

digest(data text, type text) returns bytea
digest(data bytea, type text) returns bytea

复制

其中，data 是原始数据；type 是加密算法，包括 md5、sha1、sha224、sha256、sha384 以及 sha512；函数的返回结果为二进制字符串。

postgres@[local]:5432=#create extension pgcrypto ;
CREATE EXTENSION
postgres@[local]:5432=#select digest('yanwei','md5');
               digest
------------------------------------
 \xcbc38338afaef42742eeb4c49d5d8939
(1 row)


postgres@[local]:5432=#select encode(digest('yanwei','md5'),'hex');
              encode
----------------------------------
 cbc38338afaef42742eeb4c49d5d8939

postgres@[local]:5432=#select digest('yanwei','sha224');
                           digest
------------------------------------------------------------
 \xbf39de1199054e2cd4490c33b46bfb72af5a1c0d2bed5e41168c2a45
(1 row)

postgres@[local]:5432=#select digest('yanwei','sha256');
                               digest
--------------------------------------------------------------------
 \x04c7da1df1e9e61b396fb4c99b96ee6708a61f6b7fc2f0530352171d3b8f2a5e
(1 row)

postgres@[local]:5432=#select digest('yanwei','sha512');
                                                               digest
------------------------------------------------------------------------------------------------------------------------------------
 \xca055c25af38c142baa0f0042e01dc858aeccc1965e3e4992a41ffce20bb0ce54bc19bcbd21f480e83e7042a50a957523bb9642f00323dc12be73ce876001723
(1 row)

postgres@[local]:5432=#select digest('yanwei','sha384');
                                               digest
----------------------------------------------------------------------------------------------------
 \xdd61b1cea465e8342bd7fdc1ee8e86cd3acec187aec993d59663e378dc2aa97dbd0e2f132139e4e1ebc3206c6bbccc44


postgres@[local]:5432=#CREATE TABLE users (
postgres(#   id SERIAL PRIMARY KEY,
postgres(#   username varchar(20) NOT NULL UNIQUE,
postgres(#   password text NOT NULL
postgres(# );
CREATE TABLE
postgres@[local]:5432=#INSERT INTO users(username, password)
postgres-# VALUES ('yanwei1', encode(digest('123456','md5'), 'hex'));
INSERT 0 1
postgres@[local]:5432=#
postgres@[local]:5432=#INSERT INTO users(username, password)
postgres-# VALUES ('yanwei2', encode(digest('123456','md5'), 'hex'));
INSERT 0 1
postgres@[local]:5432=#SELECT * FROM users;
 id | username |             password
----+----------+----------------------------------
  1 | yanwei1  | e10adc3949ba59abbe56e057f20f883e
  2 | yanwei2  | e10adc3949ba59abbe56e057f20f883e
(2 rows)

postgres@[local]:5432=#

-- 输入正确密码时

postgres@[local]:5432=#SELECT *
postgres-# FROM users
postgres-# WHERE username = 'yanwei1'
postgres-# AND password = encode(digest('123456','md5'), 'hex');
 id | username |             password
----+----------+----------------------------------
  1 | yanwei1  | e10adc3949ba59abbe56e057f20f883e
(1 row)


其中，encode 函数用于将二进制字符串转换为十六进制的文本。

复制

hmac()

hmac(data text, key text, type text) returns bytea
hmac(data bytea, key bytea, type text) returns bytea

复制

其中，data 是原始数据；key 是加密密钥；type 是加密算法，包括 md5、sha1、sha224、sha256、sha384 以及 sha512；函数的返回结果为二进制字符串。这与digest()相似，但是该哈希只能在知晓密钥的情况下被重新计算出来。这阻止了某人修改数据且还想更改哈希以匹配之的企图

以下语句使用 hmac() 函数重新设置了用户的密码：
postgres@[local]:5432=#UPDATE users
postgres-#    SET password = encode(hmac('123456', username, 'md5'), 'hex');
UPDATE 2
postgres@[local]:5432=#
postgres@[local]:5432=#SELECT * FROM users;
 id | username |             password
----+----------+----------------------------------
  1 | yanwei1  | 235622bcad46ee4db7e24000513eadb2
  2 | yanwei2  | e74fe87d5b6f6fa2e60c85ffe95167ef
(2 rows)

postgres@[local]:5432=#


复制

2.2.2 密码hash函数

crypt() 和 gen_salt() 函数专用于密码加密，其中 crypt() 用于加密数据，gen_salt() 用于生成 salt（加盐）。

crypt() 中的算法和普通的 MD5 或者 SHA1 哈希算法存在以下不同之处：

  • crypt() 中的算法它们更慢。由于密码包含的数据量很小，这是增加暴力破解难度的唯一方法。

  • 它们使用了一个随机值（称为盐值），因此密码的用户加密后的密码不同。这也可以针对破解算法提供一种额外的安全保护。

  • 它们的结果中包括了算法类型，因此可以针对不同用户使用不同的算法对密码进行加密。

  • 其中一些算法具有自适应性，意味着当计算机性能变得更快时，可以调整算法使其变得更慢，而不会产生与已有密码的不兼容性。

    下表列出了 crypt() 函数支持的算法：

crypt()支持的算法

算法 最大口令长度 自适应？ Salt 位数 输出长度 描述
bf 72 yes 128 60 基于 Blowfish，变体 2a
md5 unlimited no 48 34 基于 MD5 的加密
xdes 8 yes 24 20 扩展的 DES
des 8 no 12 13 原生 UNIX 加密

crypt()

crypt(password text, salt text) 返回 text

复制

计算*password的一个 crypt(3) 风格的哈希。在存储一个新口令时，你需要使用gen_salt()产生一个新的salt值。要检查一个口令，把存储的哈希值作为salt*，并且测试结果是否匹配存储的值。

设置一个新口令的例子：

postgres@[local]:5432=#UPDATE users
postgres-#    SET password = crypt('123456', gen_salt('md5'));
UPDATE 2
postgres@[local]:5432=#
postgres@[local]:5432=#SELECT * FROM users
postgres-# ;
 id | username |              password
----+----------+------------------------------------
  1 | yanwei1  | $1$35BxSguf$MUP4jXJWY4qtLFdslj1rd1
  2 | yanwei2  | $1$zvCY5e7L$6szUDyh973uWsjU0sMg6p.
(2 rows)




复制

认证的例子：

SELECT (pswhash = crypt('entered password', pswhash)) AS pswmatch FROM ... ;

复制

如果输入的口令正确，这会返回true。

对于相同的密码，crypt() 函数每次也会返回不同的结果，因为 gen_salt() 函数每次都会生成不同的 salt。校验密码时可以将之前生成的哈希结果作为 salt：


postgres@[local]:5432=#SELECT id
FROM users
WHERE username = 'yanwei1'
AND password = crypt('123456', password);
 id
----
  1
(1 row)

#验证字符串和加密后的密码是否一样？


复制

gen_salt()

gen_salt(type text [, iter_count integer ]) 返回 text

复制

gen_salt() 函数用于生成盐值 salt，该函数每次都会生成一个随机的盐值字符串，该字符串同时决定了 crypt() 函数使用的算法；type 参数用于指定一个生成字符串的哈希算法，可能的取值包括 des、xdes、md5 以及 bf。

postgres@[local]:5432=#SELECT gen_salt('des'), gen_salt('xdes'), gen_salt('md5'), gen_salt('bf');
 gen_salt | gen_salt  |  gen_salt   |           gen_salt
----------+-----------+-------------+-------------------------------
 N6       | _J9..jBTa | $1$4r/nwTFo | $2a$06$CKl5.WnaLLgReHec4tgIY.
(1 row)

postgres@[local]:5432=#SELECT gen_salt('des'), gen_salt('xdes'), gen_salt('md5'), gen_salt('bf');
 gen_salt | gen_salt  |  gen_salt   |           gen_salt
----------+-----------+-------------+-------------------------------
 9W       | _J9..d15e | $1$/oFVU5hV | $2a$06$JegOof5T/EUNbbPRsPv5eO
(1 row)
每次加盐是不同的




复制

验证密码有效性

postgres=# create table tab_user(id int,username varchar,pwd varchar);
CREATE TABLE
postgres=# insert into tab_user values(1,'test','test');
INSERT 0 1
postgres=# select * from tab_user;
 id | username | pwd  
----+----------+------
  1 | test     | test

#使用 crypt 函数进行加密
postgres=# insert into tab_user values(2,'test',crypt('test',gen_salt('md5')));
INSERT 0 1
postgres=# select * from tab_user where id = 2;
 id | username |                pwd                 
----+----------+------------------------------------
  2 | test     | $1$CTT.csuv$lYMVOZjkFoGvo6T2Dcz9P.

postgres=# select crypt('test',gen_salt('md5'));
               crypt                
------------------------------------
 $1$YoqeTOm/$ODxPIpKChDJFcqP4QYO.I1
(1 row)

postgres=# select crypt('test',gen_salt('bf'));
                            crypt                             
--------------------------------------------------------------
 $2a$06$/UMeX/FSvazh4V9/5OEIx.cAaLMkaR8CtLIFfjeWJtqTyYaBQbV/O
(1 row)

#验证字符串和加密后的密码是否一样？
postgres=# select * from tab_user where pwd = crypt('test',pwd);
 id | username |                pwd                 
----+----------+------------------------------------
  2 | test     | $1$CTT.csuv$lYMVOZjkFoGvo6T2Dcz9P.

postgres=# update tab_user set pwd = crypt('usertest',gen_salt('md5')) 
postgres-# where pwd = crypt('test',pwd);
UPDATE 1
postgres=# select * from tab_user where id = 2;
 id | username |                pwd                 
----+----------+------------------------------------
  2 | test     | $1$MIuCKVbz$SqREHS8iouDHQeqLWHVkg0


复制

2.2 双向加密

PGP 加密函数

PGP 加密函数实现了 OpenPGP（RFC 4880）标准中的加密功能，包括对称密钥加密（私钥加密）和非对称密钥加密（公钥加密）。

一个加密的 PGP 消息由两个部分或者包组成：

  • 包含一个会话密钥的包 加密过的对称密钥或者公钥。
  • 包含用会话密钥加密过的数据的包。

当用一个对称密钥（即一个口令）加密时：

  1. 给定的口令被使用一个 String2Key (S2K) 算法哈希。这更像crypt()算法 有目的地慢并且使用随机 salt 但是它会产生一个全长度的二进制密钥。
  2. 如果要求一个独立的会话密钥，将会生成一个新的随机密钥。否则该 S2K 密钥将被直接用作会话密钥。
  3. 如果直接使用 S2K 密钥，那么只有 S2K 设置将被放入会话密钥包中。否则会话密钥会用 S2K 密钥加密并且放入会话密钥包中。

当使用一个公共密钥加密时：

  1. 一个新的随机会话密钥会被生成。
  2. 它被用公共密钥加密并且放入到会话密钥包中。

在两种情况下，要被加密的数据按下列步骤被处理：

  1. 可选的数据操纵：压缩、转换成 UTF-8 或者行末转换。

  2. 数据会被加上一个随机字节的块作为前缀。这等效于使用一个随机 IV。

  3. 追加一个随机前缀和数据的 SHA1 哈希。

  4. 所有这些都用会话密钥加密并且放在数据包中。

pgp_sym_encrypt()

pgp_sym_encrypt(data text, psw text [, options text ]) returns bytea
pgp_sym_encrypt_bytea(data bytea, psw text [, options text ]) returns bytea

复制

使用一个对称 PGP 密钥 psw加密data。其中，data 是要加密的数据；psw 是 PGP 对称密钥；options 参数用于设置选项。

pgp_sym_decrypt()

pgp_sym_decrypt(msg bytea, psw text [, options text ]) 返回 text
pgp_sym_decrypt_bytea(msg bytea, psw text [, options text ]) returns bytea

复制

解密一个用对称密钥加密过的 PGP 消息。其中，msg 是要解密的消息；psw 是 PGP 对称密钥；options 参数用于设置选项，参考下文。为了避免输出无效的字符，不允许使用 pgp_sym_decrypt 函数对 bytea 数据进行解密；可以使用 pgp_sym_decrypt_bytea 对原始文本数据进行解密。

测试1

postgres@[local]:5432=#create table tab_user(id int,username varchar,pwd varchar);
CREATE TABLE
postgres@[local]:5432=#insert into tab_user values(1,'test','test');
INSERT 0 1
postgres@[local]:5432=#select * from tab_user;
 id | username | pwd
----+----------+------
  1 | test     | test
(1 row)

#插入加密数据

insert into tab_user values(1,'pguser',pgp_sym_encrypt('pguser','user'));
postgres@[local]:5432=#select * from tab_user ;
 id | username |                                                                        pwd

----+----------+---------------------------------------------------------------------------------------------------------------------------------
-------------------
  1 | test     | test
  1 | pguser   | \xc30d0407030293060fbc4df4b0a974d23701efd665c71a8eca19fd413e9884f7328b67e2f1ce3841784aed8a8c35aaa00f9199392257fe2ad0e6850dee0828
8186ba3f2fb29fdf8d
(2 rows)

#解密数据
postgres=# select username,pgp_sym_decrypt(pwd::bytea,'user') from tab_user where id = 1;
 username | pgp_sym_decrypt 
----------+-----------------
 pguser   | pguser
(1 row)


复制

pgp_pub_encrypt()

pgp_pub_encrypt(data text, key bytea [, options text ]) returns bytea
pgp_pub_encrypt_bytea(data bytea, key bytea [, options text ]) returns bytea

复制

用一个公共 PGP 密钥 key加密data。给这个函数一个私钥会产生一个错误。

pgp_pub_decrypt()

pgp_pub_decrypt(msg bytea, key bytea [, psw text [, options text ]]) 返回 text
pgp_pub_decrypt_bytea(msg bytea, key bytea [, psw text [, options text ]]) returns bytea

复制

解密一个公共密钥加密的消息。*key必须是对应于用来加密的公钥的私钥。如果私钥是用口令保护的，你必须在psw*中给出该口令。如果没有口令，但你想要指定选项，你需要给出一个空口令。

不允许使用pgp_pub_decrypt解密bytea数据。这是为了避免输出非法的字符数据。使用pgp_pub_decrypt_bytea解密原始文本数据是好的。

pgp_key_id()

pgp_key_id(bytea) 返回 text

复制

pgp_key_id抽取一个 PGP 公钥或私钥的密钥 ID。或者如果给定了一个加密过的消息，它给出一个用来加密数据的密钥 ID。

它能够返回 2 个特殊密钥 ID：

  • SYMKEY

    该消息是用一个对称密钥加密的。

  • ANYKEY

    该消息是用公钥加密的，但是密钥 ID 已经被移除。这意味着你将需要尝试你所有的密钥来看看哪个能解密该消息。pgcrypto本身不产生这样的消息。

注意不同的密钥可能具有相同的 ID。这很少见但是是一种正常事件。客户端应用则应该尝试用每一个去解密，看看哪个合适 像处理ANYKEY一样。

pgp_key_id()

pgp_key_id(bytea) 返回 text

复制

pgp_key_id抽取一个 PGP 公钥或私钥的密钥 ID。或者如果给定了一个加密过的消息，它给出一个用来加密数据的密钥 ID。

它能够返回 2 个特殊密钥 ID：

  • SYMKEY

    该消息是用一个对称密钥加密的。

  • ANYKEY

    该消息是用公钥加密的，但是密钥 ID 已经被移除。这意味着你将需要尝试你所有的密钥来看看哪个能解密该消息。pgcrypto本身不产生这样的消息。

注意不同的密钥可能具有相同的 ID。这很少见但是是一种正常事件。客户端应用则应该尝试用每一个去解密，看看哪个合适 像处理ANYKEY一样。

pgp_armor_headers

pgp_armor_headers(data text, key out text, value out text) returns setof record

复制

pgp_armor_headers()从*data*中抽取 armor header。返回值是一个有两列的行集合，包括键和值。如果键或值 包含任何非-ASCII 字符，它们会被视作 UTF-8。

测试

0 安装相关软件

[root@PGserver2 .gnupg]# yum install -y rng-tools
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
huaweiepel                                                                                                                | 4.7 kB  00:00:00
huaweirepo                                                                                                                | 3.6 kB  00:00:00
file:///mnt/repodata/repomd.xml: [Errno 14] curl#37 - "Couldn't open file /mnt/repodata/repomd.xml"
Trying other mirror.
(1/2): huaweiepel/updateinfo                                                                                              | 1.0 MB  00:00:03
(2/2): huaweiepel/primary_db                                                                                              | 7.0 MB  00:00:04
Resolving Dependencies
--> Running transaction check
---> Package rng-tools.x86_64 0:6.3.1-5.el7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

=================================================================================================================================================
 Package                           Arch                           Version                               Repository                          Size
=================================================================================================================================================
Installing:
 rng-tools                         x86_64                         6.3.1-5.el7                           huaweirepo                          49 k

Transaction Summary
=================================================================================================================================================
Install  1 Package

Total download size: 49 k
Installed size: 102 k
Downloading packages:
rng-tools-6.3.1-5.el7.x86_64.rpm                                                                                          |  49 kB  00:00:02
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : rng-tools-6.3.1-5.el7.x86_64                                                                                                  1/1
  Verifying  : rng-tools-6.3.1-5.el7.x86_64                                                                                                  1/1

Installed:
  rng-tools.x86_64 0:6.3.1-5.el7

Complete!
[root@PGserver2 .gnupg]# systemctl  start rngd
[root@PGserver2 .gnupg]# systemctl  status rngd | grep running
   Active: active (running) since Sun 2021-09-05 00:35:05 CST; 4s ago


1、生成 PGP 密钥，对于 Linux 操作系统可以使用 gpg 工具，推荐使用 DSA and Elgamal 密钥；对于 RSA 加密，必须创建一个仅用于签名的 DSA 或者 RSA 密钥作为主控密钥，然后使用 gpg --edit-key 增加一个 RSA 加密子密钥。
[root@PGserver2 ~]# 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.

Please select what kind of key you want:
   (1) RSA and RSA (default)
   (2) DSA and Elgamal
   (3) DSA (sign only)
   (4) RSA (sign only)
Your selection?
RSA keys may be between 1024 and 4096 bits long.
What keysize do you want? (2048)
Requested keysize is 2048 bits
Please specify how long the key should be valid.
         0 = key does not expire
      <n>  = key expires in n days
      <n>w = key expires in n weeks
      <n>m = key expires in n months
      <n>y = key expires in n years
Key is valid for? (0)
Key does not expire at all
Is this correct? (y/N) y

GnuPG needs to construct a user ID to identify your key.

Real name: yanwei
Email address:
Comment:
You selected this USER-ID:
    "yanwei"

Change (N)ame, (C)omment, (E)mail or (O)kay/(Q)uit? O
You need a Passphrase to protect your secret key.

We need to generate a lot of random bytes. It is a good idea to perform
some other action (type on the keyboard, move the mouse, utilize the
disks) during the prime generation; this gives the random number
generator a better chance to gain enough entropy.
We need to generate a lot of random bytes. It is a good idea to perform
some other action (type on the keyboard, move the mouse, utilize the
disks) during the prime generation; this gives the random number
generator a better chance to gain enough entropy.
gpg: key 4298A6D2 marked as ultimately trusted
public and secret key created and signed.

gpg: checking the trustdb
gpg: 3 marginal(s) needed, 1 complete(s) needed, PGP trust model
gpg: depth: 0  valid:   1  signed:   0  trust: 0-, 0q, 0n, 0m, 0f, 1u
pub   2048R/4298A6D2 2021-09-04
      Key fingerprint = 722E AE11 7B01 5465 8F44  B57F CF13 0BA2 4298 A6D2
uid                  yanwei
sub   2048R/A44FD2E5 2021-09-04

[root@PGserver2 ~]#


2、gpg --list-secret-keys查看创建的密钥：
[root@PGserver2 ~]# gpg --list-secret-keys
/root/.gnupg/secring.gpg
------------------------
sec   2048R/4298A6D2 2021-09-04
uid                  yanwei
ssb   2048R/A44FD2E5 2021-09-04

[root@PGserver2 ~]# gpg --k
gpg: option "--k" is ambiguous
[root@PGserver2 ~]# gpg -k
/root/.gnupg/pubring.gpg
------------------------
pub   2048R/4298A6D2 2021-09-04
uid                  yanwei
sub   2048R/A44FD2E5 2021-09-04


3、将公钥和私钥转换为 ASCII-armor 格式

gpg -a --export 4298A6D2 > public.key

gpg -a --export-secret-keys A44FD2E5 > secret.key

4、查看公钥和私钥内容

[root@PGserver2 ~]# sudo gpg -a  --export 4298A6D2  --公钥内容
-----BEGIN PGP PUBLIC KEY BLOCK-----
Version: GnuPG v2.0.22 (GNU/Linux)

mQENBGEzoGgBCACxG8MyHzgygsr2s5Y+iKX6qIbzKzbv3gyiZV9ZUwN8pdhJKYpW
IlJDo4VVeHlVOzihzOJZkBQsTPCOl7y20Yv8gQjdTLuTmvu/BTDoJfMtAgY+IzWU
hAiEj02Vx50HEPcH4iqHWScPseNQ1RpxnvdNaJzBH0P7zrXVu9mM6fRMYmo1OLPz
8vQpbBIs2o3sqa/j+V2cu8vujHluKMya/9yqT2uAY22plKcEgyUxdXP6DpDwM1J2
6AciRCY1ad8htOb0nIpKWbRraM8eAOnI0rwMEYYA7pmwdk6UCOG+jDJhagMxvrrj
X9MgeocxKz8ZCe1MQ5CrrX4cRlsD4wrJMxn/ABEBAAG0BnlhbndlaYkBOQQTAQIA
IwUCYTOgaAIbAwcLCQgHAwIBBhUIAgkKCwQWAgMBAh4BAheAAAoJEM8TC6JCmKbS
CdIIAIxVbmq4gEbhqlAXqXiEx2RigPaCDn7yXkcG0lCvTsvaoouNGwE83TbcDzkF
DwvZ26xqyVdUE0Dp7AsuM3tjLy02lg6Mqk69lGYDLCw7fgQzLC4FSnn+p6YVMQt0
w1UIXuq5jA6RLBgYxwnJ0HjR7hsIDuD1DqIFc9ziZxXqachB0xe0FJtMoM8xyyRT
/4y9+bE1a53EGBqQWXAylC/IXozpyvlHxA9pHKR58QCRA3foCKTN1TST18ZH/Nti
vigRYJ4FNQYTRaaZlQOmJt0bkfYZPLyfHwXi4H7O8xO+k3q2axNn4Cj8u+zIyFcp
11v7qAlBQZs7h7giMQJbLh9O4X+5AQ0EYTOgaAEIAM1w0ZeM0+KKDa83ArCKIPQT
zYADNCOAiISp6a315zjqebw7iBRCnG+2hhYrWLBUNKy4u1+veZJdpXTEIx2juCN7
LPMlWTiOwW1ChxA9adNCnXZR9FInno0PE5G/vaB0V0MoH7R9UfjZOOATuUjinvwm
5jm5SEoXArExJpvcdWXYQRTldBQV+s2W3fJZ/aG+zWBFBK+hB6fVGQwONebKx61z
PNbQo3K2HvkUzGLEDjBv9fE7gsqdJpC3d0ewTgcN+OOSOh7EL+AF3yfr8yJmUv7w
erK0QmU4aSobvBdhqTW0WtZL79HGROsM2VOROa/kaY9aT+3Uk/VsHJVNQ4AQsBUA
EQEAAYkBHwQYAQIACQUCYTOgaAIbDAAKCRDPEwuiQpim0n5sB/9qwrtSj3SWvio/
yttRzXLmIiZ43NkI2OJqzF1T3G46Ioyc9ItRoILC2uTQuTl6tyNeGvL2rukoj17w
aLAePSgXI0gEH/YkW7wz7hbidmMB6pyFFWt5e3YR5YE2PLEMrgCNbZfEBMQZd49P
oRNX0Sc82C2fOJLxlUgOCJGLCeFFRuwNSdPJb1yCNZkKhkcRiz9CsH7SsMlTrpic
bgBdwYtsRZfSxrlAPm5JLXANFFb29u9H1ZCPfXvF8K5TBcfstfLGG4dOe9fR0Zaq
J+ong7vLiKNcv8HhMsEX61hAgfqdxgAr16BCLdEM7VsI5KiONciiP2UIy5X+9Ggu
Cuf24COU
=N0nR
-----END PGP PUBLIC KEY BLOCK-----
[root@PGserver2 ~]#  sudo gpg -a  --export A44FD2E5   --私钥内容
-----BEGIN PGP PUBLIC KEY BLOCK-----
Version: GnuPG v2.0.22 (GNU/Linux)

mQENBGEzoGgBCACxG8MyHzgygsr2s5Y+iKX6qIbzKzbv3gyiZV9ZUwN8pdhJKYpW
IlJDo4VVeHlVOzihzOJZkBQsTPCOl7y20Yv8gQjdTLuTmvu/BTDoJfMtAgY+IzWU
hAiEj02Vx50HEPcH4iqHWScPseNQ1RpxnvdNaJzBH0P7zrXVu9mM6fRMYmo1OLPz
8vQpbBIs2o3sqa/j+V2cu8vujHluKMya/9yqT2uAY22plKcEgyUxdXP6DpDwM1J2
6AciRCY1ad8htOb0nIpKWbRraM8eAOnI0rwMEYYA7pmwdk6UCOG+jDJhagMxvrrj
X9MgeocxKz8ZCe1MQ5CrrX4cRlsD4wrJMxn/ABEBAAG0BnlhbndlaYkBOQQTAQIA
IwUCYTOgaAIbAwcLCQgHAwIBBhUIAgkKCwQWAgMBAh4BAheAAAoJEM8TC6JCmKbS
CdIIAIxVbmq4gEbhqlAXqXiEx2RigPaCDn7yXkcG0lCvTsvaoouNGwE83TbcDzkF
DwvZ26xqyVdUE0Dp7AsuM3tjLy02lg6Mqk69lGYDLCw7fgQzLC4FSnn+p6YVMQt0
w1UIXuq5jA6RLBgYxwnJ0HjR7hsIDuD1DqIFc9ziZxXqachB0xe0FJtMoM8xyyRT
/4y9+bE1a53EGBqQWXAylC/IXozpyvlHxA9pHKR58QCRA3foCKTN1TST18ZH/Nti
vigRYJ4FNQYTRaaZlQOmJt0bkfYZPLyfHwXi4H7O8xO+k3q2axNn4Cj8u+zIyFcp
11v7qAlBQZs7h7giMQJbLh9O4X+5AQ0EYTOgaAEIAM1w0ZeM0+KKDa83ArCKIPQT
zYADNCOAiISp6a315zjqebw7iBRCnG+2hhYrWLBUNKy4u1+veZJdpXTEIx2juCN7
LPMlWTiOwW1ChxA9adNCnXZR9FInno0PE5G/vaB0V0MoH7R9UfjZOOATuUjinvwm
5jm5SEoXArExJpvcdWXYQRTldBQV+s2W3fJZ/aG+zWBFBK+hB6fVGQwONebKx61z
PNbQo3K2HvkUzGLEDjBv9fE7gsqdJpC3d0ewTgcN+OOSOh7EL+AF3yfr8yJmUv7w
erK0QmU4aSobvBdhqTW0WtZL79HGROsM2VOROa/kaY9aT+3Uk/VsHJVNQ4AQsBUA
EQEAAYkBHwQYAQIACQUCYTOgaAIbDAAKCRDPEwuiQpim0n5sB/9qwrtSj3SWvio/
yttRzXLmIiZ43NkI2OJqzF1T3G46Ioyc9ItRoILC2uTQuTl6tyNeGvL2rukoj17w
aLAePSgXI0gEH/YkW7wz7hbidmMB6pyFFWt5e3YR5YE2PLEMrgCNbZfEBMQZd49P
oRNX0Sc82C2fOJLxlUgOCJGLCeFFRuwNSdPJb1yCNZkKhkcRiz9CsH7SsMlTrpic
bgBdwYtsRZfSxrlAPm5JLXANFFb29u9H1ZCPfXvF8K5TBcfstfLGG4dOe9fR0Zaq
J+ong7vLiKNcv8HhMsEX61hAgfqdxgAr16BCLdEM7VsI5KiONciiP2UIy5X+9Ggu
Cuf24COU
=N0nR
-----END PGP PUBLIC KEY BLOCK-----
[root@PGserver2 ~]#


5、使用 pgp_key_id() 和 dearmor() 函数获取 keyid

postgres@[local]:5432=#select pgp_key_id(dearmor('-----BEGIN PGP PUBLIC KEY BLOCK-----
postgres'# Version: GnuPG v2.0.22 (GNU/Linux)
postgres'#
postgres'# mQENBGEzoGgBCACxG8MyHzgygsr2s5Y+iKX6qIbzKzbv3gyiZV9ZUwN8pdhJKYpW
postgres'# IlJDo4VVeHlVOzihzOJZkBQsTPCOl7y20Yv8gQjdTLuTmvu/BTDoJfMtAgY+IzWU
postgres'# hAiEj02Vx50HEPcH4iqHWScPseNQ1RpxnvdNaJzBH0P7zrXVu9mM6fRMYmo1OLPz
postgres'# 8vQpbBIs2o3sqa/j+V2cu8vujHluKMya/9yqT2uAY22plKcEgyUxdXP6DpDwM1J2
postgres'# 6AciRCY1ad8htOb0nIpKWbRraM8eAOnI0rwMEYYA7pmwdk6UCOG+jDJhagMxvrrj
postgres'# X9MgeocxKz8ZCe1MQ5CrrX4cRlsD4wrJMxn/ABEBAAG0BnlhbndlaYkBOQQTAQIA
postgres'# IwUCYTOgaAIbAwcLCQgHAwIBBhUIAgkKCwQWAgMBAh4BAheAAAoJEM8TC6JCmKbS
postgres'# CdIIAIxVbmq4gEbhqlAXqXiEx2RigPaCDn7yXkcG0lCvTsvaoouNGwE83TbcDzkF
postgres'# DwvZ26xqyVdUE0Dp7AsuM3tjLy02lg6Mqk69lGYDLCw7fgQzLC4FSnn+p6YVMQt0
postgres'# w1UIXuq5jA6RLBgYxwnJ0HjR7hsIDuD1DqIFc9ziZxXqachB0xe0FJtMoM8xyyRT
postgres'# /4y9+bE1a53EGBqQWXAylC/IXozpyvlHxA9pHKR58QCRA3foCKTN1TST18ZH/Nti
postgres'# vigRYJ4FNQYTRaaZlQOmJt0bkfYZPLyfHwXi4H7O8xO+k3q2axNn4Cj8u+zIyFcp
postgres'# 11v7qAlBQZs7h7giMQJbLh9O4X+5AQ0EYTOgaAEIAM1w0ZeM0+KKDa83ArCKIPQT
postgres'# zYADNCOAiISp6a315zjqebw7iBRCnG+2hhYrWLBUNKy4u1+veZJdpXTEIx2juCN7
postgres'# LPMlWTiOwW1ChxA9adNCnXZR9FInno0PE5G/vaB0V0MoH7R9UfjZOOATuUjinvwm
postgres'# 5jm5SEoXArExJpvcdWXYQRTldBQV+s2W3fJZ/aG+zWBFBK+hB6fVGQwONebKx61z
postgres'# PNbQo3K2HvkUzGLEDjBv9fE7gsqdJpC3d0ewTgcN+OOSOh7EL+AF3yfr8yJmUv7w
postgres'# erK0QmU4aSobvBdhqTW0WtZL79HGROsM2VOROa/kaY9aT+3Uk/VsHJVNQ4AQsBUA
postgres'# EQEAAYkBHwQYAQIACQUCYTOgaAIbDAAKCRDPEwuiQpim0n5sB/9qwrtSj3SWvio/
postgres'# yttRzXLmIiZ43NkI2OJqzF1T3G46Ioyc9ItRoILC2uTQuTl6tyNeGvL2rukoj17w
postgres'# aLAePSgXI0gEH/YkW7wz7hbidmMB6pyFFWt5e3YR5YE2PLEMrgCNbZfEBMQZd49P
postgres'# oRNX0Sc82C2fOJLxlUgOCJGLCeFFRuwNSdPJb1yCNZkKhkcRiz9CsH7SsMlTrpic
postgres'# bgBdwYtsRZfSxrlAPm5JLXANFFb29u9H1ZCPfXvF8K5TBcfstfLGG4dOe9fR0Zaq
postgres'# J+ong7vLiKNcv8HhMsEX61hAgfqdxgAr16BCLdEM7VsI5KiONciiP2UIy5X+9Ggu
postgres'# Cuf24COU
postgres'# =N0nR
postgres'# -----END PGP PUBLIC KEY BLOCK-----')) as pgp_key_id;
    pgp_key_id
------------------
 3EB1B915A44FD2E5
(1 row)

6、\set temp_pub_key 简化配置使用
[root@PGserver2 ~]# chmod 777 *.key
[root@PGserver2 ~]# ll -al
total 36
dr-xr-x---.  4 root root  197 Sep  5 00:37 .
dr-xr-xr-x. 18 root root  236 Aug 28 23:53 ..
-rw-------.  1 root root 1218 Aug 28 23:49 anaconda-ks.cfg
-rw-------.  1 root root 3899 Sep  4 23:13 .bash_history
-rw-r--r--.  1 root root   18 Dec 29  2013 .bash_logout
-rw-r--r--.  1 root root  176 Dec 29  2013 .bash_profile
-rw-r--r--.  1 root root  176 Dec 29  2013 .bashrc
-rw-r--r--.  1 root root  100 Dec 29  2013 .cshrc
drwx------.  3 root root  162 Sep  5 00:36 .gnupg
drwxr-----.  3 root root   19 Aug 28 23:51 .pki
-rwxrwxrwx.  1 root root 1683 Sep  5 00:37 public.key
-rwxrwxrwx.  1 root root 3553 Sep  5 00:37 secret.key
-rw-r--r--.  1 root root  129 Dec 29  2013 .tcshrc
[root@PGserver2 ~]# chown postgres.postgres *.key
[root@PGserver2 ~]# ls -la *.eky
ls: cannot access *.eky: No such file or directory
[root@PGserver2 ~]# ls -la *.key
-rwxrwxrwx. 1 postgres postgres 1683 Sep  5 00:37 public.key
-rwxrwxrwx. 1 postgres postgres 3553 Sep  5 00:37 secret.key
[root@PGserver2 ~]#
[root@PGserver2 ~]# cp *.key /home/postgres/
[root@PGserver2 ~]# chown postgres.postgres /home/postgres/*.key


\set temp_pub_key 
postgres=# \set temp_pub_key `cat public.key`
postgres=# select :'temp_pub_key';
postgres@[local]:5432=#\set temp_pub_key `cat public.key`
postgres@[local]:5432=#select :'temp_pub_key';
                             ?column?                             
------------------------------------------------------------------
 -----BEGIN PGP PUBLIC KEY BLOCK-----                            +
 Version: GnuPG v2.0.22 (GNU/Linux)                              +
                                                                 +
 mQENBGEzoGgBCACxG8MyHzgygsr2s5Y+iKX6qIbzKzbv3gyiZV9ZUwN8pdhJKYpW+
 IlJDo4VVeHlVOzihzOJZkBQsTPCOl7y20Yv8gQjdTLuTmvu/BTDoJfMtAgY+IzWU+
 hAiEj02Vx50HEPcH4iqHWScPseNQ1RpxnvdNaJzBH0P7zrXVu9mM6fRMYmo1OLPz+
 8vQpbBIs2o3sqa/j+V2cu8vujHluKMya/9yqT2uAY22plKcEgyUxdXP6DpDwM1J2+
 6AciRCY1ad8htOb0nIpKWbRraM8eAOnI0rwMEYYA7pmwdk6UCOG+jDJhagMxvrrj+
 X9MgeocxKz8ZCe1MQ5CrrX4cRlsD4wrJMxn/ABEBAAG0BnlhbndlaYkBOQQTAQIA+
 IwUCYTOgaAIbAwcLCQgHAwIBBhUIAgkKCwQWAgMBAh4BAheAAAoJEM8TC6JCmKbS+
 CdIIAIxVbmq4gEbhqlAXqXiEx2RigPaCDn7yXkcG0lCvTsvaoouNGwE83TbcDzkF+
 DwvZ26xqyVdUE0Dp7AsuM3tjLy02lg6Mqk69lGYDLCw7fgQzLC4FSnn+p6YVMQt0+
 w1UIXuq5jA6RLBgYxwnJ0HjR7hsIDuD1DqIFc9ziZxXqachB0xe0FJtMoM8xyyRT+
 /4y9+bE1a53EGBqQWXAylC/IXozpyvlHxA9pHKR58QCRA3foCKTN1TST18ZH/Nti+
 vigRYJ4FNQYTRaaZlQOmJt0bkfYZPLyfHwXi4H7O8xO+k3q2axNn4Cj8u+zIyFcp+
 11v7qAlBQZs7h7giMQJbLh9O4X+5AQ0EYTOgaAEIAM1w0ZeM0+KKDa83ArCKIPQT+
 zYADNCOAiISp6a315zjqebw7iBRCnG+2hhYrWLBUNKy4u1+veZJdpXTEIx2juCN7+
 LPMlWTiOwW1ChxA9adNCnXZR9FInno0PE5G/vaB0V0MoH7R9UfjZOOATuUjinvwm+
 5jm5SEoXArExJpvcdWXYQRTldBQV+s2W3fJZ/aG+zWBFBK+hB6fVGQwONebKx61z+
 PNbQo3K2HvkUzGLEDjBv9fE7gsqdJpC3d0ewTgcN+OOSOh7EL+AF3yfr8yJmUv7w+
 erK0QmU4aSobvBdhqTW0WtZL79HGROsM2VOROa/kaY9aT+3Uk/VsHJVNQ4AQsBUA+
 EQEAAYkBHwQYAQIACQUCYTOgaAIbDAAKCRDPEwuiQpim0n5sB/9qwrtSj3SWvio/+
 yttRzXLmIiZ43NkI2OJqzF1T3G46Ioyc9ItRoILC2uTQuTl6tyNeGvL2rukoj17w+
 aLAePSgXI0gEH/YkW7wz7hbidmMB6pyFFWt5e3YR5YE2PLEMrgCNbZfEBMQZd49P+
 oRNX0Sc82C2fOJLxlUgOCJGLCeFFRuwNSdPJb1yCNZkKhkcRiz9CsH7SsMlTrpic+
 bgBdwYtsRZfSxrlAPm5JLXANFFb29u9H1ZCPfXvF8K5TBcfstfLGG4dOe9fR0Zaq+
 J+ong7vLiKNcv8HhMsEX61hAgfqdxgAr16BCLdEM7VsI5KiONciiP2UIy5X+9Ggu+
 Cuf24COU                                                        +
 =N0nR                                                           +
 -----END PGP PUBLIC KEY BLOCK-----
(1 row)

postgres=# insert into tab_user values(4,'PostgreSQL',pgp_pub_encrypt('Bigdata@',dearmor(:'temp_pub_key')));
INSERT 0 1

postgres@[local]:5432=#select * from tab_user ;
 id |  username  |                                                                                            
                                                                                                              
                                                                                                              
                  pwd                                                                                         
                                                                                                              
                                                                                                              
                      
----+------------+--------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------
----------------------
  1 | test       | test
  1 | pguser     | \xc30d0407030293060fbc4df4b0a974d23701efd665c71a8eca19fd413e9884f7328b67e2f1ce3841784aed8a8
c35aaa00f9199392257fe2ad0e6850dee08288186ba3f2fb29fdf8d
  4 | PostgreSQL | \xc1c04c033eb1b915a44fd2e50107ff57abd80239849bda64b5e71da235ca7379aa1a124f26d21aaad02334bfc
b2ffa4f3e55ca0404ffe858f81df262cd083c3689df98187b660a889e4137d7b76599628c3cf77a738830a5fd916f0f699bd3483755d88
80849824988dca7bacb53e261e330281e08cd35bdbc6491b55a3951d8af9ccc7f4ae28328d3e2373a9ae4b85a0d24ea2e34187a5549646
b6aabd813aea7587e3a2c800581eb1845928d18fe36a1e945170f950198a248800e0cbfb8c26c7db68a9ffa9b3a5668c2f412660c0a7cc
bcccf1a49def44acfd565a566f1fe0006aaa58a664743e8cf506992f218b1d8a82f79da5566eb4b8a1845464909fd2c1abeb1b5d2452f9
3fdc7fb74669ed239016b4e78c53bc31df920d26601abf3ee1e7cb085fda4579a4bae9a22b8efc0b88b74fc9714bc2dee22f83199aa842
9a6c84ec92197de56f5cd
(3 rows)

postgres=# \set temp_priv_key `cat secret.key`
postgres=# select username,pgp_pub_decrypt(pwd::bytea,dearmor(:'temp_priv_key'),'yanwei') from tab_user where id = 4;
  username  | pgp_pub_decrypt 
------------+-----------------
 PostgreSQL | Bigdata@
(1 row)

或者通过
CREATE TABLE keys(v text);
INSERT INTO keys VALUES ('-----BEGIN PGP PUBLIC KEY BLOCK-----
Version: GnuPG v2.0.22 (GNU/Linux)

mQENBGEzoGgBCACxG8MyHzgygsr2s5Y+iKX6qIbzKzbv3gyiZV9ZUwN8pdhJKYpW
IlJDo4VVeHlVOzihzOJZkBQsTPCOl7y20Yv8gQjdTLuTmvu/BTDoJfMtAgY+IzWU
hAiEj02Vx50HEPcH4iqHWScPseNQ1RpxnvdNaJzBH0P7zrXVu9mM6fRMYmo1OLPz
8vQpbBIs2o3sqa/j+V2cu8vujHluKMya/9yqT2uAY22plKcEgyUxdXP6DpDwM1J2
6AciRCY1ad8htOb0nIpKWbRraM8eAOnI0rwMEYYA7pmwdk6UCOG+jDJhagMxvrrj
X9MgeocxKz8ZCe1MQ5CrrX4cRlsD4wrJMxn/ABEBAAG0BnlhbndlaYkBOQQTAQIA
IwUCYTOgaAIbAwcLCQgHAwIBBhUIAgkKCwQWAgMBAh4BAheAAAoJEM8TC6JCmKbS
CdIIAIxVbmq4gEbhqlAXqXiEx2RigPaCDn7yXkcG0lCvTsvaoouNGwE83TbcDzkF
DwvZ26xqyVdUE0Dp7AsuM3tjLy02lg6Mqk69lGYDLCw7fgQzLC4FSnn+p6YVMQt0
w1UIXuq5jA6RLBgYxwnJ0HjR7hsIDuD1DqIFc9ziZxXqachB0xe0FJtMoM8xyyRT
/4y9+bE1a53EGBqQWXAylC/IXozpyvlHxA9pHKR58QCRA3foCKTN1TST18ZH/Nti
vigRYJ4FNQYTRaaZlQOmJt0bkfYZPLyfHwXi4H7O8xO+k3q2axNn4Cj8u+zIyFcp
11v7qAlBQZs7h7giMQJbLh9O4X+5AQ0EYTOgaAEIAM1w0ZeM0+KKDa83ArCKIPQT
zYADNCOAiISp6a315zjqebw7iBRCnG+2hhYrWLBUNKy4u1+veZJdpXTEIx2juCN7
LPMlWTiOwW1ChxA9adNCnXZR9FInno0PE5G/vaB0V0MoH7R9UfjZOOATuUjinvwm
5jm5SEoXArExJpvcdWXYQRTldBQV+s2W3fJZ/aG+zWBFBK+hB6fVGQwONebKx61z
PNbQo3K2HvkUzGLEDjBv9fE7gsqdJpC3d0ewTgcN+OOSOh7EL+AF3yfr8yJmUv7w
erK0QmU4aSobvBdhqTW0WtZL79HGROsM2VOROa/kaY9aT+3Uk/VsHJVNQ4AQsBUA
EQEAAYkBHwQYAQIACQUCYTOgaAIbDAAKCRDPEwuiQpim0n5sB/9qwrtSj3SWvio/
yttRzXLmIiZ43NkI2OJqzF1T3G46Ioyc9ItRoILC2uTQuTl6tyNeGvL2rukoj17w
aLAePSgXI0gEH/YkW7wz7hbidmMB6pyFFWt5e3YR5YE2PLEMrgCNbZfEBMQZd49P
oRNX0Sc82C2fOJLxlUgOCJGLCeFFRuwNSdPJb1yCNZkKhkcRiz9CsH7SsMlTrpic
bgBdwYtsRZfSxrlAPm5JLXANFFb29u9H1ZCPfXvF8K5TBcfstfLGG4dOe9fR0Zaq
J+ong7vLiKNcv8HhMsEX61hAgfqdxgAr16BCLdEM7VsI5KiONciiP2UIy5X+9Ggu
Cuf24COU
=N0nR
-----END PGP PUBLIC KEY BLOCK-----');

insert into tab_user values(4,'PostgreSQL',pgp_pub_encrypt('Bigdata@',dearmor(keys.v)));



复制



复制

PGP 函数的选项

选项 描述 取值 适用函数
cipher-algo 使用的密码算法。 bf、aes128（默认值）、aes192、aes256；使用 OpenSSL 时还支持：3des、cast5 pgp_sym_encrypt、pgp_pub_encrypt
compress-algo 使用的压缩算法，只有编译 PostgreSQL 时使用了 zlib 参数可用。 0，不压缩，默认值；1，ZIP 压缩；2，ZLIB 压缩（ZIP 加上元数据和 CRC） pgp_sym_encrypt、pgp_pub_encrypt
compress-level 压缩级别，级别越高结果越小但速度更慢，0 表示不压缩 0、1-9，默认为 6 pgp_sym_encrypt、pgp_pub_encrypt
convert-crlf 加密时是否将 \n 转换为 \r\n 并且解密时执行相反的转换，RFC 4880 指定文本数据需要使用 \r\n 作为换行符。 0（默认值）、1 pgp_sym_encrypt、pgp_pub_encrypt
pgp_sym_decrypt、pgp_pub_decrypt
disable-mdc 不使用 SHA-1 保护数据，仅用于兼容古老的 PGP 产品。 0（默认值）、1 pgp_sym_encrypt、pgp_pub_encrypt
sess-key 使用单独的会话密钥。公钥加密总是使用单独的会话密钥；该选项用于对称密钥加密，因为它默认直接使用 S2K 密钥。 0（默认值）、1 pgp_sym_encrypt
s2k-mode 使用的 S2K 算法。 0，不使用 salt，危险；1，使用 salt 但是迭代固定次数；3（默认值），使用 salt 同时迭代次数可变。 pgp_sym_encrypt
s2k-count S2K 算法的迭代次数。 大于等于 1024 并且小于等于 65011712，默认为 65536 到 253952 之间的随机数。 pgp_sym_encrypt 并且 s2k-mode=3
s2k-digest-algo S2K 计算时的摘要算法。 md5、sha1（默认值） pgp_sym_encrypt
s2k-cipher-algo 加密单独会话密钥时使用的密码。 bf、aes、aes128、aes192、aes256，默认使用 cipher-algo 的算法。 pgp_sym_encrypt
unicode-mode 是否将文本数据在数据库内部编码和 UTF-8 之间来回转换。如果数据库已经是 UTF-8、不会执行转换，但是消息将被标记为 UTF-8；如果没有指定这个选项就不会被标记。 0（默认值）、1 pgp_sym_encrypt、pgp_pub_encry

2.3 原始加密函数

原始加密函数仅仅会对数据进行一次加密，不支持 PGP 加密的任何高级功能，因此存在以下主要问题：

  1. 直接将用户密钥作为加密密钥。

  2. 不提供任何完整性检查校验加密后的数据是否被修改。

  3. 需要用户自己关联所有的加密参数，包括初始值（IV）。

  4. 不支持文本数据。

    因此，在引入了 PGP 加密之后，不建议使用这些原始加密函数，这里就不做测试了

    encrypt(data bytea, key bytea, type text) returns bytea
decrypt(data bytea, key bytea, type text) returns bytea

encrypt_iv(data bytea, key bytea, iv bytea, type text) returns bytea
decrypt_iv(data bytea, key bytea, iv bytea, type text) returns bytea

    复制

    其中，data 是需要加密的数据；type 用于指定加密方法。type 参数的语法如下：

    其中*algorithm*是下列之一：

    • bf Blowfish
    • aes AES (Rijndael-128, -192 或 -256)

    并且*mode*是下列之一：

    • cbc 下一个块依赖前一个（默认）
    • ecb 每一个块被独立加密（只用于测试）

    并且*padding*是下列之一：

    • pkcs 数据可以是任意长度（默认）
    • none 数据必须是密码块尺寸的倍数

    因此，例如这些是等效的：

    encrypt(data, 'fooz', 'bf')
encrypt(data, 'fooz', 'bf-cbc/pad:pkcs')

    复制

2.4 随机数据函数

gen_random_bytes(count integer) returns bytea

复制

返回*count*个密码上强壮的随机字节。一次最多可以抽取 1024 个字节。这是为了避免耗尽随机数发生池。

gen_random_uuid() 返回 uuid

复制

返回一个版本 4 的（随机的）UUID。（已过时，此函数现在也包含在核心PostgreSQL中。）

2.5 pgcrypto 配置

pgcrypto 可以根据 PostgreSQL 编译时的 configure 脚本进行自我配置，相关的选项包括 --with-zlib 以及 --with-openssl。

如果编译时使用了 zlib 选项，PGP 加密函数可以在加密之前对数据进行压缩。如果编译时使用了 OpenSSL 选项，PGP 加密函数可以支持更多的算法；同时公钥加密函数速度会更快，因为 OpenSSL 提供了优化的 BIGNUM 函数。下表比较了使用或者不使用 OpenSSL 时支持的功能：

使用和不用 OpenSSL 的功能总结

功能 内建 使用 OpenSSL
MD5 yes yes
SHA1 yes yes
SHA224/256/384/512 yes yes
其他摘要算法 no yes (注意 1)
Blowfish yes yes
AES yes yes
DES/3DES/CAST5 no yes
原始加密 yes yes
PGP 对称加密 yes yes
PGP 公钥加密 yes yes

2.6 NULL 处理

按照 SQL 中的标准，只要任何参数是 NULL， 所有的函数都会返回 NULL。在不当使用时这可能会导致安全风险。

2.7 安全性限制

所有pgcrypto函数都在数据库服务器内部运行。这意味着在pgcrypto和客户端应用之间移动的所有数据和口令都是明文。因此，你必须：

  1. 本地连接或者使用 SSL 连接。
  2. 信任系统管理员和数据库管理员。

如果你不能这样做，那么最好在客户端应用中进行加密。

3 参考文档

关于 pgcrypto 模块的更多信息可以参考 PostgreSQL 官方文档
不剪发的Tony老师PostgreSQL 数据加密之 pgcrypto

postgresql数据安全
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
1人已赞赏
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

文章被以下合辑收录

Murkey的PostgreSQL学习之旅（共40篇）
个人PG的学习总结

评论

目录
  • 1 Postgresql的加密技术
  • 1 TDE 数据库加密
    • 1.1 下载
    • 1.2 上传到服务器，并解压
    • 1.3 编译和安装软件
    • 1.4 设置初始化变量
    • 1.5 初始化安装
    • 1.6 启动并测试加密效果
    • 1.7 块级别查看
  • 2 pgcrypto使用
    • 2.1 单行加密
      • 2.2.1通用哈希函数
      • 2.2.2 密码hash函数
    • 2.2 双向加密
      • PGP 加密函数
    • 2.3 原始加密函数
    • 2.4 随机数据函数
    • 2.5 pgcrypto 配置
    • 2.6 NULL 处理
    • 2.7 安全性限制
  • 3 参考文档