Team Ai
Datasetpublic

codekingpro/portable-devtools

sourceHugging Faceupdated 5mo agoView on Hugging Face
1likes15kdownloads
pgcrypto.html537 linesDownload Raw Back to html
1<?xml version="1.0" encoding="UTF-8" standalone="no"?>2<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>F.28. pgcrypto — cryptographic functions</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="pgbuffercache.html" title="F.27. pg_buffercache — inspect PostgreSQL buffer cache state" /><link rel="next" href="pgfreespacemap.html" title="F.29. pg_freespacemap — examine the free space map" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">F.28. pgcrypto — cryptographic functions</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="pgbuffercache.html" title="F.27. pg_buffercache — inspect PostgreSQL&#10;    buffer cache state">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="contrib.html" title="Appendix F. Additional Supplied Modules and Extensions">Up</a></td><th width="60%" align="center">Appendix F. Additional Supplied Modules and Extensions</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="pgfreespacemap.html" title="F.29. pg_freespacemap — examine the free space map">Next</a></td></tr></table><hr /></div><div class="sect1" id="PGCRYPTO"><div class="titlepage"><div><div><h2 class="title" style="clear: both">F.28. pgcrypto — cryptographic functions <a href="#PGCRYPTO" class="id_link">#</a></h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="pgcrypto.html#PGCRYPTO-GENERAL-HASHING-FUNCS">F.28.1. General Hashing Functions</a></span></dt><dt><span class="sect2"><a href="pgcrypto.html#PGCRYPTO-PASSWORD-HASHING-FUNCS">F.28.2. Password Hashing Functions</a></span></dt><dt><span class="sect2"><a href="pgcrypto.html#PGCRYPTO-PGP-ENC-FUNCS">F.28.3. PGP Encryption Functions</a></span></dt><dt><span class="sect2"><a href="pgcrypto.html#PGCRYPTO-RAW-ENC-FUNCS">F.28.4. Raw Encryption Functions</a></span></dt><dt><span class="sect2"><a href="pgcrypto.html#PGCRYPTO-RANDOM-DATA-FUNCS">F.28.5. Random-Data Functions</a></span></dt><dt><span class="sect2"><a href="pgcrypto.html#PGCRYPTO-NOTES">F.28.6. Notes</a></span></dt><dt><span class="sect2"><a href="pgcrypto.html#PGCRYPTO-AUTHOR">F.28.7. Author</a></span></dt></dl></div><a id="id-1.11.7.38.2" class="indexterm"></a><a id="id-1.11.7.38.3" class="indexterm"></a><p>3  The <code class="filename">pgcrypto</code> module provides cryptographic functions for4  <span class="productname">PostgreSQL</span>.5 </p><p>6  This module is considered <span class="quote">“<span class="quote">trusted</span>”</span>, that is, it can be7  installed by non-superusers who have <code class="literal">CREATE</code> privilege8  on the current database.9 </p><p>10  <code class="filename">pgcrypto</code> requires OpenSSL and won't be installed if11  OpenSSL support was not selected when PostgreSQL was built.12 </p><div class="sect2" id="PGCRYPTO-GENERAL-HASHING-FUNCS"><div class="titlepage"><div><div><h3 class="title">F.28.1. General Hashing Functions <a href="#PGCRYPTO-GENERAL-HASHING-FUNCS" class="id_link">#</a></h3></div></div></div><div class="sect3" id="PGCRYPTO-GENERAL-HASHING-FUNCS-DIGEST"><div class="titlepage"><div><div><h4 class="title">F.28.1.1. <code class="function">digest()</code> <a href="#PGCRYPTO-GENERAL-HASHING-FUNCS-DIGEST" class="id_link">#</a></h4></div></div></div><a id="id-1.11.7.38.7.2.2" class="indexterm"></a><pre class="synopsis">13digest(data text, type text) returns bytea14digest(data bytea, type text) returns bytea15</pre><p>16    Computes a binary hash of the given <em class="parameter"><code>data</code></em>.17    <em class="parameter"><code>type</code></em> is the algorithm to use.18    Standard algorithms are <code class="literal">md5</code>, <code class="literal">sha1</code>,19    <code class="literal">sha224</code>, <code class="literal">sha256</code>,20    <code class="literal">sha384</code> and <code class="literal">sha512</code>.21    Moreover, any digest algorithm <span class="productname">OpenSSL</span> supports22    is automatically picked up.23   </p><p>24    If you want the digest as a hexadecimal string, use25    <code class="function">encode()</code> on the result.  For example:26</p><pre class="programlisting">27CREATE OR REPLACE FUNCTION sha1(bytea) returns text AS $$28    SELECT encode(digest($1, 'sha1'), 'hex')29$$ LANGUAGE SQL STRICT IMMUTABLE;30</pre><p>31   </p></div><div class="sect3" id="PGCRYPTO-GENERAL-HASHING-FUNCS-HMAC"><div class="titlepage"><div><div><h4 class="title">F.28.1.2. <code class="function">hmac()</code> <a href="#PGCRYPTO-GENERAL-HASHING-FUNCS-HMAC" class="id_link">#</a></h4></div></div></div><a id="id-1.11.7.38.7.3.2" class="indexterm"></a><pre class="synopsis">32hmac(data text, key text, type text) returns bytea33hmac(data bytea, key bytea, type text) returns bytea34</pre><p>35    Calculates hashed MAC for <em class="parameter"><code>data</code></em> with key <em class="parameter"><code>key</code></em>.36    <em class="parameter"><code>type</code></em> is the same as in <code class="function">digest()</code>.37   </p><p>38    This is similar to <code class="function">digest()</code> but the hash can only be39    recalculated knowing the key.  This prevents the scenario of someone40    altering data and also changing the hash to match.41   </p><p>42    If the key is larger than the hash block size it will first be hashed and43    the result will be used as key.44   </p></div></div><div class="sect2" id="PGCRYPTO-PASSWORD-HASHING-FUNCS"><div class="titlepage"><div><div><h3 class="title">F.28.2. Password Hashing Functions <a href="#PGCRYPTO-PASSWORD-HASHING-FUNCS" class="id_link">#</a></h3></div></div></div><p>45   The functions <code class="function">crypt()</code> and <code class="function">gen_salt()</code>46   are specifically designed for hashing passwords.47   <code class="function">crypt()</code> does the hashing and <code class="function">gen_salt()</code>48   prepares algorithm parameters for it.49  </p><p>50   The algorithms in <code class="function">crypt()</code> differ from the usual51   MD5 or SHA1 hashing algorithms in the following respects:52  </p><div class="orderedlist"><ol class="orderedlist" type="1"><li class="listitem"><p>53     They are slow.  As the amount of data is so small, this is the only54     way to make brute-forcing passwords hard.55    </p></li><li class="listitem"><p>56     They use a random value, called the <em class="firstterm">salt</em>, so that users57     having the same password will have different encrypted passwords.58     This is also an additional defense against reversing the algorithm.59    </p></li><li class="listitem"><p>60     They include the algorithm type in the result, so passwords hashed with61     different algorithms can co-exist.62    </p></li><li class="listitem"><p>63     Some of them are adaptive — that means when computers get64     faster, you can tune the algorithm to be slower, without65     introducing incompatibility with existing passwords.66    </p></li></ol></div><p>67   <a class="xref" href="pgcrypto.html#PGCRYPTO-CRYPT-ALGORITHMS" title="Table F.18. Supported Algorithms for crypt()">Table F.18</a> lists the algorithms68   supported by the <code class="function">crypt()</code> function.69  </p><div class="table" id="PGCRYPTO-CRYPT-ALGORITHMS"><p class="title"><strong>Table F.18. Supported Algorithms for <code class="function">crypt()</code></strong></p><div class="table-contents"><table class="table" summary="Supported Algorithms for crypt()" border="1"><colgroup><col /><col /><col /><col /><col /><col /></colgroup><thead><tr><th>Algorithm</th><th>Max Password Length</th><th>Adaptive?</th><th>Salt Bits</th><th>Output Length</th><th>Description</th></tr></thead><tbody><tr><td><code class="literal">bf</code></td><td>72</td><td>yes</td><td>128</td><td>60</td><td>Blowfish-based, variant 2a</td></tr><tr><td><code class="literal">md5</code></td><td>unlimited</td><td>no</td><td>48</td><td>34</td><td>MD5-based crypt</td></tr><tr><td><code class="literal">xdes</code></td><td>8</td><td>yes</td><td>24</td><td>20</td><td>Extended DES</td></tr><tr><td><code class="literal">des</code></td><td>8</td><td>no</td><td>12</td><td>13</td><td>Original UNIX crypt</td></tr></tbody></table></div></div><br class="table-break" /><div class="sect3" id="PGCRYPTO-PASSWORD-HASHING-FUNCS-CRYPT"><div class="titlepage"><div><div><h4 class="title">F.28.2.1. <code class="function">crypt()</code> <a href="#PGCRYPTO-PASSWORD-HASHING-FUNCS-CRYPT" class="id_link">#</a></h4></div></div></div><a id="id-1.11.7.38.8.7.2" class="indexterm"></a><pre class="synopsis">70crypt(password text, salt text) returns text71</pre><p>72    Calculates a crypt(3)-style hash of <em class="parameter"><code>password</code></em>.73    When storing a new password, you need to use74    <code class="function">gen_salt()</code> to generate a new <em class="parameter"><code>salt</code></em> value.75    To check a password, pass the stored hash value as <em class="parameter"><code>salt</code></em>,76    and test whether the result matches the stored value.77   </p><p>78    Example of setting a new password:79</p><pre class="programlisting">80UPDATE ... SET pswhash = crypt('new password', gen_salt('md5'));81</pre><p>82   </p><p>83    Example of authentication:84</p><pre class="programlisting">85SELECT (pswhash = crypt('entered password', pswhash)) AS pswmatch FROM ... ;86</pre><p>87    This returns <code class="literal">true</code> if the entered password is correct.88   </p></div><div class="sect3" id="PGCRYPTO-PASSWORD-HASHING-FUNCS-GEN-SALT"><div class="titlepage"><div><div><h4 class="title">F.28.2.2. <code class="function">gen_salt()</code> <a href="#PGCRYPTO-PASSWORD-HASHING-FUNCS-GEN-SALT" class="id_link">#</a></h4></div></div></div><a id="id-1.11.7.38.8.8.2" class="indexterm"></a><pre class="synopsis">89gen_salt(type text [, iter_count integer ]) returns text90</pre><p>91    Generates a new random salt string for use in <code class="function">crypt()</code>.92    The salt string also tells <code class="function">crypt()</code> which algorithm to use.93   </p><p>94    The <em class="parameter"><code>type</code></em> parameter specifies the hashing algorithm.95    The accepted types are: <code class="literal">des</code>, <code class="literal">xdes</code>,96    <code class="literal">md5</code> and <code class="literal">bf</code>.97   </p><p>98    The <em class="parameter"><code>iter_count</code></em> parameter lets the user specify the iteration99    count, for algorithms that have one.100    The higher the count, the more time it takes to hash101    the password and therefore the more time to break it.  Although with102    too high a count the time to calculate a hash may be several years103    — which is somewhat impractical.  If the <em class="parameter"><code>iter_count</code></em>104    parameter is omitted, the default iteration count is used.105    Allowed values for <em class="parameter"><code>iter_count</code></em> depend on the algorithm and106    are shown in <a class="xref" href="pgcrypto.html#PGCRYPTO-ICFC-TABLE" title="Table F.19. Iteration Counts for crypt()">Table F.19</a>.107   </p><div class="table" id="PGCRYPTO-ICFC-TABLE"><p class="title"><strong>Table F.19. Iteration Counts for <code class="function">crypt()</code></strong></p><div class="table-contents"><table class="table" summary="Iteration Counts for crypt()" border="1"><colgroup><col /><col /><col /><col /></colgroup><thead><tr><th>Algorithm</th><th>Default</th><th>Min</th><th>Max</th></tr></thead><tbody><tr><td><code class="literal">xdes</code></td><td>725</td><td>1</td><td>16777215</td></tr><tr><td><code class="literal">bf</code></td><td>6</td><td>4</td><td>31</td></tr></tbody></table></div></div><br class="table-break" /><p>108    For <code class="literal">xdes</code> there is an additional limitation that the109    iteration count must be an odd number.110   </p><p>111    To pick an appropriate iteration count, consider that112    the original DES crypt was designed to have the speed of 4 hashes per113    second on the hardware of that time.114    Slower than 4 hashes per second would probably dampen usability.115    Faster than 100 hashes per second is probably too fast.116   </p><p>117    <a class="xref" href="pgcrypto.html#PGCRYPTO-HASH-SPEED-TABLE" title="Table F.20. Hash Algorithm Speeds">Table F.20</a> gives an overview of the relative slowness118    of different hashing algorithms.119    The table shows how much time it would take to try all120    combinations of characters in an 8-character password, assuming121    that the password contains either only lower case letters, or122    upper- and lower-case letters and numbers.123    In the <code class="literal">crypt-bf</code> entries, the number after a slash is124    the <em class="parameter"><code>iter_count</code></em> parameter of125    <code class="function">gen_salt</code>.126   </p><div class="table" id="PGCRYPTO-HASH-SPEED-TABLE"><p class="title"><strong>Table F.20. Hash Algorithm Speeds</strong></p><div class="table-contents"><table class="table" summary="Hash Algorithm Speeds" border="1"><colgroup><col /><col /><col /><col /><col /></colgroup><thead><tr><th>Algorithm</th><th>Hashes/sec</th><th>For <code class="literal">[a-z]</code></th><th>For <code class="literal">[A-Za-z0-9]</code></th><th>Duration relative to <code class="literal">md5 hash</code></th></tr></thead><tbody><tr><td><code class="literal">crypt-bf/8</code></td><td>1792</td><td>4 years</td><td>3927 years</td><td>100k</td></tr><tr><td><code class="literal">crypt-bf/7</code></td><td>3648</td><td>2 years</td><td>1929 years</td><td>50k</td></tr><tr><td><code class="literal">crypt-bf/6</code></td><td>7168</td><td>1 year</td><td>982 years</td><td>25k</td></tr><tr><td><code class="literal">crypt-bf/5</code></td><td>13504</td><td>188 days</td><td>521 years</td><td>12.5k</td></tr><tr><td><code class="literal">crypt-md5</code></td><td>171584</td><td>15 days</td><td>41 years</td><td>1k</td></tr><tr><td><code class="literal">crypt-des</code></td><td>23221568</td><td>157.5 minutes</td><td>108 days</td><td>7</td></tr><tr><td><code class="literal">sha1</code></td><td>37774272</td><td>90 minutes</td><td>68 days</td><td>4</td></tr><tr><td><code class="literal">md5</code> (hash)</td><td>150085504</td><td>22.5 minutes</td><td>17 days</td><td>1</td></tr></tbody></table></div></div><br class="table-break" /><p>127    Notes:128   </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>129     The machine used is an Intel Mobile Core i3.130     </p></li><li class="listitem"><p>131      <code class="literal">crypt-des</code> and <code class="literal">crypt-md5</code> algorithm numbers are132      taken from John the Ripper v1.6.38 <code class="literal">-test</code> output.133     </p></li><li class="listitem"><p>134      <code class="literal">md5 hash</code> numbers are from mdcrack 1.2.135     </p></li><li class="listitem"><p>136      <code class="literal">sha1</code> numbers are from lcrack-20031130-beta.137     </p></li><li class="listitem"><p>138      <code class="literal">crypt-bf</code> numbers are taken using a simple program that139      loops over 1000 8-character passwords.  That way the speed140      with different numbers of iterations can be shown.  For reference: <code class="literal">john141      -test</code> shows 13506 loops/sec for <code class="literal">crypt-bf/5</code>.142      (The very small143      difference in results is in accordance with the fact that the144      <code class="literal">crypt-bf</code> implementation in <code class="filename">pgcrypto</code>145      is the same one used in John the Ripper.)146     </p></li></ul></div><p>147    Note that <span class="quote">“<span class="quote">try all combinations</span>”</span> is not a realistic exercise.148    Usually password cracking is done with the help of dictionaries, which149    contain both regular words and various mutations of them.  So, even150    somewhat word-like passwords could be cracked much faster than the above151    numbers suggest, while a 6-character non-word-like password may escape152    cracking.  Or not.153   </p></div></div><div class="sect2" id="PGCRYPTO-PGP-ENC-FUNCS"><div class="titlepage"><div><div><h3 class="title">F.28.3. PGP Encryption Functions <a href="#PGCRYPTO-PGP-ENC-FUNCS" class="id_link">#</a></h3></div></div></div><p>154   The functions here implement the encryption part of the OpenPGP155   (<a class="ulink" href="https://datatracker.ietf.org/doc/html/rfc4880" target="_top">RFC 4880</a>)156   standard.  Supported are both symmetric-key and public-key encryption.157  </p><p>158   An encrypted PGP message consists of 2 parts, or <em class="firstterm">packets</em>:159  </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>160     Packet containing a session key — either symmetric-key or public-key161     encrypted.162    </p></li><li class="listitem"><p>163     Packet containing data encrypted with the session key.164    </p></li></ul></div><p>165   When encrypting with a symmetric key (i.e., a password):166  </p><div class="orderedlist"><ol class="orderedlist" type="1"><li class="listitem"><p>167     The given password is hashed using a String2Key (S2K) algorithm.  This is168     rather similar to <code class="function">crypt()</code> algorithms — purposefully169     slow and with random salt — but it produces a full-length binary170     key.171    </p></li><li class="listitem"><p>172     If a separate session key is requested, a new random key will be173     generated.  Otherwise the S2K key will be used directly as the session174     key.175    </p></li><li class="listitem"><p>176     If the S2K key is to be used directly, then only S2K settings will be put177     into the session key packet.  Otherwise the session key will be encrypted178     with the S2K key and put into the session key packet.179    </p></li></ol></div><p>180   When encrypting with a public key:181  </p><div class="orderedlist"><ol class="orderedlist" type="1"><li class="listitem"><p>182     A new random session key is generated.183    </p></li><li class="listitem"><p>184     It is encrypted using the public key and put into the session key packet.185    </p></li></ol></div><p>186   In either case the data to be encrypted is processed as follows:187  </p><div class="orderedlist"><ol class="orderedlist" type="1"><li class="listitem"><p>188     Optional data-manipulation: compression, conversion to UTF-8,189     and/or conversion of line-endings.190    </p></li><li class="listitem"><p>191     The data is prefixed with a block of random bytes.  This is equivalent192     to using a random IV.193    </p></li><li class="listitem"><p>194     A SHA1 hash of the random prefix and data is appended.195    </p></li><li class="listitem"><p>196     All this is encrypted with the session key and placed in the data packet.197    </p></li></ol></div><div class="sect3" id="PGCRYPTO-PGP-ENC-FUNCS-PGP-SYM-ENCRYPT"><div class="titlepage"><div><div><h4 class="title">F.28.3.1. <code class="function">pgp_sym_encrypt()</code> <a href="#PGCRYPTO-PGP-ENC-FUNCS-PGP-SYM-ENCRYPT" class="id_link">#</a></h4></div></div></div><a id="id-1.11.7.38.9.11.2" class="indexterm"></a><a id="id-1.11.7.38.9.11.3" class="indexterm"></a><pre class="synopsis">198pgp_sym_encrypt(data text, psw text [, options text ]) returns bytea199pgp_sym_encrypt_bytea(data bytea, psw text [, options text ]) returns bytea200</pre><p>201    Encrypt <em class="parameter"><code>data</code></em> with a symmetric PGP key <em class="parameter"><code>psw</code></em>.202    The <em class="parameter"><code>options</code></em> parameter can contain option settings,203    as described below.204   </p></div><div class="sect3" id="PGCRYPTO-PGP-ENC-FUNCS-PGP-SYM-DECRYPT"><div class="titlepage"><div><div><h4 class="title">F.28.3.2. <code class="function">pgp_sym_decrypt()</code> <a href="#PGCRYPTO-PGP-ENC-FUNCS-PGP-SYM-DECRYPT" class="id_link">#</a></h4></div></div></div><a id="id-1.11.7.38.9.12.2" class="indexterm"></a><a id="id-1.11.7.38.9.12.3" class="indexterm"></a><pre class="synopsis">205pgp_sym_decrypt(msg bytea, psw text [, options text ]) returns text206pgp_sym_decrypt_bytea(msg bytea, psw text [, options text ]) returns bytea207</pre><p>208    Decrypt a symmetric-key-encrypted PGP message.209   </p><p>210    Decrypting <code class="type">bytea</code> data with <code class="function">pgp_sym_decrypt</code> is disallowed.211    This is to avoid outputting invalid character data.  Decrypting212    originally textual data with <code class="function">pgp_sym_decrypt_bytea</code> is fine.213   </p><p>214    The <em class="parameter"><code>options</code></em> parameter can contain option settings,215    as described below.216   </p></div><div class="sect3" id="PGCRYPTO-PGP-ENC-FUNCS-PGP-PUB-ENCRYPT"><div class="titlepage"><div><div><h4 class="title">F.28.3.3. <code class="function">pgp_pub_encrypt()</code> <a href="#PGCRYPTO-PGP-ENC-FUNCS-PGP-PUB-ENCRYPT" class="id_link">#</a></h4></div></div></div><a id="id-1.11.7.38.9.13.2" class="indexterm"></a><a id="id-1.11.7.38.9.13.3" class="indexterm"></a><pre class="synopsis">217pgp_pub_encrypt(data text, key bytea [, options text ]) returns bytea218pgp_pub_encrypt_bytea(data bytea, key bytea [, options text ]) returns bytea219</pre><p>220    Encrypt <em class="parameter"><code>data</code></em> with a public PGP key <em class="parameter"><code>key</code></em>.221    Giving this function a secret key will produce an error.222   </p><p>223    The <em class="parameter"><code>options</code></em> parameter can contain option settings,224    as described below.225   </p></div><div class="sect3" id="PGCRYPTO-PGP-ENC-FUNCS-PGP-PUB-DECRYPT"><div class="titlepage"><div><div><h4 class="title">F.28.3.4. <code class="function">pgp_pub_decrypt()</code> <a href="#PGCRYPTO-PGP-ENC-FUNCS-PGP-PUB-DECRYPT" class="id_link">#</a></h4></div></div></div><a id="id-1.11.7.38.9.14.2" class="indexterm"></a><a id="id-1.11.7.38.9.14.3" class="indexterm"></a><pre class="synopsis">226pgp_pub_decrypt(msg bytea, key bytea [, psw text [, options text ]]) returns text227pgp_pub_decrypt_bytea(msg bytea, key bytea [, psw text [, options text ]]) returns bytea228</pre><p>229    Decrypt a public-key-encrypted message.  <em class="parameter"><code>key</code></em> must be the230    secret key corresponding to the public key that was used to encrypt.231    If the secret key is password-protected, you must give the password in232    <em class="parameter"><code>psw</code></em>.  If there is no password, but you want to specify233    options, you need to give an empty password.234   </p><p>235    Decrypting <code class="type">bytea</code> data with <code class="function">pgp_pub_decrypt</code> is disallowed.236    This is to avoid outputting invalid character data.  Decrypting237    originally textual data with <code class="function">pgp_pub_decrypt_bytea</code> is fine.238   </p><p>239    The <em class="parameter"><code>options</code></em> parameter can contain option settings,240    as described below.241   </p></div><div class="sect3" id="PGCRYPTO-PGP-ENC-FUNCS-PGP-KEY-ID"><div class="titlepage"><div><div><h4 class="title">F.28.3.5. <code class="function">pgp_key_id()</code> <a href="#PGCRYPTO-PGP-ENC-FUNCS-PGP-KEY-ID" class="id_link">#</a></h4></div></div></div><a id="id-1.11.7.38.9.15.2" class="indexterm"></a><pre class="synopsis">242pgp_key_id(bytea) returns text243</pre><p>244    <code class="function">pgp_key_id</code> extracts the key ID of a PGP public or secret key.245    Or it gives the key ID that was used for encrypting the data, if given246    an encrypted message.247   </p><p>248    It can return 2 special key IDs:249   </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>250      <code class="literal">SYMKEY</code>251     </p><p>252      The message is encrypted with a symmetric key.253     </p></li><li class="listitem"><p>254      <code class="literal">ANYKEY</code>255     </p><p>256      The message is public-key encrypted, but the key ID has been removed.257      That means you will need to try all your secret keys on it to see258      which one decrypts it.  <code class="filename">pgcrypto</code> itself does not produce259      such messages.260     </p></li></ul></div><p>261    Note that different keys may have the same ID.   This is rare but a normal262    event. The client application should then try to decrypt with each one,263    to see which fits — like handling <code class="literal">ANYKEY</code>.264   </p></div><div class="sect3" id="PGCRYPTO-PGP-ENC-FUNCS-ARMOR"><div class="titlepage"><div><div><h4 class="title">F.28.3.6. <code class="function">armor()</code>, <code class="function">dearmor()</code> <a href="#PGCRYPTO-PGP-ENC-FUNCS-ARMOR" class="id_link">#</a></h4></div></div></div><a id="id-1.11.7.38.9.16.2" class="indexterm"></a><a id="id-1.11.7.38.9.16.3" class="indexterm"></a><pre class="synopsis">265armor(data bytea [ , keys text[], values text[] ]) returns text266dearmor(data text) returns bytea267</pre><p>268    These functions wrap/unwrap binary data into PGP ASCII-armor format,269    which is basically Base64 with CRC and additional formatting.270   </p><p>271    If the <em class="parameter"><code>keys</code></em> and <em class="parameter"><code>values</code></em> arrays are specified,272    an <em class="firstterm">armor header</em> is added to the armored format for each273    key/value pair. Both arrays must be single-dimensional, and they must274    be of the same length.  The keys and values cannot contain any non-ASCII275    characters.276   </p></div><div class="sect3" id="PGCRYPTO-PGP-ENC-FUNCS-PGP-ARMOR-HEADERS"><div class="titlepage"><div><div><h4 class="title">F.28.3.7. <code class="function">pgp_armor_headers</code> <a href="#PGCRYPTO-PGP-ENC-FUNCS-PGP-ARMOR-HEADERS" class="id_link">#</a></h4></div></div></div><a id="id-1.11.7.38.9.17.2" class="indexterm"></a><pre class="synopsis">277pgp_armor_headers(data text, key out text, value out text) returns setof record278</pre><p>279    <code class="function">pgp_armor_headers()</code> extracts the armor headers from280    <em class="parameter"><code>data</code></em>.  The return value is a set of rows with two columns,281    key and value.  If the keys or values contain any non-ASCII characters,282    they are treated as UTF-8.283   </p></div><div class="sect3" id="PGCRYPTO-PGP-ENC-FUNCS-OPTS"><div class="titlepage"><div><div><h4 class="title">F.28.3.8. Options for PGP Functions <a href="#PGCRYPTO-PGP-ENC-FUNCS-OPTS" class="id_link">#</a></h4></div></div></div><p>284    Options are named to be similar to GnuPG.  An option's value should be285    given after an equal sign; separate options from each other with commas.286    For example:287</p><pre class="programlisting">288pgp_sym_encrypt(data, psw, 'compress-algo=1, cipher-algo=aes256')289</pre><p>290   </p><p>291    All of the options except <code class="literal">convert-crlf</code> apply only to292    encrypt functions.  Decrypt functions get the parameters from the PGP293    data.294   </p><p>295    The most interesting options are probably296    <code class="literal">compress-algo</code> and <code class="literal">unicode-mode</code>.297    The rest should have reasonable defaults.298   </p><div class="sect4" id="PGCRYPTO-PGP-ENC-FUNCS-OPTS-CIPHER-ALGO"><div class="titlepage"><div><div><h5 class="title">F.28.3.8.1. cipher-algo <a href="#PGCRYPTO-PGP-ENC-FUNCS-OPTS-CIPHER-ALGO" class="id_link">#</a></h5></div></div></div><p>299    Which cipher algorithm to use.300   </p><div class="literallayout"><p><br />301Values: bf, aes128, aes192, aes256, 3des, cast5<br />302Default: aes128<br />303Applies to: pgp_sym_encrypt, pgp_pub_encrypt<br />304</p></div></div><div class="sect4" id="PGCRYPTO-PGP-ENC-FUNCS-OPTS-COMPRESS-ALGO"><div class="titlepage"><div><div><h5 class="title">F.28.3.8.2. compress-algo <a href="#PGCRYPTO-PGP-ENC-FUNCS-OPTS-COMPRESS-ALGO" class="id_link">#</a></h5></div></div></div><p>305    Which compression algorithm to use.  Only available if306    <span class="productname">PostgreSQL</span> was built with zlib.307   </p><div class="literallayout"><p><br />308Values:<br />309  0 - no compression<br />310  1 - ZIP compression<br />311  2 - ZLIB compression (= ZIP plus meta-data and block CRCs)<br />312Default: 0<br />313Applies to: pgp_sym_encrypt, pgp_pub_encrypt<br />314</p></div></div><div class="sect4" id="PGCRYPTO-PGP-ENC-FUNCS-OPTS-COMPRESS-LEVEL"><div class="titlepage"><div><div><h5 class="title">F.28.3.8.3. compress-level <a href="#PGCRYPTO-PGP-ENC-FUNCS-OPTS-COMPRESS-LEVEL" class="id_link">#</a></h5></div></div></div><p>315    How much to compress.  Higher levels compress smaller but are slower.316    0 disables compression.317   </p><div class="literallayout"><p><br />318Values: 0, 1-9<br />319Default: 6<br />320Applies to: pgp_sym_encrypt, pgp_pub_encrypt<br />321</p></div></div><div class="sect4" id="PGCRYPTO-PGP-ENC-FUNCS-OPTS-CONVERT-CRLF"><div class="titlepage"><div><div><h5 class="title">F.28.3.8.4. convert-crlf <a href="#PGCRYPTO-PGP-ENC-FUNCS-OPTS-CONVERT-CRLF" class="id_link">#</a></h5></div></div></div><p>322    Whether to convert <code class="literal">\n</code> into <code class="literal">\r\n</code> when323    encrypting and <code class="literal">\r\n</code> to <code class="literal">\n</code> when324    decrypting.  <acronym class="acronym">RFC</acronym> 4880 specifies that text data should be stored using325    <code class="literal">\r\n</code> line-feeds.  Use this to get fully RFC-compliant326    behavior.327   </p><div class="literallayout"><p><br />328Values: 0, 1<br />329Default: 0<br />330Applies to: pgp_sym_encrypt, pgp_pub_encrypt, pgp_sym_decrypt, pgp_pub_decrypt<br />331</p></div></div><div class="sect4" id="PGCRYPTO-PGP-ENC-FUNCS-OPTS-DISABLE-MDC"><div class="titlepage"><div><div><h5 class="title">F.28.3.8.5. disable-mdc <a href="#PGCRYPTO-PGP-ENC-FUNCS-OPTS-DISABLE-MDC" class="id_link">#</a></h5></div></div></div><p>332    Do not protect data with SHA-1.  The only good reason to use this333    option is to achieve compatibility with ancient PGP products, predating334    the addition of SHA-1 protected packets to <acronym class="acronym">RFC</acronym> 4880.335    Recent gnupg.org and pgp.com software supports it fine.336   </p><div class="literallayout"><p><br />337Values: 0, 1<br />338Default: 0<br />339Applies to: pgp_sym_encrypt, pgp_pub_encrypt<br />340</p></div></div><div class="sect4" id="PGCRYPTO-PGP-ENC-FUNCS-OPTS-SESS-KEY"><div class="titlepage"><div><div><h5 class="title">F.28.3.8.6. sess-key <a href="#PGCRYPTO-PGP-ENC-FUNCS-OPTS-SESS-KEY" class="id_link">#</a></h5></div></div></div><p>341    Use separate session key.  Public-key encryption always uses a separate342    session key; this option is for symmetric-key encryption, which by default343    uses the S2K key directly.344   </p><div class="literallayout"><p><br />345Values: 0, 1<br />346Default: 0<br />347Applies to: pgp_sym_encrypt<br />348</p></div></div><div class="sect4" id="PGCRYPTO-PGP-ENC-FUNCS-OPTS-S2K-MODE"><div class="titlepage"><div><div><h5 class="title">F.28.3.8.7. s2k-mode <a href="#PGCRYPTO-PGP-ENC-FUNCS-OPTS-S2K-MODE" class="id_link">#</a></h5></div></div></div><p>349    Which S2K algorithm to use.350   </p><div class="literallayout"><p><br />351Values:<br />352  0 - Without salt.  Dangerous!<br />353  1 - With salt but with fixed iteration count.<br />354  3 - Variable iteration count.<br />355Default: 3<br />356Applies to: pgp_sym_encrypt<br />357</p></div></div><div class="sect4" id="PGCRYPTO-PGP-ENC-FUNCS-OPTS-S2K-COUNT"><div class="titlepage"><div><div><h5 class="title">F.28.3.8.8. s2k-count <a href="#PGCRYPTO-PGP-ENC-FUNCS-OPTS-S2K-COUNT" class="id_link">#</a></h5></div></div></div><p>358    The number of iterations of the S2K algorithm to use.  It must359    be a value between 1024 and 65011712, inclusive.360   </p><div class="literallayout"><p><br />361Default: A random value between 65536 and 253952<br />362Applies to: pgp_sym_encrypt, only with s2k-mode=3<br />363</p></div></div><div class="sect4" id="PGCRYPTO-PGP-ENC-FUNCS-OPTS-S2K-DIGEST-ALGO"><div class="titlepage"><div><div><h5 class="title">F.28.3.8.9. s2k-digest-algo <a href="#PGCRYPTO-PGP-ENC-FUNCS-OPTS-S2K-DIGEST-ALGO" class="id_link">#</a></h5></div></div></div><p>364    Which digest algorithm to use in S2K calculation.365   </p><div class="literallayout"><p><br />366Values: md5, sha1<br />367Default: sha1<br />368Applies to: pgp_sym_encrypt<br />369</p></div></div><div class="sect4" id="PGCRYPTO-PGP-ENC-FUNCS-OPTS-S2K-CIPHER-ALGO"><div class="titlepage"><div><div><h5 class="title">F.28.3.8.10. s2k-cipher-algo <a href="#PGCRYPTO-PGP-ENC-FUNCS-OPTS-S2K-CIPHER-ALGO" class="id_link">#</a></h5></div></div></div><p>370    Which cipher to use for encrypting separate session key.371   </p><div class="literallayout"><p><br />372Values: bf, aes, aes128, aes192, aes256<br />373Default: use cipher-algo<br />374Applies to: pgp_sym_encrypt<br />375</p></div></div><div class="sect4" id="PGCRYPTO-PGP-ENC-FUNCS-OPTS-UNICODE-MODE"><div class="titlepage"><div><div><h5 class="title">F.28.3.8.11. unicode-mode <a href="#PGCRYPTO-PGP-ENC-FUNCS-OPTS-UNICODE-MODE" class="id_link">#</a></h5></div></div></div><p>376    Whether to convert textual data from database internal encoding to377    UTF-8 and back.  If your database already is UTF-8, no conversion will378    be done, but the message will be tagged as UTF-8.  Without this option379    it will not be.380   </p><div class="literallayout"><p><br />381Values: 0, 1<br />382Default: 0<br />383Applies to: pgp_sym_encrypt, pgp_pub_encrypt<br />384</p></div></div></div><div class="sect3" id="PGCRYPTO-PGP-ENC-FUNCS-GNUPG"><div class="titlepage"><div><div><h4 class="title">F.28.3.9. Generating PGP Keys with GnuPG <a href="#PGCRYPTO-PGP-ENC-FUNCS-GNUPG" class="id_link">#</a></h4></div></div></div><p>385   To generate a new key:386</p><pre class="programlisting">387gpg --gen-key388</pre><p>389  </p><p>390   The preferred key type is <span class="quote">“<span class="quote">DSA and Elgamal</span>”</span>.391  </p><p>392   For RSA encryption you must create either DSA or RSA sign-only key393   as master and then add an RSA encryption subkey with394   <code class="literal">gpg --edit-key</code>.395  </p><p>396   To list keys:397</p><pre class="programlisting">398gpg --list-secret-keys399</pre><p>400  </p><p>401   To export a public key in ASCII-armor format:402</p><pre class="programlisting">403gpg -a --export KEYID &gt; public.key404</pre><p>405  </p><p>406   To export a secret key in ASCII-armor format:407</p><pre class="programlisting">408gpg -a --export-secret-keys KEYID &gt; secret.key409</pre><p>410  </p><p>411   You need to use <code class="function">dearmor()</code> on these keys before giving them to412   the PGP functions.  Or if you can handle binary data, you can drop413   <code class="literal">-a</code> from the command.414  </p><p>415   For more details see <code class="literal">man gpg</code>,416   <a class="ulink" href="https://www.gnupg.org/gph/en/manual.html" target="_top">The GNU417   Privacy Handbook</a> and other documentation on418   <a class="ulink" href="https://www.gnupg.org/" target="_top">https://www.gnupg.org/</a>.419  </p></div><div class="sect3" id="PGCRYPTO-PGP-ENC-FUNCS-LIMITATIONS"><div class="titlepage"><div><div><h4 class="title">F.28.3.10. Limitations of PGP Code <a href="#PGCRYPTO-PGP-ENC-FUNCS-LIMITATIONS" class="id_link">#</a></h4></div></div></div><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>420    No support for signing.  That also means that it is not checked421    whether the encryption subkey belongs to the master key.422    </p></li><li class="listitem"><p>423    No support for encryption key as master key.  As such practice424    is generally discouraged, this should not be a problem.425    </p></li><li class="listitem"><p>426    No support for several subkeys.  This may seem like a problem, as this427    is common practice.  On the other hand, you should not use your regular428    GPG/PGP keys with <code class="filename">pgcrypto</code>, but create new ones,429    as the usage scenario is rather different.430    </p></li></ul></div></div></div><div class="sect2" id="PGCRYPTO-RAW-ENC-FUNCS"><div class="titlepage"><div><div><h3 class="title">F.28.4. Raw Encryption Functions <a href="#PGCRYPTO-RAW-ENC-FUNCS" class="id_link">#</a></h3></div></div></div><p>431   These functions only run a cipher over data; they don't have any advanced432   features of PGP encryption.  Therefore they have some major problems:433  </p><div class="orderedlist"><ol class="orderedlist" type="1"><li class="listitem"><p>434    They use user key directly as cipher key.435    </p></li><li class="listitem"><p>436    They don't provide any integrity checking, to see437    if the encrypted data was modified.438    </p></li><li class="listitem"><p>439    They expect that users manage all encryption parameters440    themselves, even IV.441    </p></li><li class="listitem"><p>442    They don't handle text.443    </p></li></ol></div><p>444   So, with the introduction of PGP encryption, usage of raw445   encryption functions is discouraged.446  </p><a id="id-1.11.7.38.10.5" class="indexterm"></a><a id="id-1.11.7.38.10.6" class="indexterm"></a><a id="id-1.11.7.38.10.7" class="indexterm"></a><a id="id-1.11.7.38.10.8" class="indexterm"></a><pre class="synopsis">447encrypt(data bytea, key bytea, type text) returns bytea448decrypt(data bytea, key bytea, type text) returns bytea449 450encrypt_iv(data bytea, key bytea, iv bytea, type text) returns bytea451decrypt_iv(data bytea, key bytea, iv bytea, type text) returns bytea452</pre><p>453   Encrypt/decrypt data using the cipher method specified by454   <em class="parameter"><code>type</code></em>.  The syntax of the455   <em class="parameter"><code>type</code></em> string is:456 457</p><pre class="synopsis">458<em class="replaceable"><code>algorithm</code></em> [<span class="optional"> <code class="literal">-</code> <em class="replaceable"><code>mode</code></em> </span>] [<span class="optional"> <code class="literal">/pad:</code> <em class="replaceable"><code>padding</code></em> </span>]459</pre><p>460   where <em class="replaceable"><code>algorithm</code></em> is one of:461 462  </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p><code class="literal">bf</code> — Blowfish</p></li><li class="listitem"><p><code class="literal">aes</code> — AES (Rijndael-128, -192 or -256)</p></li></ul></div><p>463   and <em class="replaceable"><code>mode</code></em> is one of:464  </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>465    <code class="literal">cbc</code> — next block depends on previous (default)466    </p></li><li class="listitem"><p>467    <code class="literal">ecb</code> — each block is encrypted separately (for468    testing only)469    </p></li></ul></div><p>470   and <em class="replaceable"><code>padding</code></em> is one of:471  </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>472    <code class="literal">pkcs</code> — data may be any length (default)473    </p></li><li class="listitem"><p>474    <code class="literal">none</code> — data must be multiple of cipher block size475    </p></li></ul></div><p>476  </p><p>477   So, for example, these are equivalent:478</p><pre class="programlisting">479encrypt(data, 'fooz', 'bf')480encrypt(data, 'fooz', 'bf-cbc/pad:pkcs')481</pre><p>482  </p><p>483   In <code class="function">encrypt_iv</code> and <code class="function">decrypt_iv</code>, the484   <em class="parameter"><code>iv</code></em> parameter is the initial value for the CBC mode;485   it is ignored for ECB.486   It is clipped or padded with zeroes if not exactly block size.487   It defaults to all zeroes in the functions without this parameter.488  </p></div><div class="sect2" id="PGCRYPTO-RANDOM-DATA-FUNCS"><div class="titlepage"><div><div><h3 class="title">F.28.5. Random-Data Functions <a href="#PGCRYPTO-RANDOM-DATA-FUNCS" class="id_link">#</a></h3></div></div></div><a id="id-1.11.7.38.11.2" class="indexterm"></a><pre class="synopsis">489gen_random_bytes(count integer) returns bytea490</pre><p>491   Returns <em class="parameter"><code>count</code></em> cryptographically strong random bytes.492   At most 1024 bytes can be extracted at a time.  This is to avoid493   draining the randomness generator pool.494  </p><a id="id-1.11.7.38.11.5" class="indexterm"></a><pre class="synopsis">495gen_random_uuid() returns uuid496</pre><p>497   Returns a version 4 (random) UUID. (Obsolete, this function498   internally calls the <a class="link" href="functions-uuid.html" title="9.14. UUID Functions">core499   function</a> of the same name.)500  </p></div><div class="sect2" id="PGCRYPTO-NOTES"><div class="titlepage"><div><div><h3 class="title">F.28.6. Notes <a href="#PGCRYPTO-NOTES" class="id_link">#</a></h3></div></div></div><div class="sect3" id="PGCRYPTO-NOTES-CONFIG"><div class="titlepage"><div><div><h4 class="title">F.28.6.1. Configuration <a href="#PGCRYPTO-NOTES-CONFIG" class="id_link">#</a></h4></div></div></div><p>501    <code class="filename">pgcrypto</code> configures itself according to the findings of the502    main PostgreSQL <code class="literal">configure</code> script.  The options that503    affect it are <code class="literal">--with-zlib</code> and504    <code class="literal">--with-ssl=openssl</code>.505   </p><p>506    When compiled with zlib, PGP encryption functions are able to507    compress data before encrypting.508   </p><p>509    <code class="filename">pgcrypto</code> requires <span class="productname">OpenSSL</span>.510    Otherwise, it will not be built or installed.511   </p><p>512    When compiled against <span class="productname">OpenSSL</span> 3.0.0 and later513    versions, the legacy provider must be activated in the514    <code class="filename">openssl.cnf</code> configuration file in order to use older515    ciphers like DES or Blowfish.516   </p></div><div class="sect3" id="PGCRYPTO-NOTES-NULL-HANDLING"><div class="titlepage"><div><div><h4 class="title">F.28.6.2. NULL Handling <a href="#PGCRYPTO-NOTES-NULL-HANDLING" class="id_link">#</a></h4></div></div></div><p>517    As is standard in SQL, all functions return NULL, if any of the arguments518    are NULL.  This may create security risks on careless usage.519   </p></div><div class="sect3" id="PGCRYPTO-NOTES-SEC-LIMITS"><div class="titlepage"><div><div><h4 class="title">F.28.6.3. Security Limitations <a href="#PGCRYPTO-NOTES-SEC-LIMITS" class="id_link">#</a></h4></div></div></div><p>520    All <code class="filename">pgcrypto</code> functions run inside the database server.521    That means that all522    the data and passwords move between <code class="filename">pgcrypto</code> and client523    applications in clear text.  Thus you must:524   </p><div class="orderedlist"><ol class="orderedlist" type="1"><li class="listitem"><p>Connect locally or use SSL connections.</p></li><li class="listitem"><p>Trust both system and database administrator.</p></li></ol></div><p>525    If you cannot, then better do crypto inside client application.526   </p><p>527    The implementation does not resist528    <a class="ulink" href="https://en.wikipedia.org/wiki/Side-channel_attack" target="_top">side-channel529    attacks</a>.  For example, the time required for530    a <code class="filename">pgcrypto</code> decryption function to complete varies among531    ciphertexts of a given size.532   </p></div></div><div class="sect2" id="PGCRYPTO-AUTHOR"><div class="titlepage"><div><div><h3 class="title">F.28.7. Author <a href="#PGCRYPTO-AUTHOR" class="id_link">#</a></h3></div></div></div><p>533   Marko Kreen <code class="email">&lt;<a class="email" href="mailto:markokr@gmail.com">markokr@gmail.com</a>&gt;</code>534  </p><p>535   <code class="filename">pgcrypto</code> uses code from the following sources:536  </p><div class="informaltable"><table class="informaltable" border="1"><colgroup><col /><col /><col /></colgroup><thead><tr><th>Algorithm</th><th>Author</th><th>Source origin</th></tr></thead><tbody><tr><td>DES crypt</td><td>David Burren and others</td><td>FreeBSD libcrypt</td></tr><tr><td>MD5 crypt</td><td>Poul-Henning Kamp</td><td>FreeBSD libcrypt</td></tr><tr><td>Blowfish crypt</td><td>Solar Designer</td><td>www.openwall.com</td></tr></tbody></table></div></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="pgbuffercache.html" title="F.27. pg_buffercache — inspect PostgreSQL&#10;    buffer cache state">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="contrib.html" title="Appendix F. Additional Supplied Modules and Extensions">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="pgfreespacemap.html" title="F.29. pg_freespacemap — examine the free space map">Next</a></td></tr><tr><td width="40%" align="left" valign="top">F.27. pg_buffercache — inspect <span class="productname">PostgreSQL</span>537    buffer cache state </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> F.29. pg_freespacemap — examine the free space map</td></tr></table></div></body></html>
codekingpro/portable-devtools · Team Ai