是否可以在PostgreSQL中创建一个用户而不提供明文密码(理想情况下,我希望只提供使用sha-256加密的密码来创建用户)?
我想做的是创建一个用户,类似于这样:
CREATE USER "martin" WITH PASSWORD '$6$kH3l2bj8iT$KKrTAKDF4OoE7w.oy(...)BPwcTBN/V42hqE.';
有没有什么方法可以做到这一点?
感谢您的帮助。
是否可以在PostgreSQL中创建一个用户而不提供明文密码(理想情况下,我希望只提供使用sha-256加密的密码来创建用户)?
我想做的是创建一个用户,类似于这样:
CREATE USER "martin" WITH PASSWORD '$6$kH3l2bj8iT$KKrTAKDF4OoE7w.oy(...)BPwcTBN/V42hqE.';
有没有什么方法可以做到这一点?
感谢您的帮助。
根据文档中所述(CREATE ROLE), 您可以提供已经使用md5
哈希过的密码:
ENCRYPTED UNENCRYPTED 这些关键词控制密码是否在系统目录中以加密方式存储。(如果没有指定其中之一,则默认行为由配置参数password_encryption确定。)如果提交的密码字符串已经是MD5加密格式,则无论指定ENCRYPTED还是UNENCRYPTED,它都将按原样存储加密(因为系统无法解密指定的加密密码字符串)。这允许在转储/还原期间重新加载加密密码。
这里缺少的信息是,MD5加密的字符串应该是将密码连接到用户名后面,并在开头添加md5
得到的结果。
例如,要创建名为u0
且密码为foobar
的用户,已知md5('foobaru0')
是ac4bbe016b808c3c0b816981f240dcae
:
CREATE USER u0 PASSWORD 'md5ac4bbe016b808c3c0b816981f240dcae';
然后,u0将能够通过输入foobar
作为密码登录。
我认为目前没有一种方法可以在PostgreSQL密码中使用SHA-256
而不是md5
。
从至少10.10版本开始,也可以使用SCRAM-SHA-256。
CREATE USER user_name
WITH PASSWORD 'SCRAM-SHA-256$4096:UunGvPETiX/JNGBvjOgW9A==$CPGNh7/MRfs0ispH9/HSJajOI8Uhp+UCRo/b/ToXIEY=:L6NzxQ3XUeWEeRa+oiuajC9Vgl7wk6ZpHAHl+pv4m00=';
GRANT CONNECT ON DATABASE database_name TO user_name;
(不要忘记授予新用户权限是很重要的)
如果您想将SCRAM设置为默认使用,可以将password_cryptography设置为SCRAM-SHA-256:
ALTER SYSTEM SET password_encryption = 'scram-sha-256';
SELECT pg_reload_conf();
Remember to set the password_encryption to SCRAM
ALTER SYSTEM SET password_encryption = 'scram-sha-256';
SELECT pg_reload_conf();
This cannot be run in a transaction block. If for instance, you're using migration files, you probably have to create two different files just ofr those two commands.
Create a user with the password you need to encode.
CREATE USER tmp_user_to_create_a_password
WITH PASSWORD 'your_password';
Read the password with SCRAM encryption.
SELECT rolpassword
FROM pg_catalog.pg_authid
WHERE rolname='tmp_user_to_create_a_password';
Drop the user
DROP USER IF EXISTS tmp_user_to_create_a_password;
Now you can create your user without using plain text.
CREATE USER user_name
WITH PASSWORD 'SCRAM-SHA-256$4096:3Lctb6GmH15cSO4bjcDsXg==$BSuI1c10J+NZ/Wmx4hwP4TvpdKEO9rl2hekZ8/DVuyA=:j8G9NJ30Xbz3Za2mjXF/j6O3DJbWyqvX886haFe4aCs=';
GRANT CONNECT ON DATABASE database_name TO user_name;
You can now use 'user_name' and 'your_password' to log-in.
我不知道有没有办法覆盖默认的MD5密码加密方式,但如果你有一个角色(也称为“用户”),其密码已经被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
postgresql.conf
中将 password_encryption
设置为 scram-sha-256
,以获得 scram-sha-256 密码加密。但请注意,旧的客户端可能不支持 SCRAM 认证(请参阅 https://www.postgresql.org/docs/11/runtime-config-connection.html#GUC-PASSWORD-ENCRYPTION)。 - Cromax创建用户 u0 密码 'foobar';
select * from pg_catalog.pg_shadow;
密码为:md5ac4bbe016b808c3c0b816981f240dcae
因为我最近在构建一个项目时需要用到这个功能,所以我想分享一下这段代码,让未来的开发者也能受益。它从标准输入中读取密码,并输出适用于CREATE USER ... ENCRYPTED PASSSWORD '...'
的SCRAM-SHA-256密码哈希值。希望对大家有所帮助。
require "openssl"
require "securerandom"
# Yes, these are the literal values specified in RFC5802, s3
CLIENT_KEY = "Client Key"
SERVER_KEY = "Server Key"
SALT = SecureRandom.bytes(16)
# This is much higher than PostgreSQL implements by default, which is 4096
# (as of https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob_plain;f=src/include/common/scram-common.h;hb=HEAD, anyway)
# but THAT'S A GOOD THING
ITERATIONS = 65_536
salted_password = OpenSSL::PKCS5.pbkdf2_hmac($stdin.read, SALT, ITERATIONS, 32, OpenSSL::Digest::SHA256.new)
client_key = OpenSSL::HMAC.digest(OpenSSL::Digest::SHA256.new, salted_password, CLIENT_KEY)
stored_key = OpenSSL::Digest::SHA256.new.digest(client_key)
server_key = OpenSSL::HMAC.digest(OpenSSL::Digest::SHA256.new, salted_password, SERVER_KEY)
def b64(s)
[s].pack("m0")
end
puts "SCRAM-SHA-256$#{ITERATIONS}:#{b64(SALT)}$#{b64(stored_key)}:#{b64(server_key)}"
echo "foobaru0" | md5sum | cut -d' ' -f1
,但得到的结果不同。之后我尝试使用CREATE USER u0 PASSWORD 'md5c49aa8b010dbdeec293c5a9504fb5531';
创建了用户,但是当我尝试使用密码foobar登录时,系统无法验证,出现错误信息psql: FATAL: password authentication failed for user "u0"
。请问是否有解决方案? - Subbuecho -n
命令来去除换行符,否则会影响哈希结果。在 SQL 中,我使用了select md5(...)
命令。 - Daniel VéritéU=u0; P=foobar; echo -n md5; echo -n $P$U | md5sum | cut -d' ' -f1
- pbnelson