doc/src/sgml/pgcrypto.sgml

   1 <!-- doc/src/sgml/pgcrypto.sgml -->
   2
   3 <sect1 id="pgcrypto" xreflabel="pgcrypto">
   4  <title>pgcrypto &mdash; cryptographic functions</title>
   5
   6  <indexterm zone="pgcrypto">
   7   <primary>pgcrypto</primary>
   8  </indexterm>
   9
  10  <indexterm zone="pgcrypto">
  11   <primary>encryption</primary>
  12   <secondary>for specific columns</secondary>
  13  </indexterm>
  14
  15  <para>
  16   The <filename>pgcrypto</filename> module provides cryptographic functions for
  17   <productname>PostgreSQL</productname>.
  18  </para>
  19
  20  <para>
  21   This module is considered <quote>trusted</quote>, that is, it can be
  22   installed by non-superusers who have <literal>CREATE</literal> privilege
  23   on the current database.
  24  </para>
  25
  26  <para>
  27   <filename>pgcrypto</filename> requires OpenSSL and won't be installed if
  28   OpenSSL support was not selected when PostgreSQL was built.
  29  </para>
  30
  31  <sect2 id="pgcrypto-general-hashing-funcs">
  32   <title>General Hashing Functions</title>
  33
  34   <sect3 id="pgcrypto-general-hashing-funcs-digest">
  35    <title><function>digest()</function></title>
  36
  37    <indexterm>
  38     <primary>digest</primary>
  39    </indexterm>
  40
  41 <synopsis>
  42 digest(data text, type text) returns bytea
  43 digest(data bytea, type text) returns bytea
  44 </synopsis>
  45
  46    <para>
  47     Computes a binary hash of the given <parameter>data</parameter>.
  48     <parameter>type</parameter> is the algorithm to use.
  49     Standard algorithms are <literal>md5</literal>, <literal>sha1</literal>,
  50     <literal>sha224</literal>, <literal>sha256</literal>,
  51     <literal>sha384</literal> and <literal>sha512</literal>.
  52     Moreover, any digest algorithm <productname>OpenSSL</productname> supports
  53     is automatically picked up.
  54    </para>
  55
  56    <para>
  57     If you want the digest as a hexadecimal string, use
  58     <function>encode()</function> on the result.  For example:
  59 <programlisting>
  60 CREATE OR REPLACE FUNCTION sha1(bytea) returns text AS $$
  61     SELECT encode(digest($1, 'sha1'), 'hex')
  62 $$ LANGUAGE SQL STRICT IMMUTABLE;
  63 </programlisting>
  64    </para>
  65   </sect3>
  66
  67   <sect3 id="pgcrypto-general-hashing-funcs-hmac">
  68    <title><function>hmac()</function></title>
  69
  70    <indexterm>
  71     <primary>hmac</primary>
  72    </indexterm>
  73
  74 <synopsis>
  75 hmac(data text, key text, type text) returns bytea
  76 hmac(data bytea, key bytea, type text) returns bytea
  77 </synopsis>
  78
  79    <para>
  80     Calculates hashed MAC for <parameter>data</parameter> with key <parameter>key</parameter>.
  81     <parameter>type</parameter> is the same as in <function>digest()</function>.
  82    </para>
  83
  84    <para>
  85     This is similar to <function>digest()</function> but the hash can only be
  86     recalculated knowing the key.  This prevents the scenario of someone
  87     altering data and also changing the hash to match.
  88    </para>
  89
  90    <para>
  91     If the key is larger than the hash block size it will first be hashed and
  92     the result will be used as key.
  93    </para>
  94   </sect3>
  95  </sect2>
  96
  97  <sect2 id="pgcrypto-password-hashing-funcs">
  98   <title>Password Hashing Functions</title>
  99
 100   <para>
 101    The functions <function>crypt()</function> and <function>gen_salt()</function>
 102    are specifically designed for hashing passwords.
 103    <function>crypt()</function> does the hashing and <function>gen_salt()</function>
 104    prepares algorithm parameters for it.
 105   </para>
 106
 107   <para>
 108    The algorithms in <function>crypt()</function> differ from the usual
 109    MD5 or SHA-1 hashing algorithms in the following respects:
 110   </para>
 111
 112   <orderedlist>
 113    <listitem>
 114     <para>
 115      They are slow.  As the amount of data is so small, this is the only
 116      way to make brute-forcing passwords hard.
 117     </para>
 118    </listitem>
 119    <listitem>
 120     <para>
 121      They use a random value, called the <firstterm>salt</firstterm>, so that users
 122      having the same password will have different encrypted passwords.
 123      This is also an additional defense against reversing the algorithm.
 124     </para>
 125    </listitem>
 126    <listitem>
 127     <para>
 128      They include the algorithm type in the result, so passwords hashed with
 129      different algorithms can co-exist.
 130     </para>
 131    </listitem>
 132    <listitem>
 133     <para>
 134      Some of them are adaptive &mdash; that means when computers get
 135      faster, you can tune the algorithm to be slower, without
 136      introducing incompatibility with existing passwords.
 137     </para>
 138    </listitem>
 139   </orderedlist>
 140
 141   <para>
 142    <xref linkend="pgcrypto-crypt-algorithms"/> lists the algorithms
 143    supported by the <function>crypt()</function> function.
 144   </para>
 145
 146   <table id="pgcrypto-crypt-algorithms">
 147    <title>Supported Algorithms for <function>crypt()</function></title>
 148    <tgroup cols="6">
 149     <thead>
 150      <row>
 151       <entry>Algorithm</entry>
 152       <entry>Max Password Length</entry>
 153       <entry>Adaptive?</entry>
 154       <entry>Salt Bits</entry>
 155       <entry>Output Length</entry>
 156       <entry>Description</entry>
 157      </row>
 158     </thead>
 159     <tbody>
 160      <row>
 161       <entry><literal>bf</literal></entry>
 162       <entry>72</entry>
 163       <entry>yes</entry>
 164       <entry>128</entry>
 165       <entry>60</entry>
 166       <entry>Blowfish-based, variant 2a</entry>
 167      </row>
 168      <row>
 169       <entry><literal>md5</literal></entry>
 170       <entry>unlimited</entry>
 171       <entry>no</entry>
 172       <entry>48</entry>
 173       <entry>34</entry>
 174       <entry>MD5-based crypt</entry>
 175      </row>
 176      <row>
 177       <entry><literal>xdes</literal></entry>
 178       <entry>8</entry>
 179       <entry>yes</entry>
 180       <entry>24</entry>
 181       <entry>20</entry>
 182       <entry>Extended DES</entry>
 183      </row>
 184      <row>
 185       <entry><literal>des</literal></entry>
 186       <entry>8</entry>
 187       <entry>no</entry>
 188       <entry>12</entry>
 189       <entry>13</entry>
 190       <entry>Original UNIX crypt</entry>
 191      </row>
 192     </tbody>
 193    </tgroup>
 194   </table>
 195
 196   <sect3 id="pgcrypto-password-hashing-funcs-crypt">
 197    <title><function>crypt()</function></title>
 198
 199    <indexterm>
 200     <primary>crypt</primary>
 201    </indexterm>
 202
 203 <synopsis>
 204 crypt(password text, salt text) returns text
 205 </synopsis>
 206
 207    <para>
 208     Calculates a crypt(3)-style hash of <parameter>password</parameter>.
 209     When storing a new password, you need to use
 210     <function>gen_salt()</function> to generate a new <parameter>salt</parameter> value.
 211     To check a password, pass the stored hash value as <parameter>salt</parameter>,
 212     and test whether the result matches the stored value.
 213    </para>
 214    <para>
 215     Example of setting a new password:
 216 <programlisting>
 217 UPDATE ... SET pswhash = crypt('new password', gen_salt('md5'));
 218 </programlisting>
 219    </para>
 220    <para>
 221     Example of authentication:
 222 <programlisting>
 223 SELECT (pswhash = crypt('entered password', pswhash)) AS pswmatch FROM ... ;
 224 </programlisting>
 225     This returns <literal>true</literal> if the entered password is correct.
 226    </para>
 227   </sect3>
 228
 229   <sect3 id="pgcrypto-password-hashing-funcs-gen-salt">
 230    <title><function>gen_salt()</function></title>
 231
 232   <indexterm>
 233    <primary>gen_salt</primary>
 234   </indexterm>
 235
 236 <synopsis>
 237 gen_salt(type text [, iter_count integer ]) returns text
 238 </synopsis>
 239
 240    <para>
 241     Generates a new random salt string for use in <function>crypt()</function>.
 242     The salt string also tells <function>crypt()</function> which algorithm to use.
 243    </para>
 244
 245    <para>
 246     The <parameter>type</parameter> parameter specifies the hashing algorithm.
 247     The accepted types are: <literal>des</literal>, <literal>xdes</literal>,
 248     <literal>md5</literal> and <literal>bf</literal>.
 249    </para>
 250
 251    <para>
 252     The <parameter>iter_count</parameter> parameter lets the user specify the iteration
 253     count, for algorithms that have one.
 254     The higher the count, the more time it takes to hash
 255     the password and therefore the more time to break it.  Although with
 256     too high a count the time to calculate a hash may be several years
 257     &mdash; which is somewhat impractical.  If the <parameter>iter_count</parameter>
 258     parameter is omitted, the default iteration count is used.
 259     Allowed values for <parameter>iter_count</parameter> depend on the algorithm and
 260     are shown in <xref linkend="pgcrypto-icfc-table"/>.
 261    </para>
 262
 263    <table id="pgcrypto-icfc-table">
 264     <title>Iteration Counts for <function>crypt()</function></title>
 265     <tgroup cols="4">
 266      <thead>
 267       <row>
 268        <entry>Algorithm</entry>
 269        <entry>Default</entry>
 270        <entry>Min</entry>
 271        <entry>Max</entry>
 272       </row>
 273      </thead>
 274      <tbody>
 275       <row>
 276        <entry><literal>xdes</literal></entry>
 277        <entry>725</entry>
 278        <entry>1</entry>
 279        <entry>16777215</entry>
 280       </row>
 281       <row>
 282        <entry><literal>bf</literal></entry>
 283        <entry>6</entry>
 284        <entry>4</entry>
 285        <entry>31</entry>
 286       </row>
 287      </tbody>
 288     </tgroup>
 289    </table>
 290
 291    <para>
 292     For <literal>xdes</literal> there is an additional limitation that the
 293     iteration count must be an odd number.
 294    </para>
 295
 296    <para>
 297     To pick an appropriate iteration count, consider that
 298     the original DES crypt was designed to have the speed of 4 hashes per
 299     second on the hardware of that time.
 300     Slower than 4 hashes per second would probably dampen usability.
 301     Faster than 100 hashes per second is probably too fast.
 302    </para>
 303
 304    <para>
 305     <xref linkend="pgcrypto-hash-speed-table"/> gives an overview of the relative slowness
 306     of different hashing algorithms.
 307     The table shows how much time it would take to try all
 308     combinations of characters in an 8-character password, assuming
 309     that the password contains either only lower case letters, or
 310     upper- and lower-case letters and numbers.
 311     In the <literal>crypt-bf</literal> entries, the number after a slash is
 312     the <parameter>iter_count</parameter> parameter of
 313     <function>gen_salt</function>.
 314    </para>
 315
 316    <table id="pgcrypto-hash-speed-table">
 317     <title>Hash Algorithm Speeds</title>
 318     <tgroup cols="5">
 319      <thead>
 320       <row>
 321        <entry>Algorithm</entry>
 322        <entry>Hashes/sec</entry>
 323        <entry>For <literal>[a-z]</literal></entry>
 324        <entry>For <literal>[A-Za-z0-9]</literal></entry>
 325        <entry>Duration relative to <literal>md5 hash</literal></entry>
 326       </row>
 327      </thead>
 328      <tbody>
 329       <row>
 330        <entry><literal>crypt-bf/8</literal></entry>
 331        <entry>1792</entry>
 332        <entry>4 years</entry>
 333        <entry>3927 years</entry>
 334        <entry>100k</entry>
 335       </row>
 336       <row>
 337        <entry><literal>crypt-bf/7</literal></entry>
 338        <entry>3648</entry>
 339        <entry>2 years</entry>
 340        <entry>1929 years</entry>
 341        <entry>50k</entry>
 342       </row>
 343       <row>
 344        <entry><literal>crypt-bf/6</literal></entry>
 345        <entry>7168</entry>
 346        <entry>1 year</entry>
 347        <entry>982 years</entry>
 348        <entry>25k</entry>
 349       </row>
 350       <row>
 351        <entry><literal>crypt-bf/5</literal></entry>
 352        <entry>13504</entry>
 353        <entry>188 days</entry>
 354        <entry>521 years</entry>
 355        <entry>12.5k</entry>
 356       </row>
 357       <row>
 358        <entry><literal>crypt-md5</literal></entry>
 359        <entry>171584</entry>
 360        <entry>15 days</entry>
 361        <entry>41 years</entry>
 362        <entry>1k</entry>
 363       </row>
 364       <row>
 365        <entry><literal>crypt-des</literal></entry>
 366        <entry>23221568</entry>
 367        <entry>157.5 minutes</entry>
 368        <entry>108 days</entry>
 369        <entry>7</entry>
 370       </row>
 371       <row>
 372        <entry><literal>sha1</literal></entry>
 373        <entry>37774272</entry>
 374        <entry>90 minutes</entry>
 375        <entry>68 days</entry>
 376        <entry>4</entry>
 377       </row>
 378       <row>
 379        <entry><literal>md5</literal> (hash)</entry>
 380        <entry>150085504</entry>
 381        <entry>22.5 minutes</entry>
 382        <entry>17 days</entry>
 383        <entry>1</entry>
 384       </row>
 385      </tbody>
 386     </tgroup>
 387    </table>
 388
 389    <para>
 390     Notes:
 391    </para>
 392
 393    <itemizedlist>
 394     <listitem>
 395      <para>
 396      The machine used is an Intel Mobile Core i3.
 397      </para>
 398     </listitem>
 399     <listitem>
 400      <para>
 401       <literal>crypt-des</literal> and <literal>crypt-md5</literal> algorithm numbers are
 402       taken from John the Ripper v1.6.38 <literal>-test</literal> output.
 403      </para>
 404     </listitem>
 405     <listitem>
 406      <para>
 407       <literal>md5 hash</literal> numbers are from mdcrack 1.2.
 408      </para>
 409     </listitem>
 410     <listitem>
 411      <para>
 412       <literal>sha1</literal> numbers are from lcrack-20031130-beta.
 413      </para>
 414     </listitem>
 415     <listitem>
 416      <para>
 417       <literal>crypt-bf</literal> numbers are taken using a simple program that
 418       loops over 1000 8-character passwords.  That way the speed
 419       with different numbers of iterations can be shown.  For reference: <literal>john
 420       -test</literal> shows 13506 loops/sec for <literal>crypt-bf/5</literal>.
 421       (The very small
 422       difference in results is in accordance with the fact that the
 423       <literal>crypt-bf</literal> implementation in <filename>pgcrypto</filename>
 424       is the same one used in John the Ripper.)
 425      </para>
 426     </listitem>
 427    </itemizedlist>
 428
 429    <para>
 430     Note that <quote>try all combinations</quote> is not a realistic exercise.
 431     Usually password cracking is done with the help of dictionaries, which
 432     contain both regular words and various mutations of them.  So, even
 433     somewhat word-like passwords could be cracked much faster than the above
 434     numbers suggest, while a 6-character non-word-like password may escape
 435     cracking.  Or not.
 436    </para>
 437   </sect3>
 438  </sect2>
 439
 440  <sect2 id="pgcrypto-pgp-enc-funcs">
 441   <title>PGP Encryption Functions</title>
 442
 443   <para>
 444    The functions here implement the encryption part of the OpenPGP
 445    (<ulink url="https://datatracker.ietf.org/doc/html/rfc4880">RFC 4880</ulink>)
 446    standard.  Supported are both symmetric-key and public-key encryption.
 447   </para>
 448
 449   <para>
 450    An encrypted PGP message consists of 2 parts, or <firstterm>packets</firstterm>:
 451   </para>
 452   <itemizedlist>
 453    <listitem>
 454     <para>
 455      Packet containing a session key &mdash; either symmetric-key or public-key
 456      encrypted.
 457     </para>
 458    </listitem>
 459    <listitem>
 460     <para>
 461      Packet containing data encrypted with the session key.
 462     </para>
 463    </listitem>
 464   </itemizedlist>
 465
 466   <para>
 467    When encrypting with a symmetric key (i.e., a password):
 468   </para>
 469   <orderedlist>
 470    <listitem>
 471     <para>
 472      The given password is hashed using a String2Key (S2K) algorithm.  This is
 473      rather similar to <function>crypt()</function> algorithms &mdash; purposefully
 474      slow and with random salt &mdash; but it produces a full-length binary
 475      key.
 476     </para>
 477    </listitem>
 478    <listitem>
 479     <para>
 480      If a separate session key is requested, a new random key will be
 481      generated.  Otherwise the S2K key will be used directly as the session
 482      key.
 483     </para>
 484    </listitem>
 485    <listitem>
 486     <para>
 487      If the S2K key is to be used directly, then only S2K settings will be put
 488      into the session key packet.  Otherwise the session key will be encrypted
 489      with the S2K key and put into the session key packet.
 490     </para>
 491    </listitem>
 492   </orderedlist>
 493
 494   <para>
 495    When encrypting with a public key:
 496   </para>
 497   <orderedlist>
 498    <listitem>
 499     <para>
 500      A new random session key is generated.
 501     </para>
 502    </listitem>
 503    <listitem>
 504     <para>
 505      It is encrypted using the public key and put into the session key packet.
 506     </para>
 507    </listitem>
 508   </orderedlist>
 509
 510   <para>
 511    In either case the data to be encrypted is processed as follows:
 512   </para>
 513   <orderedlist>
 514    <listitem>
 515     <para>
 516      Optional data-manipulation: compression, conversion to UTF-8,
 517      and/or conversion of line-endings.
 518     </para>
 519    </listitem>
 520    <listitem>
 521     <para>
 522      The data is prefixed with a block of random bytes.  This is equivalent
 523      to using a random IV.
 524     </para>
 525    </listitem>
 526    <listitem>
 527     <para>
 528      A SHA-1 hash of the random prefix and data is appended.
 529     </para>
 530    </listitem>
 531    <listitem>
 532     <para>
 533      All this is encrypted with the session key and placed in the data packet.
 534     </para>
 535    </listitem>
 536   </orderedlist>
 537
 538   <sect3 id="pgcrypto-pgp-enc-funcs-pgp-sym-encrypt">
 539    <title><function>pgp_sym_encrypt()</function></title>
 540
 541    <indexterm>
 542     <primary>pgp_sym_encrypt</primary>
 543    </indexterm>
 544
 545    <indexterm>
 546     <primary>pgp_sym_encrypt_bytea</primary>
 547    </indexterm>
 548
 549 <synopsis>
 550 pgp_sym_encrypt(data text, psw text [, options text ]) returns bytea
 551 pgp_sym_encrypt_bytea(data bytea, psw text [, options text ]) returns bytea
 552 </synopsis>
 553    <para>
 554     Encrypt <parameter>data</parameter> with a symmetric PGP key <parameter>psw</parameter>.
 555     The <parameter>options</parameter> parameter can contain option settings,
 556     as described below.
 557    </para>
 558   </sect3>
 559
 560   <sect3 id="pgcrypto-pgp-enc-funcs-pgp-sym-decrypt">
 561    <title><function>pgp_sym_decrypt()</function></title>
 562
 563    <indexterm>
 564     <primary>pgp_sym_decrypt</primary>
 565    </indexterm>
 566
 567    <indexterm>
 568     <primary>pgp_sym_decrypt_bytea</primary>
 569    </indexterm>
 570
 571 <synopsis>
 572 pgp_sym_decrypt(msg bytea, psw text [, options text ]) returns text
 573 pgp_sym_decrypt_bytea(msg bytea, psw text [, options text ]) returns bytea
 574 </synopsis>
 575    <para>
 576     Decrypt a symmetric-key-encrypted PGP message.
 577    </para>
 578    <para>
 579     Decrypting <type>bytea</type> data with <function>pgp_sym_decrypt</function> is disallowed.
 580     This is to avoid outputting invalid character data.  Decrypting
 581     originally textual data with <function>pgp_sym_decrypt_bytea</function> is fine.
 582    </para>
 583    <para>
 584     The <parameter>options</parameter> parameter can contain option settings,
 585     as described below.
 586    </para>
 587   </sect3>
 588
 589   <sect3 id="pgcrypto-pgp-enc-funcs-pgp-pub-encrypt">
 590    <title><function>pgp_pub_encrypt()</function></title>
 591
 592    <indexterm>
 593     <primary>pgp_pub_encrypt</primary>
 594    </indexterm>
 595
 596    <indexterm>
 597     <primary>pgp_pub_encrypt_bytea</primary>
 598    </indexterm>
 599
 600 <synopsis>
 601 pgp_pub_encrypt(data text, key bytea [, options text ]) returns bytea
 602 pgp_pub_encrypt_bytea(data bytea, key bytea [, options text ]) returns bytea
 603 </synopsis>
 604    <para>
 605     Encrypt <parameter>data</parameter> with a public PGP key <parameter>key</parameter>.
 606     Giving this function a secret key will produce an error.
 607    </para>
 608    <para>
 609     The <parameter>options</parameter> parameter can contain option settings,
 610     as described below.
 611    </para>
 612   </sect3>
 613
 614   <sect3 id="pgcrypto-pgp-enc-funcs-pgp-pub-decrypt">
 615    <title><function>pgp_pub_decrypt()</function></title>
 616
 617    <indexterm>
 618     <primary>pgp_pub_decrypt</primary>
 619    </indexterm>
 620
 621    <indexterm>
 622     <primary>pgp_pub_decrypt_bytea</primary>
 623    </indexterm>
 624
 625 <synopsis>
 626 pgp_pub_decrypt(msg bytea, key bytea [, psw text [, options text ]]) returns text
 627 pgp_pub_decrypt_bytea(msg bytea, key bytea [, psw text [, options text ]]) returns bytea
 628 </synopsis>
 629    <para>
 630     Decrypt a public-key-encrypted message.  <parameter>key</parameter> must be the
 631     secret key corresponding to the public key that was used to encrypt.
 632     If the secret key is password-protected, you must give the password in
 633     <parameter>psw</parameter>.  If there is no password, but you want to specify
 634     options, you need to give an empty password.
 635    </para>
 636    <para>
 637     Decrypting <type>bytea</type> data with <function>pgp_pub_decrypt</function> is disallowed.
 638     This is to avoid outputting invalid character data.  Decrypting
 639     originally textual data with <function>pgp_pub_decrypt_bytea</function> is fine.
 640    </para>
 641    <para>
 642     The <parameter>options</parameter> parameter can contain option settings,
 643     as described below.
 644    </para>
 645   </sect3>
 646
 647   <sect3 id="pgcrypto-pgp-enc-funcs-pgp-key-id">
 648    <title><function>pgp_key_id()</function></title>
 649
 650    <indexterm>
 651     <primary>pgp_key_id</primary>
 652    </indexterm>
 653
 654 <synopsis>
 655 pgp_key_id(bytea) returns text
 656 </synopsis>
 657    <para>
 658     <function>pgp_key_id</function> extracts the key ID of a PGP public or secret key.
 659     Or it gives the key ID that was used for encrypting the data, if given
 660     an encrypted message.
 661    </para>
 662    <para>
 663     It can return 2 special key IDs:
 664    </para>
 665    <itemizedlist>
 666     <listitem>
 667      <para>
 668       <literal>SYMKEY</literal>
 669      </para>
 670      <para>
 671       The message is encrypted with a symmetric key.
 672      </para>
 673     </listitem>
 674     <listitem>
 675      <para>
 676       <literal>ANYKEY</literal>
 677      </para>
 678      <para>
 679       The message is public-key encrypted, but the key ID has been removed.
 680       That means you will need to try all your secret keys on it to see
 681       which one decrypts it.  <filename>pgcrypto</filename> itself does not produce
 682       such messages.
 683      </para>
 684     </listitem>
 685    </itemizedlist>
 686    <para>
 687     Note that different keys may have the same ID.   This is rare but a normal
 688     event. The client application should then try to decrypt with each one,
 689     to see which fits &mdash; like handling <literal>ANYKEY</literal>.
 690    </para>
 691   </sect3>
 692
 693   <sect3 id="pgcrypto-pgp-enc-funcs-armor">
 694    <title><function>armor()</function>, <function>dearmor()</function></title>
 695
 696    <indexterm>
 697     <primary>armor</primary>
 698    </indexterm>
 699
 700    <indexterm>
 701     <primary>dearmor</primary>
 702    </indexterm>
 703
 704 <synopsis>
 705 armor(data bytea [ , keys text[], values text[] ]) returns text
 706 dearmor(data text) returns bytea
 707 </synopsis>
 708    <para>
 709     These functions wrap/unwrap binary data into PGP ASCII-armor format,
 710     which is basically Base64 with CRC and additional formatting.
 711    </para>
 712
 713    <para>
 714     If the <parameter>keys</parameter> and <parameter>values</parameter> arrays are specified,
 715     an <firstterm>armor header</firstterm> is added to the armored format for each
 716     key/value pair. Both arrays must be single-dimensional, and they must
 717     be of the same length.  The keys and values cannot contain any non-ASCII
 718     characters.
 719    </para>
 720   </sect3>
 721
 722   <sect3 id="pgcrypto-pgp-enc-funcs-pgp-armor-headers">
 723    <title><function>pgp_armor_headers</function></title>
 724
 725    <indexterm>
 726     <primary>pgp_armor_headers</primary>
 727    </indexterm>
 728
 729 <synopsis>
 730 pgp_armor_headers(data text, key out text, value out text) returns setof record
 731 </synopsis>
 732    <para>
 733     <function>pgp_armor_headers()</function> extracts the armor headers from
 734     <parameter>data</parameter>.  The return value is a set of rows with two columns,
 735     key and value.  If the keys or values contain any non-ASCII characters,
 736     they are treated as UTF-8.
 737    </para>
 738   </sect3>
 739
 740   <sect3 id="pgcrypto-pgp-enc-funcs-opts">
 741    <title>Options for PGP Functions</title>
 742
 743    <para>
 744     Options are named to be similar to GnuPG.  An option's value should be
 745     given after an equal sign; separate options from each other with commas.
 746     For example:
 747 <programlisting>
 748 pgp_sym_encrypt(data, psw, 'compress-algo=1, cipher-algo=aes256')
 749 </programlisting>
 750    </para>
 751
 752    <para>
 753     All of the options except <literal>convert-crlf</literal> apply only to
 754     encrypt functions.  Decrypt functions get the parameters from the PGP
 755     data.
 756    </para>
 757
 758    <para>
 759     The most interesting options are probably
 760     <literal>compress-algo</literal> and <literal>unicode-mode</literal>.
 761     The rest should have reasonable defaults.
 762    </para>
 763
 764   <sect4 id="pgcrypto-pgp-enc-funcs-opts-cipher-algo">
 765    <title>cipher-algo</title>
 766
 767    <para>
 768     Which cipher algorithm to use.
 769    </para>
 770 <literallayout>
 771 Values: bf, aes128, aes192, aes256, 3des, cast5
 772 Default: aes128
 773 Applies to: pgp_sym_encrypt, pgp_pub_encrypt
 774 </literallayout>
 775   </sect4>
 776
 777   <sect4 id="pgcrypto-pgp-enc-funcs-opts-compress-algo">
 778    <title>compress-algo</title>
 779
 780    <para>
 781     Which compression algorithm to use.  Only available if
 782     <productname>PostgreSQL</productname> was built with zlib.
 783    </para>
 784 <literallayout>
 785 Values:
 786   0 - no compression
 787   1 - ZIP compression
 788   2 - ZLIB compression (= ZIP plus meta-data and block CRCs)
 789 Default: 0
 790 Applies to: pgp_sym_encrypt, pgp_pub_encrypt
 791 </literallayout>
 792   </sect4>
 793
 794   <sect4 id="pgcrypto-pgp-enc-funcs-opts-compress-level">
 795    <title>compress-level</title>
 796
 797    <para>
 798     How much to compress.  Higher levels compress smaller but are slower.
 799     0 disables compression.
 800    </para>
 801 <literallayout>
 802 Values: 0, 1-9
 803 Default: 6
 804 Applies to: pgp_sym_encrypt, pgp_pub_encrypt
 805 </literallayout>
 806   </sect4>
 807
 808   <sect4 id="pgcrypto-pgp-enc-funcs-opts-convert-crlf">
 809    <title>convert-crlf</title>
 810
 811    <para>
 812     Whether to convert <literal>\n</literal> into <literal>\r\n</literal> when
 813     encrypting and <literal>\r\n</literal> to <literal>\n</literal> when
 814     decrypting.  <acronym>RFC</acronym> 4880 specifies that text data should be stored using
 815     <literal>\r\n</literal> line-feeds.  Use this to get fully RFC-compliant
 816     behavior.
 817    </para>
 818 <literallayout>
 819 Values: 0, 1
 820 Default: 0
 821 Applies to: pgp_sym_encrypt, pgp_pub_encrypt, pgp_sym_decrypt, pgp_pub_decrypt
 822 </literallayout>
 823   </sect4>
 824
 825   <sect4 id="pgcrypto-pgp-enc-funcs-opts-disable-mdc">
 826    <title>disable-mdc</title>
 827
 828    <para>
 829     Do not protect data with SHA-1.  The only good reason to use this
 830     option is to achieve compatibility with ancient PGP products, predating
 831     the addition of SHA-1 protected packets to <acronym>RFC</acronym> 4880.
 832     Recent gnupg.org and pgp.com software supports it fine.
 833    </para>
 834 <literallayout>
 835 Values: 0, 1
 836 Default: 0
 837 Applies to: pgp_sym_encrypt, pgp_pub_encrypt
 838 </literallayout>
 839   </sect4>
 840
 841   <sect4 id="pgcrypto-pgp-enc-funcs-opts-sess-key">
 842    <title>sess-key</title>
 843
 844    <para>
 845     Use separate session key.  Public-key encryption always uses a separate
 846     session key; this option is for symmetric-key encryption, which by default
 847     uses the S2K key directly.
 848    </para>
 849 <literallayout>
 850 Values: 0, 1
 851 Default: 0
 852 Applies to: pgp_sym_encrypt
 853 </literallayout>
 854   </sect4>
 855
 856   <sect4 id="pgcrypto-pgp-enc-funcs-opts-s2k-mode">
 857    <title>s2k-mode</title>
 858
 859    <para>
 860     Which S2K algorithm to use.
 861    </para>
 862 <literallayout>
 863 Values:
 864   0 - Without salt.  Dangerous!
 865   1 - With salt but with fixed iteration count.
 866   3 - Variable iteration count.
 867 Default: 3
 868 Applies to: pgp_sym_encrypt
 869 </literallayout>
 870   </sect4>
 871
 872   <sect4 id="pgcrypto-pgp-enc-funcs-opts-s2k-count">
 873    <title>s2k-count</title>
 874
 875    <para>
 876     The number of iterations of the S2K algorithm to use.  It must
 877     be a value between 1024 and 65011712, inclusive.
 878    </para>
 879 <literallayout>
 880 Default: A random value between 65536 and 253952
 881 Applies to: pgp_sym_encrypt, only with s2k-mode=3
 882 </literallayout>
 883   </sect4>
 884
 885   <sect4 id="pgcrypto-pgp-enc-funcs-opts-s2k-digest-algo">
 886    <title>s2k-digest-algo</title>
 887
 888    <para>
 889     Which digest algorithm to use in S2K calculation.
 890    </para>
 891 <literallayout>
 892 Values: md5, sha1
 893 Default: sha1
 894 Applies to: pgp_sym_encrypt
 895 </literallayout>
 896   </sect4>
 897
 898   <sect4 id="pgcrypto-pgp-enc-funcs-opts-s2k-cipher-algo">
 899    <title>s2k-cipher-algo</title>
 900
 901    <para>
 902     Which cipher to use for encrypting separate session key.
 903    </para>
 904 <literallayout>
 905 Values: bf, aes, aes128, aes192, aes256
 906 Default: use cipher-algo
 907 Applies to: pgp_sym_encrypt
 908 </literallayout>
 909   </sect4>
 910
 911   <sect4 id="pgcrypto-pgp-enc-funcs-opts-unicode-mode">
 912    <title>unicode-mode</title>
 913
 914    <para>
 915     Whether to convert textual data from database internal encoding to
 916     UTF-8 and back.  If your database already is UTF-8, no conversion will
 917     be done, but the message will be tagged as UTF-8.  Without this option
 918     it will not be.
 919    </para>
 920 <literallayout>
 921 Values: 0, 1
 922 Default: 0
 923 Applies to: pgp_sym_encrypt, pgp_pub_encrypt
 924 </literallayout>
 925   </sect4>
 926   </sect3>
 927
 928  <sect3 id="pgcrypto-pgp-enc-funcs-gnupg">
 929   <title>Generating PGP Keys with GnuPG</title>
 930
 931   <para>
 932    To generate a new key:
 933 <programlisting>
 934 gpg --gen-key
 935 </programlisting>
 936   </para>
 937   <para>
 938    The preferred key type is <quote>DSA and Elgamal</quote>.
 939   </para>
 940   <para>
 941    For RSA encryption you must create either DSA or RSA sign-only key
 942    as master and then add an RSA encryption subkey with
 943    <literal>gpg --edit-key</literal>.
 944   </para>
 945   <para>
 946    To list keys:
 947 <programlisting>
 948 gpg --list-secret-keys
 949 </programlisting>
 950   </para>
 951   <para>
 952    To export a public key in ASCII-armor format:
 953 <programlisting>
 954 gpg -a --export KEYID > public.key
 955 </programlisting>
 956   </para>
 957   <para>
 958    To export a secret key in ASCII-armor format:
 959 <programlisting>
 960 gpg -a --export-secret-keys KEYID > secret.key
 961 </programlisting>
 962   </para>
 963   <para>
 964    You need to use <function>dearmor()</function> on these keys before giving them to
 965    the PGP functions.  Or if you can handle binary data, you can drop
 966    <literal>-a</literal> from the command.
 967   </para>
 968   <para>
 969    For more details see <literal>man gpg</literal>,
 970    <ulink url="https://www.gnupg.org/gph/en/manual.html">The GNU
 971    Privacy Handbook</ulink> and other documentation on
 972    <ulink url="https://www.gnupg.org/"></ulink>.
 973   </para>
 974  </sect3>
 975
 976  <sect3 id="pgcrypto-pgp-enc-funcs-limitations">
 977   <title>Limitations of PGP Code</title>
 978
 979   <itemizedlist>
 980    <listitem>
 981     <para>
 982     No support for signing.  That also means that it is not checked
 983     whether the encryption subkey belongs to the master key.
 984     </para>
 985    </listitem>
 986    <listitem>
 987     <para>
 988     No support for encryption key as master key.  As such practice
 989     is generally discouraged, this should not be a problem.
 990     </para>
 991    </listitem>
 992    <listitem>
 993     <para>
 994     No support for several subkeys.  This may seem like a problem, as this
 995     is common practice.  On the other hand, you should not use your regular
 996     GPG/PGP keys with <filename>pgcrypto</filename>, but create new ones,
 997     as the usage scenario is rather different.
 998     </para>
 999    </listitem>
1000   </itemizedlist>
1001   </sect3>
1002  </sect2>
1003
1004  <sect2 id="pgcrypto-raw-enc-funcs">
1005   <title>Raw Encryption Functions</title>
1006
1007   <para>
1008    These functions only run a cipher over data; they don't have any advanced
1009    features of PGP encryption.  Therefore they have some major problems:
1010   </para>
1011   <orderedlist>
1012    <listitem>
1013     <para>
1014     They use user key directly as cipher key.
1015     </para>
1016    </listitem>
1017    <listitem>
1018     <para>
1019     They don't provide any integrity checking, to see
1020     if the encrypted data was modified.
1021     </para>
1022    </listitem>
1023    <listitem>
1024     <para>
1025     They expect that users manage all encryption parameters
1026     themselves, even IV.
1027     </para>
1028    </listitem>
1029    <listitem>
1030     <para>
1031     They don't handle text.
1032     </para>
1033    </listitem>
1034   </orderedlist>
1035   <para>
1036    So, with the introduction of PGP encryption, usage of raw
1037    encryption functions is discouraged.
1038   </para>
1039
1040   <indexterm>
1041    <primary>encrypt</primary>
1042   </indexterm>
1043
1044   <indexterm>
1045    <primary>decrypt</primary>
1046   </indexterm>
1047
1048   <indexterm>
1049    <primary>encrypt_iv</primary>
1050   </indexterm>
1051
1052   <indexterm>
1053    <primary>decrypt_iv</primary>
1054   </indexterm>
1055
1056 <synopsis>
1057 encrypt(data bytea, key bytea, type text) returns bytea
1058 decrypt(data bytea, key bytea, type text) returns bytea
1059
1060 encrypt_iv(data bytea, key bytea, iv bytea, type text) returns bytea
1061 decrypt_iv(data bytea, key bytea, iv bytea, type text) returns bytea
1062 </synopsis>
1063
1064   <para>
1065    Encrypt/decrypt data using the cipher method specified by
1066    <parameter>type</parameter>.  The syntax of the
1067    <parameter>type</parameter> string is:
1068
1069 <synopsis>
1070 <replaceable>algorithm</replaceable> <optional> <literal>-</literal> <replaceable>mode</replaceable> </optional> <optional> <literal>/pad:</literal> <replaceable>padding</replaceable> </optional>
1071 </synopsis>
1072    where <replaceable>algorithm</replaceable> is one of:
1073
1074   <itemizedlist>
1075    <listitem><para><literal>bf</literal> &mdash; Blowfish</para></listitem>
1076    <listitem><para><literal>aes</literal> &mdash; AES (Rijndael-128, -192 or -256)</para></listitem>
1077   </itemizedlist>
1078    and <replaceable>mode</replaceable> is one of:
1079   <itemizedlist>
1080    <listitem>
1081     <para>
1082     <literal>cbc</literal> &mdash; next block depends on previous (default)
1083     </para>
1084    </listitem>
1085    <listitem>
1086     <para>
1087     <literal>ecb</literal> &mdash; each block is encrypted separately (for
1088     testing only)
1089     </para>
1090    </listitem>
1091   </itemizedlist>
1092    and <replaceable>padding</replaceable> is one of:
1093   <itemizedlist>
1094    <listitem>
1095     <para>
1096     <literal>pkcs</literal> &mdash; data may be any length (default)
1097     </para>
1098    </listitem>
1099    <listitem>
1100     <para>
1101     <literal>none</literal> &mdash; data must be multiple of cipher block size
1102     </para>
1103    </listitem>
1104   </itemizedlist>
1105   </para>
1106   <para>
1107    So, for example, these are equivalent:
1108 <programlisting>
1109 encrypt(data, 'fooz', 'bf')
1110 encrypt(data, 'fooz', 'bf-cbc/pad:pkcs')
1111 </programlisting>
1112   </para>
1113   <para>
1114    In <function>encrypt_iv</function> and <function>decrypt_iv</function>, the
1115    <parameter>iv</parameter> parameter is the initial value for the CBC mode;
1116    it is ignored for ECB.
1117    It is clipped or padded with zeroes if not exactly block size.
1118    It defaults to all zeroes in the functions without this parameter.
1119   </para>
1120  </sect2>
1121
1122  <sect2 id="pgcrypto-random-data-funcs">
1123   <title>Random-Data Functions</title>
1124
1125   <indexterm>
1126    <primary>gen_random_bytes</primary>
1127   </indexterm>
1128
1129 <synopsis>
1130 gen_random_bytes(count integer) returns bytea
1131 </synopsis>
1132   <para>
1133    Returns <parameter>count</parameter> cryptographically strong random bytes.
1134    At most 1024 bytes can be extracted at a time.  This is to avoid
1135    draining the randomness generator pool.
1136   </para>
1137
1138   <indexterm>
1139    <primary>gen_random_uuid</primary>
1140   </indexterm>
1141
1142 <synopsis>
1143 gen_random_uuid() returns uuid
1144 </synopsis>
1145   <para>
1146    Returns a version 4 (random) UUID. (Obsolete, this function
1147    internally calls the <link linkend="functions-uuid">core
1148    function</link> of the same name.)
1149   </para>
1150  </sect2>
1151
1152  <sect2 id="pgcrypto-notes">
1153   <title>Notes</title>
1154
1155   <sect3 id="pgcrypto-notes-config">
1156    <title>Configuration</title>
1157
1158    <para>
1159     <filename>pgcrypto</filename> configures itself according to the findings of the
1160     main PostgreSQL <literal>configure</literal> script.  The options that
1161     affect it are <literal>--with-zlib</literal> and
1162     <literal>--with-ssl=openssl</literal>.
1163    </para>
1164
1165    <para>
1166     When compiled with zlib, PGP encryption functions are able to
1167     compress data before encrypting.
1168    </para>
1169
1170    <para>
1171     <filename>pgcrypto</filename> requires <productname>OpenSSL</productname>.
1172     Otherwise, it will not be built or installed.
1173    </para>
1174
1175    <para>
1176     When compiled against <productname>OpenSSL</productname> 3.0.0 and later
1177     versions, the legacy provider must be activated in the
1178     <filename>openssl.cnf</filename> configuration file in order to use older
1179     ciphers like DES or Blowfish.
1180    </para>
1181   </sect3>
1182
1183   <sect3 id="pgcrypto-notes-null-handling">
1184    <title>NULL Handling</title>
1185
1186    <para>
1187     As is standard in SQL, all functions return NULL, if any of the arguments
1188     are NULL.  This may create security risks on careless usage.
1189    </para>
1190   </sect3>
1191
1192   <sect3 id="pgcrypto-notes-sec-limits">
1193    <title>Security Limitations</title>
1194
1195    <para>
1196     All <filename>pgcrypto</filename> functions run inside the database server.
1197     That means that all
1198     the data and passwords move between <filename>pgcrypto</filename> and client
1199     applications in clear text.  Thus you must:
1200    </para>
1201
1202    <orderedlist>
1203     <listitem>
1204      <para>Connect locally or use SSL connections.</para>
1205     </listitem>
1206     <listitem>
1207      <para>Trust both system and database administrator.</para>
1208     </listitem>
1209    </orderedlist>
1210
1211    <para>
1212     If you cannot, then better do crypto inside client application.
1213    </para>
1214
1215    <para>
1216     The implementation does not resist
1217     <ulink url="https://en.wikipedia.org/wiki/Side-channel_attack">side-channel
1218     attacks</ulink>.  For example, the time required for
1219     a <filename>pgcrypto</filename> decryption function to complete varies among
1220     ciphertexts of a given size.
1221    </para>
1222   </sect3>
1223  </sect2>
1224
1225  <sect2 id="pgcrypto-author">
1226   <title>Author</title>
1227
1228   <para>
1229    Marko Kreen <email>markokr@gmail.com</email>
1230   </para>
1231
1232   <para>
1233    <filename>pgcrypto</filename> uses code from the following sources:
1234   </para>
1235
1236   <informaltable>
1237    <tgroup cols="3">
1238     <thead>
1239      <row>
1240       <entry>Algorithm</entry>
1241       <entry>Author</entry>
1242       <entry>Source origin</entry>
1243      </row>
1244     </thead>
1245     <tbody>
1246      <row>
1247       <entry>DES crypt</entry>
1248       <entry>David Burren and others</entry>
1249       <entry>FreeBSD libcrypt</entry>
1250      </row>
1251      <row>
1252       <entry>MD5 crypt</entry>
1253       <entry>Poul-Henning Kamp</entry>
1254       <entry>FreeBSD libcrypt</entry>
1255      </row>
1256      <row>
1257       <entry>Blowfish crypt</entry>
1258       <entry>Solar Designer</entry>
1259       <entry>www.openwall.com</entry>
1260      </row>
1261     </tbody>
1262    </tgroup>
1263   </informaltable>
1264  </sect2>
1265
1266 </sect1>