Postgres 创建用户并使用加密密码

huangapple go评论64阅读模式
英文:

Postgres create user with encrypted password

问题

我有一个Flyway脚本,它在Postgres中创建一个带有加密密码的用户。我希望Flyway创建用户账户,然后在Spring Boot Java应用程序中使用该账户。我想要使用加密密码,因为脚本将存储在Git仓库中,我不想将其存储为明文。因此,该过程涉及将所需密码加密,以便将其添加到Flyway SQL脚本中,然后将密码添加为可以由Java应用程序读取为属性的秘密:


CREATE USER special_user ENCRYPTED PASSWORD 'ab2843414119e861a2202b6e77bb4c48b4d32172a23a0268e31b972985754e3e';

我相信Postgres实例正在使用默认的(sha256sum)进行加密:


show password_encryption;

> scram-sha-256

SQL中使用的密码如下加密:

➜  workspace echo Password! > input.txt
➜  workspace sha256sum input.txt 
ab2843414119e861a2202b6e77bb4c48b4d32172a23a0268e31b972985754e3e  input.txt

然而,当我尝试使用用户名+密码组合进行身份验证时,我收到错误消息。这似乎是因为密码不正确。如果我设置密码而不指定加密(明文),则用户名密码正常工作。

我做错了什么?我已经看到其他帖子中,加密密码前缀为SCRAM-SHA-256$4096:,但我尝试过这种方法,也没有成功(我在文档中找不到该格式的提及)。

谢谢

英文:

I have a flyway script which creates a User in postgres with an encrypted password. I want flyway to create the user account, and then use the account with a springboot java application. I want to use an encrypted password because the script will be stored in a git repository, and I dont want to store it as plain-text. The process therefore involves encrypting the desired password so it can be added to the flyway sql script, and then adding the password as a secret which can be read as a property by the java application:


CREATE USER special_user ENCRYPTED PASSWORD 'ab2843414119e861a2202b6e77bb4c48b4d32172a23a0268e31b972985754e3e';

I believe the postgres instance is using the default (sha256sum) for encryption:


show password_encryption;

> scram-sha-256

The password used in the sql was encrypted as follows:

➜  workspace echo Password! > input.txt
➜  workspace sha256sum input.txt 
ab2843414119e861a2202b6e77bb4c48b4d32172a23a0268e31b972985754e3e  input.txt

However when I try to use the username+password combination to authenticate I get errors. This appears to be due to the password being incorrect. If I set the password without specifying encryption (as plain text) the username-password works ok.

What am I doing wrong? I have seen other posts where the encrypted password is prefixed with SCRAM-SHA-256$4096: but I have tried this and it did not work either (I cant find any mention of the format in the documentation)

thanks

答案1

得分: 1

scram-sha-256不同于sha256sum。

CREATE语句中的密码是明文。ENCRYPTED PASSWORD中的'ENCRYPTED'关键词实际上没有任何作用,只是为了兼容性。系统会根据格式自动判断是否已加密,但该字符串的格式不正确,无法自动判断是否已加密。

构建适当的scram-sha-256校验器并不是一件简单的事情。显然,PostgreSQL本身有代码执行此操作,实现在前端libpq中的'pg_fe_scram_build_secret'中,作为'PQencryptPasswordConn'暴露。

如果只是一次性操作,您可以在psql中设置密码,然后查询pg_shadow以获取校验器。然后,您可以使用该校验器将其输入到新系统中,以设置新系统的密码。

英文:

scram-sha-256 is not the same thing as sha256sum.

That password in the CREATE statement is in plain text. The 'ENCRYPTED' keyword in ENCRYPTED PASSWORD doesn't actually do anything, it is just for compatibility. Rather the system automatically decides if it is encrypted or not just by inspecting the format, and that string is not in the correct format for it to be already encrypted.

It is not trivial to construct a proper scram-sha-256 verifier. Obviously there is code in PostgreSQL itself that does it, implemented in 'pg_fe_scram_build_secret' exposed in the front-end libpq as 'PQencryptPasswordConn'.

If it is just a one-time thing, you can set the password in psql, then query to pg_shadow to obtain the verifier. Then you can use that verifier to stuff it into a new system in order to set the password for that new system.

答案2

得分: 0

以下是翻译好的内容:

这个问题的答案包含在这个帖子这个帖子中。

我发现创建加密密码的最简单方法是使用明文密码创建用户:

create user special_user with password 'Password!';

然后从数据库中复制加密值:

SELECT rolname, rolpassword
FROM pg_catalog.pg_authid
where rolname = 'special_user';

...

special_user, SCRAM-SHA-256$4096:cxoSfsrgfD44vKa0UxC7zw==$X9Ff7zIz6RHAUK9RuBrCrFq0zgb/AFKWZHXfnytnIgw=:82LlxgQl+kv++jWYBzhHf+420gJtObUwQkw9FJYuFD8=

然后返回的值可以添加到Flyway SQL脚本中。这些步骤可以在本地运行的Postgres实例上执行,并且然后可以在不同的数据库上使用加密值。文档建议ServerKey包含在加密字符串中,但似乎不会引起任何问题。

英文:

The answer to this question was contained in this post
and this post

The easiest way I found to create the encrypted password is to create a user with the desired password as plain text:

create user special_user with password 'Password!';

then copy the encrypted value from the database:

SELECT rolname, rolpassword
FROM pg_catalog.pg_authid
where rolname = 'special_user';

...

special_user, SCRAM-SHA-256$4096:cxoSfsrgfD44vKa0UxC7zw==$X9Ff7zIz6RHAUK9RuBrCrFq0zgb/AFKWZHXfnytnIgw=:82LlxgQl+kv++jWYBzhHf+420gJtObUwQkw9FJYuFD8=

the value returned can then be added to the flyway sql script. These steps can be carried out on an instance of postgres running locally, and the encrypted value then used on a different database. The doc does suggest the ServerKey is contained in the encrypted string, but this doesnt appear to cause any problems.

huangapple
  • 本文由 发表于 2023年6月26日 22:15:34
  • 转载请务必保留本文链接:https://go.coder-hub.com/76557532.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定