在 PostgreSQL 中使用加密密码创建用户
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17429040/
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
Creating user with encrypted password in PostgreSQL
提问by Martin
Is it possible to create a user in PostgreSQL without providing the plain text password (ideally, I would like to be able to create a user providing only its password crypted with sha-256) ?
是否可以在不提供纯文本密码的情况下在 PostgreSQL 中创建用户（理想情况下，我希望能够创建一个仅提供使用 sha-256 加密的密码的用户）？
What I would like to do is to create a user with something like that :
我想做的是创建一个类似这样的用户：
CREATE USER "martin" WITH PASSWORD '$kH3l2bj8iT$KKrTAKDF4OoE7w.oy(...)BPwcTBN/V42hqE.';
Is there some way to do that ?
有没有办法做到这一点？
Thank you for your help.
感谢您的帮助。
回答by Daniel Vérité
You may provide the password already hashed with md5, as said in the doc (CREATE ROLE):
您可以提供已经用 散列的密码md5，如文档（CREATE ROLE）中所述：
ENCRYPTED UNENCRYPTED These key words control whether the password is stored encrypted in the system catalogs. (If neither is specified, the default behavior is determined by the configuration parameter password_encryption.) If the presented password string is already in MD5-encrypted format, then it is stored encrypted as-is, regardless of whether ENCRYPTED or UNENCRYPTED is specified (since the system cannot decrypt the specified encrypted password string). This allows reloading of encrypted passwords during dump/restore.
ENCRYPTED UNENCRYPTED 这些关键字控制密码是否以加密方式存储在系统目录中。（如果两者均未指定，则默认行为由配置参数 password_encryption 确定。）如果提供的密码字符串已采用 MD5 加密格式，则无论指定了 ENCRYPTED 还是 UNENCRYPTED（因为系统无法解密指定的加密密码字符串）。这允许在转储/恢复期间重新加载加密密码。
The information that's missing here is that the MD5-encrypted string should be the password concatened with the username, plus md5at the beginning.
这里缺少的信息是 MD5 加密的字符串应该是与用户名连接的密码，加上md5开头。
So for example to create u0with the password foobar, knowing that md5('foobaru0')is ac4bbe016b808c3c0b816981f240dcae:
因此，例如u0使用密码创建foobar，知道md5('foobaru0')是ac4bbe016b808c3c0b816981f240dcae：
CREATE USER u0 PASSWORD 'md5ac4bbe016b808c3c0b816981f240dcae';
and then u0 will be able to log in by typing foobaras the password.
然后 u0 将能够通过输入foobar密码登录。
I don't think that there's currently a way to use SHA-256instead of md5for PostgreSQL passwords.
我认为目前没有一种方法可以SHA-256代替md5PostgreSQL 密码使用。
回答by bma
I'm not aware of a way to override the default md5 encryption of passwords, but if you have a ROLE (aka "USER") that has an already md5-encrypted password it appears that you can supply that. Verify this using pg_dumpall -g (to see the globals from the cluster) Eg.
我不知道有什么方法可以覆盖密码的默认 md5 加密，但是如果您有一个 ROLE（又名“用户”）已经有一个已进行 md5 加密的密码，那么您似乎可以提供该密码。使用 pg_dumpall -g（以查看集群中的全局变量）验证这一点。
psql postgres
create role foo with encrypted password foobar;
\q
-- View the role from pg_dumpall -g
pg_dumpall -g | grep foo
CREATE ROLE foo;
ALTER ROLE foo WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB NOLOGIN NOREPLICATION PASSWORD 'md5c98cbfeb6a347a47eb8e96cfb4c4b890';
Or get it from:
select * from pg_catalog.pg_shadow;
-- create the role again with the already-encrypted password
psql postgres
drop role foo;
CREATE ROLE foo;
ALTER ROLE foo WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB NOLOGIN NOREPLICATION PASSWORD 'md5c98cbfeb6a347a47eb8e96cfb4c4b890';
\q
-- view the ROLE with the same password
pg_dumpall -g | grep foo
CREATE ROLE foo;
ALTER ROLE foo WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB NOLOGIN NOREPLICATION PASSWORD 'md5c98cbfeb6a347a47eb8e96cfb4c4b890';
Docs for CREATE ROLE
回答by Kamil D
Much easier way to to this is:
更简单的方法是：
CREATE USER u0 PASSWORD 'foobar';
CREATE USER u0 PASSWORD 'foobar';
select * from pg_catalog.pg_shadow;
Gives passwd: md5ac4bbe016b808c3c0b816981f240dcae
给出密码： md5ac4bbe016b808c3c0b816981f240dcae
