doc/src/sgml/pgcrypto.sgml - master - githubmirror/postgres

doc/src/sgml/pgcrypto.sgml
1 408 строк41 KB
Jacob Champion
pgcrypto: Add option to revert to prior decryption behavior
10 авг 2026, 16:37
10 авг 2026, 16:3749e7954
<!-- doc/src/sgml/pgcrypto.sgml -->

<sect1 id="pgcrypto" xreflabel="pgcrypto">
 <title>pgcrypto &mdash; cryptographic functions</title>

 <indexterm zone="pgcrypto">
  <primary>pgcrypto</primary>
 </indexterm>

 <indexterm zone="pgcrypto">
  <primary>encryption</primary>
  <secondary>for specific columns</secondary>
 </indexterm>

 <para>
  The <filename>pgcrypto</filename> module provides cryptographic functions for
  <productname>PostgreSQL</productname>.
 </para>

 <para>
  This module is considered <quote>trusted</quote>, that is, it can be
  installed by non-superusers who have <literal>CREATE</literal> privilege
  on the current database.
 </para>

 <para>
  <filename>pgcrypto</filename> requires OpenSSL and won't be installed if
  OpenSSL support was not selected when PostgreSQL was built.
 </para>

 <sect2 id="pgcrypto-general-hashing-funcs">
  <title>General Hashing Functions</title>

  <sect3 id="pgcrypto-general-hashing-funcs-digest">
   <title><function>digest()</function></title>

   <indexterm>
    <primary>digest</primary>
   </indexterm>

<synopsis>
digest(data text, type text) returns bytea
digest(data bytea, type text) returns bytea
</synopsis>

   <para>
    Computes a binary hash of the given <parameter>data</parameter>.
    <parameter>type</parameter> is the algorithm to use.
    Standard algorithms are <literal>md5</literal>, <literal>sha1</literal>,
    <literal>sha224</literal>, <literal>sha256</literal>,
    <literal>sha384</literal> and <literal>sha512</literal>.
    Moreover, any digest algorithm <productname>OpenSSL</productname> supports
    is automatically picked up.
   </para>

   <para>
    If you want the digest as a hexadecimal string, use
    <function>encode()</function> on the result.  For example:
<programlisting>
CREATE OR REPLACE FUNCTION sha1(bytea) RETURNS text AS $$
    SELECT encode(digest($1, 'sha1'), 'hex')
$$ LANGUAGE SQL STRICT IMMUTABLE;
</programlisting>
   </para>
  </sect3>

  <sect3 id="pgcrypto-general-hashing-funcs-hmac">
   <title><function>hmac()</function></title>

   <indexterm>
    <primary>hmac</primary>
   </indexterm>

<synopsis>
hmac(data text, key text, type text) returns bytea
hmac(data bytea, key bytea, type text) returns bytea
</synopsis>

   <para>
    Calculates hashed MAC for <parameter>data</parameter> with key <parameter>key</parameter>.
    <parameter>type</parameter> is the same as in <function>digest()</function>.
   </para>

   <para>
    This is similar to <function>digest()</function> 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.
   </para>

   <para>
    If the key is larger than the hash block size it will first be hashed and
    the result will be used as key.
   </para>
  </sect3>
 </sect2>

 <sect2 id="pgcrypto-password-hashing-funcs">
  <title>Password Hashing Functions</title>

  <para>
   The functions <function>crypt()</function> and <function>gen_salt()</function>
   are specifically designed for hashing passwords.
   <function>crypt()</function> does the hashing and <function>gen_salt()</function>
   prepares algorithm parameters for it.
  </para>

  <para>
   The algorithms in <function>crypt()</function> differ from the usual
   MD5 or SHA-1 hashing algorithms in the following respects:
  </para>

  <orderedlist>
   <listitem>
    <para>
     They are slow.  As the amount of data is so small, this is the only
     way to make brute-forcing passwords hard.
    </para>
   </listitem>
   <listitem>
    <para>
     They use a random value, called the <firstterm>salt</firstterm>, so that users
     having the same password will have different encrypted passwords.
     This is also an additional defense against reversing the algorithm.
    </para>
   </listitem>
   <listitem>
    <para>
     They include the algorithm type in the result, so passwords hashed with
     different algorithms can co-exist.
    </para>
   </listitem>
   <listitem>
    <para>
     Some of them are adaptive &mdash; that means when computers get
     faster, you can tune the algorithm to be slower, without
     introducing incompatibility with existing passwords.
    </para>
   </listitem>
  </orderedlist>

  <para>
   <xref linkend="pgcrypto-crypt-algorithms"/> lists the algorithms
   supported by the <function>crypt()</function> function.
  </para>

  <table id="pgcrypto-crypt-algorithms">
   <title>Supported Algorithms for <function>crypt()</function></title>
   <tgroup cols="6">
    <colspec colname="col1" colwidth="1.5*"/>
    <colspec colname="col2" colwidth="1.25*"/>
    <colspec colname="col3" colwidth="1*"/>
    <colspec colname="col4" colwidth="1*"/>
    <colspec colname="col5" colwidth="1*"/>
    <colspec colname="col6" colwidth="2.5*"/>
    <thead>
     <row>
      <entry>Algorithm</entry>
      <entry>Max Password Length</entry>
      <entry>Adaptive?</entry>
      <entry>Salt Bits</entry>
      <entry>Output Length</entry>
      <entry>Description</entry>
     </row>
    </thead>
    <tbody>
     <row>
      <entry><literal>bf</literal></entry>
      <entry>72</entry>
      <entry>yes</entry>
      <entry>128</entry>
      <entry>60</entry>
      <entry>Blowfish-based, variant 2a</entry>
     </row>
     <row>
      <entry><literal>md5</literal></entry>
      <entry>unlimited</entry>
      <entry>no</entry>
      <entry>48</entry>
      <entry>34</entry>
      <entry>MD5-based crypt</entry>
     </row>
     <row>
      <entry><literal>xdes</literal></entry>
      <entry>8</entry>
      <entry>yes</entry>
      <entry>24</entry>
      <entry>20</entry>
      <entry>Extended DES</entry>
     </row>
     <row>
      <entry><literal>des</literal></entry>
      <entry>8</entry>
      <entry>no</entry>
      <entry>12</entry>
      <entry>13</entry>
      <entry>Original UNIX crypt</entry>
     </row>
     <row>
      <entry><literal>sha256crypt</literal></entry>
      <entry>unlimited</entry>
      <entry>yes</entry>
      <entry>up to 32</entry>
      <entry>80</entry>
      <entry>Adapted from publicly available reference implementation
       <ulink url="https://www.akkadia.org/drepper/SHA-crypt.txt">Unix crypt using SHA-256 and SHA-512
       </ulink>
      </entry>
     </row>
     <row>
      <entry><literal>sha512crypt</literal></entry>
      <entry>unlimited</entry>
      <entry>yes</entry>
      <entry>up to 32</entry>
      <entry>123</entry>
      <entry>Adapted from publicly available reference implementation
       <ulink url="https://www.akkadia.org/drepper/SHA-crypt.txt">Unix crypt using SHA-256 and SHA-512
       </ulink>
      </entry>
     </row>

    </tbody>
   </tgroup>
  </table>

  <sect3 id="pgcrypto-password-hashing-funcs-crypt">
   <title><function>crypt()</function></title>

   <indexterm>
    <primary>crypt</primary>
   </indexterm>

<synopsis>
crypt(password text, salt text) returns text
</synopsis>

   <para>
    Calculates a crypt(3)-style hash of <parameter>password</parameter>.
    When storing a new password, you need to use
    <function>gen_salt()</function> to generate a new <parameter>salt</parameter> value.
    To check a password, pass the stored hash value as <parameter>salt</parameter>,
    and test whether the result matches the stored value.
   </para>
   <para>
    Example of setting a new password:
<programlisting>
UPDATE ... SET pswhash = crypt('new password', gen_salt('md5'));
</programlisting>
   </para>
   <para>
    Example of authentication:
<programlisting>
SELECT (pswhash = crypt('entered password', pswhash)) AS pswmatch FROM ... ;
</programlisting>
    This returns <literal>true</literal> if the entered password is correct.
   </para>
  </sect3>

  <sect3 id="pgcrypto-password-hashing-funcs-gen-salt">
   <title><function>gen_salt()</function></title>

  <indexterm>
   <primary>gen_salt</primary>
  </indexterm>

<synopsis>
gen_salt(type text [, iter_count integer ]) returns text
</synopsis>

   <para>
    Generates a new random salt string for use in <function>crypt()</function>.
    The salt string also tells <function>crypt()</function> which algorithm to use.
   </para>

   <para>
    The <parameter>type</parameter> parameter specifies the hashing algorithm.
    The accepted types are: <literal>des</literal>, <literal>xdes</literal>,
    <literal>md5</literal>, <literal>bf</literal>, <literal>sha256crypt</literal> and
    <literal>sha512crypt</literal>. The last two, <literal>sha256crypt</literal> and
    <literal>sha512crypt</literal> are modern <literal>SHA-2</literal> based password hashes.
   </para>

   <para>
    The <parameter>iter_count</parameter> 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
    &mdash; which is somewhat impractical.  If the <parameter>iter_count</parameter>
    parameter is omitted, the default iteration count is used.
    Allowed values for <parameter>iter_count</parameter> depend on the algorithm and
    are shown in <xref linkend="pgcrypto-icfc-table"/>.
   </para>

   <table id="pgcrypto-icfc-table">
    <title>Iteration Counts for <function>crypt()</function></title>
    <tgroup cols="4">
     <thead>
      <row>
       <entry>Algorithm</entry>
       <entry>Default</entry>
       <entry>Min</entry>
       <entry>Max</entry>
      </row>
     </thead>
     <tbody>
      <row>
       <entry><literal>xdes</literal></entry>
       <entry>725</entry>
       <entry>1</entry>
       <entry>16777215</entry>
      </row>
      <row>
       <entry><literal>bf</literal></entry>
       <entry>6</entry>
       <entry>4</entry>
       <entry>31</entry>
      </row>
      <row>
       <entry><literal>sha256crypt, sha512crypt</literal></entry>
       <entry>5000</entry>
       <entry>1000</entry>
       <entry>999999999</entry>
      </row>
     </tbody>
    </tgroup>
   </table>

   <para>
    For <literal>xdes</literal> there is an additional limitation that the
    iteration count must be an odd number.
   </para>

   <para>
    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.
   </para>

   <para>
    <xref linkend="pgcrypto-hash-speed-table"/> 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 lower case letters, or
    upper- and lower-case letters and numbers.
    In the <literal>crypt-bf</literal> entries, the number after a slash is
    the <parameter>iter_count</parameter> parameter of
    <function>gen_salt</function>.
   </para>

   <para>
   The default <parameter>iter_count</parameter> for <literal>sha256crypt</literal> and
   <literal>sha512crypt</literal> of <literal>5000</literal> is considered too low for modern
   hardware, but can be adjusted to generate stronger password hashes.
   Otherwise both hashes, <literal>sha256crypt</literal> and <literal>sha512crypt</literal> are
   considered safe.
   </para>

   <table id="pgcrypto-hash-speed-table">
    <title>Hash Algorithm Speeds</title>
    <tgroup cols="5">
     <thead>
      <row>
       <entry>Algorithm</entry>
       <entry>Hashes/sec</entry>
       <entry>For <literal>[a-z]</literal></entry>
       <entry>For <literal>[A-Za-z0-9]</literal></entry>
       <entry>Duration relative to <literal>md5 hash</literal></entry>
      </row>
     </thead>
     <tbody>
      <row>
       <entry><literal>crypt-bf/8</literal></entry>
       <entry>1792</entry>
       <entry>4 years</entry>
       <entry>3927 years</entry>
       <entry>100k</entry>
      </row>
      <row>
       <entry><literal>crypt-bf/7</literal></entry>
       <entry>3648</entry>
       <entry>2 years</entry>
       <entry>1929 years</entry>
       <entry>50k</entry>
      </row>
      <row>
       <entry><literal>crypt-bf/6</literal></entry>
       <entry>7168</entry>
       <entry>1 year</entry>
       <entry>982 years</entry>
       <entry>25k</entry>
      </row>
      <row>
       <entry><literal>crypt-bf/5</literal></entry>
       <entry>13504</entry>
       <entry>188 days</entry>
       <entry>521 years</entry>
       <entry>12.5k</entry>
      </row>
      <row>
       <entry><literal>crypt-md5</literal></entry>
       <entry>171584</entry>
       <entry>15 days</entry>
       <entry>41 years</entry>
       <entry>1k</entry>
      </row>
      <row>
       <entry><literal>crypt-des</literal></entry>
       <entry>23221568</entry>
       <entry>157.5 minutes</entry>
       <entry>108 days</entry>
       <entry>7</entry>
      </row>
      <row>
       <entry><literal>sha1</literal></entry>
       <entry>37774272</entry>
       <entry>90 minutes</entry>
       <entry>68 days</entry>
       <entry>4</entry>
      </row>
      <row>
       <entry><literal>md5</literal> (hash)</entry>
       <entry>150085504</entry>
       <entry>22.5 minutes</entry>
       <entry>17 days</entry>
       <entry>1</entry>
      </row>
     </tbody>
    </tgroup>
   </table>

   <para>
    Notes:
   </para>

   <itemizedlist>
    <listitem>
     <para>
     The machine used is an Intel Mobile Core i3.
     </para>
    </listitem>
    <listitem>
     <para>
      <literal>crypt-des</literal> and <literal>crypt-md5</literal> algorithm numbers are
      taken from John the Ripper v1.6.38 <literal>-test</literal> output.
     </para>
    </listitem>
    <listitem>
     <para>
      <literal>md5 hash</literal> numbers are from mdcrack 1.2.
     </para>
    </listitem>
    <listitem>
     <para>
      <literal>sha1</literal> numbers are from lcrack-20031130-beta.
     </para>
    </listitem>
    <listitem>
     <para>
      <literal>crypt-bf</literal> numbers are taken using a simple program that
      loops over 1000 8-character passwords.  That way the speed
      with different numbers of iterations can be shown.  For reference: <literal>john
      -test</literal> shows 13506 loops/sec for <literal>crypt-bf/5</literal>.
      (The very small
      difference in results is in accordance with the fact that the
      <literal>crypt-bf</literal> implementation in <filename>pgcrypto</filename>
      is the same one used in John the Ripper.)
     </para>
    </listitem>
   </itemizedlist>

   <para>
    Note that <quote>try all combinations</quote> 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, while a 6-character non-word-like password may escape
    cracking.  Or not.
   </para>
  </sect3>
 </sect2>

 <sect2 id="pgcrypto-pgp-enc-funcs">
  <title>PGP Encryption Functions</title>

  <para>
   The functions here implement the encryption part of the OpenPGP
   (<ulink url="https://datatracker.ietf.org/doc/html/rfc4880">RFC 4880</ulink>)
   standard.  Supported are both symmetric-key and public-key encryption.
  </para>

  <para>
   An encrypted PGP message consists of 2 parts, or <firstterm>packets</firstterm>:
  </para>
  <itemizedlist>
   <listitem>
    <para>
     Packet containing a session key &mdash; either symmetric-key or public-key
     encrypted.
    </para>
   </listitem>
   <listitem>
    <para>
     Packet containing data encrypted with the session key.
    </para>
   </listitem>
  </itemizedlist>

  <para>
   When encrypting with a symmetric key (i.e., a password):
  </para>
  <orderedlist>
   <listitem>
    <para>
     The given password is hashed using a String2Key (S2K) algorithm.  This is
     rather similar to <function>crypt()</function> algorithms &mdash; purposefully
     slow and with random salt &mdash; but it produces a full-length binary
     key.
    </para>
   </listitem>
   <listitem>
    <para>
     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.
    </para>
   </listitem>
   <listitem>
    <para>
     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.
    </para>
   </listitem>
  </orderedlist>

  <para>
   When encrypting with a public key:
  </para>
  <orderedlist>
   <listitem>
    <para>
     A new random session key is generated.
    </para>
   </listitem>
   <listitem>
    <para>
     It is encrypted using the public key and put into the session key packet.
    </para>
   </listitem>
  </orderedlist>

  <para>
   In either case the data to be encrypted is processed as follows:
  </para>
  <orderedlist>
   <listitem>
    <para>
     Optional data-manipulation: compression, conversion to UTF-8,
     and/or conversion of line-endings.
    </para>
   </listitem>
   <listitem>
    <para>
     The data is prefixed with a block of random bytes.  This is equivalent
     to using a random IV.
    </para>
   </listitem>
   <listitem>
    <para>
     A SHA-1 hash of the random prefix and data is appended.
    </para>
   </listitem>
   <listitem>
    <para>
     All this is encrypted with the session key and placed in the data packet.
    </para>
   </listitem>
  </orderedlist>

  <sect3 id="pgcrypto-pgp-enc-funcs-pgp-sym-encrypt">
   <title><function>pgp_sym_encrypt()</function></title>

   <indexterm>
    <primary>pgp_sym_encrypt</primary>
   </indexterm>

   <indexterm>
    <primary>pgp_sym_encrypt_bytea</primary>
   </indexterm>

<synopsis>
pgp_sym_encrypt(data text, psw text [, options text ]) returns bytea
pgp_sym_encrypt_bytea(data bytea, psw text [, options text ]) returns bytea
</synopsis>
   <para>
    Encrypt <parameter>data</parameter> with a symmetric PGP key <parameter>psw</parameter>.
    The <parameter>options</parameter> parameter can contain option settings,
    as described below.
   </para>
  </sect3>

  <sect3 id="pgcrypto-pgp-enc-funcs-pgp-sym-decrypt">
   <title><function>pgp_sym_decrypt()</function></title>

   <indexterm>
    <primary>pgp_sym_decrypt</primary>
   </indexterm>

   <indexterm>
    <primary>pgp_sym_decrypt_bytea</primary>
   </indexterm>

<synopsis>
pgp_sym_decrypt(msg bytea, psw text [, options text ]) returns text
pgp_sym_decrypt_bytea(msg bytea, psw text [, options text ]) returns bytea
</synopsis>
   <para>
    Decrypt a symmetric-key-encrypted PGP message.
   </para>
   <para>
    Decrypting <type>bytea</type> data with <function>pgp_sym_decrypt</function> is disallowed.
    This is to avoid outputting invalid character data.  Decrypting
    originally textual data with <function>pgp_sym_decrypt_bytea</function> is fine.
   </para>
   <para>
    The <parameter>options</parameter> parameter can contain option settings,
    as described below.
   </para>
  </sect3>

  <sect3 id="pgcrypto-pgp-enc-funcs-pgp-pub-encrypt">
   <title><function>pgp_pub_encrypt()</function></title>

   <indexterm>
    <primary>pgp_pub_encrypt</primary>
   </indexterm>

   <indexterm>
    <primary>pgp_pub_encrypt_bytea</primary>
   </indexterm>

<synopsis>
pgp_pub_encrypt(data text, key bytea [, options text ]) returns bytea
pgp_pub_encrypt_bytea(data bytea, key bytea [, options text ]) returns bytea
</synopsis>
   <para>
    Encrypt <parameter>data</parameter> with a public PGP key <parameter>key</parameter>.
    Giving this function a secret key will produce an error.
   </para>
   <para>
    The <parameter>options</parameter> parameter can contain option settings,
    as described below.
   </para>
  </sect3>

  <sect3 id="pgcrypto-pgp-enc-funcs-pgp-pub-decrypt">
   <title><function>pgp_pub_decrypt()</function></title>

   <indexterm>
    <primary>pgp_pub_decrypt</primary>
   </indexterm>

   <indexterm>
    <primary>pgp_pub_decrypt_bytea</primary>
   </indexterm>

<synopsis>
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
</synopsis>
   <para>
    Decrypt a public-key-encrypted message.  <parameter>key</parameter> 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
    <parameter>psw</parameter>.  If there is no password, but you want to specify
    options, you need to give an empty password.
   </para>
   <para>
    Decrypting <type>bytea</type> data with <function>pgp_pub_decrypt</function> is disallowed.
    This is to avoid outputting invalid character data.  Decrypting
    originally textual data with <function>pgp_pub_decrypt_bytea</function> is fine.
   </para>
   <para>
    The <parameter>options</parameter> parameter can contain option settings,
    as described below.
   </para>
  </sect3>

  <sect3 id="pgcrypto-pgp-enc-funcs-pgp-key-id">
   <title><function>pgp_key_id()</function></title>

   <indexterm>
    <primary>pgp_key_id</primary>
   </indexterm>

<synopsis>
pgp_key_id(bytea) returns text
</synopsis>
   <para>
    <function>pgp_key_id</function> 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.
   </para>
   <para>
    It can return 2 special key IDs:
   </para>
   <itemizedlist>
    <listitem>
     <para>
      <literal>SYMKEY</literal>
     </para>
     <para>
      The message is encrypted with a symmetric key.
     </para>
    </listitem>
    <listitem>
     <para>
      <literal>ANYKEY</literal>
     </para>
     <para>
      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.  <filename>pgcrypto</filename> itself does not produce
      such messages.
     </para>
    </listitem>
   </itemizedlist>
   <para>
    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 &mdash; like handling <literal>ANYKEY</literal>.
   </para>
  </sect3>

  <sect3 id="pgcrypto-pgp-enc-funcs-armor">
   <title><function>armor()</function>, <function>dearmor()</function></title>

   <indexterm>
    <primary>armor</primary>
   </indexterm>

   <indexterm>
    <primary>dearmor</primary>
   </indexterm>

<synopsis>
armor(data bytea [ , keys text[], values text[] ]) returns text
dearmor(data text) returns bytea
</synopsis>
   <para>
    These functions wrap/unwrap binary data into PGP ASCII-armor format,
    which is basically Base64 with CRC and additional formatting.
   </para>

   <para>
    If the <parameter>keys</parameter> and <parameter>values</parameter> arrays are specified,
    an <firstterm>armor header</firstterm> is added to the armored format for each
    key/value pair. Both arrays must be single-dimensional, and they must
    be of the same length.  The keys and values cannot contain any non-ASCII
    characters.
   </para>
  </sect3>

  <sect3 id="pgcrypto-pgp-enc-funcs-pgp-armor-headers">
   <title><function>pgp_armor_headers</function></title>

   <indexterm>
    <primary>pgp_armor_headers</primary>
   </indexterm>

<synopsis>
pgp_armor_headers(data text, key out text, value out text) returns setof record
</synopsis>
   <para>
    <function>pgp_armor_headers()</function> extracts the armor headers from
    <parameter>data</parameter>.  The return value is a set of rows with two columns,
    key and value.  If the keys or values contain any non-ASCII characters,
    they are treated as UTF-8.
   </para>
  </sect3>

  <sect3 id="pgcrypto-pgp-enc-funcs-opts">
   <title>Options for PGP Functions</title>

   <para>
    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:
<programlisting>
pgp_sym_encrypt(data, psw, 'compress-algo=1, cipher-algo=aes256')
</programlisting>
   </para>

   <para>
    All of the options except <literal>convert-crlf</literal> apply only to
    encrypt functions.  Decrypt functions get the parameters from the PGP
    data.
   </para>

   <para>
    The most interesting options are probably
    <literal>compress-algo</literal> and <literal>unicode-mode</literal>.
    The rest should have reasonable defaults.
   </para>

  <sect4 id="pgcrypto-pgp-enc-funcs-opts-cipher-algo">
   <title>cipher-algo</title>

   <para>
    Which cipher algorithm to use.
   </para>
<literallayout>
Values: bf, aes128, aes192, aes256, 3des, cast5
Default: aes128
Applies to: pgp_sym_encrypt, pgp_pub_encrypt
</literallayout>
  </sect4>

  <sect4 id="pgcrypto-pgp-enc-funcs-opts-compress-algo">
   <title>compress-algo</title>

   <para>
    Which compression algorithm to use.  Only available if
    <productname>PostgreSQL</productname> was built with zlib.
   </para>
<literallayout>
Values:
  0 - no compression
  1 - ZIP compression
  2 - ZLIB compression (= ZIP plus meta-data and block CRCs)
Default: 0
Applies to: pgp_sym_encrypt, pgp_pub_encrypt
</literallayout>
  </sect4>

  <sect4 id="pgcrypto-pgp-enc-funcs-opts-compress-level">
   <title>compress-level</title>

   <para>
    How much to compress.  Higher levels compress smaller but are slower.
    0 disables compression.
   </para>
<literallayout>
Values: 0, 1-9
Default: 6
Applies to: pgp_sym_encrypt, pgp_pub_encrypt
</literallayout>
  </sect4>

  <sect4 id="pgcrypto-pgp-enc-funcs-opts-convert-crlf">
   <title>convert-crlf</title>

   <para>
    Whether to convert <literal>\n</literal> into <literal>\r\n</literal> when
    encrypting and <literal>\r\n</literal> to <literal>\n</literal> when
    decrypting.  <acronym>RFC</acronym> 4880 specifies that text data should be stored using
    <literal>\r\n</literal> line-feeds.  Use this to get fully RFC-compliant
    behavior.
   </para>
<literallayout>
Values: 0, 1
Default: 0
Applies to: pgp_sym_encrypt, pgp_pub_encrypt, pgp_sym_decrypt, pgp_pub_decrypt
</literallayout>
  </sect4>

  <sect4 id="pgcrypto-pgp-enc-funcs-opts-disable-mdc">
   <title>disable-mdc</title>

   <para>
    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 <acronym>RFC</acronym> 4880.
    Recent gnupg.org and pgp.com software supports it fine.
   </para>
<literallayout>
Values: 0, 1
Default: 0
Applies to: pgp_sym_encrypt, pgp_pub_encrypt
</literallayout>
  </sect4>

  <sect4 id="pgcrypto-pgp-enc-funcs-opts-sess-key">
   <title>sess-key</title>

   <para>
    Use separate session key.  Public-key encryption always uses a separate
    session key; this option is for symmetric-key encryption, which by default
    uses the S2K key directly.
   </para>
<literallayout>
Values: 0, 1
Default: 0
Applies to: pgp_sym_encrypt
</literallayout>
  </sect4>

  <sect4 id="pgcrypto-pgp-enc-funcs-opts-s2k-mode">
   <title>s2k-mode</title>

   <para>
    Which S2K algorithm to use.
   </para>
<literallayout>
Values:
  0 - Without salt.  Dangerous!
  1 - With salt but with fixed iteration count.
  3 - Variable iteration count.
Default: 3
Applies to: pgp_sym_encrypt
</literallayout>
  </sect4>

  <sect4 id="pgcrypto-pgp-enc-funcs-opts-s2k-count">
   <title>s2k-count</title>

   <para>
    The number of iterations of the S2K algorithm to use.  It must
    be a value between 1024 and 65011712, inclusive.
   </para>
<literallayout>
Default: A random value between 65536 and 253952
Applies to: pgp_sym_encrypt, only with s2k-mode=3
</literallayout>
  </sect4>

  <sect4 id="pgcrypto-pgp-enc-funcs-opts-s2k-digest-algo">
   <title>s2k-digest-algo</title>

   <para>
    Which digest algorithm to use in S2K calculation.
   </para>
<literallayout>
Values: md5, sha1
Default: sha1
Applies to: pgp_sym_encrypt
</literallayout>
  </sect4>

  <sect4 id="pgcrypto-pgp-enc-funcs-opts-s2k-cipher-algo">
   <title>s2k-cipher-algo</title>

   <para>
    Which cipher to use for encrypting separate session key.
   </para>
<literallayout>
Values: bf, aes, aes128, aes192, aes256
Default: use cipher-algo
Applies to: pgp_sym_encrypt
</literallayout>
  </sect4>

  <sect4 id="pgcrypto-pgp-enc-funcs-opts-unicode-mode">
   <title>unicode-mode</title>

   <para>
    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, but the message will be tagged as UTF-8.  Without this option
    it will not be.
   </para>
<literallayout>
Values: 0, 1
Default: 0
Applies to: pgp_sym_encrypt, pgp_pub_encrypt
</literallayout>
  </sect4>

  <sect4 id="pgcrypto-pgp-enc-funcs-opts-ignore-cipher-failure">
   <title>ignore-cipher-failure</title>

   <para>
    Dangerous! Instructs pgcrypto to use an incorrect decryption algorithm
    matching the historical behavior prior to the fix for CVE-2026-14663, by
    completely ignoring failures from the OpenSSL cipher in use. This is
    intended only for users who need to recover incorrectly-encrypted messages
    created when the <literal>cipher-algo</literal> was unavailable under the
    OpenSSL configuration in use. Such faulty messages do not require the
    correct decryption key when <literal>ignore-cipher-failure</literal> is
    enabled, so there is no guarantee that the decrypted plaintext actually
    originated from a holder of the key.
   </para>
   <para>
    Contrast the case of a message which was correctly encrypted, but the cipher
    that produced it is unavailable under the current <productname>OpenSSL</productname>
    configuration. Recovering such plaintext via <filename>pgcrypto</filename>
    requires making the actual cipher available to OpenSSL by, for example,
    enabling the appropriate provider. <literal>ignore-cipher-failure</literal>
    is not necessary or helpful for that scenario. If <quote>decryption</quote>
    of a correctly encrypted message with this option happens to pass PGP
    integrity checks, that result is coincidental and does not make the
    recovered plaintext trustworthy.
   </para>
<literallayout>
Values: 0, 1
Default: 0
Applies to: pgp_sym_decrypt, pgp_pub_decrypt
</literallayout>
  </sect4>
  </sect3>

 <sect3 id="pgcrypto-pgp-enc-funcs-gnupg">
  <title>Generating PGP Keys with GnuPG</title>

  <para>
   To generate a new key:
<programlisting>
gpg --gen-key
</programlisting>
  </para>
  <para>
   The preferred key type is <quote>DSA and Elgamal</quote>.
  </para>
  <para>
   For RSA encryption you must create either DSA or RSA sign-only key
   as master and then add an RSA encryption subkey with
   <literal>gpg --edit-key</literal>.
  </para>
  <para>
   To list keys:
<programlisting>
gpg --list-secret-keys
</programlisting>
  </para>
  <para>
   To export a public key in ASCII-armor format:
<programlisting>
gpg -a --export KEYID > public.key
</programlisting>
  </para>
  <para>
   To export a secret key in ASCII-armor format:
<programlisting>
gpg -a --export-secret-keys KEYID > secret.key
</programlisting>
  </para>
  <para>
   You need to use <function>dearmor()</function> on these keys before giving them to
   the PGP functions.  Or if you can handle binary data, you can drop
   <literal>-a</literal> from the command.
  </para>
  <para>
   For more details see <literal>man gpg</literal>,
   <ulink url="https://www.gnupg.org/gph/en/manual.html">The GNU
   Privacy Handbook</ulink> and other documentation on
   <ulink url="https://www.gnupg.org/"></ulink>.
  </para>
 </sect3>

 <sect3 id="pgcrypto-pgp-enc-funcs-limitations">
  <title>Limitations of PGP Code</title>

  <itemizedlist>
   <listitem>
    <para>
    No support for signing.  That also means that it is not checked
    whether the encryption subkey belongs to the master key.
    </para>
   </listitem>
   <listitem>
    <para>
    No support for encryption key as master key.  As such practice
    is generally discouraged, this should not be a problem.
    </para>
   </listitem>
   <listitem>
    <para>
    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 <filename>pgcrypto</filename>, but create new ones,
    as the usage scenario is rather different.
    </para>
   </listitem>
  </itemizedlist>
  </sect3>
 </sect2>

 <sect2 id="pgcrypto-raw-enc-funcs">
  <title>Raw Encryption Functions</title>

  <para>
   These functions only run a cipher over data; they don't have any advanced
   features of PGP encryption.  Therefore they have some major problems:
  </para>
  <orderedlist>
   <listitem>
    <para>
    They use user key directly as cipher key.
    </para>
   </listitem>
   <listitem>
    <para>
    They don't provide any integrity checking, to see
    if the encrypted data was modified.
    </para>
   </listitem>
   <listitem>
    <para>
    They expect that users manage all encryption parameters
    themselves, even IV.
    </para>
   </listitem>
   <listitem>
    <para>
    They don't handle text.
    </para>
   </listitem>
  </orderedlist>
  <para>
   So, with the introduction of PGP encryption, usage of raw
   encryption functions is discouraged.
  </para>

  <indexterm>
   <primary>encrypt</primary>
  </indexterm>

  <indexterm>
   <primary>decrypt</primary>
  </indexterm>

  <indexterm>
   <primary>encrypt_iv</primary>
  </indexterm>

  <indexterm>
   <primary>decrypt_iv</primary>
  </indexterm>

<synopsis>
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
</synopsis>

  <para>
   Encrypt/decrypt data using the cipher method specified by
   <parameter>type</parameter>.  The syntax of the
   <parameter>type</parameter> string is:

<synopsis>
<replaceable>algorithm</replaceable> <optional> <literal>-</literal> <replaceable>mode</replaceable> </optional> <optional> <literal>/pad:</literal> <replaceable>padding</replaceable> </optional>
</synopsis>
   where <replaceable>algorithm</replaceable> is one of:

  <itemizedlist>
   <listitem><para><literal>bf</literal> &mdash; Blowfish</para></listitem>
   <listitem><para><literal>aes</literal> &mdash; AES (Rijndael-128, -192 or -256)</para></listitem>
  </itemizedlist>
   and <replaceable>mode</replaceable> is one of:
  <itemizedlist>
   <listitem>
    <para>
    <literal>cbc</literal> &mdash; next block depends on previous (default)
    </para>
   </listitem>
   <listitem>
    <para>
    <literal>cfb</literal> &mdash; next block depends on previous encrypted block
    </para>
   </listitem>
   <listitem>
    <para>
    <literal>ecb</literal> &mdash; each block is encrypted separately (for
    testing only)
    </para>
   </listitem>
  </itemizedlist>
   and <replaceable>padding</replaceable> is one of:
  <itemizedlist>
   <listitem>
    <para>
    <literal>pkcs</literal> &mdash; data may be any length (default)
    </para>
   </listitem>
   <listitem>
    <para>
    <literal>none</literal> &mdash; data must be multiple of cipher block size
    </para>
   </listitem>
  </itemizedlist>
  </para>
  <para>
   So, for example, these are equivalent:
<programlisting>
encrypt(data, 'fooz', 'bf')
encrypt(data, 'fooz', 'bf-cbc/pad:pkcs')
</programlisting>
  </para>
  <para>
   In <function>encrypt_iv</function> and <function>decrypt_iv</function>, the
   <parameter>iv</parameter> parameter is the initial value for the CBC and
   CFB 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.
  </para>
 </sect2>

 <sect2 id="pgcrypto-random-data-funcs">
  <title>Random-Data Functions</title>

  <indexterm>
   <primary>gen_random_bytes</primary>
  </indexterm>

<synopsis>
gen_random_bytes(count integer) returns bytea
</synopsis>
  <para>
   Returns <parameter>count</parameter> cryptographically strong random bytes.
   At most 1024 bytes can be extracted at a time.  This is to avoid
   draining the randomness generator pool.
  </para>

  <indexterm>
   <primary>gen_random_uuid</primary>
  </indexterm>

<synopsis>
gen_random_uuid() returns uuid
</synopsis>
  <para>
   Returns a version 4 (random) UUID. (Obsolete, this function
   internally calls the <link linkend="functions-uuid">core
   function</link> of the same name.)
  </para>
 </sect2>

 <sect2 id="pgcrypto-openssl-support-funcs">
  <title>OpenSSL Support Functions</title>

  <indexterm>
   <primary>fips_mode</primary>
  </indexterm>

<synopsis>
fips_mode() returns boolean
</synopsis>
  <para>
   Returns <literal>true</literal> if <productname>OpenSSL</productname> is
   running with FIPS mode enabled, otherwise <literal>false</literal>.
  </para>
 </sect2>

 <sect2 id="pgcrypto-configuration-parameters">
  <title>Configuration Parameters</title>

 <para>
  There is one configuration parameter that controls the behavior of
  <filename>pgcrypto</filename>.
 </para>

  <variablelist>
   <varlistentry id="pgcrypto-configuration-parameters-builtin_crypto_enabled">
    <term>
     <varname>pgcrypto.builtin_crypto_enabled</varname> (<type>enum</type>)
     <indexterm>
      <primary><varname>pgcrypto.builtin_crypto_enabled</varname> configuration
      parameter</primary>
     </indexterm>
    </term>
    <listitem>
     <para>
      <varname>pgcrypto.builtin_crypto_enabled</varname> determines if the
      built in crypto functions <function>gen_salt()</function>, and
      <function>crypt()</function> are available for use. Setting this to
      <literal>off</literal> disables these functions. <literal>on</literal>
      (the default) enables these functions to work normally.
      <literal>fips</literal> disables these functions if
      <productname>OpenSSL</productname> is detected to operate in FIPS mode.
     </para>
     <para>
      <function>pgp_sym_encrypt()</function> and
      <function>pgp_pub_encrypt()</function> do not use built in crypto so
      they are not affected.
     </para>
    </listitem>
   </varlistentry>
  </variablelist>

  <para>
   In ordinary usage, this parameter is set
   in <filename>postgresql.conf</filename>, although superusers can alter it
   on-the-fly within their own sessions.
  </para>
 </sect2>

 <sect2 id="pgcrypto-notes">
  <title>Notes</title>

  <sect3 id="pgcrypto-notes-config">
   <title>Configuration</title>

   <para>
    <filename>pgcrypto</filename> configures itself according to the findings of the
    main PostgreSQL <literal>configure</literal> script.  The options that
    affect it are <literal>--with-zlib</literal> and
    <literal>--with-ssl=openssl</literal>.
   </para>

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

   <para>
    <filename>pgcrypto</filename> requires <productname>OpenSSL</productname>.
    Otherwise, it will not be built or installed.
   </para>

   <para>
    When compiled against <productname>OpenSSL</productname> 3.0.0 and later
    versions, the legacy provider must be activated in the
    <filename>openssl.cnf</filename> configuration file in order to use older
    ciphers like DES or Blowfish.
   </para>
  </sect3>

  <sect3 id="pgcrypto-notes-null-handling">
   <title>NULL Handling</title>

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

  <sect3 id="pgcrypto-notes-sec-limits">
   <title>Security Limitations</title>

   <para>
    All <filename>pgcrypto</filename> functions run inside the database server.
    That means that all
    the data and passwords move between <filename>pgcrypto</filename> and client
    applications in clear text.  Thus you must:
   </para>

   <orderedlist>
    <listitem>
     <para>Connect locally or use SSL connections.</para>
    </listitem>
    <listitem>
     <para>Trust both system and database administrator.</para>
    </listitem>
   </orderedlist>

   <para>
    If you cannot, then better do crypto inside client application.
   </para>

   <para>
    The implementation does not resist
    <ulink url="https://en.wikipedia.org/wiki/Side-channel_attack">side-channel
    attacks</ulink>.  For example, the time required for
    a <filename>pgcrypto</filename> decryption function to complete varies among
    ciphertexts of a given size.
   </para>
  </sect3>
 </sect2>

 <sect2 id="pgcrypto-author">
  <title>Author</title>

  <para>
   Marko Kreen <email>markokr@gmail.com</email>
  </para>

  <para>
   <filename>pgcrypto</filename> uses code from the following sources:
  </para>

  <informaltable>
   <tgroup cols="3">
    <thead>
     <row>
      <entry>Algorithm</entry>
      <entry>Author</entry>
      <entry>Source origin</entry>
     </row>
    </thead>
    <tbody>
     <row>
      <entry>DES crypt</entry>
      <entry>David Burren and others</entry>
      <entry>FreeBSD libcrypt</entry>
     </row>
     <row>
      <entry>MD5 crypt</entry>
      <entry>Poul-Henning Kamp</entry>
      <entry>FreeBSD libcrypt</entry>
     </row>
     <row>
      <entry>Blowfish crypt</entry>
      <entry>Solar Designer</entry>
      <entry>www.openwall.com</entry>
     </row>
    </tbody>
   </tgroup>
  </informaltable>
 </sect2>

</sect1>