readme.pgcrypto

来自「PostgreSQL 8.2中增加了很多企业用户所需要的功能和性能上的提高,其开」· PGCRYPTO 代码 · 共 711 行 · 第 1/2 页

pgcrypto - cryptographic functions for PostgreSQL
=================================================
Marko Kreen <[e-mail]>

// Note: this document is in asciidoc format.


1.  Installation
-----------------

Run following commands:

    make
    make install
    make installcheck

The `make installcheck` command is important.  It runs regression tests
for the module.  They make sure the functions here produce correct
results.

Next, to put the functions into a particular database, run the commands in
file pgcrypto.sql, which has been installed into the shared files directory.

Example using psql:

    psql -d DBNAME -f pgcrypto.sql


2.  Notes
----------

2.1.  Configuration
~~~~~~~~~~~~~~~~~~~~

pgcrypto configures itself according to the findings of main PostgreSQL
`configure` script.  The options that affect it are `--with-zlib` and
`--with-openssl`.

When compiled with zlib, PGP encryption functions are able to
compress data before encrypting.

When compiled with OpenSSL there will be more algorithms available.
Also public-key encryption functions will be faster as OpenSSL
has more optimized BIGNUM functions.

Summary of functionality with and without OpenSSL:

`----------------------------`---------`------------
 Functionality                built-in   OpenSSL
----------------------------------------------------
 MD5                          yes       yes
 SHA1                         yes       yes
 SHA224/256/384/512           yes       yes (3)
 Any other digest algo        no        yes (1)
 Blowfish                     yes       yes
 AES                          yes       yes (2)
 DES/3DES/CAST5               no        yes
 Raw encryption               yes       yes
 PGP Symmetric encryption     yes       yes
 PGP Public-Key encryption    yes       yes
----------------------------------------------------

1. Any digest algorithm OpenSSL supports is automatically picked up.
   This is not possible with ciphers, which need to be supported
   explicitly.

2. AES is included in OpenSSL since version 0.9.7.  If pgcrypto is
   compiled against older version, it will use built-in AES code,
   so it has AES always available.

3. SHA2 algorithms were added to OpenSSL in version 0.9.8.  For
   older versions, pgcrypto will use built-in code.


2.2.  NULL handling
~~~~~~~~~~~~~~~~~~~~

As standard in SQL, all functions return NULL, if any of the arguments
are NULL.  This may create security risks on careless usage.


2.3.  Security
~~~~~~~~~~~~~~~

All the functions here run inside database server.  That means that all
the data and passwords move between pgcrypto and client application in
clear-text.  Thus you must:

1.  Connect locally or use SSL connections.
2.  Trust both system and database administrator.

If you cannot, then better do crypto inside client application.


3.  General hashing
--------------------

3.1.  digest(data, type)
~~~~~~~~~~~~~~~~~~~~~~~~~

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

Type is here the algorithm to use.  Standard algorithms are `md5` and
`sha1`, although there may be more supported, depending on build
options.

Returns binary hash.

If you want hexadecimal string, use `encode()` on result.  Example:

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


3.2.  hmac(data, key, type)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~

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

Calculates Hashed MAC over data.  `type` is the same as in `digest()`.
If the key is larger than hash block size it will first hashed and the
hash will be used as key.

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

Returns binary hash.



4.  Password hashing
---------------------

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 following respects:

1. They are slow.  As the amount of data is so small, this is only
   way to make brute-forcing passwords hard.
2. Include random 'salt' with result, so that users having same
   password would have different crypted passwords.  This is also
   additional defense against reversing the algorithm.
3. Include algorithm type in the result, so passwords hashed with
   different algorithms can co-exist.
4. Some of them are adaptive - that means after computers get
   faster, you can tune the algorithm to be slower, without
   introducing incompatibility with existing passwords.

Supported algorithms:
`------`-------------`---------`----------`---------------------------
 Type   Max password  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
----------------------------------------------------------------------


4.1.  crypt(password, salt)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~

  crypt(password text, salt text) RETURNS text

Calculates UN*X crypt(3) style hash of password.  When storing new
password, you need to use function `gen_salt()` to generate new salt.
When checking password you should use existing hash as salt.

Example - setting new password:

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

Example - authentication:

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

returns true or false whether the entered password is correct.
It also can return NULL if `pswhash` field is NULL.


4.2.  gen_salt(type)
~~~~~~~~~~~~~~~~~~~~~

  gen_salt(type text) RETURNS text

Generates a new random salt for usage in `crypt()`.  For adaptible
algorithms, it uses the default iteration count.

Accepted types are: `des`, `xdes`, `md5` and `bf`.


4.3.  gen_salt(type, rounds)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

  gen_salt(type text, rounds integer) RETURNS text

Same as above, but lets user specify iteration count for some
algorithms.  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 count the time to calculate a hash may be several years
- which is somewhat impractical.

Number is algorithm specific:

`-----'---------'-----'----------
 type   default   min   max
---------------------------------
 `xdes`     725     1   16777215
 `bf`         6     4         31
---------------------------------

In case of xdes there is a additional limitation that the count must be
a odd number.

Notes:

- Original DES crypt was designed to have the speed of 4 hashes per
  second on the hardware of that time.
- Slower than 4 hashes per second would probably dampen usability.
- Faster than 100 hashes per second is probably too fast.
- See next section about possible values for `crypt-bf`.


4.4.  Comparison of crypt and regular hashes
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Here is a table that should give overview of relative slowness
of different hashing algorithms.

* The goal is to crack a 8-character password, which consists:
  1.  Only of lowercase letters
  2.  Numbers, lower- and uppercase letters.
* The table below shows how much time it would take to try all
  combinations of characters.
* The `crypt-bf` is featured in several settings - the number
  after slash is the `rounds` parameter of `gen_salt()`.

`------------'----------'--------------'--------------------
Algorithm     Hashes/sec  Chars: [a-z]   Chars: [A-Za-z0-9]
------------------------------------------------------------
crypt-bf/8            28     246 years         251322 years
crypt-bf/7            57     121 years         123457 years
crypt-bf/6           112      62 years          62831 years
crypt-bf/5           211      33 years          33351 years
crypt-md5           2681     2.6 years           2625 years
crypt-des         362837        7 days             19 years
sha1              590223        4 days             12 years
md5              2345086         1 day              3 years
------------------------------------------------------------

* The machine used is 1.5GHz Pentium 4.
* crypt-des and crypt-md5 algorithm numbers are taken from
  John the Ripper v1.6.38 `-test` output.
* MD5 numbers are from mdcrack 1.2.
* SHA1 numbers are from lcrack-20031130-beta.
* `crypt-bf` numbers are taken using simple program that loops
  over 1000 8-character passwords.  That way I can show the speed with
  different number of rounds.  For reference: `john -test` shows 213
  loops/sec for crypt-bf/5.  (The small difference in results is in
  accordance to the fact that the `crypt-bf` implementation in pgcrypto
  is same one that is used in John the Ripper.)

Note that "try all combinations" is not a realistic exercise.
Usually password cracking is done with the help of dictionaries, which
contain both regular words and various mutations of them.  So, even
somewhat word-like passwords could be cracked much faster than the above
numbers suggest, and a 6-character non-word like password may escape
cracking.  Or not.


5.  PGP encryption
-------------------

The functions here implement the encryption part of OpenPGP (RFC2440)
standard.   Supported are both symmetric-key and public-key encryption.


5.1.  Overview
~~~~~~~~~~~~~~~

Encrypted PGP message consists of 2 packets:

- Packet for session key - either symmetric- or public-key encrypted.
- Packet for session-key encrypted data.

When encrypting with password:

1. Given password is hashed using String2Key (S2K) algorithm.  This
   is rather similar to `crypt()` algorithm - purposefully slow
   and with random salt - but it produces a full-length binary key.
2. If separate session key is requested, new random key will be
   generated.  Otherwise S2K key will be used directly as session key.
3. If S2K key is to be used directly, then only S2K settings will be put
   into session key packet.  Otherwise session key will be encrypted with
   S2K key and put into session key packet.

When encrypting with public key:

1. New random session key is generated.
2. It is encrypted using public key and put into session key packet.

Now common part, the session-key encrypted data packet:

1. Optional data-manipulation: compression, conversion to UTF-8,
   conversion of line-endings.
2. Data is prefixed with block of random bytes.  This is equal
   to using random IV.
3. A SHA1 hash of random prefix and data is appended.
4. All this is encrypted with session key.


5.2.  pgp_sym_encrypt(data, psw)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

  pgp_sym_encrypt(data text, psw text [, options text] ) RETURNS bytea
  pgp_sym_encrypt_bytea(data bytea, psw text [, options text] ) RETURNS bytea

Return a symmetric-key encrypted PGP message.

Options are described in section 5.8.


5.3. pgp_sym_decrypt(msg, psw)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

  pgp_sym_decrypt(msg bytea, psw text [, options text] ) RETURNS text
  pgp_sym_decrypt_bytea(msg bytea, psw text [, options text] ) RETURNS bytea

Decrypt a symmetric-key encrypted PGP message.

Decrypting bytea data with `pgp_sym_decrypt` is disallowed.
This is to avoid outputting invalid character data.  Decrypting
originally textual data with `pgp_sym_decrypt_bytea` is fine.

Options are described in section 5.8.


5.4.  pgp_pub_encrypt(data, pub_key)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

  pgp_pub_encrypt(data text, key bytea [, options text] ) RETURNS bytea
  pgp_pub_encrypt_bytea(data bytea, key bytea [, options text] ) RETURNS bytea

Encrypt data with a public key.  Giving this function a secret key will
produce a error.

Options are described in section 5.8.