HMAC
Use a secret defined by a hexadecimal string to return an HMAC (hash-based message authentication code).
HMAC(string, hex_string [, algorithm ])
- string
Specify an input character string, or an expression that evaluates to a character string.
- hex_string
Specify a hexadecimal string that provides a secret for running the function.
- algorithm
Optionally, specify one of the following algorithms. The default is
2
(SHA-2
).Algorithm Mode Output Length (Bits) Output Type 0 MD5 128 VARCHAR(128) 1 SHA-1 160 2 SHA-2 256 3 SHA-2 512
The return type of this function is VARCHAR(128)
.
Examples
For example, using SHA-1
mode and two different "secret" values:
premdb=> select hmac(nickname,'f5e4d3c2b1a0',1) from team order by teamid limit 3;
hmac
------------------------------------------
be4a4a1100473015d41a658ab01040e23f0cccbe
56156fa4e1c30e890849323bef6ca23f272bbc3f
068516ffb71a4c585cc16fce3e5afa418ac17016
(3 rows)
premdb=> select hmac(nickname,'d3c2b1a0',1) from team order by teamid limit 3;
hmac
------------------------------------------
22957e439e9c79b6fa790f9665a44f5847a00cf1
f40feb8474bb96832bf6469fd6df7832133a7cef
f3ff8dcf59cfdc8b70607f45e2c1ba0e3213a500
(3 rows)
In this example, the same secrets are used with SHA-2
mode (with a length of 256 bits):
premdb=> select hmac(nickname,'f5e4d3c2b1a0',2) from team order by teamid limit 3;
hmac
------------------------------------------------------------------
b28f48afa05c480db5f6ade36d6c79636ba0ad7b0cfd81b7f45de139da7927e8
f7da1a52b3d6a0613a0ee654c3a02bd900cc15a0ac52d6ad5e4db84c68b92380
5bc029cf8e0be0cadc026e01cfc88209bba7a24cb01eebed400cd29d323dfb3f
(3 rows)
premdb=> select hmac(nickname,'d3c2b1a0',2) from team order by teamid limit 3;
hmac
------------------------------------------------------------------
403ea5b09d9f29a145a2373806b41d8f3651f7e2ac0e6275cb01d7cb93247d15
052f4b11b93de81b0707f1a1fa1c9d991a64c704c2bc5c0a6e068c5aab8b0c35
7f06ac46b2c99d398a5624d7cece79a4517e87cc491bf837cc81929b942993e6
(3 rows)
This example uses SHA-2
mode (with a length of 512 bits):
premdb=# select hmac(nickname,'d3c2b1a0',3) from team order by teamid limit 3;
hmac
----------------------------------------------------------------------------------------------------------------------------------
5a724fdd70367f56a2d290f6dbbd729782821846d7e4cfa7c99606b16446fc76b8c3012fe6bfc6ae43b0c65ca25193a5e61b184cd9b3a678de6b6d347571615d
c23caf7fd7029b3a5d14ecfd38947b87a8a0dbc51bbfaa10c72dd5138f97b201aa171b425f24999a1ab046c83726cca67a4bfb93fa26ca17f681a71ac1d2afaa
16a5c1e96d75b3f3d99df35530ba13877cd29accae35799f3c6a1b2779679857423085553bb41ff904b2b27ccfe56a9dc2e98fcf4519a9eef7129eb243a89eec
(3 rows)
This example uses MD5
mode:
premdb=# select hmac(nickname,'d3c2b1a0',0) from team order by teamid limit 3;
hmac
----------------------------------
d84f1cf1380d1e2d44c330096e141ce6
2c7e526250cf6a584bbcdd269b8bc620
59a11ef3c6f4f86a8ae40c503653938c
(3 rows)
Parent topic:String Functions