生成 postgresql 用户密码
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14918763/
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
Generating postgresql user password
提问by CppLearner
I tried to generate password for postgresusing hashlibfrom Python.
我试图生成密码以供从 Pythonpostgres使用hashlib。
>>> import hashlib
>>> hashlib.md5("psql123").hexdigest()
2636d1ddc54901f98d011ffe050c0eb7
But postgresql requires md5prefix, so then
但是 postgresql 需要md5前缀，所以
sudo -u postgres psql
ALTER USER postgres PASSWORD 'md52636d1ddc54901f98d011ffe050c0eb7';
However, authentication would fail if I use psql123as password.
但是，如果我psql123用作密码，身份验证将失败。
If I use passlib, I am fine. See http://pythonhosted.org/passlib/lib/passlib.hash.postgres_md5.html
如果我使用passlib，我很好。见http://pythonhosted.org/passlib/lib/passlib.hash.postgres_md5.html
Doing the following using psql123as password is okay.
使用psql123密码执行以下操作是可以的。
ALTER USER postgres PASSWORD 'md556074e7318bd4cee558faab0678a2fad';
I don't understand what the warning in passlibwant to say. Is it okay to use this hash for postgresuser? Also, where in the doc does it say usernamehas to be part of the input?
我不明白警告passlib想说什么。可以为postgres用户使用这个哈希吗？另外，它在文档中的哪个地方说username必须是输入的一部分？
I assume this is why postgrescan't understand the result from hashlib. As a LDAP user, I can generate a password in the shell. Does postgres has a built-in command to do that? Does psycopg2has that? It looks like it doesn't.
我认为这就是为什么postgres无法理解hashlib. 作为 LDAP 用户，我可以在 shell 中生成密码。postgres 是否有一个内置的命令来做到这一点？是否psycopg2有？看起来没有。
回答by Eli Collins
Postgres' password hash is very close to what you did, it just needs the username to be included as follows:
Postgres 的密码哈希与您所做的非常接近，它只需要按如下方式包含用户名：
pghash = "md5" + hashlib.md5(password + username).hexdigest()
AFAIK, the postgres docs don't really document this hash format at all, and seem to assume admins will rarely deal with these hashes directly :( There are no builtin methods for generating these hashes that I know of. If the password provided to the ALTER USERcommand doesn't conform to the postgres hash format, it assumes the password hasn't been hashed, and takes care of that internally - per the docs for CREATE ROLE's ENCRYPTED keyword. (IMHO this is a flawed behavior, because if a hash depends on the username, it means hashes can't be copied and pasted between different accounts, break when the account is renamed, and (guessing entropy wise) only has ~6 bits of effective salt).
AFAIK，postgres 文档根本没有真正记录这种散列格式，似乎假设管理员很少直接处理这些散列:( 我知道没有生成这些散列的内置方法。如果密码提供给ALTER USER命令不符合 postgres 散列格式，它假定密码没有经过散列，并在内部处理 - 根据CREATE ROLE的 ENCRYPTED 关键字的文档。（恕我直言，这是一个有缺陷的行为，因为如果哈希取决于用户名，这意味着不能在不同帐户之间复制和粘贴哈希，在重命名帐户时会中断，并且（猜测熵明智）只有约 6 位有效盐）。
The warning at the top of passlib's documentation for the hash could probably be clearer. It was meant to warn people browsing through the passlib documentation that 1) this hash was horribly insecure, 2) that they shouldn't adopt it for use in their own applications, and 3) that it was only fit for the purpose of working with postgres user accounts, since it's the strongest (and only) hash format postgres supports for it's own accounts.
passlib 的哈希文档顶部的警告可能会更清楚。它的目的是警告浏览 passlib 文档的人们：1）这个哈希非常不安全，2）他们不应该在自己的应用程序中使用它，3）它只适合使用postgres 用户帐户，因为它是 postgres 对其自己帐户支持的最强（也是唯一）散列格式。
(If you're trying to use postgres to hash passwords for your own application's user accounts, I'd strongly second Clodoaldo's recommendation to use bcrypt by way of the pgcrypto extension).
（如果您尝试使用 postgres 为您自己的应用程序的用户帐户散列密码，我强烈建议 Clodoaldo 建议通过 pgcrypto 扩展使用 bcrypt）。
回答by Clodoaldo Neto
alter user postgres ENCRYPTED password 'psql123';
For other uses use the pgcryptomodule.
对于其他用途，请使用该pgcrypto模块。
create table "user" (name text, password_hash text);
insert into "user" (name, password_hash) values
('u1', crypt('psql123', gen_salt('bf')));
select * from "user";
name | password_hash
------+--------------------------------------------------------------
u1 | a$SeH4u4aRtT2Zr39er4eSiONT/0IBQHYMbQXn2RauPJKCYdNX1.58G
select name, password_hash = crypt('psql123', password_hash)
from "user"
;
name | ?column?
------+----------
u1 | t
Install it as super user logged in the target database:
将其安装为登录目标数据库的超级用户：
create extension pgcrypto;
