1
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
8
CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK
10
HREF="mailto:pgsql-docs@postgresql.org"><LINK
12
TITLE="PostgreSQL 9.1beta1 Documentation"
13
HREF="index.html"><LINK
15
TITLE="Additional Supplied Modules"
16
HREF="contrib.html"><LINK
18
TITLE="pg_buffercache"
19
HREF="pgbuffercache.html"><LINK
21
TITLE="pg_freespacemap"
22
HREF="pgfreespacemap.html"><LINK
25
HREF="stylesheet.css"><META
26
HTTP-EQUIV="Content-Type"
27
CONTENT="text/html; charset=ISO-8859-1"><META
29
CONTENT="2011-04-27T21:20:33"></HEAD
35
SUMMARY="Header navigation table"
47
>PostgreSQL 9.1beta1 Documentation</A
56
TITLE="pg_buffercache"
57
HREF="pgbuffercache.html"
66
TITLE="Additional Supplied Modules"
74
>Appendix F. Additional Supplied Modules</TD
80
TITLE="Additional Supplied Modules"
89
TITLE="pg_freespacemap"
90
HREF="pgfreespacemap.html"
111
> module provides cryptographic functions for
123
>F.28.1. General Hashing Functions</A
138
>digest(data text, type text) returns bytea
139
digest(data bytea, type text) returns bytea</PRE
141
> Computes a binary hash of the given <TT
148
> is the algorithm to use.
149
Standard algorithms are <TT
174
OpenSSL, more algorithms are available, as detailed in
176
HREF="pgcrypto.html#PGCRYPTO-WITH-WITHOUT-OPENSSL"
181
> If you want the digest as a hexadecimal string, use
185
> on the result. For example:
187
CLASS="PROGRAMLISTING"
188
>CREATE OR REPLACE FUNCTION sha1(bytea) returns text AS $$
189
SELECT encode(digest($1, 'sha1'), 'hex')
190
$$ LANGUAGE SQL STRICT IMMUTABLE;</PRE
207
>hmac(data text, key text, type text) returns bytea
208
hmac(data bytea, key text, type text) returns bytea</PRE
210
> Calculates hashed MAC for <TT
220
> is the same as in <CODE
226
> This is similar to <CODE
229
> but the hash can only be
230
recalculated knowing the key. This prevents the scenario of someone
231
altering data and also changing the hash to match.
234
> If the key is larger than the hash block size it will first be hashed and
235
the result will be used as key.
245
>F.28.2. Password Hashing Functions</A
248
> The functions <CODE
255
are specifically designed for hashing passwords.
259
> does the hashing and <CODE
263
prepares algorithm parameters for it.
266
> The algorithms in <CODE
269
> differ from usual hashing algorithms
270
like MD5 or SHA1 in the following respects:
278
> They are slow. As the amount of data is so small, this is the only
279
way to make brute-forcing passwords hard.
284
> They use a random value, called the <I
288
having the same password will have different encrypted passwords.
289
This is also an additional defense against reversing the algorithm.
294
> They include the algorithm type in the result, so passwords hashed with
295
different algorithms can co-exist.
300
> Some of them are adaptive — that means when computers get
301
faster, you can tune the algorithm to be slower, without
302
introducing incompatibility with existing passwords.
308
HREF="pgcrypto.html#PGCRYPTO-CRYPT-ALGORITHMS"
310
> lists the algorithms
311
supported by the <CODE
319
NAME="PGCRYPTO-CRYPT-ALGORITHMS"
323
>Table F-15. Supported Algorithms for <CODE
331
><COL><COL><COL><COL><COL><THEAD
336
>Max Password Length</TH
359
>Blowfish-based, variant 2a</TD
404
>Original UNIX crypt</TD
422
>crypt(password text, salt text) returns text</PRE
424
> Calculates a crypt(3)-style hash of <TT
428
When storing a new password, you need to use
432
> to generate a new <TT
436
To check a password, pass the stored hash value as <TT
440
and test whether the result matches the stored value.
443
> Example of setting a new password:
445
CLASS="PROGRAMLISTING"
446
>UPDATE ... SET pswhash = crypt('new password', gen_salt('md5'));</PRE
450
> Example of authentication:
452
CLASS="PROGRAMLISTING"
453
>SELECT pswhash = crypt('entered password', pswhash) FROM ... ;</PRE
458
> if the entered password is correct.
474
>gen_salt(type text [, iter_count integer ]) returns text</PRE
476
> Generates a new random salt string for use in <CODE
480
The salt string also tells <CODE
483
> which algorithm to use.
489
> parameter specifies the hashing algorithm.
490
The accepted types are: <TT
509
> parameter lets the user specify the iteration
510
count, for algorithms that have one.
511
The higher the count, the more time it takes to hash
512
the password and therefore the more time to break it. Although with
513
too high a count the time to calculate a hash may be several years
514
— which is somewhat impractical. If the <TT
518
parameter is omitted, the default iteration count is used.
519
Allowed values for <TT
522
> depend on the algorithm and
524
HREF="pgcrypto.html#PGCRYPTO-ICFC-TABLE"
531
NAME="PGCRYPTO-ICFC-TABLE"
535
>Table F-16. Iteration Counts for <CODE
543
><COL><COL><COL><COL><THEAD
589
> there is an additional limitation that the
590
iteration count must be an odd number.
593
> To pick an appropriate iteration count, consider that
594
the original DES crypt was designed to have the speed of 4 hashes per
595
second on the hardware of that time.
596
Slower than 4 hashes per second would probably dampen usability.
597
Faster than 100 hashes per second is probably too fast.
601
HREF="pgcrypto.html#PGCRYPTO-HASH-SPEED-TABLE"
603
> gives an overview of the relative slowness
604
of different hashing algorithms.
605
The table shows how much time it would take to try all
606
combinations of characters in an 8-character password, assuming
607
that the password contains either only lower case letters, or
608
upper- and lower-case letters and numbers.
612
> entries, the number after a slash is
625
NAME="PGCRYPTO-HASH-SPEED-TABLE"
629
>Table F-17. Hash Algorithm Speeds</B
634
><COL><COL><COL><COL><THEAD
768
> The machine used is a 1.5GHz Pentium 4.
779
> algorithm numbers are
780
taken from John the Ripper v1.6.38 <TT
791
> numbers are from mdcrack 1.2.
799
> numbers are from lcrack-20031130-beta.
807
> numbers are taken using a simple program that
808
loops over 1000 8-character passwords. That way I can show the speed
809
with different numbers of iterations. For reference: <TT
813
> shows 213 loops/sec for <TT
818
difference in results is in accordance with the fact that the
822
> implementation in <TT
826
is the same one used in John the Ripper.)
833
>"try all combinations"</SPAN
834
> is not a realistic exercise.
835
Usually password cracking is done with the help of dictionaries, which
836
contain both regular words and various mutations of them. So, even
837
somewhat word-like passwords could be cracked much faster than the above
838
numbers suggest, while a 6-character non-word-like password may escape
849
>F.28.3. PGP Encryption Functions</A
852
> The functions here implement the encryption part of the OpenPGP (RFC 4880)
853
standard. Supported are both symmetric-key and public-key encryption.
856
> An encrypted PGP message consists of 2 parts, or <I
866
> Packet containing a session key — either symmetric-key or public-key
872
> Packet containing data encrypted with the session key.
877
> When encrypting with a symmetric key (i.e., a password):
885
> The given password is hashed using a String2Key (S2K) algorithm. This is
886
rather similar to <CODE
889
> algorithms — purposefully
890
slow and with random salt — but it produces a full-length binary
896
> If a separate session key is requested, a new random key will be
897
generated. Otherwise the S2K key will be used directly as the session
903
> If the S2K key is to be used directly, then only S2K settings will be put
904
into the session key packet. Otherwise the session key will be encrypted
905
with the S2K key and put into the session key packet.
910
> When encrypting with a public key:
918
> A new random session key is generated.
923
> It is encrypted using the public key and put into the session key packet.
928
> In either case the data to be encrypted is processed as follows:
936
> Optional data-manipulation: compression, conversion to UTF-8,
937
and/or conversion of line-endings.
942
> The data is prefixed with a block of random bytes. This is equivalent
943
to using a random IV.
948
> An SHA1 hash of the random prefix and data is appended.
953
> All this is encrypted with the session key and placed in the data packet.
965
>pgp_sym_encrypt()</CODE
970
>pgp_sym_encrypt(data text, psw text [, options text ]) returns bytea
971
pgp_sym_encrypt_bytea(data bytea, psw text [, options text ]) returns bytea</PRE
976
> with a symmetric PGP key <TT
983
> parameter can contain option settings,
995
>pgp_sym_decrypt()</CODE
1000
>pgp_sym_decrypt(msg bytea, psw text [, options text ]) returns text
1001
pgp_sym_decrypt_bytea(msg bytea, psw text [, options text ]) returns bytea</PRE
1003
> Decrypt a symmetric-key-encrypted PGP message.
1011
>pgp_sym_decrypt</CODE
1013
This is to avoid outputting invalid character data. Decrypting
1014
originally textual data with <CODE
1016
>pgp_sym_decrypt_bytea</CODE
1023
> parameter can contain option settings,
1035
>pgp_pub_encrypt()</CODE
1040
>pgp_pub_encrypt(data text, key bytea [, options text ]) returns bytea
1041
pgp_pub_encrypt_bytea(data bytea, key bytea [, options text ]) returns bytea</PRE
1046
> with a public PGP key <TT
1050
Giving this function a secret key will produce a error.
1056
> parameter can contain option settings,
1068
>pgp_pub_decrypt()</CODE
1073
>pgp_pub_decrypt(msg bytea, key bytea [, psw text [, options text ]]) returns text
1074
pgp_pub_decrypt_bytea(msg bytea, key bytea [, psw text [, options text ]]) returns bytea</PRE
1076
> Decrypt a public-key-encrypted message. <TT
1080
secret key corresponding to the public key that was used to encrypt.
1081
If the secret key is password-protected, you must give the password in
1085
>. If there is no password, but you want to specify
1086
options, you need to give an empty password.
1094
>pgp_pub_decrypt</CODE
1096
This is to avoid outputting invalid character data. Decrypting
1097
originally textual data with <CODE
1099
>pgp_pub_decrypt_bytea</CODE
1106
> parameter can contain option settings,
1123
>pgp_key_id(bytea) returns text</PRE
1128
> extracts the key ID of a PGP public or secret key.
1129
Or it gives the key ID that was used for encrypting the data, if given
1130
an encrypted message.
1133
> It can return 2 special key IDs:
1146
> The message is encrypted with a symmetric key.
1157
> The message is public-key encrypted, but the key ID has been removed.
1158
That means you will need to try all your secret keys on it to see
1159
which one decrypts it. <TT
1162
> itself does not produce
1168
> Note that different keys may have the same ID. This is rare but a normal
1169
event. The client application should then try to decrypt with each one,
1170
to see which fits — like handling <TT
1192
>armor(data bytea) returns text
1193
dearmor(data text) returns bytea</PRE
1195
> These functions wrap/unwrap binary data into PGP ASCII-armor format,
1196
which is basically Base64 with CRC and additional formatting.
1205
>F.28.3.7. Options for PGP Functions</A
1208
> Options are named to be similar to GnuPG. An option's value should be
1209
given after an equal sign; separate options from each other with commas.
1212
CLASS="PROGRAMLISTING"
1213
>pgp_sym_encrypt(data, psw, 'compress-algo=1, cipher-algo=aes256')</PRE
1217
> All of the options except <TT
1221
encrypt functions. Decrypt functions get the parameters from the PGP
1225
> The most interesting options are probably
1233
The rest should have reasonable defaults.
1241
>F.28.3.7.1. cipher-algo</A
1244
> Which cipher algorithm to use.
1247
CLASS="LITERALLAYOUT"
1248
>Values: bf, aes128, aes192, aes256 (OpenSSL-only: <TT
1255
Default: aes128<br>
1256
Applies to: pgp_sym_encrypt, pgp_pub_encrypt</P
1264
>F.28.3.7.2. compress-algo</A
1267
> Which compression algorithm to use. Only available if
1271
> was built with zlib.
1274
CLASS="LITERALLAYOUT"
1276
0 - no compression<br>
1277
1 - ZIP compression<br>
1278
2 - ZLIB compression (= ZIP plus meta-data and block CRCs)<br>
1280
Applies to: pgp_sym_encrypt, pgp_pub_encrypt</P
1288
>F.28.3.7.3. compress-level</A
1291
> How much to compress. Higher levels compress smaller but are slower.
1292
0 disables compression.
1295
CLASS="LITERALLAYOUT"
1296
>Values: 0, 1-9<br>
1298
Applies to: pgp_sym_encrypt, pgp_pub_encrypt</P
1306
>F.28.3.7.4. convert-crlf</A
1309
> Whether to convert <TT
1323
decrypting. RFC 4880 specifies that text data should be stored using
1327
> line-feeds. Use this to get fully RFC-compliant
1331
CLASS="LITERALLAYOUT"
1332
>Values: 0, 1<br>
1334
Applies to: pgp_sym_encrypt, pgp_pub_encrypt, pgp_sym_decrypt, pgp_pub_decrypt</P
1342
>F.28.3.7.5. disable-mdc</A
1345
> Do not protect data with SHA-1. The only good reason to use this
1346
option is to achieve compatibility with ancient PGP products, predating
1347
the addition of SHA-1 protected packets to RFC 4880.
1348
Recent gnupg.org and pgp.com software supports it fine.
1351
CLASS="LITERALLAYOUT"
1352
>Values: 0, 1<br>
1354
Applies to: pgp_sym_encrypt, pgp_pub_encrypt</P
1362
>F.28.3.7.6. enable-session-key</A
1365
> Use separate session key. Public-key encryption always uses a separate
1366
session key; this is for symmetric-key encryption, which by default
1367
uses the S2K key directly.
1370
CLASS="LITERALLAYOUT"
1371
>Values: 0, 1<br>
1373
Applies to: pgp_sym_encrypt</P
1381
>F.28.3.7.7. s2k-mode</A
1384
> Which S2K algorithm to use.
1387
CLASS="LITERALLAYOUT"
1389
0 - Without salt. Dangerous!<br>
1390
1 - With salt but with fixed iteration count.<br>
1391
3 - Variable iteration count.<br>
1393
Applies to: pgp_sym_encrypt</P
1401
>F.28.3.7.8. s2k-digest-algo</A
1404
> Which digest algorithm to use in S2K calculation.
1407
CLASS="LITERALLAYOUT"
1408
>Values: md5, sha1<br>
1409
Default: sha1<br>
1410
Applies to: pgp_sym_encrypt</P
1418
>F.28.3.7.9. s2k-cipher-algo</A
1421
> Which cipher to use for encrypting separate session key.
1424
CLASS="LITERALLAYOUT"
1425
>Values: bf, aes, aes128, aes192, aes256<br>
1426
Default: use cipher-algo<br>
1427
Applies to: pgp_sym_encrypt</P
1435
>F.28.3.7.10. unicode-mode</A
1438
> Whether to convert textual data from database internal encoding to
1439
UTF-8 and back. If your database already is UTF-8, no conversion will
1440
be done, but the message will be tagged as UTF-8. Without this option
1444
CLASS="LITERALLAYOUT"
1445
>Values: 0, 1<br>
1447
Applies to: pgp_sym_encrypt, pgp_pub_encrypt</P
1456
>F.28.3.8. Generating PGP Keys with GnuPG</A
1459
> To generate a new key:
1461
CLASS="PROGRAMLISTING"
1466
> The preferred key type is <SPAN
1468
>"DSA and Elgamal"</SPAN
1472
> For RSA encryption you must create either DSA or RSA sign-only key
1473
as master and then add an RSA encryption subkey with
1482
CLASS="PROGRAMLISTING"
1483
>gpg --list-secret-keys</PRE
1487
> To export a public key in ASCII-armor format:
1489
CLASS="PROGRAMLISTING"
1490
>gpg -a --export KEYID > public.key</PRE
1494
> To export a secret key in ASCII-armor format:
1496
CLASS="PROGRAMLISTING"
1497
>gpg -a --export-secret-keys KEYID > secret.key</PRE
1501
> You need to use <CODE
1504
> on these keys before giving them to
1505
the PGP functions. Or if you can handle binary data, you can drop
1512
> For more details see <TT
1517
HREF="http://www.gnupg.org/gph/en/manual.html"
1521
> and other documentation on
1523
HREF="http://www.gnupg.org"
1525
>http://www.gnupg.org</A
1535
>F.28.3.9. Limitations of PGP Code</A
1542
> No support for signing. That also means that it is not checked
1543
whether the encryption subkey belongs to the master key.
1548
> No support for encryption key as master key. As such practice
1549
is generally discouraged, this should not be a problem.
1554
> No support for several subkeys. This may seem like a problem, as this
1555
is common practice. On the other hand, you should not use your regular
1556
GPG/PGP keys with <TT
1559
>, but create new ones,
1560
as the usage scenario is rather different.
1572
>F.28.4. Raw Encryption Functions</A
1575
> These functions only run a cipher over data; they don't have any advanced
1576
features of PGP encryption. Therefore they have some major problems:
1584
> They use user key directly as cipher key.
1589
> They don't provide any integrity checking, to see
1590
if the encrypted data was modified.
1595
> They expect that users manage all encryption parameters
1596
themselves, even IV.
1601
> They don't handle text.
1606
> So, with the introduction of PGP encryption, usage of raw
1607
encryption functions is discouraged.
1611
>encrypt(data bytea, key bytea, type text) returns bytea
1612
decrypt(data bytea, key bytea, type text) returns bytea
1614
encrypt_iv(data bytea, key bytea, iv bytea, type text) returns bytea
1615
decrypt_iv(data bytea, key bytea, iv bytea, type text) returns bytea</PRE
1617
> Encrypt/decrypt data using the cipher method specified by
1621
>. The syntax of the
1673
> — Blowfish</P
1680
> — AES (Rijndael-128)</P
1698
> — next block depends on previous (default)
1706
> — each block is encrypted separately (for
1726
> — data may be any length (default)
1734
> — data must be multiple of cipher block size
1741
> So, for example, these are equivalent:
1743
CLASS="PROGRAMLISTING"
1744
>encrypt(data, 'fooz', 'bf')
1745
encrypt(data, 'fooz', 'bf-cbc/pad:pkcs')</PRE
1759
> parameter is the initial value for the CBC mode;
1760
it is ignored for ECB.
1761
It is clipped or padded with zeroes if not exactly block size.
1762
It defaults to all zeroes in the functions without this parameter.
1771
>F.28.5. Random-Data Functions</A
1775
>gen_random_bytes(count integer) returns bytea</PRE
1780
> cryptographically strong random bytes.
1781
At most 1024 bytes can be extracted at a time. This is to avoid
1782
draining the randomness generator pool.
1799
>F.28.6.1. Configuration</A
1805
> configures itself according to the findings of the
1809
> script. The options that
1820
> When compiled with zlib, PGP encryption functions are able to
1821
compress data before encrypting.
1824
> When compiled with OpenSSL, there will be more algorithms available.
1825
Also public-key encryption functions will be faster as OpenSSL
1826
has more optimized BIGNUM functions.
1831
NAME="PGCRYPTO-WITH-WITHOUT-OPENSSL"
1835
>Table F-18. Summary of Functionality with and without OpenSSL</B
1840
><COL><COL><COL><THEAD
1869
>SHA224/256/384/512</TD
1877
>Other digest algorithms</TD
1917
>PGP Symmetric encryption</TD
1925
>PGP Public-Key encryption</TD
1943
> SHA2 algorithms were added to OpenSSL in version 0.9.8. For
1947
> will use built-in code.
1952
> Any digest algorithm OpenSSL supports is automatically picked up.
1953
This is not possible with ciphers, which need to be supported
1959
> AES is included in OpenSSL since version 0.9.7. For
1963
> will use built-in code.
1974
>F.28.6.2. NULL Handling</A
1977
> As is standard in SQL, all functions return NULL, if any of the arguments
1978
are NULL. This may create security risks on careless usage.
1987
>F.28.6.3. Security Limitations</A
1993
> functions run inside the database server.
1995
the data and passwords move between <TT
1999
applications in clear text. Thus you must:
2007
>Connect locally or use SSL connections.</P
2011
>Trust both system and database administrator.</P
2015
> If you cannot, then better do crypto inside client application.
2024
>F.28.6.4. Useful Reading</A
2032
HREF="http://www.gnupg.org/gph/en/manual.html"
2034
>http://www.gnupg.org/gph/en/manual.html</A
2037
>The GNU Privacy Handbook.</P
2042
HREF="http://www.openwall.com/crypt/"
2044
>http://www.openwall.com/crypt/</A
2047
>Describes the crypt-blowfish algorithm.</P
2052
HREF="http://www.stack.nl/~galactus/remailers/passphrase-faq.html"
2054
>http://www.stack.nl/~galactus/remailers/passphrase-faq.html</A
2058
>How to choose a good password.</P
2063
HREF="http://world.std.com/~reinhold/diceware.html"
2065
>http://world.std.com/~reinhold/diceware.html</A
2068
>Interesting idea for picking passwords.</P
2073
HREF="http://www.interhack.net/people/cmcurtin/snake-oil-faq.html"
2075
>http://www.interhack.net/people/cmcurtin/snake-oil-faq.html</A
2079
>Describes good and bad cryptography.</P
2089
>F.28.6.5. Technical References</A
2097
HREF="http://www.ietf.org/rfc/rfc4880.txt"
2099
>http://www.ietf.org/rfc/rfc4880.txt</A
2102
>OpenPGP message format.</P
2107
HREF="http://www.ietf.org/rfc/rfc1321.txt"
2109
>http://www.ietf.org/rfc/rfc1321.txt</A
2112
>The MD5 Message-Digest Algorithm.</P
2117
HREF="http://www.ietf.org/rfc/rfc2104.txt"
2119
>http://www.ietf.org/rfc/rfc2104.txt</A
2122
>HMAC: Keyed-Hashing for Message Authentication.</P
2127
HREF="http://www.usenix.org/events/usenix99/provos.html"
2129
>http://www.usenix.org/events/usenix99/provos.html</A
2133
>Comparison of crypt-des, crypt-md5 and bcrypt algorithms.</P
2138
HREF="http://csrc.nist.gov/cryptval/des.htm"
2140
>http://csrc.nist.gov/cryptval/des.htm</A
2143
>Standards for DES, 3DES and AES.</P
2148
HREF="http://en.wikipedia.org/wiki/Fortuna_(PRNG)"
2150
>http://en.wikipedia.org/wiki/Fortuna_(PRNG)</A
2154
>Description of Fortuna CSPRNG.</P
2159
HREF="http://jlcooke.ca/random/"
2161
>http://jlcooke.ca/random/</A
2164
>Jean-Luc Cooke Fortuna-based <TT
2167
> driver for Linux.</P
2172
HREF="http://research.cyber.ee/~lipmaa/crypto/"
2174
>http://research.cyber.ee/~lipmaa/crypto/</A
2177
>Collection of cryptology pointers.</P
2194
HREF="mailto:markokr@gmail.com"
2195
>markokr@gmail.com</A
2203
> uses code from the following sources:
2206
CLASS="INFORMALTABLE"
2215
><COL><COL><COL><THEAD
2230
>David Burren and others</TD
2232
>FreeBSD libcrypt</TD
2238
>Poul-Henning Kamp</TD
2240
>FreeBSD libcrypt</TD
2248
>www.openwall.com</TD
2252
>Blowfish cipher</TD
2260
>Rijndael cipher</TD
2264
>OpenBSD sys/crypto</TD
2272
>KAME kame/sys/crypto</TD
2276
>SHA256/384/512 </TD
2278
>Aaron D. Gifford</TD
2280
>OpenBSD sys/crypto</TD
2286
>Michael J. Fromberger</TD
2288
>dartmouth.edu/~sting/sw/imath</TD
2302
SUMMARY="Footer navigation table"
2313
HREF="pgbuffercache.html"
2331
HREF="pgfreespacemap.html"
2355
>pg_freespacemap</TD
b'\\ No newline at end of file'
added
removed
doc/src/sgml/html/pgcrypto.html
