PostgreSQL 数据安全之数据加密
数据库作为存储电子数据的主要方式，需要通过加密技术保护敏感数据，如个人医疗记录或金融交易数据。本文给大家介绍一些 PostgreSQL 提供的加密技术。PostgreSQL 支持多个不同级别的灵活加密技术，可以保护数据不因数据库服务器被盗、内部管理员或者不安全的网络传输而导致泄露。
密码加密
PostgreSQL 支持用户密码的加密存储（加密方式由 password_encryption 参数决定），确保数据库管理员无法得到用户的密码。
如果我们采用了 SCRAM 或者 MD5 加密的客户端认证，明文密码甚至不会在服务器中出现，因为客户端在发送之前就已经进行了密码加密。推荐使用 SCRAM 加密，因为它是一个互联网标准，而且比 PostgreSQL 使用的 MD5 认证协议更安全。
注意，某些旧的客户端可能不支持 SCRAM 认证机制，因此无法使用 SCRAM-SHA-256 加密后的密码。关于密码认证的详细信息可以参考官方文档。
字段加密
PostgreSQL pgcrypto 模块可以用于加密指定字段。这种方式对于敏感数据的包含非常有用。查询数据时，客户端提供解密密钥，数据在服务器端解密后发送给客户端。
解密后的数据和解密密钥在解密以及传输过程中会在服务器中短暂存在，这一短暂时刻可能导致拥有数据库服务器完全访问权限的用户（例如系统管理员）拦截获取数据和密钥。
存储加密
存储加密可以在文件系统级别或者块级别实现。Linux 文件系统加密选项包括 eCryptfs 和 EncFS，FreeBSD 使用 PEFS 加密技术。块级别或者整盘加密选项包括 Linux 操作系统的 dm-crypt + LUKS ，以及 FreeBSD 操作系统的 GEOM 模块 geli 和 gbde。包括 Windows 在内的许多其他操作系统都支持加密功能。
存储加密技术可以防止计算机被盗后未加密数据的泄露。这种方式无法组织文件系统加载之后的攻击，因为一旦加载文件系统，操作系统就提供了一个未加密的数据视图。尽管如此，为了能够加载文件系统，用户必须采用某种方式将加密密钥传递给操作系统，有时候密钥就存储在加载磁盘的主机中。
传输加密
SSL 连接可以加密网络传输中的所有数据，包括：密码、查询语句以及返回的结果。pg_hba.conf 配置文件可以允许管理员指定哪些主机可以使用非加密连接（host），以及哪些主机需要使用 SSL 加密连接（hostssl）。同时，客户端可以指定只通过 SSL 连接到服务器。
GSSAPI 加密连接可以加密网络传输中的所有数据，包括查询语句和返回的结果（密码不会通过网络传输）。pg_hba.conf 配置文件可以允许管理员指定哪些主机可以使用非加密连接（host），以及哪些主机需要使用 GSSAPI 加密连接（hostgssenc）。同时，客户端可以指定只通过 SSL 连接到服务器（gssencmode=require）。
除此之外，Stunnel 或者 SSH 也可用于加密传输。
认证加密
PostgreSQL 客户端和服务器都可以提供相互的 SSL 认证。认证加密需要客户端和服务器的一些额外配置，但是可以提供比密码更安全的身份认证。
认证加密可以防止机器伪装成数据库服务器读取客户端发送的密码，同时也可以防止“中间人”攻击，也就是在客户端和服务器中间的机器伪装成服务器读取和传递它们之间的所有数据。
应用加密
如果数据库服务器的管理员不可信，需要使用客户端加密技术。这样的话明文数据首先会在客户端应用中进行加密，网络中传输的就是密文数据，数据库中不会出现明文数据。这种方式也被称为全加密数据库解决方案。
注意，这种方式查询的数据结果需要在客户端应用中进行解密后才能使用，无法在数据库中使用 SQL 进行操作。
安装加密扩展插件 pgcrypto
使用数据库的加密与解密方法，先将部分注意事项写下备查：
首先要安装pgcrypto模块，安装方法进入postgresql的源安装包文件夹，进入contrib目录，找到pgcrypto文件夹，进入进行编译安装，命令如下：
make USE_PGXS=1
make install
安装插件
create extension pgcrypto;
查看插件是否安装成功
Server [localhost]:
Database [postgres]:
Port [5432]:
Username [postgres]:
用户 postgres 的口令：
psql (14.0)
输入 "help" 来获取帮助信息.
postgres=# \c szsfs20220220
您现在已经连接到数据库 "szsfs20220220",用户 "postgres".
szsfs20220220=# select * from pg_available_extensions ;
name | default_version | installed_version | comment
---------------------+-----------------+-------------------+------------------------------------------------------------------------
adminpack | 2.1 | | administrative functions for PostgreSQL
amcheck | 1.3 | | functions for verifying relation integrity
autoinc | 1.0 | | functions for autoincrementing fields
bloom | 1.0 | | bloom access method - signature file based index
bool_plperl | 1.0 | | transform between bool and plperl
bool_plperlu | 1.0 | | transform between bool and plperlu
btree_gin | 1.3 | | support for indexing common datatypes in GIN
btree_gist | 1.6 | | support for indexing common datatypes in GiST
citext | 1.6 | | data type for case-insensitive character strings
cube | 1.5 | | data type for multidimensional cubes
dblink | 1.2 | | connect to other PostgreSQL databases from within a database
dict_int | 1.0 | | text search dictionary template for integers
dict_xsyn | 1.0 | | text search dictionary template for extended synonym processing
dummy_index_am | 1.0 | | dummy_index_am - index access method template
earthdistance | 1.1 | | calculate great-circle distances on the surface of the Earth
file_fdw | 1.0 | | foreign-data wrapper for flat file access
fuzzystrmatch | 1.1 | | determine similarities and distance between strings
hstore | 1.8 | | data type for storing sets of (key, value) pairs
hstore_plperl | 1.0 | | transform between hstore and plperl
hstore_plperlu | 1.0 | | transform between hstore and plperlu
hstore_plpython2u | 1.0 | | transform between hstore and plpython2u
hstore_plpython3u | 1.0 | | transform between hstore and plpython3u
hstore_plpythonu | 1.0 | | transform between hstore and plpythonu
insert_username | 1.0 | | functions for tracking who changed a table
intagg | 1.1 | | integer aggregator and enumerator (obsolete)
isn | 1.2 | | data types for international product numbering standards
jsonb_plperl | 1.0 | | transform between jsonb and plperl
jsonb_plperlu | 1.0 | | transform between jsonb and plperlu
jsonb_plpython2u | 1.0 | | transform between jsonb and plpython2u
jsonb_plpython3u | 1.0 | | transform between jsonb and plpython3u
jsonb_plpythonu | 1.0 | | transform between jsonb and plpythonu
lo | 1.1 | | Large Object maintenance
ltree | 1.2 | | data type for hierarchical tree-like structures
ltree_plpython2u | 1.0 | | transform between ltree and plpython2u
ltree_plpython3u | 1.0 | | transform between ltree and plpython3u
ltree_plpythonu | 1.0 | | transform between ltree and plpythonu
moddatetime | 1.0 | | functions for tracking last modification time
old_snapshot | 1.0 | | utilities in support of old_snapshot_threshold
pageinspect | 1.9 | | inspect the contents of database pages at a low level
pgcrypto | 1.3 | 1.3 | cryptographic functions
pgrowlocks | 1.2 | | show row-level locking information
pgstattuple | 1.5 | | show tuple-level statistics
pg_buffercache | 1.3 | | examine the shared buffer cache
pg_freespacemap | 1.2 | | examine the free space map (FSM)
pg_prewarm | 1.2 | | prewarm relation data
pg_surgery | 1.0 | | extension to perform surgery on a damaged relation
pldbgapi | 1.1 | 1.1 | server-side support for debugging PL/pgSQL functions
plperl | 1.0 | | PL/Perl procedural language
plperlu | 1.0 | | PL/PerlU untrusted procedural language
plpgsql | 1.0 | 1.0 | PL/pgSQL procedural language
plpython2u | 1.0 | | PL/Python2U untrusted procedural language
plpython3u | 1.0 | | PL/Python3U untrusted procedural language
plpythonu | 1.0 | | PL/PythonU untrusted procedural language
plsample | 1.0 | | PL/Sample
pltcl | 1.0 | | PL/Tcl procedural language
pltclu | 1.0 | | PL/TclU untrusted procedural language
postgres_fdw | 1.1 | | foreign-data wrapper for remote PostgreSQL servers
spgist_name_ops | 1.0 | | Test opclass for SP-GiST
sslinfo | 1.2 | | information about SSL certificates
system_stats | 1.0 | | EnterpriseDB system statistics for PostgreSQL
tablefunc | 1.0 | | functions that manipulate whole tables, including crosstab
tcn | 1.0 | | Triggered change notifications
test_bloomfilter | 1.0 | | Test code for Bloom filter library
test_ext1 | 1.0 | | Test extension 1
test_ext2 | 1.0 | | Test extension 2
test_ext3 | 1.0 | | Test extension 3
test_ext4 | 1.0 | | Test extension 4
test_ext5 | 1.0 | | Test extension 5
test_ext6 | 1.0 | | test_ext6
test_ext7 | 1.0 | | Test extension 7
test_ext8 | 1.0 | | Test extension 8
test_ext_cyclic1 | 1.0 | | Test extension cyclic 1
test_ext_cyclic2 | 1.0 | | Test extension cyclic 2
test_ext_evttrig | 1.0 | | Test extension - event trigger
test_ginpostinglist | 1.0 | | Test code for ginpostinglist.c
test_integerset | 1.0 | | Test code for integerset
test_pg_dump | 1.0 | | Test pg_dump with an extension
test_predtest | 1.0 | | Test code for optimizer/util/predtest.c
test_rbtree | 1.0 | | Test code for red-black tree library
test_regex | 1.0 | | Test code for backend/regex/
tsm_system_rows | 1.0 | | TABLESAMPLE method which accepts number of rows as a limit
unaccent | 1.1 | | text search dictionary that removes accents
uuid-ossp | 1.1 | | generate universally unique identifiers (UUIDs)
xml2 | 1.1 | | XPath querying and XSLT
(91 行记录)
szsfs20220220=# \dx
已安装扩展列表
名称 | 版本 | 架构模式 | 描述
----------+------+------------+------------------------------------------------------
pgcrypto | 1.3 | public | cryptographic functions
pldbgapi | 1.1 | public | server-side support for debugging PL/pgSQL functions
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(3 行记录)
szsfs20220220=#
pgcrypto 提供了可逆加密算法：
加密函数
szsfs20220220=# select encrypt('123456','aa','aes');
encrypt
------------------------------------
\x39c3c665757a0ff973b83fb98cc3d63f
(1 行记录)
szsfs20220220=#View Code
解密函数
szsfs20220220=# select convert_from(decrypt('\x39c3c665757a0ff973b83fb98cc3d63f','aa','aes'),'SQL_ASCII');
convert_from
--------------
123456
(1 行记录)View Code
aes加解密函数简单介绍
encrypt(data bytea, key bytea, type text) --加密
decrypt(data bytea, key bytea, type text) --解密
View Code
data 是需要加密的数据；type 用于指定加密方法
ASE方式加密：
szsfs20220220=# select encrypt('postgres','abc','aes');
encrypt
------------------------------------
\xd664687424b2806001d0744177284420
(1 行记录)
szsfs20220220=#View Code
解密：
szsfs20220220=# select convert_from(decrypt('\xd664687424b2806001d0744177284420','abc','aes'),'SQL_ASCII');
convert_from
--------------
postgres
(1 行记录)
szsfs20220220=#View Code
建表测试一下
创建表及插入数据
szsfs20220220=# create table user_test(username varchar(20),password varchar(60));
CREATE TABLE
szsfs20220220=# insert into user_test values('miya',encode(encrypt('123','abc','aes'),'hex'));
INSERT 0 1
szsfs20220220=# insert into user_test values('kimi',encode(encrypt('456','abc','aes'),'hex'));
INSERT 0 1
szsfs20220220=# select * from user_test;
username | password
----------+----------------------------------
miya | a4bf9afce727dbd2805393a86a24096c
kimi | 84279efc7942ca7364abcce78db90b0b
(2 行记录)
szsfs20220220=#
View Code
解密后可以看出加密前的密码
szsfs20220220=# select convert_from(decrypt(decode(password,'hex'),'abc','aes'),'SQL_ASCII') as real_pw,* from user_test;
real_pw | username | password
---------+----------+----------------------------------
123 | miya | a4bf9afce727dbd2805393a86a24096c
456 | kimi | 84279efc7942ca7364abcce78db90b0b
(2 行记录)
szsfs20220220=#
View Code
原始加密函数
这些函数只对数据运行密码；他们没有任何 PGP 加密的高级功能。因此，它们存在一些主要问题：
- 他们直接使用用户密钥作为密码密钥。
- 它们不提供任何完整性检查，以查看加密数据是否被修改。
- 他们希望用户自己管理所有加密参数，甚至 IV。
- 他们不处理文本。
因此，随着 PGP 加密的引入，不鼓励使用原始加密函数。
encrypt(data bytea, key bytea, type text) 返回 bytea
decrypt(data bytea, key bytea, type text) 返回 bytea
encrypt_iv(data bytea, key bytea, iv bytea, type text) 返回 bytea
decrypt_iv(data bytea, key bytea, iv bytea, type text) 返回 bytea
View Code
其他加密解密方案
pgcrypto加密还支持很多如md5，bf等详细可以查看官方文档
PGSQL数据库层面的加密解密
需求：需要将现在的用户关键信息在数据库层面做加密。
--安装pgcrypto扩展
create extension pgcrypto;
select * from pg_available_extensions --查看pgcrypto是否安装成功
--加密
szsfs20220220=# select encode(encrypt('17817719973'::bytea,'0000000ctsdev123','aes-ecb'),'base64');
encode
--------------------------
gGBEj3ScUIF1Ow08cftkpA==
(1 行记录)
szsfs20220220=#
--解密
szsfs20220220=# select convert_from(decrypt(decode('gGBEj3ScUIF1Ow08cftkpA==','base64'),'0000000ctsdev123','aes-ecb'),'SQL_ASCII');
convert_from
--------------
17817719973
(1 行记录)
szsfs20220220=#
--为了以后方便直接建立一个加密函数（cts_encrypt） 参数1为明文，参数2为密钥
CREATE or replace FUNCTION cts_encrypt(var1 VARCHAR,var2 varchar(16))
RETURNS VARCHAR as $$
BEGIN
RETURN (SELECT encode(encrypt(var1::bytea,var2 :: bytea,'aes-ecb'),'base64'));
END; $$
language plpgsql;
测试加密
select cts_encrypt('18700000000','0000000ctsdev123') ;
encrypt
------------------------------------
XyFXGdD/gt8Fjc+lsjWLKg==
--解密函数cts_decrypt 参数1为密文，参数2为密钥
CREATE or replace FUNCTION cts_decrypt(var1 VARCHAR,var2 varchar(16))
RETURNS VARCHAR as $$
BEGIN
RETURN (select convert_from(decrypt(decode(var1,'base64'),var2 :: bytea,'aes-ecb'),'SQL_ASCII'));
END; $$
language plpgsql;
测试解密：
select cts_decrypt('XyFXGdD/gt8Fjc+lsjWLKg==','0000000ctsdev123') ;
备份用户信息表：
create table base_user_detail as ( select * from base_user);
ALTER TABLE base_user_detail ADD PRIMARY KEY (id) ;
加密用户表里面的手机号信息
update base_user_detail set mobile=cts_encrypt(mobile,'0000000ctsdev123')
对应的Java应用层面的加解密：
package com.todaytech.pwp.acl.func.accountmanagement;/**
@author Alan -liu
@Web:
@Create 2022-05-07 10:42
*/
import javax.crypto.Cipher;
import javax.crypto.spec.SecretKeySpec;
import org.apache.commons.codec.binary.Base64;
import org.apache.commons.lang3.StringUtils;
/**
*
* @author Alan - liu
* @date 2022/05/07 10:42
*
* ==============PostgreSql 加密和解密================================================
*
* --- 开启 加密插件
* create extension pgcrypto;
*
* ---- - 执行脚本
* CREATE or replace FUNCTION cts_encrypt(var1 VARCHAR,var2 varchar(16))
* RETURNS VARCHAR as $$
* BEGIN
* RETURN (SELECT encode(encrypt(var1::bytea,var2 :: bytea,'aes-ecb'),'base64'));
* END; $$
* language plpgsql;
*
*
*
* CREATE or replace FUNCTION cts_decrypt(var1 VARCHAR,var2 varchar(16))
* RETURNS VARCHAR as $$
* BEGIN
* RETURN (select convert_from(decrypt(decode(var1,'base64'),var2 :: bytea,'aes-ecb'),'SQL_ASCII'));
* END; $$
* language plpgsql;
*
*
* ----- 加密 解密
*
* select cts_encrypt(t.account_mobile,'0000000ctsdev123') "加密" ,
cts_decrypt(cts_encrypt(t.account_mobile,'0000000ctsdev123'),'0000000ctsdev123') "解密",
t.account_mobile from pwp_account t where t.account_code ='admin';
*
*
*
*
*
**/
public class PostgreSQLBase64 {
/**
*
*/
private static String MODEL = "AES/ECB/PKCS5Padding";
private static String useKey ="0000000ctsdev123";
public static String encrypt(String content) {
if(StringUtils.isEmpty(content)){
return content;
}
String result = content;
try {
byte[] contentBytes = content.getBytes("UTF-8");
SecretKeySpec skeySpec = new SecretKeySpec(useKey.getBytes("UTF-8"), "AES");
Cipher cipher = Cipher.getInstance(MODEL);
cipher.init(Cipher.ENCRYPT_MODE, skeySpec);
byte[] encryptResult = cipher.doFinal(contentBytes);
result = Base64.encodeBase64String(encryptResult);
//替换\r \n
result = result.replace("\n", "").replace("\r", "");
} catch (Exception ex) {
throw new RuntimeException(ex);
}
return result;
}
public static String decrypt(String content){
if(StringUtils.isEmpty(content)){
return content;
}
String result = content;
byte[] contentBytes =null;
try{
if(content.length()%4==0){
contentBytes =Base64.decodeBase64(content);
}else{
throw new RuntimeException("字符串"+content+"不是base64编码过的字符串！");
}
} catch (Exception ex) {
throw new RuntimeException(ex);
}
if(contentBytes!=null){
try{
SecretKeySpec skeySpec = new SecretKeySpec(useKey.getBytes("UTF-8"), "AES");
Cipher cipher = Cipher.getInstance(MODEL);
cipher.init(Cipher.DECRYPT_MODE, skeySpec);
byte[] decryptResult = cipher.doFinal(contentBytes);
if (decryptResult != null) {
result = new String(decryptResult, "UTF-8");
}
} catch (Exception ex) {
throw new RuntimeException(ex);
}
}
return result;
}
}
搜索
复制
为人：谦逊、激情、博学、审问、慎思、明辨、 笃行
学问：纸上得来终觉浅，绝知此事要躬行
为事：工欲善其事，必先利其器。
态度：道阻且长，行则将至；行而不辍，未来可期
