Debian Codegraph @

doc/src/sgml/pgcrypto.sgml

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