1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
|
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!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</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" /><link rel="next" href="pgfreespacemap.html" title="F.29. pg_freespacemap" /></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</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="pgbuffercache.html" title="F.27. pg_buffercache">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="contrib.html" title="Appendix F. Additional Supplied Modules">Up</a></td><th width="60%" align="center">Appendix F. Additional Supplied Modules</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 15.4 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="pgfreespacemap.html" title="F.29. pg_freespacemap">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</h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="pgcrypto.html#id-1.11.7.37.7">F.28.1. General Hashing Functions</a></span></dt><dt><span class="sect2"><a href="pgcrypto.html#id-1.11.7.37.8">F.28.2. Password Hashing Functions</a></span></dt><dt><span class="sect2"><a href="pgcrypto.html#id-1.11.7.37.9">F.28.3. PGP Encryption Functions</a></span></dt><dt><span class="sect2"><a href="pgcrypto.html#id-1.11.7.37.10">F.28.4. Raw Encryption Functions</a></span></dt><dt><span class="sect2"><a href="pgcrypto.html#id-1.11.7.37.11">F.28.5. Random-Data Functions</a></span></dt><dt><span class="sect2"><a href="pgcrypto.html#id-1.11.7.37.12">F.28.6. Notes</a></span></dt><dt><span class="sect2"><a href="pgcrypto.html#id-1.11.7.37.13">F.28.7. Author</a></span></dt></dl></div><a id="id-1.11.7.37.2" class="indexterm"></a><a id="id-1.11.7.37.3" class="indexterm"></a><p>
The <code class="filename">pgcrypto</code> module provides cryptographic functions for
<span class="productname">PostgreSQL</span>.
</p><p>
This module is considered <span class="quote">“<span class="quote">trusted</span>”</span>, that is, it can be
installed by non-superusers who have <code class="literal">CREATE</code> privilege
on the current database.
</p><p>
<code class="filename">pgcrypto</code> requires OpenSSL and won't be installed if
OpenSSL support was not selected when PostgreSQL was built.
</p><div class="sect2" id="id-1.11.7.37.7"><div class="titlepage"><div><div><h3 class="title">F.28.1. General Hashing Functions</h3></div></div></div><div class="sect3" id="id-1.11.7.37.7.2"><div class="titlepage"><div><div><h4 class="title">F.28.1.1. <code class="function">digest()</code></h4></div></div></div><a id="id-1.11.7.37.7.2.2" class="indexterm"></a><pre class="synopsis">
digest(data text, type text) returns bytea
digest(data bytea, type text) returns bytea
</pre><p>
Computes a binary hash of the given <em class="parameter"><code>data</code></em>.
<em class="parameter"><code>type</code></em> is the algorithm to use.
Standard algorithms are <code class="literal">md5</code>, <code class="literal">sha1</code>,
<code class="literal">sha224</code>, <code class="literal">sha256</code>,
<code class="literal">sha384</code> and <code class="literal">sha512</code>.
Moreover, any digest algorithm <span class="productname">OpenSSL</span> supports
is automatically picked up.
</p><p>
If you want the digest as a hexadecimal string, use
<code class="function">encode()</code> on the result. For example:
</p><pre class="programlisting">
CREATE OR REPLACE FUNCTION sha1(bytea) returns text AS $$
SELECT encode(digest($1, 'sha1'), 'hex')
$$ LANGUAGE SQL STRICT IMMUTABLE;
</pre><p>
</p></div><div class="sect3" id="id-1.11.7.37.7.3"><div class="titlepage"><div><div><h4 class="title">F.28.1.2. <code class="function">hmac()</code></h4></div></div></div><a id="id-1.11.7.37.7.3.2" class="indexterm"></a><pre class="synopsis">
hmac(data text, key text, type text) returns bytea
hmac(data bytea, key bytea, type text) returns bytea
</pre><p>
Calculates hashed MAC for <em class="parameter"><code>data</code></em> with key <em class="parameter"><code>key</code></em>.
<em class="parameter"><code>type</code></em> is the same as in <code class="function">digest()</code>.
</p><p>
This is similar to <code class="function">digest()</code> but the hash can only be
recalculated knowing the key. This prevents the scenario of someone
altering data and also changing the hash to match.
</p><p>
If the key is larger than the hash block size it will first be hashed and
the result will be used as key.
</p></div></div><div class="sect2" id="id-1.11.7.37.8"><div class="titlepage"><div><div><h3 class="title">F.28.2. Password Hashing Functions</h3></div></div></div><p>
The functions <code class="function">crypt()</code> and <code class="function">gen_salt()</code>
are specifically designed for hashing passwords.
<code class="function">crypt()</code> does the hashing and <code class="function">gen_salt()</code>
prepares algorithm parameters for it.
</p><p>
The algorithms in <code class="function">crypt()</code> differ from the usual
MD5 or SHA1 hashing algorithms in the following respects:
</p><div class="orderedlist"><ol class="orderedlist" type="1"><li class="listitem"><p>
They are slow. As the amount of data is so small, this is the only
way to make brute-forcing passwords hard.
</p></li><li class="listitem"><p>
They use a random value, called the <em class="firstterm">salt</em>, so that users
having the same password will have different encrypted passwords.
This is also an additional defense against reversing the algorithm.
</p></li><li class="listitem"><p>
They include the algorithm type in the result, so passwords hashed with
different algorithms can co-exist.
</p></li><li class="listitem"><p>
Some of them are adaptive — that means when computers get
faster, you can tune the algorithm to be slower, without
introducing incompatibility with existing passwords.
</p></li></ol></div><p>
<a class="xref" href="pgcrypto.html#PGCRYPTO-CRYPT-ALGORITHMS" title="Table F.16. Supported Algorithms for crypt()">Table F.16</a> lists the algorithms
supported by the <code class="function">crypt()</code> function.
</p><div class="table" id="PGCRYPTO-CRYPT-ALGORITHMS"><p class="title"><strong>Table F.16. 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="id-1.11.7.37.8.7"><div class="titlepage"><div><div><h4 class="title">F.28.2.1. <code class="function">crypt()</code></h4></div></div></div><a id="id-1.11.7.37.8.7.2" class="indexterm"></a><pre class="synopsis">
crypt(password text, salt text) returns text
</pre><p>
Calculates a crypt(3)-style hash of <em class="parameter"><code>password</code></em>.
When storing a new password, you need to use
<code class="function">gen_salt()</code> to generate a new <em class="parameter"><code>salt</code></em> value.
To check a password, pass the stored hash value as <em class="parameter"><code>salt</code></em>,
and test whether the result matches the stored value.
</p><p>
Example of setting a new password:
</p><pre class="programlisting">
UPDATE ... SET pswhash = crypt('new password', gen_salt('md5'));
</pre><p>
</p><p>
Example of authentication:
</p><pre class="programlisting">
SELECT (pswhash = crypt('entered password', pswhash)) AS pswmatch FROM ... ;
</pre><p>
This returns <code class="literal">true</code> if the entered password is correct.
</p></div><div class="sect3" id="id-1.11.7.37.8.8"><div class="titlepage"><div><div><h4 class="title">F.28.2.2. <code class="function">gen_salt()</code></h4></div></div></div><a id="id-1.11.7.37.8.8.2" class="indexterm"></a><pre class="synopsis">
gen_salt(type text [, iter_count integer ]) returns text
</pre><p>
Generates a new random salt string for use in <code class="function">crypt()</code>.
The salt string also tells <code class="function">crypt()</code> which algorithm to use.
</p><p>
The <em class="parameter"><code>type</code></em> parameter specifies the hashing algorithm.
The accepted types are: <code class="literal">des</code>, <code class="literal">xdes</code>,
<code class="literal">md5</code> and <code class="literal">bf</code>.
</p><p>
The <em class="parameter"><code>iter_count</code></em> parameter lets the user specify the iteration
count, for algorithms that have one.
The higher the count, the more time it takes to hash
the password and therefore the more time to break it. Although with
too high a count the time to calculate a hash may be several years
— which is somewhat impractical. If the <em class="parameter"><code>iter_count</code></em>
parameter is omitted, the default iteration count is used.
Allowed values for <em class="parameter"><code>iter_count</code></em> depend on the algorithm and
are shown in <a class="xref" href="pgcrypto.html#PGCRYPTO-ICFC-TABLE" title="Table F.17. Iteration Counts for crypt()">Table F.17</a>.
</p><div class="table" id="PGCRYPTO-ICFC-TABLE"><p class="title"><strong>Table F.17. 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>
For <code class="literal">xdes</code> there is an additional limitation that the
iteration count must be an odd number.
</p><p>
To pick an appropriate iteration count, consider that
the original DES crypt was designed to have the speed of 4 hashes per
second on the hardware of that time.
Slower than 4 hashes per second would probably dampen usability.
Faster than 100 hashes per second is probably too fast.
</p><p>
<a class="xref" href="pgcrypto.html#PGCRYPTO-HASH-SPEED-TABLE" title="Table F.18. Hash Algorithm Speeds">Table F.18</a> gives an overview of the relative slowness
of different hashing algorithms.
The table shows how much time it would take to try all
combinations of characters in an 8-character password, assuming
that the password contains either only lower case letters, or
upper- and lower-case letters and numbers.
In the <code class="literal">crypt-bf</code> entries, the number after a slash is
the <em class="parameter"><code>iter_count</code></em> parameter of
<code class="function">gen_salt</code>.
</p><div class="table" id="PGCRYPTO-HASH-SPEED-TABLE"><p class="title"><strong>Table F.18. 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>
Notes:
</p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
The machine used is an Intel Mobile Core i3.
</p></li><li class="listitem"><p>
<code class="literal">crypt-des</code> and <code class="literal">crypt-md5</code> algorithm numbers are
taken from John the Ripper v1.6.38 <code class="literal">-test</code> output.
</p></li><li class="listitem"><p>
<code class="literal">md5 hash</code> numbers are from mdcrack 1.2.
</p></li><li class="listitem"><p>
<code class="literal">sha1</code> numbers are from lcrack-20031130-beta.
</p></li><li class="listitem"><p>
<code class="literal">crypt-bf</code> numbers are taken using a simple program that
loops over 1000 8-character passwords. That way I can show the speed
with different numbers of iterations. For reference: <code class="literal">john
-test</code> shows 13506 loops/sec for <code class="literal">crypt-bf/5</code>.
(The very small
difference in results is in accordance with the fact that the
<code class="literal">crypt-bf</code> implementation in <code class="filename">pgcrypto</code>
is the same one used in John the Ripper.)
</p></li></ul></div><p>
Note that <span class="quote">“<span class="quote">try all combinations</span>”</span> is not a realistic exercise.
Usually password cracking is done with the help of dictionaries, which
contain both regular words and various mutations of them. So, even
somewhat word-like passwords could be cracked much faster than the above
numbers suggest, while a 6-character non-word-like password may escape
cracking. Or not.
</p></div></div><div class="sect2" id="id-1.11.7.37.9"><div class="titlepage"><div><div><h3 class="title">F.28.3. PGP Encryption Functions</h3></div></div></div><p>
The functions here implement the encryption part of the OpenPGP
(<a class="ulink" href="https://tools.ietf.org/html/rfc4880" target="_top">RFC 4880</a>)
standard. Supported are both symmetric-key and public-key encryption.
</p><p>
An encrypted PGP message consists of 2 parts, or <em class="firstterm">packets</em>:
</p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
Packet containing a session key — either symmetric-key or public-key
encrypted.
</p></li><li class="listitem"><p>
Packet containing data encrypted with the session key.
</p></li></ul></div><p>
When encrypting with a symmetric key (i.e., a password):
</p><div class="orderedlist"><ol class="orderedlist" type="1"><li class="listitem"><p>
The given password is hashed using a String2Key (S2K) algorithm. This is
rather similar to <code class="function">crypt()</code> algorithms — purposefully
slow and with random salt — but it produces a full-length binary
key.
</p></li><li class="listitem"><p>
If a separate session key is requested, a new random key will be
generated. Otherwise the S2K key will be used directly as the session
key.
</p></li><li class="listitem"><p>
If the S2K key is to be used directly, then only S2K settings will be put
into the session key packet. Otherwise the session key will be encrypted
with the S2K key and put into the session key packet.
</p></li></ol></div><p>
When encrypting with a public key:
</p><div class="orderedlist"><ol class="orderedlist" type="1"><li class="listitem"><p>
A new random session key is generated.
</p></li><li class="listitem"><p>
It is encrypted using the public key and put into the session key packet.
</p></li></ol></div><p>
In either case the data to be encrypted is processed as follows:
</p><div class="orderedlist"><ol class="orderedlist" type="1"><li class="listitem"><p>
Optional data-manipulation: compression, conversion to UTF-8,
and/or conversion of line-endings.
</p></li><li class="listitem"><p>
The data is prefixed with a block of random bytes. This is equivalent
to using a random IV.
</p></li><li class="listitem"><p>
A SHA1 hash of the random prefix and data is appended.
</p></li><li class="listitem"><p>
All this is encrypted with the session key and placed in the data packet.
</p></li></ol></div><div class="sect3" id="id-1.11.7.37.9.11"><div class="titlepage"><div><div><h4 class="title">F.28.3.1. <code class="function">pgp_sym_encrypt()</code></h4></div></div></div><a id="id-1.11.7.37.9.11.2" class="indexterm"></a><a id="id-1.11.7.37.9.11.3" class="indexterm"></a><pre class="synopsis">
pgp_sym_encrypt(data text, psw text [, options text ]) returns bytea
pgp_sym_encrypt_bytea(data bytea, psw text [, options text ]) returns bytea
</pre><p>
Encrypt <em class="parameter"><code>data</code></em> with a symmetric PGP key <em class="parameter"><code>psw</code></em>.
The <em class="parameter"><code>options</code></em> parameter can contain option settings,
as described below.
</p></div><div class="sect3" id="id-1.11.7.37.9.12"><div class="titlepage"><div><div><h4 class="title">F.28.3.2. <code class="function">pgp_sym_decrypt()</code></h4></div></div></div><a id="id-1.11.7.37.9.12.2" class="indexterm"></a><a id="id-1.11.7.37.9.12.3" class="indexterm"></a><pre class="synopsis">
pgp_sym_decrypt(msg bytea, psw text [, options text ]) returns text
pgp_sym_decrypt_bytea(msg bytea, psw text [, options text ]) returns bytea
</pre><p>
Decrypt a symmetric-key-encrypted PGP message.
</p><p>
Decrypting <code class="type">bytea</code> data with <code class="function">pgp_sym_decrypt</code> is disallowed.
This is to avoid outputting invalid character data. Decrypting
originally textual data with <code class="function">pgp_sym_decrypt_bytea</code> is fine.
</p><p>
The <em class="parameter"><code>options</code></em> parameter can contain option settings,
as described below.
</p></div><div class="sect3" id="id-1.11.7.37.9.13"><div class="titlepage"><div><div><h4 class="title">F.28.3.3. <code class="function">pgp_pub_encrypt()</code></h4></div></div></div><a id="id-1.11.7.37.9.13.2" class="indexterm"></a><a id="id-1.11.7.37.9.13.3" class="indexterm"></a><pre class="synopsis">
pgp_pub_encrypt(data text, key bytea [, options text ]) returns bytea
pgp_pub_encrypt_bytea(data bytea, key bytea [, options text ]) returns bytea
</pre><p>
Encrypt <em class="parameter"><code>data</code></em> with a public PGP key <em class="parameter"><code>key</code></em>.
Giving this function a secret key will produce an error.
</p><p>
The <em class="parameter"><code>options</code></em> parameter can contain option settings,
as described below.
</p></div><div class="sect3" id="id-1.11.7.37.9.14"><div class="titlepage"><div><div><h4 class="title">F.28.3.4. <code class="function">pgp_pub_decrypt()</code></h4></div></div></div><a id="id-1.11.7.37.9.14.2" class="indexterm"></a><a id="id-1.11.7.37.9.14.3" class="indexterm"></a><pre class="synopsis">
pgp_pub_decrypt(msg bytea, key bytea [, psw text [, options text ]]) returns text
pgp_pub_decrypt_bytea(msg bytea, key bytea [, psw text [, options text ]]) returns bytea
</pre><p>
Decrypt a public-key-encrypted message. <em class="parameter"><code>key</code></em> must be the
secret key corresponding to the public key that was used to encrypt.
If the secret key is password-protected, you must give the password in
<em class="parameter"><code>psw</code></em>. If there is no password, but you want to specify
options, you need to give an empty password.
</p><p>
Decrypting <code class="type">bytea</code> data with <code class="function">pgp_pub_decrypt</code> is disallowed.
This is to avoid outputting invalid character data. Decrypting
originally textual data with <code class="function">pgp_pub_decrypt_bytea</code> is fine.
</p><p>
The <em class="parameter"><code>options</code></em> parameter can contain option settings,
as described below.
</p></div><div class="sect3" id="id-1.11.7.37.9.15"><div class="titlepage"><div><div><h4 class="title">F.28.3.5. <code class="function">pgp_key_id()</code></h4></div></div></div><a id="id-1.11.7.37.9.15.2" class="indexterm"></a><pre class="synopsis">
pgp_key_id(bytea) returns text
</pre><p>
<code class="function">pgp_key_id</code> extracts the key ID of a PGP public or secret key.
Or it gives the key ID that was used for encrypting the data, if given
an encrypted message.
</p><p>
It can return 2 special key IDs:
</p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
<code class="literal">SYMKEY</code>
</p><p>
The message is encrypted with a symmetric key.
</p></li><li class="listitem"><p>
<code class="literal">ANYKEY</code>
</p><p>
The message is public-key encrypted, but the key ID has been removed.
That means you will need to try all your secret keys on it to see
which one decrypts it. <code class="filename">pgcrypto</code> itself does not produce
such messages.
</p></li></ul></div><p>
Note that different keys may have the same ID. This is rare but a normal
event. The client application should then try to decrypt with each one,
to see which fits — like handling <code class="literal">ANYKEY</code>.
</p></div><div class="sect3" id="id-1.11.7.37.9.16"><div class="titlepage"><div><div><h4 class="title">F.28.3.6. <code class="function">armor()</code>, <code class="function">dearmor()</code></h4></div></div></div><a id="id-1.11.7.37.9.16.2" class="indexterm"></a><a id="id-1.11.7.37.9.16.3" class="indexterm"></a><pre class="synopsis">
armor(data bytea [ , keys text[], values text[] ]) returns text
dearmor(data text) returns bytea
</pre><p>
These functions wrap/unwrap binary data into PGP ASCII-armor format,
which is basically Base64 with CRC and additional formatting.
</p><p>
If the <em class="parameter"><code>keys</code></em> and <em class="parameter"><code>values</code></em> arrays are specified,
an <em class="firstterm">armor header</em> is added to the armored format for each
key/value pair. Both arrays must be single-dimensional, and they must
be of the same length. The keys and values cannot contain any non-ASCII
characters.
</p></div><div class="sect3" id="id-1.11.7.37.9.17"><div class="titlepage"><div><div><h4 class="title">F.28.3.7. <code class="function">pgp_armor_headers</code></h4></div></div></div><a id="id-1.11.7.37.9.17.2" class="indexterm"></a><pre class="synopsis">
pgp_armor_headers(data text, key out text, value out text) returns setof record
</pre><p>
<code class="function">pgp_armor_headers()</code> extracts the armor headers from
<em class="parameter"><code>data</code></em>. The return value is a set of rows with two columns,
key and value. If the keys or values contain any non-ASCII characters,
they are treated as UTF-8.
</p></div><div class="sect3" id="id-1.11.7.37.9.18"><div class="titlepage"><div><div><h4 class="title">F.28.3.8. Options for PGP Functions</h4></div></div></div><p>
Options are named to be similar to GnuPG. An option's value should be
given after an equal sign; separate options from each other with commas.
For example:
</p><pre class="programlisting">
pgp_sym_encrypt(data, psw, 'compress-algo=1, cipher-algo=aes256')
</pre><p>
</p><p>
All of the options except <code class="literal">convert-crlf</code> apply only to
encrypt functions. Decrypt functions get the parameters from the PGP
data.
</p><p>
The most interesting options are probably
<code class="literal">compress-algo</code> and <code class="literal">unicode-mode</code>.
The rest should have reasonable defaults.
</p><div class="sect4" id="id-1.11.7.37.9.18.5"><div class="titlepage"><div><div><h5 class="title">F.28.3.8.1. cipher-algo</h5></div></div></div><p>
Which cipher algorithm to use.
</p><div class="literallayout"><p><br />
Values: bf, aes128, aes192, aes256, 3des, cast5<br />
Default: aes128<br />
Applies to: pgp_sym_encrypt, pgp_pub_encrypt<br />
</p></div></div><div class="sect4" id="id-1.11.7.37.9.18.6"><div class="titlepage"><div><div><h5 class="title">F.28.3.8.2. compress-algo</h5></div></div></div><p>
Which compression algorithm to use. Only available if
<span class="productname">PostgreSQL</span> was built with zlib.
</p><div class="literallayout"><p><br />
Values:<br />
0 - no compression<br />
1 - ZIP compression<br />
2 - ZLIB compression (= ZIP plus meta-data and block CRCs)<br />
Default: 0<br />
Applies to: pgp_sym_encrypt, pgp_pub_encrypt<br />
</p></div></div><div class="sect4" id="id-1.11.7.37.9.18.7"><div class="titlepage"><div><div><h5 class="title">F.28.3.8.3. compress-level</h5></div></div></div><p>
How much to compress. Higher levels compress smaller but are slower.
0 disables compression.
</p><div class="literallayout"><p><br />
Values: 0, 1-9<br />
Default: 6<br />
Applies to: pgp_sym_encrypt, pgp_pub_encrypt<br />
</p></div></div><div class="sect4" id="id-1.11.7.37.9.18.8"><div class="titlepage"><div><div><h5 class="title">F.28.3.8.4. convert-crlf</h5></div></div></div><p>
Whether to convert <code class="literal">\n</code> into <code class="literal">\r\n</code> when
encrypting and <code class="literal">\r\n</code> to <code class="literal">\n</code> when
decrypting. <acronym class="acronym">RFC</acronym> 4880 specifies that text data should be stored using
<code class="literal">\r\n</code> line-feeds. Use this to get fully RFC-compliant
behavior.
</p><div class="literallayout"><p><br />
Values: 0, 1<br />
Default: 0<br />
Applies to: pgp_sym_encrypt, pgp_pub_encrypt, pgp_sym_decrypt, pgp_pub_decrypt<br />
</p></div></div><div class="sect4" id="id-1.11.7.37.9.18.9"><div class="titlepage"><div><div><h5 class="title">F.28.3.8.5. disable-mdc</h5></div></div></div><p>
Do not protect data with SHA-1. The only good reason to use this
option is to achieve compatibility with ancient PGP products, predating
the addition of SHA-1 protected packets to <acronym class="acronym">RFC</acronym> 4880.
Recent gnupg.org and pgp.com software supports it fine.
</p><div class="literallayout"><p><br />
Values: 0, 1<br />
Default: 0<br />
Applies to: pgp_sym_encrypt, pgp_pub_encrypt<br />
</p></div></div><div class="sect4" id="id-1.11.7.37.9.18.10"><div class="titlepage"><div><div><h5 class="title">F.28.3.8.6. sess-key</h5></div></div></div><p>
Use separate session key. Public-key encryption always uses a separate
session key; this option is for symmetric-key encryption, which by default
uses the S2K key directly.
</p><div class="literallayout"><p><br />
Values: 0, 1<br />
Default: 0<br />
Applies to: pgp_sym_encrypt<br />
</p></div></div><div class="sect4" id="id-1.11.7.37.9.18.11"><div class="titlepage"><div><div><h5 class="title">F.28.3.8.7. s2k-mode</h5></div></div></div><p>
Which S2K algorithm to use.
</p><div class="literallayout"><p><br />
Values:<br />
0 - Without salt. Dangerous!<br />
1 - With salt but with fixed iteration count.<br />
3 - Variable iteration count.<br />
Default: 3<br />
Applies to: pgp_sym_encrypt<br />
</p></div></div><div class="sect4" id="id-1.11.7.37.9.18.12"><div class="titlepage"><div><div><h5 class="title">F.28.3.8.8. s2k-count</h5></div></div></div><p>
The number of iterations of the S2K algorithm to use. It must
be a value between 1024 and 65011712, inclusive.
</p><div class="literallayout"><p><br />
Default: A random value between 65536 and 253952<br />
Applies to: pgp_sym_encrypt, only with s2k-mode=3<br />
</p></div></div><div class="sect4" id="id-1.11.7.37.9.18.13"><div class="titlepage"><div><div><h5 class="title">F.28.3.8.9. s2k-digest-algo</h5></div></div></div><p>
Which digest algorithm to use in S2K calculation.
</p><div class="literallayout"><p><br />
Values: md5, sha1<br />
Default: sha1<br />
Applies to: pgp_sym_encrypt<br />
</p></div></div><div class="sect4" id="id-1.11.7.37.9.18.14"><div class="titlepage"><div><div><h5 class="title">F.28.3.8.10. s2k-cipher-algo</h5></div></div></div><p>
Which cipher to use for encrypting separate session key.
</p><div class="literallayout"><p><br />
Values: bf, aes, aes128, aes192, aes256<br />
Default: use cipher-algo<br />
Applies to: pgp_sym_encrypt<br />
</p></div></div><div class="sect4" id="id-1.11.7.37.9.18.15"><div class="titlepage"><div><div><h5 class="title">F.28.3.8.11. unicode-mode</h5></div></div></div><p>
Whether to convert textual data from database internal encoding to
UTF-8 and back. If your database already is UTF-8, no conversion will
be done, but the message will be tagged as UTF-8. Without this option
it will not be.
</p><div class="literallayout"><p><br />
Values: 0, 1<br />
Default: 0<br />
Applies to: pgp_sym_encrypt, pgp_pub_encrypt<br />
</p></div></div></div><div class="sect3" id="id-1.11.7.37.9.19"><div class="titlepage"><div><div><h4 class="title">F.28.3.9. Generating PGP Keys with GnuPG</h4></div></div></div><p>
To generate a new key:
</p><pre class="programlisting">
gpg --gen-key
</pre><p>
</p><p>
The preferred key type is <span class="quote">“<span class="quote">DSA and Elgamal</span>”</span>.
</p><p>
For RSA encryption you must create either DSA or RSA sign-only key
as master and then add an RSA encryption subkey with
<code class="literal">gpg --edit-key</code>.
</p><p>
To list keys:
</p><pre class="programlisting">
gpg --list-secret-keys
</pre><p>
</p><p>
To export a public key in ASCII-armor format:
</p><pre class="programlisting">
gpg -a --export KEYID > public.key
</pre><p>
</p><p>
To export a secret key in ASCII-armor format:
</p><pre class="programlisting">
gpg -a --export-secret-keys KEYID > secret.key
</pre><p>
</p><p>
You need to use <code class="function">dearmor()</code> on these keys before giving them to
the PGP functions. Or if you can handle binary data, you can drop
<code class="literal">-a</code> from the command.
</p><p>
For more details see <code class="literal">man gpg</code>,
<a class="ulink" href="https://www.gnupg.org/gph/en/manual.html" target="_top">The GNU
Privacy Handbook</a> and other documentation on
<a class="ulink" href="https://www.gnupg.org/" target="_top">https://www.gnupg.org/</a>.
</p></div><div class="sect3" id="id-1.11.7.37.9.20"><div class="titlepage"><div><div><h4 class="title">F.28.3.10. Limitations of PGP Code</h4></div></div></div><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
No support for signing. That also means that it is not checked
whether the encryption subkey belongs to the master key.
</p></li><li class="listitem"><p>
No support for encryption key as master key. As such practice
is generally discouraged, this should not be a problem.
</p></li><li class="listitem"><p>
No support for several subkeys. This may seem like a problem, as this
is common practice. On the other hand, you should not use your regular
GPG/PGP keys with <code class="filename">pgcrypto</code>, but create new ones,
as the usage scenario is rather different.
</p></li></ul></div></div></div><div class="sect2" id="id-1.11.7.37.10"><div class="titlepage"><div><div><h3 class="title">F.28.4. Raw Encryption Functions</h3></div></div></div><p>
These functions only run a cipher over data; they don't have any advanced
features of PGP encryption. Therefore they have some major problems:
</p><div class="orderedlist"><ol class="orderedlist" type="1"><li class="listitem"><p>
They use user key directly as cipher key.
</p></li><li class="listitem"><p>
They don't provide any integrity checking, to see
if the encrypted data was modified.
</p></li><li class="listitem"><p>
They expect that users manage all encryption parameters
themselves, even IV.
</p></li><li class="listitem"><p>
They don't handle text.
</p></li></ol></div><p>
So, with the introduction of PGP encryption, usage of raw
encryption functions is discouraged.
</p><a id="id-1.11.7.37.10.5" class="indexterm"></a><a id="id-1.11.7.37.10.6" class="indexterm"></a><a id="id-1.11.7.37.10.7" class="indexterm"></a><a id="id-1.11.7.37.10.8" class="indexterm"></a><pre class="synopsis">
encrypt(data bytea, key bytea, type text) returns bytea
decrypt(data bytea, key bytea, type text) returns bytea
encrypt_iv(data bytea, key bytea, iv bytea, type text) returns bytea
decrypt_iv(data bytea, key bytea, iv bytea, type text) returns bytea
</pre><p>
Encrypt/decrypt data using the cipher method specified by
<em class="parameter"><code>type</code></em>. The syntax of the
<em class="parameter"><code>type</code></em> string is:
</p><pre class="synopsis">
<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>]
</pre><p>
where <em class="replaceable"><code>algorithm</code></em> is one of:
</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>
and <em class="replaceable"><code>mode</code></em> is one of:
</p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
<code class="literal">cbc</code> — next block depends on previous (default)
</p></li><li class="listitem"><p>
<code class="literal">ecb</code> — each block is encrypted separately (for
testing only)
</p></li></ul></div><p>
and <em class="replaceable"><code>padding</code></em> is one of:
</p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>
<code class="literal">pkcs</code> — data may be any length (default)
</p></li><li class="listitem"><p>
<code class="literal">none</code> — data must be multiple of cipher block size
</p></li></ul></div><p>
</p><p>
So, for example, these are equivalent:
</p><pre class="programlisting">
encrypt(data, 'fooz', 'bf')
encrypt(data, 'fooz', 'bf-cbc/pad:pkcs')
</pre><p>
</p><p>
In <code class="function">encrypt_iv</code> and <code class="function">decrypt_iv</code>, the
<em class="parameter"><code>iv</code></em> parameter is the initial value for the CBC mode;
it is ignored for ECB.
It is clipped or padded with zeroes if not exactly block size.
It defaults to all zeroes in the functions without this parameter.
</p></div><div class="sect2" id="id-1.11.7.37.11"><div class="titlepage"><div><div><h3 class="title">F.28.5. Random-Data Functions</h3></div></div></div><a id="id-1.11.7.37.11.2" class="indexterm"></a><pre class="synopsis">
gen_random_bytes(count integer) returns bytea
</pre><p>
Returns <em class="parameter"><code>count</code></em> cryptographically strong random bytes.
At most 1024 bytes can be extracted at a time. This is to avoid
draining the randomness generator pool.
</p><a id="id-1.11.7.37.11.5" class="indexterm"></a><pre class="synopsis">
gen_random_uuid() returns uuid
</pre><p>
Returns a version 4 (random) UUID. (Obsolete, this function
internally calls the <a class="link" href="functions-uuid.html" title="9.14. UUID Functions">core
function</a> of the same name.)
</p></div><div class="sect2" id="id-1.11.7.37.12"><div class="titlepage"><div><div><h3 class="title">F.28.6. Notes</h3></div></div></div><div class="sect3" id="id-1.11.7.37.12.2"><div class="titlepage"><div><div><h4 class="title">F.28.6.1. Configuration</h4></div></div></div><p>
<code class="filename">pgcrypto</code> configures itself according to the findings of the
main PostgreSQL <code class="literal">configure</code> script. The options that
affect it are <code class="literal">--with-zlib</code> and
<code class="literal">--with-ssl=openssl</code>.
</p><p>
When compiled with zlib, PGP encryption functions are able to
compress data before encrypting.
</p><p>
<code class="filename">pgcrypto</code> requires <span class="productname">OpenSSL</span>.
Otherwise, it will not be built or installed.
</p><p>
When compiled against <span class="productname">OpenSSL</span> 3.0.0 and later
versions, the legacy provider must be activated in the
<code class="filename">openssl.cnf</code> configuration file in order to use older
ciphers like DES or Blowfish.
</p></div><div class="sect3" id="id-1.11.7.37.12.3"><div class="titlepage"><div><div><h4 class="title">F.28.6.2. NULL Handling</h4></div></div></div><p>
As is standard in SQL, all functions return NULL, if any of the arguments
are NULL. This may create security risks on careless usage.
</p></div><div class="sect3" id="id-1.11.7.37.12.4"><div class="titlepage"><div><div><h4 class="title">F.28.6.3. Security Limitations</h4></div></div></div><p>
All <code class="filename">pgcrypto</code> functions run inside the database server.
That means that all
the data and passwords move between <code class="filename">pgcrypto</code> and client
applications in clear text. Thus you must:
</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>
If you cannot, then better do crypto inside client application.
</p><p>
The implementation does not resist
<a class="ulink" href="https://en.wikipedia.org/wiki/Side-channel_attack" target="_top">side-channel
attacks</a>. For example, the time required for
a <code class="filename">pgcrypto</code> decryption function to complete varies among
ciphertexts of a given size.
</p></div><div class="sect3" id="id-1.11.7.37.12.5"><div class="titlepage"><div><div><h4 class="title">F.28.6.4. Useful Reading</h4></div></div></div><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p><a class="ulink" href="https://www.gnupg.org/gph/en/manual.html" target="_top">https://www.gnupg.org/gph/en/manual.html</a></p><p>The GNU Privacy Handbook.</p></li><li class="listitem"><p><a class="ulink" href="https://www.openwall.com/crypt/" target="_top">https://www.openwall.com/crypt/</a></p><p>Describes the crypt-blowfish algorithm.</p></li><li class="listitem"><p>
<a class="ulink" href="https://www.iusmentis.com/security/passphrasefaq/" target="_top">https://www.iusmentis.com/security/passphrasefaq/</a>
</p><p>How to choose a good password.</p></li><li class="listitem"><p><a class="ulink" href="http://world.std.com/~reinhold/diceware.html" target="_top">http://world.std.com/~reinhold/diceware.html</a></p><p>Interesting idea for picking passwords.</p></li><li class="listitem"><p>
<a class="ulink" href="http://www.interhack.net/people/cmcurtin/snake-oil-faq.html" target="_top">http://www.interhack.net/people/cmcurtin/snake-oil-faq.html</a>
</p><p>Describes good and bad cryptography.</p></li></ul></div></div><div class="sect3" id="id-1.11.7.37.12.6"><div class="titlepage"><div><div><h4 class="title">F.28.6.5. Technical References</h4></div></div></div><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p><a class="ulink" href="https://tools.ietf.org/html/rfc4880" target="_top">https://tools.ietf.org/html/rfc4880</a></p><p>OpenPGP message format.</p></li><li class="listitem"><p><a class="ulink" href="https://tools.ietf.org/html/rfc1321" target="_top">https://tools.ietf.org/html/rfc1321</a></p><p>The MD5 Message-Digest Algorithm.</p></li><li class="listitem"><p><a class="ulink" href="https://tools.ietf.org/html/rfc2104" target="_top">https://tools.ietf.org/html/rfc2104</a></p><p>HMAC: Keyed-Hashing for Message Authentication.</p></li><li class="listitem"><p>
<a class="ulink" href="https://www.usenix.org/legacy/events/usenix99/provos.html" target="_top">https://www.usenix.org/legacy/events/usenix99/provos.html</a>
</p><p>Comparison of crypt-des, crypt-md5 and bcrypt algorithms.</p></li><li class="listitem"><p>
<a class="ulink" href="https://en.wikipedia.org/wiki/Fortuna_(PRNG)" target="_top">https://en.wikipedia.org/wiki/Fortuna_(PRNG)</a>
</p><p>Description of Fortuna CSPRNG.</p></li><li class="listitem"><p><a class="ulink" href="https://jlcooke.ca/random/" target="_top">https://jlcooke.ca/random/</a></p><p>Jean-Luc Cooke Fortuna-based <code class="filename">/dev/random</code> driver for Linux.</p></li></ul></div></div></div><div class="sect2" id="id-1.11.7.37.13"><div class="titlepage"><div><div><h3 class="title">F.28.7. Author</h3></div></div></div><p>
Marko Kreen <code class="email"><<a class="email" href="mailto:markokr@gmail.com">markokr@gmail.com</a>></code>
</p><p>
<code class="filename">pgcrypto</code> uses code from the following sources:
</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">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="contrib.html" title="Appendix F. Additional Supplied Modules">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="pgfreespacemap.html" title="F.29. pg_freespacemap">Next</a></td></tr><tr><td width="40%" align="left" valign="top">F.27. pg_buffercache </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 15.4 Documentation">Home</a></td><td width="40%" align="right" valign="top"> F.29. pg_freespacemap</td></tr></table></div></body></html>
|
