Product Documentation

Appearance

All platforms

HMAC_KS ​

Use a secret defined by an encryption key to run the HMAC function.

HMAC_KS(string, key [, algorithm])
string
Specify a character string or an expression that evaluates to a character string.
key
Specify the name of an encryption key, as defined with the CREATE KEY command. The secret associated with this key will be used to run the function. Users of this function must be granted HMAC privilege on the key. See ON KEY.
algorithm
Optionally, specify mode 0, 1, 2, or 3. The default is 2. See HMAC for mode definitions.

The return type of this function is VARCHAR(128).

Examples ​

For example:

premdb=# grant hmac on key playerkey to bobr;
GRANT
premdb=# \c premdb bobr
You are now connected to database "premdb" as user "bobr".
premdb=> select hmac_ks(nickname,playerkey,2) from team order by teamid limit 3;
                            hmac_ks                              
------------------------------------------------------------------
 3a2a310e0fe61e9c2c9fad2dfde4943354a18a24c888571c9721b8f0f1b93bed
 1cc33a31a00f002fdcc598b181715acbf6979ba5059054f102cfb917a8bdd4ab
 3213bc9c7217952b5a87c5d4a6fa807b01ada59f4cf4cfa98351a6c3b66880fc
(3 rows)

Here is the same query, using the MD5 mode, then the SHA-2 mode (with a length of 512 bits):

premdb=> select hmac_ks(nickname,playerkey,0) from team order by teamid limit 3;
            hmac_ks              
----------------------------------
 d9f47dbfb2733dd2c8c301ad12f34a4e
 61d8ac8a9868ef8c29385a9c8faf94df
 45e7977a441970afae90af4523497b8b
(3 rows)

premdb=> select hmac_ks(nickname,playerkey,3) from team order by teamid limit 3;
                                                            hmac_ks                                                              
----------------------------------------------------------------------------------------------------------------------------------
 c000eb5a52cbb8452ab36e84856a5d4fe9d29d12c841eed6cc19f4824a5a1f56c058e78b15ea2736b0cd87b825439a992d697ef07e4ddf74fb61df63402f6218
 d3e3b3e354b8b6eb3c927476f33c22c0aba497e34d972a4643bf0fec3eb012dc707178ff71c4bc0d87cddc7df6b6f6c07a30b32f92045a100553b0909f9888b4
 86617d840fd40ebd627790d24495cce28ec06851d3255e4517680ac89fab6284f93e02a8a376a2fc2d397eea2bd966b0ff1c52110603f36a9705235081d504c1
(3 rows)