postgresql 加解密

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 data. type is the algorithm to use. Standard algorithms are md5, sha1, sha224, sha256, sha384 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 key. type 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: des, xdes, md5 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