postgresql 加解密

本文详细介绍了PostgreSQL中的pgcrypto模块,包括通用哈希函数、密码哈希函数、PGP加密函数和一些相关选项。这些函数提供了一种在数据库中加密和解密数据的方法,支持多种加密算法和安全特性,如使用随机盐和适应性加密强度。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

F.20. pgcrypto

The pgcrypto module provides cryptographic functions for PostgreSQL.

F.20.1. General hashing functions

F.20.1.1. digest()

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

Computes a binary hash of the given datatype is the algorithm to use. Standard algorithms are md5sha1sha224sha256sha384 and sha512. If pgcrypto was built with OpenSSL, more algorithms are available, as detailed in Table F-21.

If you want the digest as a hexadecimal string, use encode() on the result. For example:

    CREATE OR REPLACE FUNCTION sha1(bytea) returns text AS $$
      SELECT encode(digest($1, 'sha1'), 'hex')
    $$ LANGUAGE SQL STRICT IMMUTABLE;
   

F.20.1.2. hmac()

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

Calculates hashed MAC for data with key keytype is the same as in digest().

This is similar to digest() but the hash can only be recalculated knowing the key. This prevents the scenario of someone altering data and also changing the hash to match.

If the key is larger than the hash block size it will first be hashed and the result will be used as key.

F.20.2. Password hashing functions

The functions crypt() and gen_salt() are specifically designed for hashing passwords. crypt() does the hashing and gen_salt() prepares algorithm parameters for it.

The algorithms in crypt() differ from usual hashing algorithms like MD5 or SHA1 in the following respects:

  1. They are slow. As the amount of data is so small, this is the only way to make brute-forcing passwords hard.

  2. They use a random value, called the salt, so that users having the same password will have different encrypted passwords. This is also an additional defense against reversing the algorithm.

  3. They include the algorithm type in the result, so passwords hashed with different algorithms can co-exist.

  4. Some of them are adaptive — that means when computers get faster, you can tune the algorithm to be slower, without introducing incompatibility with existing passwords.

Table F-18. Supported algorithms for crypt()

Algorithm Max password length Adaptive? Salt bits Description
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

F.20.2.1. crypt()

    crypt(password text, salt text) returns text
   

Calculates a crypt(3)-style hash of password. When storing a new password, you need to use gen_salt() to generate a new salt value. To check a password, pass the stored hash value as salt, and test whether the result matches the stored value.

Example of setting a new password:

    UPDATE ... SET pswhash = crypt('new password', gen_salt('md5'));
   

Example of authentication:

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

This returns true if the entered password is correct.

F.20.2.2. gen_salt()

    gen_salt(type text [, iter_count integer ]) returns text
   

Generates a new random salt string for use in crypt(). The salt string also tells crypt() which algorithm to use.

The type parameter specifies the hashing algorithm. The accepted types are: desxdesmd5 and bf.

The iter_count parameter lets the user specify the iteration count, for algorithms that have one. The higher the count, the more time it takes to hash the password and therefore the more time to break it. Although with too high a count the time to calculate a hash may be several years — which is somewhat impractical. If the iter_count parameter is omitted, the default iteration count is used. Allowed values for iter_count depend on the algorithm:

Table F-19. Iteration counts for crypt()

Algorithm Default Min Max
xdes 725 1
### PostgreSQL 加密功能概述 PostgreSQL 提供多种级别的加密支持,确保数据在网络传输、存储和访问过程中的安全性。主要的加密机制涵盖了字段级加密、行级加密以及磁盘级加密。 #### 字段级加密 为了保护敏感字段的数据,在应用程序层面可以采用对称或非对称算法来处理特定列的信息。这种方式允许开发者针对具体需求设计个性化的加解密逻辑[^1]。 ```sql CREATE EXTENSION pgcrypto; INSERT INTO users (id, name, encrypted_ssn) VALUES (1, 'Alice', crypt('herSSNValue', gen_salt('bf'))); ``` 此代码片段展示了如何利用 `pgcrypto` 扩展模块实现 SSN 列的单向哈希化存储。 #### 行级加密 通过自定义函数或者触发器可以在每一行记录写入时自动执行加密操作,并在读取时进行相应的解码动作。这种方法适用于更细粒度的安全控制场景下。 ```sql CREATE OR REPLACE FUNCTION encrypt_row() RETURNS trigger AS $$ BEGIN NEW.encrypted_data := public.pgp_sym_encrypt(NEW.plain_text_column::text,'your-key'); RETURN NEW; END $$ LANGUAGE plpgsql; CREATE TRIGGER before_insert_update BEFORE INSERT OR UPDATE ON your_table FOR EACH ROW EXECUTE PROCEDURE encrypt_row(); ``` 上述 PL/pgSQL 函数配合触发器实现了表内某列内容基于会话密钥的透明加密/解密流程。 #### 磁盘级加密 对于整个文件系统的加密,则依赖于操作系统层面上的技术方案,比如 Linux 的 LUKS 或 Windows BitLocker 。而从数据库角度出发,可以通过配置参数启用 WAL 日志压缩并设置合理的备份策略间接增强物理介质上持久化对象的安全防护能力[^3]。 另外,PostgreSQL 自身也提供了某些形式下的静态数据保护手段——即当客户端请求访问被标记为需加密的对象时,服务端负责即时完成相应转换工作而不暴露明文给外部环境。 #### 官方文档与最佳实践建议 官方文档详尽描述了有关 SSL/TLS 连接认证、PGP 密钥管理以及其他高级特性等内容;遵循其指导原则有助于构建稳健可靠的加密体系结构[^4]。 - 尽量减少特权账户数量及其权限范围; - 记录详细的审计日志便于事后审查分析。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值