postgresql 加解密

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

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 16777215
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值