From 0e88fc08d6217779d96c7371a74081bd253f5bb7 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Sun, 16 Dec 2007 23:00:42 +0000 Subject: [PATCH] Make an editorial pass over the newly SGML-ified contrib documentation. Fix lots of bad markup, bad English, bad explanations. Last ones ... whew. Man, that was tedious. --- doc/src/sgml/pgcrypto.sgml | 1391 +++++++++++++++++++---------------- doc/src/sgml/pgstandby.sgml | 408 +++++----- 2 files changed, 987 insertions(+), 812 deletions(-) diff --git a/doc/src/sgml/pgcrypto.sgml b/doc/src/sgml/pgcrypto.sgml index e0e6d0c421..f7fe07c525 100644 --- a/doc/src/sgml/pgcrypto.sgml +++ b/doc/src/sgml/pgcrypto.sgml @@ -1,600 +1,575 @@ + pgcrypto - + pgcrypto - This module provides cryptographic functions for PostgreSQL. + The pgcrypto module provides cryptographic functions for + PostgreSQL. - Notes - - 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: - - - 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 Symetric encryption - yes - yes - - - PGP Public-Key encryption - yes - yes - - - -
- - - - - Any digest algorithm OpenSSL supports is automatically picked up. - This is not possible with ciphers, which need to be supported - explicitly. - - - - - 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. - - - - - SHA2 algorithms were added to OpenSSL in version 0.9.8. For - older versions, pgcrypto will use built-in code. - - - - + General hashing functions - 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. - - + <function>digest()</function> - - 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: - + + digest(data text, type text) returns bytea + digest(data bytea, type text) returns bytea + - - - Connect locally or use SSL connections. - - - Trust both system and database administrator. - - - If you cannot, then better do crypto inside client application. + Computes a binary hash of the given data. + type is the algorithm to use. + Standard algorithms are md5 and + sha1. If pgcrypto was built with + OpenSSL, more algorithms are available, as detailed in + . - - - - General hashing - - - <literal>digest(data, type)</literal> - - 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: + 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 $$ + CREATE OR REPLACE FUNCTION sha1(bytea) returns text AS $$ SELECT encode(digest($1, 'sha1'), 'hex') $$ LANGUAGE SQL STRICT IMMUTABLE; - <literal>hmac(data, key, type)</literal> - - hmac(data text, key text, type text) RETURNS bytea - hmac(data bytea, key text, type text) RETURNS bytea - + <function>hmac()</function> + + + 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. + Calculates hashed MAC for data with key key. + type is the same as in digest(). + - 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. + 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. + - Returns binary hash. + If the key is larger than the hash block size it will first be hashed and + the result will be used as key. - Password hashing + Password hashing functions + - The functions crypt() and gen_salt() are specifically designed - for hashing passwords. crypt() does the hashing and `gen_salt()` + 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: + The algorithms in crypt() differ from usual hashing algorithms + like MD5 or SHA1 in the following respects: + - They are slow. As the amount of data is so small, this is only + They are slow. As the amount of data is so small, this is the only way to make brute-forcing passwords hard. - 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. + 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. - Include algorithm type in the result, so passwords hashed with + They include the algorithm type in the result, so passwords hashed with different algorithms can co-exist. - Some of them are adaptive - that means after computers get + 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. - - 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 ----------------------------------------------------------------------- - + + Supported algorithms for <function>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 + + + +
- crypt(password, salt) - - crypt(password text, salt text) RETURNS text - + <function>crypt()</> + + + 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. + 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 - setting new password: + Example of setting a new password: - UPDATE .. SET pswhash = crypt('new password', gen_salt('md5')); + UPDATE ... SET pswhash = crypt('new password', gen_salt('md5')); - Example - authentication: + Example of authentication: - SELECT pswhash = crypt('entered password', pswhash) WHERE .. ; + SELECT pswhash = crypt('entered password', pswhash) FROM ... ; - returns true or false whether the entered password is correct. - It also can return NULL if `pswhash` field is NULL. + This returns true if the entered password is correct. - gen_salt(type) - - gen_salt(type text) RETURNS text - + <function>gen_salt()</> + + + gen_salt(type text [, iter_count integer ]) returns text + + - Generates a new random salt for usage in `crypt()`. For adaptible - algorithms, it uses the default iteration count. + Generates a new random salt string for use in crypt(). + The salt string also tells crypt() which algorithm to use. + - Accepted types are: `des`, `xdes`, `md5` and `bf`. + The type parameter specifies the hashing algorithm. + The accepted types are: des, xdes, + md5 and bf. - - - gen_salt(type, rounds) - - gen_salt(type text, rounds integer) RETURNS text - + - algorithms. The higher the count, the more time it takes to hash + 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 count the time to calculate a hash may be several years - - which is somewhat impractical. + 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: + + + Iteration counts for <function>crypt()</> + + + + Algorithm + Default + Min + Max + + + + + xdes + 725 + 1 + 16777215 + + + bf + 6 + 4 + 31 + + + +
+ - Number is algorithm specific: + For xdes there is an additional limitation that the + iteration count must be an odd number. - -`-----'---------'-----'---------- - 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. + To pick an appropriate iteration count, consider that + the 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. + - Notes: + Here is a table that gives an overview of the relative slowness + of different hashing algorithms. + The table shows how much time it would take to try all + combinations of characters in an 8-character password, assuming + that the password contains either only lowercase letters, or + upper- and lower-case letters and numbers. + In the crypt-bf entries, the number after a slash is + the iter_count parameter of + gen_salt. - - - - 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`. - - - - - - Comparison of crypt and regular hashes + + Hash algorithm speeds + + + + Algorithm + Hashes/sec + For [a-z] + For [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 + + + +
+ - Here is a table that should give overview of relative slowness - of different hashing algorithms. + Notes: + - The goal is to crack a 8-character password, which consists: - - - Only of lowercase letters - 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. + The machine used is a 1.5GHz Pentium 4. - crypt-des and crypt-md5 algorithm numbers are taken from - John the Ripper v1.6.38 `-test` output. + 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. + md5 numbers are from mdcrack 1.2. - SHA1 numbers are from lcrack-20031130-beta. + 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.) + crypt-bf numbers are taken using a simple program that + loops over 1000 8-character passwords. That way I can show the speed + with different numbers of iterations. For reference: john + -test shows 213 loops/sec for crypt-bf/5. + (The very small + difference in results is in accordance with the fact that the + crypt-bf implementation in pgcrypto + is the same one used in John the Ripper.) - Note that "try all combinations" is not a realistic exercise. + 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 + numbers suggest, while a 6-character non-word-like password may escape cracking. Or not. - - PGP encryption + PGP encryption functions + - The functions here implement the encryption part of OpenPGP (RFC2440) - standard. Supported are both symmetric-key and public-key encryption. + The functions here implement the encryption part of the OpenPGP (RFC2440) + standard. Supported are both symmetric-key and public-key encryption. - - 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: - - - - - 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. - - - - - If separate session key is requested, new random key will be - generated. Otherwise S2K key will be used directly as session key. - - - - - 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: - - - New random session key is generated. - It is encrypted using public key and put into session key packet. - + + An encrypted PGP message consists of 2 parts, or packets: + + + + + Packet containing a session key — either symmetric-key or public-key + encrypted. + + + + + Packet containing data encrypted with the session key. + + + - - Now common part, the session-key encrypted data packet: - - - - - Optional data-manipulation: compression, conversion to UTF-8, - conversion of line-endings. - - - - - Data is prefixed with block of random bytes. This is equal - to using random IV. - - - - - A SHA1 hash of random prefix and data is appended. - - - - - All this is encrypted with session key. - - - - + + When encrypting with a symmetric key (i.e., a password): + + + + + The given password is hashed using a String2Key (S2K) algorithm. This is + rather similar to crypt() algorithms — purposefully + slow and with random salt — but it produces a full-length binary + key. + + + + + If a separate session key is requested, a new random key will be + generated. Otherwise the S2K key will be used directly as the session + key. + + + + + If the S2K key is to be used directly, then only S2K settings will be put + into the session key packet. Otherwise the session key will be encrypted + with the S2K key and put into the session key packet. + + + + + + When encrypting with a public key: + + + + + A new random session key is generated. + + + + + It is encrypted using the public key and put into the session key packet. + + + + + + In either case the data to be encrypted is processed as follows: + + + + + Optional data-manipulation: compression, conversion to UTF-8, + and/or conversion of line-endings. + + + + + The data is prefixed with a block of random bytes. This is equivalent + to using a random IV. + + + + + An SHA1 hash of the random prefix and data is appended. + + + + + All this is encrypted with the session key and placed in the data packet. + + + - <literal>pgp_sym_encrypt(data, psw)</literal> - - 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. - + <function>pgp_sym_encrypt()</function> + + + pgp_sym_encrypt(data text, psw text [, options text ]) returns bytea + pgp_sym_encrypt_bytea(data bytea, psw text [, options text ]) returns bytea + - Options are described in section 5.8. + Encrypt data with a symmetric PGP key psw. + The options parameter can contain option settings, + as described below. - <literal>pgp_sym_decrypt(msg, psw)</literal> - - pgp_sym_decrypt(msg bytea, psw text [, options text] ) RETURNS text - pgp_sym_decrypt_bytea(msg bytea, psw text [, options text] ) RETURNS bytea - + <function>pgp_sym_decrypt()</function> + + + 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. + Decrypt a symmetric-key-encrypted PGP message. - Decrypting bytea data with `pgp_sym_decrypt` is disallowed. + 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. + originally textual data with pgp_sym_decrypt_bytea is fine. - Options are described in section 5.8. + The options parameter can contain option settings, + as described below. - <literal>pgp_pub_encrypt(data, pub_key)</literal> - - pgp_pub_encrypt(data text, key bytea [, options text] ) RETURNS bytea - pgp_pub_encrypt_bytea(data bytea, key bytea [, options text] ) RETURNS bytea - + <function>pgp_pub_encrypt()</function> + + + 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. + Encrypt data with a public PGP key key. + Giving this function a secret key will produce a error. - Options are described in section 5.8. + The options parameter can contain option settings, + as described below. - <literal>pgp_pub_decrypt(msg, sec_key [, psw])</literal> - - pgp_pub_decrypt(msg bytea, key bytea [, psw text [, options text]] ) RETURNS text - pgp_pub_decrypt_bytea(msg bytea, key bytea [,psw text [, options text]] ) RETURNS bytea - + <function>pgp_pub_decrypt()</function> + + + pgp_pub_decrypt(msg bytea, key bytea [, psw text [, options text ]]) returns text + pgp_pub_decrypt_bytea(msg bytea, key bytea [, psw text [, options text ]]) returns bytea + - Decrypt a public-key encrypted message with secret key. If the secret - key is password-protected, you must give the password in `psw`. If - there is no password, but you want to specify option for function, you - need to give empty password. + Decrypt a public-key-encrypted message. key must be the + secret key corresponding to the public key that was used to encrypt. + If the secret key is password-protected, you must give the password in + psw. If there is no password, but you want to specify + options, you need to give an empty password. - Decrypting bytea data with `pgp_pub_decrypt` is disallowed. + Decrypting bytea data with pgp_pub_decrypt is disallowed. This is to avoid outputting invalid character data. Decrypting - originally textual data with `pgp_pub_decrypt_bytea` is fine. + originally textual data with pgp_pub_decrypt_bytea is fine. - Options are described in section 5.8. + The options parameter can contain option settings, + as described below. - <literal>pgp_key_id(key / msg)</literal> - - pgp_key_id(key or msg bytea) RETURNS text - + <function>pgp_key_id()</function> + + + pgp_key_id(bytea) returns text + - It shows you either key ID if given PGP public or secret key. Or it - gives the key ID that was used for encrypting the data, if given - encrypted message. + pgp_key_id extracts the key ID of a PGP public or secret key. + Or it gives the key ID that was used for encrypting the data, if given + an encrypted message. It can return 2 special key IDs: @@ -602,151 +577,163 @@ md5 2345086 1 day 3 years - SYMKEY: + SYMKEY - The data is encrypted with symmetric key. + The message is encrypted with a symmetric key. - ANYKEY: + ANYKEY - The data is public-key encrypted, but the key ID is cleared. - That means you need to try all your secret keys on it to see - which one decrypts it. pgcrypto itself does not produce such - messages. + The message is public-key encrypted, but the key ID has been removed. + That means you will need to try all your secret keys on it to see + which one decrypts it. pgcrypto itself does not produce + such messages. - Note that different keys may have same ID. This is rare but normal - event. Client application should then try to decrypt with each one, - to see which fits - like handling ANYKEY. + Note that different keys may have the same ID. This is rare but a normal + event. The client application should then try to decrypt with each one, + to see which fits — like handling ANYKEY. - <literal>armor / dearmor</literal> - - armor(data bytea) RETURNS text - dearmor(data text) RETURNS bytea - + <function>armor()</function>, <function>dearmor()</function> + + + armor(data bytea) returns text + dearmor(data text) returns bytea + - Those wrap/unwrap data into PGP Ascii Armor which is basically Base64 - with CRC and additional formatting. + These functions wrap/unwrap binary data into PGP Ascii Armor format, + which is basically Base64 with CRC and additional formatting. Options for PGP functions + - Options are named to be similar to GnuPG. Values should be given after - an equal sign; separate options from each other with commas. Example: + Options are named to be similar to GnuPG. An option's value should be + given after an equal sign; separate options from each other with commas. + For example: pgp_sym_encrypt(data, psw, 'compress-algo=1, cipher-algo=aes256') + - All of the options except `convert-crlf` apply only to encrypt - functions. Decrypt functions get the parameters from PGP data. + All of the options except convert-crlf apply only to + encrypt functions. Decrypt functions get the parameters from the PGP + data. + - Most interesting options are probably `compression-algo` and - unicode-mode. The rest should have reasonable defaults. + The most interesting options are probably + compress-algo and unicode-mode. + The rest should have reasonable defaults. - - + cipher-algo + - What cipher algorithm to use. + Which cipher algorithm to use. - Values: bf, aes128, aes192, aes256 (OpenSSL-only: `3des`, `cast5`) + Values: bf, aes128, aes192, aes256 (OpenSSL-only: 3des, cast5) Default: aes128 - Applies: pgp_sym_encrypt, pgp_pub_encrypt + Applies to: pgp_sym_encrypt, pgp_pub_encrypt - + - + compress-algo + - Which compression algorithm to use. Needs building with zlib. - - - Values: + Which compression algorithm to use. Only available if + pgcrypto was built with zlib. - 0 - no compression - 1 - ZIP compression - 2 - ZLIB compression [=ZIP plus meta-data and block-CRC's] + Values: + 0 - no compression + 1 - ZIP compression + 2 - ZLIB compression (= ZIP plus meta-data and block CRCs) Default: 0 - Applies: pgp_sym_encrypt, pgp_pub_encrypt + Applies to: pgp_sym_encrypt, pgp_pub_encrypt - + - + compress-level + - How much to compress. Bigger level compresses smaller but is slower. + How much to compress. Higher levels compress smaller but are slower. 0 disables compression. Values: 0, 1-9 Default: 6 - Applies: pgp_sym_encrypt, pgp_pub_encrypt + Applies to: pgp_sym_encrypt, pgp_pub_encrypt - + - + convert-crlf + - Whether to convert `\n` into `\r\n` when encrypting and `\r\n` to `\n` - when decrypting. RFC2440 specifies that text data should be stored - using `\r\n` line-feeds. Use this to get fully RFC-compliant + Whether to convert \n into \r\n when + encrypting and \r\n to \n when + decrypting. RFC2440 specifies that text data should be stored using + \r\n line-feeds. Use this to get fully RFC-compliant behavior. Values: 0, 1 Default: 0 - Applies: pgp_sym_encrypt, pgp_pub_encrypt, pgp_sym_decrypt, pgp_pub_decrypt + Applies to: pgp_sym_encrypt, pgp_pub_encrypt, pgp_sym_decrypt, pgp_pub_decrypt - + - + disable-mdc + - Do not protect data with SHA-1. Only good reason to use this - option is to achieve compatibility with ancient PGP products, as the - SHA-1 protected packet is from upcoming update to RFC2440. (Currently - at version RFC2440bis-14.) Recent gnupg.org and pgp.com software - supports it fine. + Do not protect data with SHA-1. The only good reason to use this + option is to achieve compatibility with ancient PGP products, predating + the addition of SHA-1 protected packets to RFC2440. + Recent gnupg.org and pgp.com software supports it fine. Values: 0, 1 Default: 0 - Applies: pgp_sym_encrypt, pgp_pub_encrypt + Applies to: pgp_sym_encrypt, pgp_pub_encrypt - + - + enable-session-key + - Use separate session key. Public-key encryption always uses separate - session key, this is for symmetric-key encryption, which by default - uses S2K directly. + Use separate session key. Public-key encryption always uses a separate + session key; this is for symmetric-key encryption, which by default + uses the S2K key directly. Values: 0, 1 Default: 0 - Applies: pgp_sym_encrypt + Applies to: pgp_sym_encrypt - + - + s2k-mode + Which S2K algorithm to use. @@ -756,126 +743,134 @@ md5 2345086 1 day 3 years 1 - With salt but with fixed iteration count. 3 - Variable iteration count. Default: 3 - Applies: pgp_sym_encrypt + Applies to: pgp_sym_encrypt - + - + s2k-digest-algo + Which digest algorithm to use in S2K calculation. Values: md5, sha1 Default: sha1 - Applies: pgp_sym_encrypt + Applies to: pgp_sym_encrypt - + - + s2k-cipher-algo + Which cipher to use for encrypting separate session key. Values: bf, aes, aes128, aes192, aes256 - Default: use cipher-algo. - Applies: pgp_sym_encrypt + Default: use cipher-algo + Applies to: pgp_sym_encrypt - + - + unicode-mode + Whether to convert textual data from database internal encoding to UTF-8 and back. If your database already is UTF-8, no conversion will - be done, only the data will be tagged as UTF-8. Without this option + be done, but the message will be tagged as UTF-8. Without this option it will not be. Values: 0, 1 Default: 0 - Applies: pgp_sym_encrypt, pgp_pub_encrypt + Applies to: pgp_sym_encrypt, pgp_pub_encrypt + - - - Generating keys with GnuPG + + Generating PGP keys with GnuPG + - Generate a new key: + To generate a new key: gpg --gen-key - The preferred key type is "DSA and Elgamal". + The preferred key type is DSA and Elgamal. For RSA encryption you must create either DSA or RSA sign-only key - as master and then add RSA encryption subkey with `gpg --edit-key`. + as master and then add an RSA encryption subkey with + gpg --edit-key. - List keys: + To list keys: gpg --list-secret-keys - Export ascii-armored public key: + To export a public key in ascii-armor format: gpg -a --export KEYID > public.key - Export ascii-armored secret key: + To export a secret key in ascii-armor format: gpg -a --export-secret-keys KEYID > secret.key - - You need to use `dearmor()` on them before giving them to - pgp_pub_* functions. Or if you can handle binary data, you can drop - "-a" from gpg. + + You need to use dearmor() on these keys before giving them to + the PGP functions. Or if you can handle binary data, you can drop + -a from the command. - For more details see `man gpg`, - [The GNU - Privacy Handbook] and other docs on - site. + For more details see man gpg, + The GNU + Privacy Handbook and other documentation on + . - + - + Limitations of PGP code + No support for signing. That also means that it is not checked - whether the encryption subkey belongs to master key. + whether the encryption subkey belongs to the master key. No support for encryption key as master key. As such practice - is generally discouraged, it should not be a problem. + is generally discouraged, this should not be a problem. No support for several subkeys. This may seem like a problem, as this is common practice. On the other hand, you should not use your regular - GPG/PGP keys with pgcrypto, but create new ones, as the usage scenario - is rather different. + GPG/PGP keys with pgcrypto, but create new ones, + as the usage scenario is rather different. + - Raw encryption + Raw encryption functions + - Those functions only run a cipher over data, they don't have any advanced + These functions only run a cipher over data; they don't have any advanced features of PGP encryption. Therefore they have some major problems: @@ -906,117 +901,266 @@ md5 2345086 1 day 3 years So, with the introduction of PGP encryption, usage of raw encryption functions is discouraged. - - encrypt(data bytea, key bytea, type text) RETURNS bytea - decrypt(data bytea, key bytea, type text) RETURNS bytea - encrypt_iv(data bytea, key bytea, iv bytea, type text) RETURNS bytea - decrypt_iv(data bytea, key bytea, iv bytea, type text) RETURNS bytea - - - Encrypt/decrypt data with cipher, padding data if needed. - + + encrypt(data bytea, key bytea, type text) returns bytea + decrypt(data bytea, key bytea, type text) returns bytea + + encrypt_iv(data bytea, key bytea, iv bytea, type text) returns bytea + decrypt_iv(data bytea, key bytea, iv bytea, type text) returns bytea + + - type parameter description in pseudo-noteup: + Encrypt/decrypt data using the cipher method specified by + type. The syntax of the + type string is: - - algo ['-' mode] ['/pad:' padding] - + + + algorithm - mode /pad: padding + + - Supported algorithms: + where algorithm is one of: - bf- Blowfish - aes- AES (Rijndael-128) + bf — Blowfish + aes — AES (Rijndael-128) - Modes: + and mode is one of: - cbc- next block depends on previous. (default) + cbc — next block depends on previous (default) - ecb- each block is encrypted separately. (for testing - only) + ecb — each block is encrypted separately (for + testing only) - Padding: + and padding is one of: - pkcs-data may be any length (default) + pkcs — data may be any length (default) - none- data must be multiple of cipher block size. + none — data must be multiple of cipher block size - IV is initial value for mode, defaults to all zeroes. It is ignored for - ECB. It is clipped or padded with zeroes if not exactly block size. - - - So, example: + So, for example, these are equivalent: encrypt(data, 'fooz', 'bf') + encrypt(data, 'fooz', 'bf-cbc/pad:pkcs') - is equal to + In encrypt_iv and decrypt_iv, the + iv parameter is the initial value for the CBC mode; + it is ignored for ECB. + It is clipped or padded with zeroes if not exactly block size. + It defaults to all zeroes in the functions without this parameter. - - encrypt(data, 'fooz', 'bf-cbc/pad:pkcs') - - Random bytes - - gen_random_bytes(count integer) - + Random-data functions + + + gen_random_bytes(count integer) returns bytea + - Returns `count` cryptographically strong random bytes as bytea value. - There can be maximally 1024 bytes extracted at a time. This is to avoid + Returns count cryptographically strong random bytes. + At most 1024 bytes can be extracted at a time. This is to avoid draining the randomness generator pool. - References/Links - + Notes + + + Configuration + + + pgcrypto configures itself according to the findings of the + 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 + With OpenSSL + + + + + MD5 + yes + yes + + + SHA1 + yes + yes + + + SHA224/256/384/512 + yes + yes (Note 1) + + + Other digest algorithms + no + yes (Note 2) + + + Blowfish + yes + yes + + + AES + yes + yes (Note 3) + + + DES/3DES/CAST5 + no + yes + + + Raw encryption + yes + yes + + + PGP Symmetric encryption + yes + yes + + + PGP Public-Key encryption + yes + yes + + + +
+ + + Notes: + + + + + + SHA2 algorithms were added to OpenSSL in version 0.9.8. For + older versions, pgcrypto will use built-in code. + + + + + Any digest algorithm OpenSSL supports is automatically picked up. + This is not possible with ciphers, which need to be supported + explicitly. + + + + + AES is included in OpenSSL since version 0.9.7. For + older versions, pgcrypto will use built-in code. + + + + + + + NULL handling + + + As is standard in SQL, all functions return NULL, if any of the arguments + are NULL. This may create security risks on careless usage. + + + + + Security limitations + + + All pgcrypto functions run inside the database server. + That means that all + the data and passwords move between pgcrypto and client + applications in clear text. Thus you must: + + + + + Connect locally or use SSL connections. + + + Trust both system and database administrator. + + + + + If you cannot, then better do crypto inside client application. + + + Useful reading + - : - The GNU Privacy Handbook + + The GNU Privacy Handbook. - : + Describes the crypt-blowfish algorithm. - : + - How to choose good password. + How to choose a good password. - : + Interesting idea for picking passwords. - : + Describes good and bad cryptography. @@ -1025,47 +1169,48 @@ md5 2345086 1 day 3 years Technical references + - : - OpenPGP message format + + OpenPGP message format. - : + New version of RFC2440. - : - The MD5 Message-Digest Algorithm + + The MD5 Message-Digest Algorithm. - : - HMAC: Keyed-Hashing for Message Authentication + + HMAC: Keyed-Hashing for Message Authentication. - : + Comparison of crypt-des, crypt-md5 and bcrypt algorithms. - : + Standards for DES, 3DES and AES. - : + Description of Fortuna CSPRNG. - : + Jean-Luc Cooke Fortuna-based /dev/random driver for Linux. - : + Collection of cryptology pointers. @@ -1073,72 +1218,70 @@ md5 2345086 1 day 3 years - Credits + Author + + + Marko Kreen markokr@gmail.com + + - pgcrypto uses code from the following sources: + pgcrypto uses code from the following sources: + Credits - Algorithm - Author - Source origin + Algorithm + Author + Source origin - DES crypt() - David Burren and others - FreeBSD libcrypt + DES crypt + David Burren and others + FreeBSD libcrypt - MD5 crypt() - Poul-Henning Kamp - FreeBSD libcrypt + MD5 crypt + Poul-Henning Kamp + FreeBSD libcrypt - Blowfish crypt() - Solar Designer - www.openwall.com + Blowfish crypt + Solar Designer + www.openwall.com - Blowfish cipher - Simon Tatham - PuTTY + Blowfish cipher + Simon Tatham + PuTTY - Rijndael cipher - Brian Gladman - OpenBSD sys/crypto + Rijndael cipher + Brian Gladman + OpenBSD sys/crypto - MD5 and SHA1 - WIDE Project - KAME kame/sys/crypto + MD5 and SHA1 + WIDE Project + KAME kame/sys/crypto - SHA256/384/512 - Aaron D. Gifford - OpenBSD sys/crypto + SHA256/384/512 + Aaron D. Gifford + OpenBSD sys/crypto - BIGNUM math - Michael J. Fromberger - dartmouth.edu/~sting/sw/imath + BIGNUM math + Michael J. Fromberger + dartmouth.edu/~sting/sw/imath
- - Author - - Marko Kreen markokr@gmail.com - - - - diff --git a/doc/src/sgml/pgstandby.sgml b/doc/src/sgml/pgstandby.sgml index ac4bca63ee..935061055f 100644 --- a/doc/src/sgml/pgstandby.sgml +++ b/doc/src/sgml/pgstandby.sgml @@ -1,207 +1,199 @@ + + pg_standby - + pg_standby - pg_standby allows the creation of a Warm Standby server. - Other configuration is required as well, all of which is described in the - main server manual. - - - The program is designed to be a wait-for restore_command, - required to turn a normal archive recovery into a Warm Standby. Within the - restore_command of the recovery.conf - you could configure pg_standby in the following way: + pg_standby supports creation of a warm standby + database server. It is designed to be a production-ready program, as well + as a customizable template should you require specific modifications. - -restore_command = 'pg_standby archiveDir %f %p %r' - + - which would be sufficient to define that files will be restored from - archiveDir. + pg_standby is designed to be a waiting + restore_command, which is needed to turn a standard + archive recovery into a warm standby operation. Other + configuration is required as well, all of which is described in the main + server manual (see ). - pg_standby features include: + pg_standby features include: - It is written in C. So it is very portable - and easy to install. + Supports copy or link for restoring WAL files - Supports copy or link from a directory (only) + Written in C, so very portable and easy to install - Source easy to modify, with specifically designated - sections to modify for your own needs, allowing - interfaces to be written for additional Backup Archive Restore - (BAR) systems + Easy-to-modify source code, with specifically designated + sections to modify for your own needs - Already tested on Linux and Windows + Already tested on Linux and Windows Usage + - pg_standby should be used within the - restore_command of the recovery.conf - file. - - - The basic usage should be like this: + To configure a standby + server to use pg_standby, put this into its + recovery.conf configuration file: - restore_command = 'pg_standby archiveDir %f %p' +restore_command = 'pg_standby archiveDir %f %p %r' - with the pg_standby command usage as + where archiveDir is the directory from which WAL segment + files should be restored. - - pg_standby [OPTION]... ARCHIVELOCATION NEXTWALFILE XLOGFILEPATH [RESTARTWALFILE] - - When used within the restore_command the %f and %p macros - will provide the actual file and path required for the restore/recovery. + The full syntax of pg_standby's command line is + + +pg_standby option ... archivelocation nextwalfile xlogfilepath restartwalfile + + + When used within restore_command, the %f and + %p macros should be specified for nextwalfile + and xlogfilepath respectively, to provide the actual file + and path required for the restore. - pg_standby assumes that ARCHIVELOCATION - is a directory accessible by the server-owning user. + If restartwalfile is specified, normally by using the + %r macro, then all WAL files logically preceding this + file will be removed from archivelocation. This minimizes + the number of files that need to be retained, while preserving + crash-restart capability. Use of this parameter is appropriate if the + archivelocation is a transient staging area for this + particular standby server, but not when the + archivelocation is intended as a long-term WAL archive area. - If RESTARTWALFILE is specified, typically by using the - %r option, then all files prior to this file will be - removed from ARCHIVELOCATION. This then minimises the - number of files that need to be held, whilst at the same time maintaining - restart capability. This capability additionally assumes that - ARCHIVELOCATION directory is writable. + pg_standby assumes that + archivelocation is a directory readable by the + server-owning user. If restartwalfile (or -k) + is specified, + the archivelocation directory must be writable too. - Options - + <application>pg_standby</> options + + + + Option + Default + Description + + - -c - use copy/cp command to restore WAL files from archive + -c + yes + + Use cp or copy command to restore WAL files + from archive. + - -d - debug/logging option. + -d + no + Print lots of debug logging output on stderr. - -k numfiles + -k numfiles + 0 - - Cleanup files in the archive so that we maintain no more than this - many files in the archive. This parameter will be silently ignored if - RESTARTWALFILE is specified, since that - specification method is more accurate in determining the correct - cut-off point in archive. - - - You should be wary against setting this number too low, - since this may mean you cannot restart the standby. This - is because the last restartpoint marked in the WAL files - may be many files in the past and can vary considerably. - This should be set to a value exceeding the number of WAL - files that can be recovered in 2*checkpoint_timeout seconds, - according to the value in the warm standby postgresql.conf. - It is wholly unrelated to the setting of checkpoint_segments - on either primary or standby. - - - Setting numfiles to be zero will disable deletion - of files from ARCHIVELOCATION. - - - If in doubt, use a large value or do not set a value at all. - - - If you specify neither RESTARTWALFILE nor -k, - then -k 0 will be assumed, i.e. keep all files in archive. - + Remove files from archivelocation so that + no more than this many WAL files before the current one are kept in the + archive. Zero (the default) means not to remove any files from + archivelocation. + This parameter will be silently ignored if + restartwalfile is specified, since that + specification method is more accurate in determining the correct + archive cut-off point. + Use of this parameter is deprecated as of + PostgreSQL 8.3; it is safer and more efficient to + specify a restartwalfile parameter. A too + small setting could result in removal of files that are still needed + for a restart of the standby server, while a too large setting wastes + archive space. - -l + -l + no - - use ln command to restore WAL files from archive - WAL files will remain in archive - - - Link is more efficient, but the default is copy to allow you to - maintain the WAL archive for recovery purposes as well as - high-availability. The default setting is not necessarily recommended, - consult the main database server manual for discussion. - - - This option uses the Windows Vista command mklink - to provide a file-to-file symbolic link. -l will - not work on versions of Windows prior to Vista. - Use the -c option instead. - see - + Use ln command to restore WAL files from archive. + Link is more efficient than copy, but the default is copy since link + will not work in all scenarios. + On Windows, this option uses the mklink command + to provide a file-to-file symbolic link. -l will + not work on versions of Windows prior to Vista. - -r maxretries + -r maxretries + 3 - - the maximum number of times to retry the restore command if it - fails. After each failure, we wait for sleeptime * num_retries - so that the wait time increases progressively, so by default - we will wait 5 secs, 10 secs then 15 secs before reporting - the failure back to the database server. This will be - interpreted as and end of recovery and the Standby will come - up fully as a result. Default=3 Min=0 - + Set the maximum number of times to retry the copy or link command if it + fails. After each failure, we wait for sleeptime * + num_retries + so that the wait time increases progressively. So by default, + we will wait 5 secs, 10 secs, then 15 secs before reporting + the failure back to the standby server. This will be + interpreted as end of recovery and the standby will come + up fully as a result. - -s sleeptime + -s sleeptime + 5 - the number of seconds to sleep between testing to see - if the file to be restored is available in the archive yet. - The default setting is not necessarily recommended, - consult the main database server manual for discussion. - Default=5, Min=1, Max=60 + Set the number of seconds (up to 60) to sleep between tests to see + if the WAL file to be restored is available in the archive yet. + The default setting is not necessarily recommended; + consult for discussion. - -t triggerfile + -t triggerfile + none - the presence of the triggerfile will cause recovery to end - whether or not the next file is available - It is recommended that you use a structured filename to + Specify a trigger file whose presence should cause recovery to end + whether or not the next WAL file is available. + It is recommended that you use a structured filename to avoid confusion as to which server is being triggered - when multiple servers exist on same system. - e.g. /tmp/pgsql.trigger.5432 + when multiple servers exist on the same system; for example + /tmp/pgsql.trigger.5432. - -w maxwaittime + -w maxwaittime + 0 - the maximum number of seconds to wait for the next file, - after which recovery will end and the Standby will come up. - The default setting is not necessarily recommended, - consult the main database server manual for discussion. A setting of - zero means wait forever. - Default=0, Min=0 + Set the maximum number of seconds to wait for the next WAL file, + after which recovery will end and the standby will come up. + A setting of zero (the default) means wait forever. + The default setting is not necessarily recommended; + consult for discussion. @@ -209,89 +201,129 @@ restore_command = 'pg_standby archiveDir %f %p %r'
- --help is not supported since - pg_standby is not intended for interactive use, except - during development and testing. + --help is not supported since + pg_standby is not intended for interactive use, + except during development and testing. - Supported versions - - pg_standby is designed to work with PostgreSQL 8.2 and - and later. It is currently compatible across minor changes between the way - 8.3 and 8.2 operate. - - - PostgreSQL 8.3 provides the %r command line substitution, - designed to let pg_standby know the last file it needs to - keep. If the last parameter is omitted, no error is generated, allowing - pg_standby to function correctly with PostgreSQL 8.2 - also. With PostgreSQL 8.2, the -k option must be used if - archive cleanup is required. This option remains available in 8.3. - - + Examples - - Additional design notes + On Linux or Unix systems, you might use: + + +archive_command = 'cp %p .../archive/%f' + +restore_command = 'pg_standby -l -d -s 2 -t /tmp/pgsql.trigger.5442 .../archive %f %p %r 2>>standby.log' + - The use of a move command seems like it would be a good idea, but this would - prevent recovery from being restartable. Also, the last WAL file is always - requested twice from the archive. + where the archive directory is physically located on the standby server, + so that the archive_command is accessing it across NFS, + but the files are local to the standby (enabling use of ln). + This will: - + + + + use the ln command to restore WAL files from archive + + + + + produce debugging output in standby.log + + + + + sleep for 2 seconds between checks for next WAL file availability + + + + + stop waiting only when a trigger file called + /tmp/pgsql.trigger.5442 appears + + + + + remove no-longer-needed files from the archive directory + + + - - Examples + On Windows, you might use: + + +archive_command = 'copy %p ...\\archive\\%f' +restore_command = 'pg_standby -d -s 5 -t C:\pgsql.trigger.5442 ...\archive %f %p %r 2>>standby.log' + + + Note that backslashes need to be doubled in the + archive_command, but not in the + restore_command. This will: + - Example on Linux - -archive_command = 'cp %p ../archive/%f' - -restore_command = 'pg_standby -l -d -k 255 -r 2 -s 2 -w 0 -t /tmp/pgsql.trigger.5442 $PWD/../archive %f %p 2>> standby.log' - - which will + use the copy command to restore WAL files from archive - - use a ln command to restore WAL files from archive - produce logfile output in standby.log - keep the last 255 full WAL files, plus the current one - sleep for 2 seconds between checks for next WAL file is full - never timeout if file not found - stop waiting when a trigger file called /tmp/pgsql.trigger.5442 appears - - - Example on Windows + produce debugging output in standby.log - -archive_command = 'copy %p ..\\archive\\%f' - + + - Note that backslashes need to be doubled in the archive_command, but - *not* in the restore_command, in 8.2, 8.1, 8.0 on Windows. + sleep for 5 seconds between checks for next WAL file availability - -restore_command = 'pg_standby -c -d -s 5 -w 0 -t C:\pgsql.trigger.5442 ..\archive %f %p 2>> standby.log' - + + - which will + stop waiting only when a trigger file called + C:\pgsql.trigger.5442 appears + + + + + remove no-longer-needed files from the archive directory - - use a copy command to restore WAL files from archive - produce logfile output in standby.log - sleep for 5 seconds between checks for next WAL file is full - never timeout if file not found - stop waiting when a trigger file called C:\pgsql.trigger.5442 appears - + + + Since the Windows example uses copy at both ends, either + or both servers might be accessing the archive directory across the + network. + + - + + + Supported server versions + + + pg_standby is designed to work with + PostgreSQL 8.2 and later. + + + PostgreSQL 8.3 provides the %r macro, + which is designed to let pg_standby know the + last file it needs to keep. With PostgreSQL 8.2, the + -k option must be used if archive cleanup is + required. This option remains available in 8.3, but its use is deprecated. + + + + + Author + + + Simon Riggs + + + -- 2.40.0