codekingpro/portable-devtools
115k
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 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 > 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 > 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"><<a class="email" href="mailto:markokr@gmail.com">markokr@gmail.com</a>></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 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>