postgresql 使用 sha-256 在 postgres 中散列一列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13683533/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Hash a column in postgres using sha-256
提问by roykasa
I have a postrges database with a table contains key codes that I have generated using a python function. I would like to be able to hash this column such that each time a key code is added to it, the key is hashed. How can I get postgres to do that? Or what will be the best way to store these codes. Here is a sample of the column I would like to hash in my table.
我有一个带有表的 postrges 数据库，其中包含我使用 python 函数生成的关键代码。我希望能够散列此列，以便每次向其中添加密钥代码时，都会对密钥进行散列。我怎样才能让 postgres 做到这一点？或者什么是存储这些代码的最佳方式。这是我想在我的表中散列的列的示例。
key_codes |
-----------+
L7G4J83K |
J70KG169 |
L69E540K |
GL8E9C3J |
6C0LE215 |
9G01C8JA |
1G9KC58A |
回答by Craig Ringer
Use a trigger to set the hash column on insert and update. For SHA-256, use the pgcryptoextension module's digestfunction.
使用触发器在插入和更新时设置哈希列。对于 SHA-256，使用pgcrypto扩展模块的digest功能。
Since you haven't specified your PostgreSQL version I'll assume you're using the current 9.2 in the following examples.
由于您尚未指定 PostgreSQL 版本，因此我假设您在以下示例中使用的是当前的 9.2。
Here's how to invoke a sha256 digest function:
以下是调用 sha256 摘要函数的方法：
regress=# CREATE EXTENSION pgcrypto;
CREATE EXTENSION
regress=> SELECT digest('blah', 'sha256');
digest
--------------------------------------------------------------------
\x8b7df143d91c716ecfa5fc1730022f6b421b05cedee8fd52b1fc65a96030ad52
(1 row)
Note that the CREATE EXTENSIONfunction must be run as a superuser.
请注意，该CREATE EXTENSION功能必须以超级用户身份运行。
The trigger is pretty simple. Something like this would do, assuming your table looks like this:
触发器非常简单。假设您的表如下所示，这样的事情会做：
CREATE TABLE some_table ( key_codes text, hash bytea );
CREATE OR REPLACE FUNCTION hash_update_tg() RETURNS trigger AS $$
BEGIN
IF tg_op = 'INSERT' OR tg_op = 'UPDATE' THEN
NEW.hash = digest(NEW.key_codes, 'sha256');
RETURN NEW;
END IF;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER some_table_hash_update
BEFORE INSERT OR UPDATE ON some_table
FOR EACH ROW EXECUTE PROCEDURE hash_update_tg();
Usage:
用法：
regress=> INSERT INTO some_table(key_codes) VALUES ('fred');
INSERT 0 1
regress=> SELECT * FROM some_table;
key_codes | hash
-----------+--------------------------------------------------------------------
fred | \xd0cfc2e5319b82cdc71a33873e826c93d7ee11363f8ac91c4fa3a2cfcd2286e5
(1 row)
You can reduce the overhead of the trigger execution by making the update trigger conditional. Instead of the above CREATE TRIGGER, use both of these:
您可以通过使更新触发器有条件来减少触发器执行的开销。代替上面的CREATE TRIGGER，使用这两个：
CREATE TRIGGER some_table_hash_insert
BEFORE INSERT ON some_table
FOR EACH ROW
EXECUTE PROCEDURE hash_update_tg();
CREATE TRIGGER some_table_hash_update
BEFORE UPDATE ON some_table
FOR EACH ROW
WHEN ( NEW.key_codes IS DISTINCT FROM OLD.key_codes )
EXECUTE PROCEDURE hash_update_tg();
回答by Lukasz Szozda
Starting from PostgreSQL 11 you could use built-in functions to calculate hash value:
从 PostgreSQL 11 开始，您可以使用内置函数来计算哈希值：
SELECT sha256('hello world!');
-- x7509e5bda0c762d2bac7f90d758b5b2263fa01ccbc542ab5e3df163be08e6ca9
+----------------+--------------+---------------+ | Function | Return Type | Description | +----------------+--------------+---------------+ | sha224(bytea) | bytea | SHA-224 hash | | sha256(bytea) | bytea | SHA-256 hash | | sha384(bytea) | bytea | SHA-384 hash | | sha512(bytea) | bytea | SHA-512 hash | +----------------+--------------+---------------+Note that for historic reasons, the function md5 returns a hex-encoded value of type text whereas the SHA-2 functions return type bytea. Use the functions encode and decode to convert between the two, for example encode(sha256('abc'), 'hex') to get a hex-encoded text representation.
+----------------+--------------+---------------+ | Function | Return Type | Description | +----------------+--------------+---------------+ | sha224(bytea) | bytea | SHA-224 hash | | sha256(bytea) | bytea | SHA-256 hash | | sha384(bytea) | bytea | SHA-384 hash | | sha512(bytea) | bytea | SHA-512 hash | +----------------+--------------+---------------+请注意，由于历史原因，函数 md5 返回文本类型的十六进制编码值，而 SHA-2 函数返回类型字节。使用函数 encode 和 decode 在两者之间进行转换，例如 encode(sha256('abc'), 'hex') 获得十六进制编码的文本表示。
