SHA-2 functions

Started by Peter Eisentrautalmost 8 years ago12 messages

peter.eisentraut@2ndquadrant.com

almost 8 years ago

2 attachment(s)

There was a complaint recently about the documentation using the widely
frowned-upon md5() function in an unrelated context as an example hash
function. This is quite common in many examples, such as hashing row
values to compare them, or hashing datums if they don't fit into an
index. But there is nothing we can easily replace md5 with, without
going to things like pgcrypto.

I also noticed while working on some SSL code that we have perfectly
good SHA-2 functionality in the server already, but it has no test
coverage outside the SCRAM tests.

So I suggest these patches that expose the new functions sha224(),
sha256(), sha384(), sha512(). That allows us to make the SSL and SCRAM
tests more robust, and it will allow them to be used in general purpose
contexts over md5().

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Attachments:

0001-Add-user-callable-SHA-2-functions.patchtext/plain; charset=UTF-8; name=0001-Add-user-callable-SHA-2-functions.patch; x-mac-creator=0; x-mac-type=0Download

From 52edfab8f1175c69ba791139fde26feb9279364a Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <peter_e@gmx.net>
Date: Tue, 6 Feb 2018 21:46:46 -0500
Subject: [PATCH 1/2] Add user-callable SHA-2 functions

Add the user-callable functions sha224, sha256, sha384, sha512.  We
already had these in the C code to support SCRAM, but there was no test
coverage outside of the SCRAM tests.  Adding these as user-callable
functions allows writing some tests.  Also, we have a user-callable md5
function but no more modern alternative, which led to wide use of md5 as
a general-purpose hash function, which leads to occasional complaints
about using md5.

Also mark the existing md5 functions as leak-proof.
---
 doc/src/sgml/func.sgml                   |  70 ++++++++++++++++++++-
 src/backend/utils/adt/varlena.c          | 101 +++++++++++++++++++++++++++++++
 src/include/catalog/pg_proc.h            |  12 +++-
 src/test/regress/expected/opr_sanity.out |   6 ++
 src/test/regress/expected/strings.out    |  53 ++++++++++++++++
 src/test/regress/sql/strings.sql         |  18 ++++++
 6 files changed, 257 insertions(+), 3 deletions(-)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 1e535cf215..bab828d507 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -3640,7 +3640,7 @@ <title>Other Binary String Functions</title>
        returning the result in hexadecimal
       </entry>
       <entry><literal>md5(E'Th\\000omas'::bytea)</literal></entry>
-      <entry><literal>8ab2d3c9689aaf18 b4958c334c82d8b1</literal></entry>
+      <entry><literal>8ab2d3c9689aaf18&#x200B;b4958c334c82d8b1</literal></entry>
      </row>
 
       <row>
@@ -3674,6 +3674,66 @@ <title>Other Binary String Functions</title>
        <entry><literal>set_byte(E'Th\\000omas'::bytea, 4, 64)</literal></entry>
        <entry><literal>Th\000o@as</literal></entry>
       </row>
+
+      <row>
+       <entry>
+        <indexterm>
+         <primary>sha224</primary>
+        </indexterm>
+        <literal><function>sha224(<type>bytea</type>)</function></literal>
+       </entry>
+       <entry><type>bytea</type></entry>
+       <entry>
+        SHA-224 hash
+       </entry>
+       <entry><literal>sha224('abc')</literal></entry>
+       <entry><literal>\x23097d223405d8228642a477bda2&#x200B;55b32aadbce4bda0b3f7e36c9da7</literal></entry>
+      </row>
+
+      <row>
+       <entry>
+        <indexterm>
+         <primary>sha256</primary>
+        </indexterm>
+        <literal><function>sha256(<type>bytea</type>)</function></literal>
+       </entry>
+       <entry><type>bytea</type></entry>
+       <entry>
+        SHA-256 hash
+       </entry>
+       <entry><literal>sha256('abc')</literal></entry>
+       <entry><literal>\xba7816bf8f01cfea414140de5dae2223&#x200B;b00361a396177a9cb410ff61f20015ad</literal></entry>
+      </row>
+
+      <row>
+       <entry>
+        <indexterm>
+         <primary>sha384</primary>
+        </indexterm>
+        <literal><function>sha384(<type>bytea</type>)</function></literal>
+       </entry>
+       <entry><type>bytea</type></entry>
+       <entry>
+        SHA-384 hash
+       </entry>
+       <entry><literal>sha384('abc')</literal></entry>
+       <entry><literal>\xcb00753f45a35e8bb5a03d699ac65007&#x200B;272c32ab0eded1631a8b605a43ff5bed&#x200B;8086072ba1e7cc2358baeca134c825a7</literal></entry>
+      </row>
+
+      <row>
+       <entry>
+        <indexterm>
+         <primary>sha512</primary>
+        </indexterm>
+        <literal><function>sha512(<type>bytea</type>)</function></literal>
+       </entry>
+       <entry><type>bytea</type></entry>
+       <entry>
+        SHA-512 hash
+       </entry>
+       <entry><literal>sha512('abc')</literal></entry>
+       <entry><literal>\xddaf35a193617abacc417349ae204131&#x200B;12e6fa4e89a97ea20a9eeee64b55d39a&#x200B;2192992a274fc1a836ba3c23a3feebbd&#x200B;454d4423643ce80e2a9ac94fa54ca49f</literal></entry>
+      </row>
     </tbody>
    </tgroup>
   </table>
@@ -3686,6 +3746,14 @@ <title>Other Binary String Functions</title>
    the first byte, and bit 15 is the most significant bit of the second byte.
   </para>
 
+  <para>
+   Note that for historic reasons, the function <function>md5</function>
+   returns a hex-encoded value of type <type>text</type> whereas the SHA-2
+   functions return type <type>bytea</type>.  Use the functions
+   <function>encode</function> and <function>decode</function> to convert
+   between the two.
+  </para>
+
   <para>
    See also the aggregate function <function>string_agg</function> in
    <xref linkend="functions-aggregate"/> and the large object functions
diff --git a/src/backend/utils/adt/varlena.c b/src/backend/utils/adt/varlena.c
index 304cb26691..d1266f26f5 100644
--- a/src/backend/utils/adt/varlena.c
+++ b/src/backend/utils/adt/varlena.c
@@ -23,6 +23,7 @@
 #include "catalog/pg_type.h"
 #include "common/int.h"
 #include "common/md5.h"
+#include "common/sha2.h"
 #include "lib/hyperloglog.h"
 #include "libpq/pqformat.h"
 #include "miscadmin.h"
@@ -4611,6 +4612,106 @@ md5_bytea(PG_FUNCTION_ARGS)
 	PG_RETURN_TEXT_P(cstring_to_text(hexsum));
 }
 
+/*
+ * SHA-2 variants
+ */
+
+Datum
+sha224_bytea(PG_FUNCTION_ARGS)
+{
+	bytea	   *in = PG_GETARG_BYTEA_PP(0);
+	const uint8 *data;
+	size_t		len;
+	pg_sha224_ctx ctx;
+	unsigned char buf[PG_SHA224_DIGEST_LENGTH];
+	bytea	   *result;
+
+	len = VARSIZE_ANY_EXHDR(in);
+	data = (unsigned char *) VARDATA_ANY(in);
+
+	pg_sha224_init(&ctx);
+	pg_sha224_update(&ctx, data, len);
+	pg_sha224_final(&ctx, buf);
+
+	result = palloc(sizeof(buf) + VARHDRSZ);
+	SET_VARSIZE(result, sizeof(buf) + VARHDRSZ);
+	memcpy(VARDATA(result), buf, sizeof(buf));
+
+	PG_RETURN_BYTEA_P(result);
+}
+
+Datum
+sha256_bytea(PG_FUNCTION_ARGS)
+{
+	bytea	   *in = PG_GETARG_BYTEA_PP(0);
+	const uint8 *data;
+	size_t		len;
+	pg_sha256_ctx ctx;
+	unsigned char buf[PG_SHA256_DIGEST_LENGTH];
+	bytea	   *result;
+
+	len = VARSIZE_ANY_EXHDR(in);
+	data = (unsigned char *) VARDATA_ANY(in);
+
+	pg_sha256_init(&ctx);
+	pg_sha256_update(&ctx, data, len);
+	pg_sha256_final(&ctx, buf);
+
+	result = palloc(sizeof(buf) + VARHDRSZ);
+	SET_VARSIZE(result, sizeof(buf) + VARHDRSZ);
+	memcpy(VARDATA(result), buf, sizeof(buf));
+
+	PG_RETURN_BYTEA_P(result);
+}
+
+Datum
+sha384_bytea(PG_FUNCTION_ARGS)
+{
+	bytea	   *in = PG_GETARG_BYTEA_PP(0);
+	const uint8 *data;
+	size_t		len;
+	pg_sha384_ctx ctx;
+	unsigned char buf[PG_SHA384_DIGEST_LENGTH];
+	bytea	   *result;
+
+	len = VARSIZE_ANY_EXHDR(in);
+	data = (unsigned char *) VARDATA_ANY(in);
+
+	pg_sha384_init(&ctx);
+	pg_sha384_update(&ctx, data, len);
+	pg_sha384_final(&ctx, buf);
+
+	result = palloc(sizeof(buf) + VARHDRSZ);
+	SET_VARSIZE(result, sizeof(buf) + VARHDRSZ);
+	memcpy(VARDATA(result), buf, sizeof(buf));
+
+	PG_RETURN_BYTEA_P(result);
+}
+
+Datum
+sha512_bytea(PG_FUNCTION_ARGS)
+{
+	bytea	   *in = PG_GETARG_BYTEA_PP(0);
+	const uint8 *data;
+	size_t		len;
+	pg_sha512_ctx ctx;
+	unsigned char buf[PG_SHA512_DIGEST_LENGTH];
+	bytea	   *result;
+
+	len = VARSIZE_ANY_EXHDR(in);
+	data = (unsigned char *) VARDATA_ANY(in);
+
+	pg_sha512_init(&ctx);
+	pg_sha512_update(&ctx, data, len);
+	pg_sha512_final(&ctx, buf);
+
+	result = palloc(sizeof(buf) + VARHDRSZ);
+	SET_VARSIZE(result, sizeof(buf) + VARHDRSZ);
+	memcpy(VARDATA(result), buf, sizeof(buf));
+
+	PG_RETURN_BYTEA_P(result);
+}
+
 /*
  * Return the size of a datum, possibly compressed
  *
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index 2a5321315a..62e16514cc 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -3917,10 +3917,18 @@ DATA(insert OID = 3314 (  system			PGNSP PGUID 12 1 0 0 0 f f f f t f v s 1 0 33
 DESCR("SYSTEM tablesample method handler");
 
 /* cryptographic */
-DATA(insert OID =  2311 (  md5	   PGNSP PGUID 12 1 0 0 0 f f f f t f i s 1 0 25 "25" _null_ _null_ _null_ _null_ _null_ md5_text _null_ _null_ _null_ ));
+DATA(insert OID =  2311 (  md5	   PGNSP PGUID 12 1 0 0 0 f f f t t f i s 1 0 25 "25" _null_ _null_ _null_ _null_ _null_ md5_text _null_ _null_ _null_ ));
 DESCR("MD5 hash");
-DATA(insert OID =  2321 (  md5	   PGNSP PGUID 12 1 0 0 0 f f f f t f i s 1 0 25 "17" _null_ _null_ _null_ _null_ _null_ md5_bytea _null_ _null_ _null_ ));
+DATA(insert OID =  2321 (  md5	   PGNSP PGUID 12 1 0 0 0 f f f t t f i s 1 0 25 "17" _null_ _null_ _null_ _null_ _null_ md5_bytea _null_ _null_ _null_ ));
 DESCR("MD5 hash");
+DATA(insert OID =  3419 (  sha224  PGNSP PGUID 12 1 0 0 0 f f f t t f i s 1 0 17 "17" _null_ _null_ _null_ _null_ _null_ sha224_bytea _null_ _null_ _null_ ));
+DESCR("SHA-224 hash");
+DATA(insert OID =  3420 (  sha256  PGNSP PGUID 12 1 0 0 0 f f f t t f i s 1 0 17 "17" _null_ _null_ _null_ _null_ _null_ sha256_bytea _null_ _null_ _null_ ));
+DESCR("SHA-256 hash");
+DATA(insert OID =  3421 (  sha384  PGNSP PGUID 12 1 0 0 0 f f f t t f i s 1 0 17 "17" _null_ _null_ _null_ _null_ _null_ sha384_bytea _null_ _null_ _null_ ));
+DESCR("SHA-384 hash");
+DATA(insert OID =  3422 (  sha512  PGNSP PGUID 12 1 0 0 0 f f f t t f i s 1 0 17 "17" _null_ _null_ _null_ _null_ _null_ sha512_bytea _null_ _null_ _null_ ));
+DESCR("SHA-512 hash");
 
 /* crosstype operations for date vs. timestamp and timestamptz */
 DATA(insert OID = 2338 (  date_lt_timestamp		   PGNSP PGUID 12 1 0 0 0 f f f f t f i s 2 0 16 "1082 1114" _null_ _null_ _null_ _null_ _null_ date_lt_timestamp _null_ _null_ _null_ ));
diff --git a/src/test/regress/expected/opr_sanity.out b/src/test/regress/expected/opr_sanity.out
index 684f7f20a8..6616cc1bf0 100644
--- a/src/test/regress/expected/opr_sanity.out
+++ b/src/test/regress/expected/opr_sanity.out
@@ -699,6 +699,8 @@ timestamp_lt(timestamp without time zone,timestamp without time zone)
 timestamp_le(timestamp without time zone,timestamp without time zone)
 timestamp_ge(timestamp without time zone,timestamp without time zone)
 timestamp_gt(timestamp without time zone,timestamp without time zone)
+md5(text)
+md5(bytea)
 tidgt(tid,tid)
 tidlt(tid,tid)
 tidge(tid,tid)
@@ -711,6 +713,10 @@ uuid_gt(uuid,uuid)
 uuid_ne(uuid,uuid)
 xidneq(xid,xid)
 xidneqint4(xid,integer)
+sha224(bytea)
+sha256(bytea)
+sha384(bytea)
+sha512(bytea)
 macaddr8_eq(macaddr8,macaddr8)
 macaddr8_lt(macaddr8,macaddr8)
 macaddr8_le(macaddr8,macaddr8)
diff --git a/src/test/regress/expected/strings.out b/src/test/regress/expected/strings.out
index 8073eb4fad..cbe66c375c 100644
--- a/src/test/regress/expected/strings.out
+++ b/src/test/regress/expected/strings.out
@@ -1439,6 +1439,58 @@ select md5('12345678901234567890123456789012345678901234567890123456789012345678
  t
 (1 row)
 
+--
+-- SHA-2
+--
+SET bytea_output TO hex;
+SELECT sha224('');
+                           sha224                           
+------------------------------------------------------------
+ \xd14a028c2a3a2bc9476102bb288234c415a2b01f828ea62ac5b3e42f
+(1 row)
+
+SELECT sha224('The quick brown fox jumps over the lazy dog.');
+                           sha224                           
+------------------------------------------------------------
+ \x619cba8e8e05826e9b8c519c0a5c68f4fb653e8a3d8aa04bb2c8cd4c
+(1 row)
+
+SELECT sha256('');
+                               sha256                               
+--------------------------------------------------------------------
+ \xe3b0c44298fc1c149afbf4c8996fb92427ae41e4649b934ca495991b7852b855
+(1 row)
+
+SELECT sha256('The quick brown fox jumps over the lazy dog.');
+                               sha256                               
+--------------------------------------------------------------------
+ \xef537f25c895bfa782526529a9b63d97aa631564d5d789c2b765448c8635fb6c
+(1 row)
+
+SELECT sha384('');
+                                               sha384                                               
+----------------------------------------------------------------------------------------------------
+ \x38b060a751ac96384cd9327eb1b1e36a21fdb71114be07434c0cc7bf63f6e1da274edebfe76f65fbd51ad2f14898b95b
+(1 row)
+
+SELECT sha384('The quick brown fox jumps over the lazy dog.');
+                                               sha384                                               
+----------------------------------------------------------------------------------------------------
+ \xed892481d8272ca6df370bf706e4d7bc1b5739fa2177aae6c50e946678718fc67a7af2819a021c2fc34e91bdb63409d7
+(1 row)
+
+SELECT sha512('');
+                                                               sha512                                                               
+------------------------------------------------------------------------------------------------------------------------------------
+ \xcf83e1357eefb8bdf1542850d66d8007d620e4050b5715dc83f4a921d36ce9ce47d0d13c5d85f2b0ff8318d2877eec2f63b931bd47417a81a538327af927da3e
+(1 row)
+
+SELECT sha512('The quick brown fox jumps over the lazy dog.');
+                                                               sha512                                                               
+------------------------------------------------------------------------------------------------------------------------------------
+ \x91ea1245f20d46ae9a037a989f54f1f790f0a47607eeb8a14d12890cea77a1bbc6c7ed9cf205e67b7f2b8fd4c7dfd3a7a8617e45f3c463d481c7e586c39ac1ed
+(1 row)
+
 --
 -- test behavior of escape_string_warning and standard_conforming_strings options
 --
@@ -1525,6 +1577,7 @@ select 'a\\bcd' as f1, 'a\\b\'cd' as f2, 'a\\b\'''cd' as f3, 'abcd\\'   as f4, '
 --
 -- Additional string functions
 --
+SET bytea_output TO escape;
 SELECT initcap('hi THOMAS');
   initcap  
 -----------
diff --git a/src/test/regress/sql/strings.sql b/src/test/regress/sql/strings.sql
index 9ed242208f..5a82237870 100644
--- a/src/test/regress/sql/strings.sql
+++ b/src/test/regress/sql/strings.sql
@@ -506,6 +506,23 @@ CREATE TABLE toasttest (c char(4096));
 
 select md5('12345678901234567890123456789012345678901234567890123456789012345678901234567890'::bytea) = '57edf4a22be3c955ac49da2e2107b67a' AS "TRUE";
 
+--
+-- SHA-2
+--
+SET bytea_output TO hex;
+
+SELECT sha224('');
+SELECT sha224('The quick brown fox jumps over the lazy dog.');
+
+SELECT sha256('');
+SELECT sha256('The quick brown fox jumps over the lazy dog.');
+
+SELECT sha384('');
+SELECT sha384('The quick brown fox jumps over the lazy dog.');
+
+SELECT sha512('');
+SELECT sha512('The quick brown fox jumps over the lazy dog.');
+
 --
 -- test behavior of escape_string_warning and standard_conforming_strings options
 --
@@ -540,6 +557,7 @@ CREATE TABLE toasttest (c char(4096));
 --
 -- Additional string functions
 --
+SET bytea_output TO escape;
 
 SELECT initcap('hi THOMAS');
 
-- 
2.16.2

0002-Update-gratuitous-use-of-MD5-in-documentation.patchtext/plain; charset=UTF-8; name=0002-Update-gratuitous-use-of-MD5-in-documentation.patch; x-mac-creator=0; x-mac-type=0Download

From a3dd53816093a4aea47c999280813c3e319771d0 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <peter_e@gmx.net>
Date: Tue, 6 Feb 2018 21:59:40 -0500
Subject: [PATCH 2/2] Update gratuitous use of MD5 in documentation

---
 doc/src/sgml/citext.sgml  | 10 +++++-----
 doc/src/sgml/sepgsql.sgml |  2 +-
 2 files changed, 6 insertions(+), 6 deletions(-)

diff --git a/doc/src/sgml/citext.sgml b/doc/src/sgml/citext.sgml
index 82251de852..b1fe7101b2 100644
--- a/doc/src/sgml/citext.sgml
+++ b/doc/src/sgml/citext.sgml
@@ -80,11 +80,11 @@ <title>How to Use It</title>
     pass TEXT   NOT NULL
 );
 
-INSERT INTO users VALUES ( 'larry',  md5(random()::text) );
-INSERT INTO users VALUES ( 'Tom',    md5(random()::text) );
-INSERT INTO users VALUES ( 'Damian', md5(random()::text) );
-INSERT INTO users VALUES ( 'NEAL',   md5(random()::text) );
-INSERT INTO users VALUES ( 'Bj&oslash;rn',  md5(random()::text) );
+INSERT INTO users VALUES ( 'larry',  sha256(random()::text::bytea) );
+INSERT INTO users VALUES ( 'Tom',    sha256(random()::text::bytea) );
+INSERT INTO users VALUES ( 'Damian', sha256(random()::text::bytea) );
+INSERT INTO users VALUES ( 'NEAL',   sha256(random()::text::bytea) );
+INSERT INTO users VALUES ( 'Bj&oslash;rn',  sha256(random()::text::bytea) );
 
 SELECT * FROM users WHERE nick = 'Larry';
 </programlisting>
diff --git a/doc/src/sgml/sepgsql.sgml b/doc/src/sgml/sepgsql.sgml
index 273efc6e27..f8c99e1b00 100644
--- a/doc/src/sgml/sepgsql.sgml
+++ b/doc/src/sgml/sepgsql.sgml
@@ -370,7 +370,7 @@ <title>DML Permissions</title>
    <para>
    For example, consider:
 <synopsis>
-UPDATE t1 SET x = 2, y = md5sum(y) WHERE z = 100;
+UPDATE t1 SET x = 2, y = func1(y) WHERE z = 100;
 </synopsis>
 
     Here, <literal>db_column:update</literal> will be checked for
-- 
2.16.2

Aleksander Alekseev

a.alekseev@postgrespro.ru

almost 8 years ago

In reply to: Peter Eisentraut (#1)

Re: SHA-2 functions

Hello Peter,

So I suggest these patches that expose the new functions sha224(),
sha256(), sha384(), sha512(). That allows us to make the SSL and SCRAM
tests more robust, and it will allow them to be used in general purpose
contexts over md5().

Nice patch. I wonder though whether tests should include hashing
non-ASCII characters as well.

--
Best regards,
Aleksander Alekseev

Joe Conway

mail@joeconway.com

almost 8 years ago

In reply to: Peter Eisentraut (#1)

Re: SHA-2 functions

On 02/19/2018 08:43 AM, Peter Eisentraut wrote:

I also noticed while working on some SSL code that we have perfectly
good SHA-2 functionality in the server already, but it has no test
coverage outside the SCRAM tests.

So I suggest these patches that expose the new functions sha224(),
sha256(), sha384(), sha512(). That allows us to make the SSL and SCRAM
tests more robust, and it will allow them to be used in general purpose
contexts over md5().

I didn't look closely at the patch itself, but +1 on the concept.

Joe

--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development

Peter Eisentraut

peter.eisentraut@2ndquadrant.com

almost 8 years ago

In reply to: Aleksander Alekseev (#2)

Re: SHA-2 functions

On 2/19/18 09:06, Aleksander Alekseev wrote:

So I suggest these patches that expose the new functions sha224(),
sha256(), sha384(), sha512(). That allows us to make the SSL and SCRAM
tests more robust, and it will allow them to be used in general purpose
contexts over md5().

Nice patch. I wonder though whether tests should include hashing
non-ASCII characters as well.

The input is of type bytea, so the concept of non-ASCII characters
doesn't quite apply.

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Michael Paquier

michael@paquier.xyz

almost 8 years ago

In reply to: Peter Eisentraut (#4)

Re: SHA-2 functions

On Mon, Feb 19, 2018 at 03:02:02PM -0500, Peter Eisentraut wrote:

On 2/19/18 09:06, Aleksander Alekseev wrote:

So I suggest these patches that expose the new functions sha224(),
sha256(), sha384(), sha512(). That allows us to make the SSL and SCRAM
tests more robust, and it will allow them to be used in general purpose
contexts over md5().

Nice patch. I wonder though whether tests should include hashing
non-ASCII characters as well.

The input is of type bytea, so the concept of non-ASCII characters
doesn't quite apply.

Encoding issues is a reason to use bytea output in some cases. For
logical decoding this is for example also why an interface like
pg_logical_slot_peek_binary_changes() exists... Back to the patch.
--
Michael

Michael Paquier

michael@paquier.xyz

almost 8 years ago

In reply to: Peter Eisentraut (#1)

Re: SHA-2 functions

On Mon, Feb 19, 2018 at 08:43:44AM -0500, Peter Eisentraut wrote:

I also noticed while working on some SSL code that we have perfectly
good SHA-2 functionality in the server already, but it has no test
coverage outside the SCRAM tests.

Yep, the refactoring in src/common/ has been done for SCRAM. As The
first versions of the patch were for SCRAM-SHA-1, only SHA-1 functions
were moved. With SCRAM-SHA-256, the full set of APIs for 256, 386 and
512 has been moved as they share much code.

So I suggest these patches that expose the new functions sha224(),
sha256(), sha384(), sha512(). That allows us to make the SSL and SCRAM
tests more robust, and it will allow them to be used in general purpose
contexts over md5().

Huge +1 as well. This also makes sure that the non-SSL implementation
carried in Postgres is consistent what what OpenSSL has. This would
matter as well if new SSL implementations are added in the future.

+       <entry><literal>sha224('abc')</literal></entry>
+       <entry><literal>\x23097d223405d8228642a477bda2&#x200B;55b32aadbce4bda0b3f7e36c9da7</literal></entry>
Some bytea characters from the hash are not able to show up correctly?
This does not result in spaces.

+   Note that for historic reasons, the function <function>md5</function>
+   returns a hex-encoded value of type <type>text</type> whereas the SHA-2
+   functions return type <type>bytea</type>.  Use the functions
+   <function>encode</function> and <function>decode</function> to convert
+   between the two.
Adding an example would be nice.

varlena.c is already large and messy. I would suggest to split into a
new file all the user-facing cryptographic functions, including md5 and
hex functions, say in src/backend/utils/adt/crypt.c.
--
Michael

Peter Eisentraut

peter.eisentraut@2ndquadrant.com

almost 8 years ago

In reply to: Michael Paquier (#6)

Re: SHA-2 functions

On 2/19/18 21:07, Michael Paquier wrote:

+       <entry><literal>sha224('abc')</literal></entry>
+       <entry><literal>\x23097d223405d8228642a477bda2&#x200B;55b32aadbce4bda0b3f7e36c9da7</literal></entry>
Some bytea characters from the hash are not able to show up correctly?
This does not result in spaces.

U+200B is a zero-width space, used here to hint for possible line breaks.

+   Note that for historic reasons, the function <function>md5</function>
+   returns a hex-encoded value of type <type>text</type> whereas the SHA-2
+   functions return type <type>bytea</type>.  Use the functions
+   <function>encode</function> and <function>decode</function> to convert
+   between the two.
Adding an example would be nice.

varlena.c is already large and messy. I would suggest to split into a
new file all the user-facing cryptographic functions, including md5 and
hex functions, say in src/backend/utils/adt/crypt.c.

I had originally started a new file called hash.c, but I figured that
would be quite confusing. I can use crypt.c or a similar name.
Although crypt.c sounds a lot like crypt().

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Tom Lane

tgl@sss.pgh.pa.us

almost 8 years ago

In reply to: Peter Eisentraut (#7)

Re: SHA-2 functions

Peter Eisentraut <peter.eisentraut@2ndquadrant.com> writes:

On 2/19/18 21:07, Michael Paquier wrote:

varlena.c is already large and messy. I would suggest to split into a
new file all the user-facing cryptographic functions, including md5 and
hex functions, say in src/backend/utils/adt/crypt.c.

I had originally started a new file called hash.c, but I figured that
would be quite confusing. I can use crypt.c or a similar name.
Although crypt.c sounds a lot like crypt().

cryptohashes.c or some such? I concur with Michael that dropping this
into varlena.c isn't a great plan.

regards, tom lane

Michael Paquier

michael@paquier.xyz

almost 8 years ago

In reply to: Tom Lane (#8)

Re: SHA-2 functions

On Tue, Feb 20, 2018 at 05:09:48PM -0500, Tom Lane wrote:

Peter Eisentraut <peter.eisentraut@2ndquadrant.com> writes:

On 2/19/18 21:07, Michael Paquier wrote:

varlena.c is already large and messy. I would suggest to split into a
new file all the user-facing cryptographic functions, including md5 and
hex functions, say in src/backend/utils/adt/crypt.c.

I had originally started a new file called hash.c, but I figured that
would be quite confusing. I can use crypt.c or a similar name.
Although crypt.c sounds a lot like crypt().

cryptohashes.c or some such? I concur with Michael that dropping this
into varlena.c isn't a great plan.

I think that crypto_hash.c or hash_crypt.c would be adapted as well.
crypt.c is too much generic, so including both concepts in the name is
the way to go. The name given by Tom here sounds actually nice.
--
Michael

#10

Peter Eisentraut

peter.eisentraut@2ndquadrant.com

almost 8 years ago

In reply to: Michael Paquier (#9)

2 attachment(s)

Re: SHA-2 functions

On 2/20/18 23:04, Michael Paquier wrote:

I think that crypto_hash.c or hash_crypt.c would be adapted as well.
crypt.c is too much generic, so including both concepts in the name is
the way to go. The name given by Tom here sounds actually nice.

Updated patches

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Attachments:

v2-0001-Add-user-callable-SHA-2-functions.patchtext/plain; charset=UTF-8; name=v2-0001-Add-user-callable-SHA-2-functions.patch; x-mac-creator=0; x-mac-type=0Download

From 2debe6cf7eec396e269a2c3d89ee56f5aea711e2 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <peter_e@gmx.net>
Date: Tue, 6 Feb 2018 21:46:46 -0500
Subject: [PATCH v2 1/2] Add user-callable SHA-2 functions

Add the user-callable functions sha224, sha256, sha384, sha512.  We
already had these in the C code to support SCRAM, but there was no test
coverage outside of the SCRAM tests.  Adding these as user-callable
functions allows writing some tests.  Also, we have a user-callable md5
function but no more modern alternative, which led to wide use of md5 as
a general-purpose hash function, which leads to occasional complaints
about using md5.

Also mark the existing md5 functions as leak-proof.
---
 doc/src/sgml/func.sgml                   |  71 ++++++++++++-
 src/backend/utils/adt/Makefile           |   3 +-
 src/backend/utils/adt/cryptohashes.c     | 170 +++++++++++++++++++++++++++++++
 src/backend/utils/adt/varlena.c          |  48 ---------
 src/include/catalog/pg_proc.h            |  12 ++-
 src/test/regress/expected/opr_sanity.out |   6 ++
 src/test/regress/expected/strings.out    |  53 ++++++++++
 src/test/regress/sql/strings.sql         |  18 ++++
 8 files changed, 329 insertions(+), 52 deletions(-)
 create mode 100644 src/backend/utils/adt/cryptohashes.c

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 1e535cf215..2f59af25a6 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -3640,7 +3640,7 @@ <title>Other Binary String Functions</title>
        returning the result in hexadecimal
       </entry>
       <entry><literal>md5(E'Th\\000omas'::bytea)</literal></entry>
-      <entry><literal>8ab2d3c9689aaf18 b4958c334c82d8b1</literal></entry>
+      <entry><literal>8ab2d3c9689aaf18&#x200B;b4958c334c82d8b1</literal></entry>
      </row>
 
       <row>
@@ -3674,6 +3674,66 @@ <title>Other Binary String Functions</title>
        <entry><literal>set_byte(E'Th\\000omas'::bytea, 4, 64)</literal></entry>
        <entry><literal>Th\000o@as</literal></entry>
       </row>
+
+      <row>
+       <entry>
+        <indexterm>
+         <primary>sha224</primary>
+        </indexterm>
+        <literal><function>sha224(<type>bytea</type>)</function></literal>
+       </entry>
+       <entry><type>bytea</type></entry>
+       <entry>
+        SHA-224 hash
+       </entry>
+       <entry><literal>sha224('abc')</literal></entry>
+       <entry><literal>\x23097d223405d8228642a477bda2&#x200B;55b32aadbce4bda0b3f7e36c9da7</literal></entry>
+      </row>
+
+      <row>
+       <entry>
+        <indexterm>
+         <primary>sha256</primary>
+        </indexterm>
+        <literal><function>sha256(<type>bytea</type>)</function></literal>
+       </entry>
+       <entry><type>bytea</type></entry>
+       <entry>
+        SHA-256 hash
+       </entry>
+       <entry><literal>sha256('abc')</literal></entry>
+       <entry><literal>\xba7816bf8f01cfea414140de5dae2223&#x200B;b00361a396177a9cb410ff61f20015ad</literal></entry>
+      </row>
+
+      <row>
+       <entry>
+        <indexterm>
+         <primary>sha384</primary>
+        </indexterm>
+        <literal><function>sha384(<type>bytea</type>)</function></literal>
+       </entry>
+       <entry><type>bytea</type></entry>
+       <entry>
+        SHA-384 hash
+       </entry>
+       <entry><literal>sha384('abc')</literal></entry>
+       <entry><literal>\xcb00753f45a35e8bb5a03d699ac65007&#x200B;272c32ab0eded1631a8b605a43ff5bed&#x200B;8086072ba1e7cc2358baeca134c825a7</literal></entry>
+      </row>
+
+      <row>
+       <entry>
+        <indexterm>
+         <primary>sha512</primary>
+        </indexterm>
+        <literal><function>sha512(<type>bytea</type>)</function></literal>
+       </entry>
+       <entry><type>bytea</type></entry>
+       <entry>
+        SHA-512 hash
+       </entry>
+       <entry><literal>sha512('abc')</literal></entry>
+       <entry><literal>\xddaf35a193617abacc417349ae204131&#x200B;12e6fa4e89a97ea20a9eeee64b55d39a&#x200B;2192992a274fc1a836ba3c23a3feebbd&#x200B;454d4423643ce80e2a9ac94fa54ca49f</literal></entry>
+      </row>
     </tbody>
    </tgroup>
   </table>
@@ -3686,6 +3746,15 @@ <title>Other Binary String Functions</title>
    the first byte, and bit 15 is the most significant bit of the second byte.
   </para>
 
+  <para>
+   Note that for historic reasons, the function <function>md5</function>
+   returns a hex-encoded value of type <type>text</type> whereas the SHA-2
+   functions return type <type>bytea</type>.  Use the functions
+   <function>encode</function> and <function>decode</function> to convert
+   between the two, for example <literal>encode(sha256('abc'),
+   'hex')</literal> to get a hex-encoded text representation.
+  </para>
+
   <para>
    See also the aggregate function <function>string_agg</function> in
    <xref linkend="functions-aggregate"/> and the large object functions
diff --git a/src/backend/utils/adt/Makefile b/src/backend/utils/adt/Makefile
index 61ca90312f..4b35dbb8bb 100644
--- a/src/backend/utils/adt/Makefile
+++ b/src/backend/utils/adt/Makefile
@@ -11,7 +11,8 @@ include $(top_builddir)/src/Makefile.global
 # keep this list arranged alphabetically or it gets to be a mess
 OBJS = acl.o amutils.o arrayfuncs.o array_expanded.o array_selfuncs.o \
 	array_typanalyze.o array_userfuncs.o arrayutils.o ascii.o \
-	bool.o cash.o char.o date.o datetime.o datum.o dbsize.o domains.o \
+	bool.o cash.o char.o cryptohashes.o \
+	date.o datetime.o datum.o dbsize.o domains.o \
 	encode.o enum.o expandeddatum.o expandedrecord.o \
 	float.o format_type.o formatting.o genfile.o \
 	geo_ops.o geo_selfuncs.o geo_spgist.o inet_cidr_ntop.o inet_net_pton.o \
diff --git a/src/backend/utils/adt/cryptohashes.c b/src/backend/utils/adt/cryptohashes.c
new file mode 100644
index 0000000000..1f242eba71
--- /dev/null
+++ b/src/backend/utils/adt/cryptohashes.c
@@ -0,0 +1,170 @@
+/*-------------------------------------------------------------------------
+ *
+ * cryptohashes.c
+ *	  Cryptographic hash functions
+ *
+ * Portions Copyright (c) 2018, PostgreSQL Global Development Group
+ *
+ *
+ * IDENTIFICATION
+ *	  src/backend/utils/adt/cryptohashes.c
+ *
+ *-------------------------------------------------------------------------
+ */
+#include "postgres.h"
+
+#include "common/md5.h"
+#include "common/sha2.h"
+#include "utils/builtins.h"
+
+
+/*
+ * MD5
+ */
+
+/* MD5 produces a 16 byte (128 bit) hash; double it for hex */
+#define MD5_HASH_LEN  32
+
+/*
+ * Create an MD5 hash of a text value and return it as hex string.
+ */
+Datum
+md5_text(PG_FUNCTION_ARGS)
+{
+	text	   *in_text = PG_GETARG_TEXT_PP(0);
+	size_t		len;
+	char		hexsum[MD5_HASH_LEN + 1];
+
+	/* Calculate the length of the buffer using varlena metadata */
+	len = VARSIZE_ANY_EXHDR(in_text);
+
+	/* get the hash result */
+	if (pg_md5_hash(VARDATA_ANY(in_text), len, hexsum) == false)
+		ereport(ERROR,
+				(errcode(ERRCODE_OUT_OF_MEMORY),
+				 errmsg("out of memory")));
+
+	/* convert to text and return it */
+	PG_RETURN_TEXT_P(cstring_to_text(hexsum));
+}
+
+/*
+ * Create an MD5 hash of a bytea value and return it as a hex string.
+ */
+Datum
+md5_bytea(PG_FUNCTION_ARGS)
+{
+	bytea	   *in = PG_GETARG_BYTEA_PP(0);
+	size_t		len;
+	char		hexsum[MD5_HASH_LEN + 1];
+
+	len = VARSIZE_ANY_EXHDR(in);
+	if (pg_md5_hash(VARDATA_ANY(in), len, hexsum) == false)
+		ereport(ERROR,
+				(errcode(ERRCODE_OUT_OF_MEMORY),
+				 errmsg("out of memory")));
+
+	PG_RETURN_TEXT_P(cstring_to_text(hexsum));
+}
+
+
+/*
+ * SHA-2 variants
+ */
+
+Datum
+sha224_bytea(PG_FUNCTION_ARGS)
+{
+	bytea	   *in = PG_GETARG_BYTEA_PP(0);
+	const uint8 *data;
+	size_t		len;
+	pg_sha224_ctx ctx;
+	unsigned char buf[PG_SHA224_DIGEST_LENGTH];
+	bytea	   *result;
+
+	len = VARSIZE_ANY_EXHDR(in);
+	data = (unsigned char *) VARDATA_ANY(in);
+
+	pg_sha224_init(&ctx);
+	pg_sha224_update(&ctx, data, len);
+	pg_sha224_final(&ctx, buf);
+
+	result = palloc(sizeof(buf) + VARHDRSZ);
+	SET_VARSIZE(result, sizeof(buf) + VARHDRSZ);
+	memcpy(VARDATA(result), buf, sizeof(buf));
+
+	PG_RETURN_BYTEA_P(result);
+}
+
+Datum
+sha256_bytea(PG_FUNCTION_ARGS)
+{
+	bytea	   *in = PG_GETARG_BYTEA_PP(0);
+	const uint8 *data;
+	size_t		len;
+	pg_sha256_ctx ctx;
+	unsigned char buf[PG_SHA256_DIGEST_LENGTH];
+	bytea	   *result;
+
+	len = VARSIZE_ANY_EXHDR(in);
+	data = (unsigned char *) VARDATA_ANY(in);
+
+	pg_sha256_init(&ctx);
+	pg_sha256_update(&ctx, data, len);
+	pg_sha256_final(&ctx, buf);
+
+	result = palloc(sizeof(buf) + VARHDRSZ);
+	SET_VARSIZE(result, sizeof(buf) + VARHDRSZ);
+	memcpy(VARDATA(result), buf, sizeof(buf));
+
+	PG_RETURN_BYTEA_P(result);
+}
+
+Datum
+sha384_bytea(PG_FUNCTION_ARGS)
+{
+	bytea	   *in = PG_GETARG_BYTEA_PP(0);
+	const uint8 *data;
+	size_t		len;
+	pg_sha384_ctx ctx;
+	unsigned char buf[PG_SHA384_DIGEST_LENGTH];
+	bytea	   *result;
+
+	len = VARSIZE_ANY_EXHDR(in);
+	data = (unsigned char *) VARDATA_ANY(in);
+
+	pg_sha384_init(&ctx);
+	pg_sha384_update(&ctx, data, len);
+	pg_sha384_final(&ctx, buf);
+
+	result = palloc(sizeof(buf) + VARHDRSZ);
+	SET_VARSIZE(result, sizeof(buf) + VARHDRSZ);
+	memcpy(VARDATA(result), buf, sizeof(buf));
+
+	PG_RETURN_BYTEA_P(result);
+}
+
+Datum
+sha512_bytea(PG_FUNCTION_ARGS)
+{
+	bytea	   *in = PG_GETARG_BYTEA_PP(0);
+	const uint8 *data;
+	size_t		len;
+	pg_sha512_ctx ctx;
+	unsigned char buf[PG_SHA512_DIGEST_LENGTH];
+	bytea	   *result;
+
+	len = VARSIZE_ANY_EXHDR(in);
+	data = (unsigned char *) VARDATA_ANY(in);
+
+	pg_sha512_init(&ctx);
+	pg_sha512_update(&ctx, data, len);
+	pg_sha512_final(&ctx, buf);
+
+	result = palloc(sizeof(buf) + VARHDRSZ);
+	SET_VARSIZE(result, sizeof(buf) + VARHDRSZ);
+	memcpy(VARDATA(result), buf, sizeof(buf));
+
+	PG_RETURN_BYTEA_P(result);
+}
+
diff --git a/src/backend/utils/adt/varlena.c b/src/backend/utils/adt/varlena.c
index 304cb26691..4346410d5a 100644
--- a/src/backend/utils/adt/varlena.c
+++ b/src/backend/utils/adt/varlena.c
@@ -22,7 +22,6 @@
 #include "catalog/pg_collation.h"
 #include "catalog/pg_type.h"
 #include "common/int.h"
-#include "common/md5.h"
 #include "lib/hyperloglog.h"
 #include "libpq/pqformat.h"
 #include "miscadmin.h"
@@ -4564,53 +4563,6 @@ to_hex64(PG_FUNCTION_ARGS)
 	PG_RETURN_TEXT_P(cstring_to_text(ptr));
 }
 
-/*
- * Create an md5 hash of a text string and return it as hex
- *
- * md5 produces a 16 byte (128 bit) hash; double it for hex
- */
-#define MD5_HASH_LEN  32
-
-Datum
-md5_text(PG_FUNCTION_ARGS)
-{
-	text	   *in_text = PG_GETARG_TEXT_PP(0);
-	size_t		len;
-	char		hexsum[MD5_HASH_LEN + 1];
-
-	/* Calculate the length of the buffer using varlena metadata */
-	len = VARSIZE_ANY_EXHDR(in_text);
-
-	/* get the hash result */
-	if (pg_md5_hash(VARDATA_ANY(in_text), len, hexsum) == false)
-		ereport(ERROR,
-				(errcode(ERRCODE_OUT_OF_MEMORY),
-				 errmsg("out of memory")));
-
-	/* convert to text and return it */
-	PG_RETURN_TEXT_P(cstring_to_text(hexsum));
-}
-
-/*
- * Create an md5 hash of a bytea field and return it as a hex string:
- * 16-byte md5 digest is represented in 32 hex characters.
- */
-Datum
-md5_bytea(PG_FUNCTION_ARGS)
-{
-	bytea	   *in = PG_GETARG_BYTEA_PP(0);
-	size_t		len;
-	char		hexsum[MD5_HASH_LEN + 1];
-
-	len = VARSIZE_ANY_EXHDR(in);
-	if (pg_md5_hash(VARDATA_ANY(in), len, hexsum) == false)
-		ereport(ERROR,
-				(errcode(ERRCODE_OUT_OF_MEMORY),
-				 errmsg("out of memory")));
-
-	PG_RETURN_TEXT_P(cstring_to_text(hexsum));
-}
-
 /*
  * Return the size of a datum, possibly compressed
  *
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index 2a5321315a..62e16514cc 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -3917,10 +3917,18 @@ DATA(insert OID = 3314 (  system			PGNSP PGUID 12 1 0 0 0 f f f f t f v s 1 0 33
 DESCR("SYSTEM tablesample method handler");
 
 /* cryptographic */
-DATA(insert OID =  2311 (  md5	   PGNSP PGUID 12 1 0 0 0 f f f f t f i s 1 0 25 "25" _null_ _null_ _null_ _null_ _null_ md5_text _null_ _null_ _null_ ));
+DATA(insert OID =  2311 (  md5	   PGNSP PGUID 12 1 0 0 0 f f f t t f i s 1 0 25 "25" _null_ _null_ _null_ _null_ _null_ md5_text _null_ _null_ _null_ ));
 DESCR("MD5 hash");
-DATA(insert OID =  2321 (  md5	   PGNSP PGUID 12 1 0 0 0 f f f f t f i s 1 0 25 "17" _null_ _null_ _null_ _null_ _null_ md5_bytea _null_ _null_ _null_ ));
+DATA(insert OID =  2321 (  md5	   PGNSP PGUID 12 1 0 0 0 f f f t t f i s 1 0 25 "17" _null_ _null_ _null_ _null_ _null_ md5_bytea _null_ _null_ _null_ ));
 DESCR("MD5 hash");
+DATA(insert OID =  3419 (  sha224  PGNSP PGUID 12 1 0 0 0 f f f t t f i s 1 0 17 "17" _null_ _null_ _null_ _null_ _null_ sha224_bytea _null_ _null_ _null_ ));
+DESCR("SHA-224 hash");
+DATA(insert OID =  3420 (  sha256  PGNSP PGUID 12 1 0 0 0 f f f t t f i s 1 0 17 "17" _null_ _null_ _null_ _null_ _null_ sha256_bytea _null_ _null_ _null_ ));
+DESCR("SHA-256 hash");
+DATA(insert OID =  3421 (  sha384  PGNSP PGUID 12 1 0 0 0 f f f t t f i s 1 0 17 "17" _null_ _null_ _null_ _null_ _null_ sha384_bytea _null_ _null_ _null_ ));
+DESCR("SHA-384 hash");
+DATA(insert OID =  3422 (  sha512  PGNSP PGUID 12 1 0 0 0 f f f t t f i s 1 0 17 "17" _null_ _null_ _null_ _null_ _null_ sha512_bytea _null_ _null_ _null_ ));
+DESCR("SHA-512 hash");
 
 /* crosstype operations for date vs. timestamp and timestamptz */
 DATA(insert OID = 2338 (  date_lt_timestamp		   PGNSP PGUID 12 1 0 0 0 f f f f t f i s 2 0 16 "1082 1114" _null_ _null_ _null_ _null_ _null_ date_lt_timestamp _null_ _null_ _null_ ));
diff --git a/src/test/regress/expected/opr_sanity.out b/src/test/regress/expected/opr_sanity.out
index 684f7f20a8..6616cc1bf0 100644
--- a/src/test/regress/expected/opr_sanity.out
+++ b/src/test/regress/expected/opr_sanity.out
@@ -699,6 +699,8 @@ timestamp_lt(timestamp without time zone,timestamp without time zone)
 timestamp_le(timestamp without time zone,timestamp without time zone)
 timestamp_ge(timestamp without time zone,timestamp without time zone)
 timestamp_gt(timestamp without time zone,timestamp without time zone)
+md5(text)
+md5(bytea)
 tidgt(tid,tid)
 tidlt(tid,tid)
 tidge(tid,tid)
@@ -711,6 +713,10 @@ uuid_gt(uuid,uuid)
 uuid_ne(uuid,uuid)
 xidneq(xid,xid)
 xidneqint4(xid,integer)
+sha224(bytea)
+sha256(bytea)
+sha384(bytea)
+sha512(bytea)
 macaddr8_eq(macaddr8,macaddr8)
 macaddr8_lt(macaddr8,macaddr8)
 macaddr8_le(macaddr8,macaddr8)
diff --git a/src/test/regress/expected/strings.out b/src/test/regress/expected/strings.out
index 8073eb4fad..cbe66c375c 100644
--- a/src/test/regress/expected/strings.out
+++ b/src/test/regress/expected/strings.out
@@ -1439,6 +1439,58 @@ select md5('12345678901234567890123456789012345678901234567890123456789012345678
  t
 (1 row)
 
+--
+-- SHA-2
+--
+SET bytea_output TO hex;
+SELECT sha224('');
+                           sha224                           
+------------------------------------------------------------
+ \xd14a028c2a3a2bc9476102bb288234c415a2b01f828ea62ac5b3e42f
+(1 row)
+
+SELECT sha224('The quick brown fox jumps over the lazy dog.');
+                           sha224                           
+------------------------------------------------------------
+ \x619cba8e8e05826e9b8c519c0a5c68f4fb653e8a3d8aa04bb2c8cd4c
+(1 row)
+
+SELECT sha256('');
+                               sha256                               
+--------------------------------------------------------------------
+ \xe3b0c44298fc1c149afbf4c8996fb92427ae41e4649b934ca495991b7852b855
+(1 row)
+
+SELECT sha256('The quick brown fox jumps over the lazy dog.');
+                               sha256                               
+--------------------------------------------------------------------
+ \xef537f25c895bfa782526529a9b63d97aa631564d5d789c2b765448c8635fb6c
+(1 row)
+
+SELECT sha384('');
+                                               sha384                                               
+----------------------------------------------------------------------------------------------------
+ \x38b060a751ac96384cd9327eb1b1e36a21fdb71114be07434c0cc7bf63f6e1da274edebfe76f65fbd51ad2f14898b95b
+(1 row)
+
+SELECT sha384('The quick brown fox jumps over the lazy dog.');
+                                               sha384                                               
+----------------------------------------------------------------------------------------------------
+ \xed892481d8272ca6df370bf706e4d7bc1b5739fa2177aae6c50e946678718fc67a7af2819a021c2fc34e91bdb63409d7
+(1 row)
+
+SELECT sha512('');
+                                                               sha512                                                               
+------------------------------------------------------------------------------------------------------------------------------------
+ \xcf83e1357eefb8bdf1542850d66d8007d620e4050b5715dc83f4a921d36ce9ce47d0d13c5d85f2b0ff8318d2877eec2f63b931bd47417a81a538327af927da3e
+(1 row)
+
+SELECT sha512('The quick brown fox jumps over the lazy dog.');
+                                                               sha512                                                               
+------------------------------------------------------------------------------------------------------------------------------------
+ \x91ea1245f20d46ae9a037a989f54f1f790f0a47607eeb8a14d12890cea77a1bbc6c7ed9cf205e67b7f2b8fd4c7dfd3a7a8617e45f3c463d481c7e586c39ac1ed
+(1 row)
+
 --
 -- test behavior of escape_string_warning and standard_conforming_strings options
 --
@@ -1525,6 +1577,7 @@ select 'a\\bcd' as f1, 'a\\b\'cd' as f2, 'a\\b\'''cd' as f3, 'abcd\\'   as f4, '
 --
 -- Additional string functions
 --
+SET bytea_output TO escape;
 SELECT initcap('hi THOMAS');
   initcap  
 -----------
diff --git a/src/test/regress/sql/strings.sql b/src/test/regress/sql/strings.sql
index 9ed242208f..5a82237870 100644
--- a/src/test/regress/sql/strings.sql
+++ b/src/test/regress/sql/strings.sql
@@ -506,6 +506,23 @@ CREATE TABLE toasttest (c char(4096));
 
 select md5('12345678901234567890123456789012345678901234567890123456789012345678901234567890'::bytea) = '57edf4a22be3c955ac49da2e2107b67a' AS "TRUE";
 
+--
+-- SHA-2
+--
+SET bytea_output TO hex;
+
+SELECT sha224('');
+SELECT sha224('The quick brown fox jumps over the lazy dog.');
+
+SELECT sha256('');
+SELECT sha256('The quick brown fox jumps over the lazy dog.');
+
+SELECT sha384('');
+SELECT sha384('The quick brown fox jumps over the lazy dog.');
+
+SELECT sha512('');
+SELECT sha512('The quick brown fox jumps over the lazy dog.');
+
 --
 -- test behavior of escape_string_warning and standard_conforming_strings options
 --
@@ -540,6 +557,7 @@ CREATE TABLE toasttest (c char(4096));
 --
 -- Additional string functions
 --
+SET bytea_output TO escape;
 
 SELECT initcap('hi THOMAS');
 

base-commit: 29d432e477a99f4c1e18820c5fc820a6b178c695
-- 
2.16.2

v2-0002-Update-gratuitous-use-of-MD5-in-documentation.patchtext/plain; charset=UTF-8; name=v2-0002-Update-gratuitous-use-of-MD5-in-documentation.patch; x-mac-creator=0; x-mac-type=0Download

From 375cbcbceaeac8deb9abe3f6b06685be6dd9fb3e Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <peter_e@gmx.net>
Date: Tue, 6 Feb 2018 21:59:40 -0500
Subject: [PATCH v2 2/2] Update gratuitous use of MD5 in documentation

---
 doc/src/sgml/citext.sgml  | 10 +++++-----
 doc/src/sgml/sepgsql.sgml |  2 +-
 2 files changed, 6 insertions(+), 6 deletions(-)

diff --git a/doc/src/sgml/citext.sgml b/doc/src/sgml/citext.sgml
index 82251de852..b1fe7101b2 100644
--- a/doc/src/sgml/citext.sgml
+++ b/doc/src/sgml/citext.sgml
@@ -80,11 +80,11 @@ <title>How to Use It</title>
     pass TEXT   NOT NULL
 );
 
-INSERT INTO users VALUES ( 'larry',  md5(random()::text) );
-INSERT INTO users VALUES ( 'Tom',    md5(random()::text) );
-INSERT INTO users VALUES ( 'Damian', md5(random()::text) );
-INSERT INTO users VALUES ( 'NEAL',   md5(random()::text) );
-INSERT INTO users VALUES ( 'Bj&oslash;rn',  md5(random()::text) );
+INSERT INTO users VALUES ( 'larry',  sha256(random()::text::bytea) );
+INSERT INTO users VALUES ( 'Tom',    sha256(random()::text::bytea) );
+INSERT INTO users VALUES ( 'Damian', sha256(random()::text::bytea) );
+INSERT INTO users VALUES ( 'NEAL',   sha256(random()::text::bytea) );
+INSERT INTO users VALUES ( 'Bj&oslash;rn',  sha256(random()::text::bytea) );
 
 SELECT * FROM users WHERE nick = 'Larry';
 </programlisting>
diff --git a/doc/src/sgml/sepgsql.sgml b/doc/src/sgml/sepgsql.sgml
index 273efc6e27..f8c99e1b00 100644
--- a/doc/src/sgml/sepgsql.sgml
+++ b/doc/src/sgml/sepgsql.sgml
@@ -370,7 +370,7 @@ <title>DML Permissions</title>
    <para>
    For example, consider:
 <synopsis>
-UPDATE t1 SET x = 2, y = md5sum(y) WHERE z = 100;
+UPDATE t1 SET x = 2, y = func1(y) WHERE z = 100;
 </synopsis>
 
     Here, <literal>db_column:update</literal> will be checked for
-- 
2.16.2

#11

Michael Paquier

michael@paquier.xyz

almost 8 years ago

In reply to: Peter Eisentraut (#10)

Re: SHA-2 functions

On Wed, Feb 21, 2018 at 03:45:17PM -0500, Peter Eisentraut wrote:

On 2/20/18 23:04, Michael Paquier wrote:

I think that crypto_hash.c or hash_crypt.c would be adapted as well.
crypt.c is too much generic, so including both concepts in the name is
the way to go. The name given by Tom here sounds actually nice.

Updated patches

I have been reviewing both patches, and those look good to me.

git diff --check has one complain:
src/backend/utils/adt/cryptohashes.c:170: new blank line at EOF.
--
Michael

#12

Peter Eisentraut

peter.eisentraut@2ndquadrant.com

almost 8 years ago

In reply to: Michael Paquier (#11)

Re: SHA-2 functions

On 2/22/18 01:05, Michael Paquier wrote:

On Wed, Feb 21, 2018 at 03:45:17PM -0500, Peter Eisentraut wrote:

On 2/20/18 23:04, Michael Paquier wrote:

I think that crypto_hash.c or hash_crypt.c would be adapted as well.
crypt.c is too much generic, so including both concepts in the name is
the way to go. The name given by Tom here sounds actually nice.

Updated patches

I have been reviewing both patches, and those look good to me.

Committed, thanks

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services