如何使用 PostgreSQL 加密密码？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18656528/
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
How do I encrypt passwords with PostgreSQL?
提问by Karen Manukyan
I have some problems with encoding passwords,how can I do it. Type of encoding md5
我在编码密码时遇到了一些问题，我该怎么做。编码类型 md5
digest(data text, type text) returns bytea;
CREATE OR REPLACE FUNCTION md(bytea) returns text AS $$
SELECT encode(digest(, 'sha1'), 'md5')
$$ LANGUAGE SQL STRICT IMMUTABLE;
INSERT INTO "login"(login, password, employee_id)
VALUES ( 'email',crypt('password', md('md5')), 1);
***Error ***
***错误 ** *
ERROR: syntax error at or near "digest"
SQL state: 42601
Character: 1
回答by Mark Stosberg
digest(data text, type text) returns bytea;is not valid syntax.
digest(data text, type text) returns bytea;不是有效的语法。
I recommend using bcryptinstead. No additional function definitions are required:
我建议bcrypt改用。不需要额外的函数定义：
INSERT into "login" (login, password, employee_id)
VALUES ('email',crypt('password', gen_salt('bf'));
Later...
之后...
UPDATE table SET password = crypt('password',gen_salt('bf'))
And checking the password:
并检查密码：
SELECT ... FROM table
WHERE password is NOT NULL
AND password = crypt('password-to-test',password);
Bcrypt is recommended by Crafted Softwareand Jeff Atwood. The official pgcrypto docsmay also be of interest.
Bcrypt 是由Crafted Software和Jeff Atwood推荐的。该负责人pgcrypto文档也可能是感兴趣。
回答by Piyush Sharma
I know this question is old but for those who having the same issue.
我知道这个问题很老，但对于那些有同样问题的人。
Step 1:first check whether prcrypto is installed or not
第一步：首先检查prcrypto是否安装
select e.extname, n.nspname from pg_catalog.pg_extension e left join pg_catalog.pg_namespace n on n.oid = e.extnamespace;
Step 2:If it is not installed then create extension
第 2 步：如果未安装，则创建扩展
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE EXTENSION IF NOT EXISTS pgcrypto;
Step 3:Computes a binary hash of the given data.
第 3 步：计算给定数据的二进制哈希。
CREATE OR REPLACE FUNCTION sha1(bytea) returns text AS $$
SELECT encode(digest(, 'sha1'), 'hex')
$$ LANGUAGE SQL STRICT IMMUTABLE;
Last Step:
最后一步：
Also use encode function If you want the digest as a hexadecimal string
如果您希望摘要为十六进制字符串，也可以使用 encode 函数
SELECT encode(digest('blue', 'sha1'), 'hex');
SELECT encode(digest('blue', 'sha1'), 'hex');
or
或者
directly sha('blue')
直接地 sha('blue')
