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=#
解密函数
szsfs20220220=# select convert_from(decrypt('\x39c3c665757a0ff973b83fb98cc3d63f','aa','aes'),'SQL_ASCII'); convert_from -------------- 123456 (1 行记录)
aes加解密函数简单介绍
View Codedata 是需要加密的数据；type 用于指定加密方法
ASE方式加密：
szsfs20220220=# select encrypt('postgres','abc','aes'); encrypt ------------------------------------ \xd664687424b2806001d0744177284420 (1 行记录) szsfs20220220=#
解密：
szsfs20220220=# select convert_from(decrypt('\xd664687424b2806001d0744177284420','abc','aes'),'SQL_ASCII'); convert_from -------------- postgres (1 行记录) szsfs20220220=#
建表测试一下
创建表及插入数据
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=#
解密后可以看出加密前的密码
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=#
原始加密函数
这些函数只对数据运行密码；他们没有任何 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
其他加密解密方案
pgcrypto加密还支持很多如md5，bf等详细可以查看官方文档
PGSQL数据库层面的加密解密
需求：需要将现在的用户关键信息在数据库层面做加密。
--安装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') ;
备份用户信息表：
|
1
2
|
create table base_user_detail as ( select * from base_user);ALTER TABLE base_user_detail ADD PRIMARY KEY (id) ; |
加密用户表里面的手机号信息
|
1
|
update base_user_detail set mobile=cts_encrypt(mobile,'0000000ctsdev123') |
对应的Java应用层面的加解密：
package com.todaytech.pwp.acl.func.accountmanagement;/** @author Alan -liu @Email AlanLiu_2021@163.com @Web: https://www.cnblogs.com/ios9 @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; } }
您可能感兴趣的文章:
- postgresql修改自增序列操作
- PostgreSQL 实现登录及修改密码操作
- Postgresql 默认用户名与密码的操作
- PostgreSQL表膨胀监控案例(精确计算)
- postgresql查看表和索引的情况,判断是否膨胀的操作
- 在PostgreSQL中设置表中某列值自增或循环方式