如何在 postgresql 中散列密码？

How can I hash passwords in postgresql?

提问by Kzqai

I need to hash some passwords with salt on postgresql, and I haven't been able to find any relevant documentation on how to get that done.

我需要在 postgresql 上用 salt 散列一些密码，但我找不到任何有关如何完成的相关文档。

So how can I hash passwords (with some salts) in postgresql?

那么如何在 postgresql 中散列密码（使用一些盐）？

回答by Kzqai

It's been a while since I asked this question, and I'm much more familiar with the cryptographic theory now, so here is the more modern approach:

自从我问这个问题已经有一段时间了，我现在对密码理论更加熟悉了，所以这里是更现代的方法：

Reasoning

推理

• Don't use md5. Don't use a single cycle of sha-family quick hashes. Quick hashes help attackers, so you don't want that.
• Use a resource-intensive hash, like bcrypt, instead. Bcrypt is time tested and scales up to be future-proof-able.
• Don't bother rolling your own salt, you might screw up your own security or portability, rely on gen_salt() to generate it's awesome unique-to-each-use salts on it's own.
• In general, don't be an idiot, don't try to write your own homegrown crypto, just use what smart people have provided.

• 不要使用 md5。不要使用单一循环的 sha-family 快速散列。快速散列有助于攻击者，所以你不希望那样。
• 改用资源密集型哈希，如 bcrypt。Bcrypt 是经过时间考验的，并且可以扩展以适应未来的需求。
• 不要费心滚动你自己的盐，你可能会搞砸你自己的安全性或可移植性，依靠 gen_salt() 来生成它自己独特的每次使用的盐。
• 一般来说，不要当白痴，不要尝试编写自己的本地加密货币，只需使用聪明人提供的东西即可。

Debian/Ubuntu install packages

Debian/Ubuntu 安装包

sudo apt-get install postgresql   // (of course)
sudo apt-get install postgresql-contrib libpq-dev   // (gets bcrypt, crypt() and gen_salt())
sudo apt-get install php5-pgsql   // (optional if you're using postgresql with php)

Activate crypt() and bcrypt in postgresql in your database

在你的数据库中的 postgresql 中激活 crypt() 和 bcrypt

// Create your database first, then:
cd `pg_config --sharedir` // Move to the postgres directory that holds these scripts.
echo "create extension pgcrypto" | psql -d yOuRdATaBaSeNaMe // enable the pgcrypo extension

Use crypt() and gen_salt() in queries

在查询中使用 crypt() 和 gen_salt()

Compare :pass to existing hash with:

将 :pass 与现有哈希进行比较：

select * from accounts where password_hash = crypt(:pass, password_hash);
//(note how the existing hash is used as its own individualized salt)

Create a hash of :password with a great random salt:

用一个很好的随机盐创建一个 :password 的哈希：

insert into accounts (password) values crypt(:password, gen_salt('bf', 8));
//(the 8 is the work factor)

From-in-Php bcrypt hashing is slightly preferrable

From-in-Php bcrypt 哈希稍微更可取

There are password_*functions in php 5.5 and above that allow trivially simple password hashing with bcrypt (about time!), and there is a backward compatibility library for versions below that. Generallythat hashing falls back to wrapping a linux system call for lower CPU usage anyway, though you may want to ensure it's installed on your server. See: https://github.com/ircmaxell/password_compat(requires php 5.3.7+)

password_*php 5.5 及更高版本中有一些函数允许使用 bcrypt 进行简单的密码散列（大约是时候了！），并且有一个用于低于该版本的向后兼容库。 通常，散列返回到包装 linux 系统调用以降低 CPU 使用率，尽管您可能希望确保它已安装在您的服务器上。请参阅：https: //github.com/ircmaxell/password_compat（需要 php 5.3.7+）

Be careful of logging

小心记录

Note that with pg_crypto, the passwords are in plaintext all during the transmission from the browser, to php, to the database. This means they can be logged in plaintextfrom queries if you're not careful with your database logs. e.g. having a postgresql slow query log could catch and log the password from a login query in progress.

请注意，使用 pg_crypto，密码在从浏览器到 php 到数据库的传输过程中都是明文形式的。这意味着如果您不小心处理数据库日志，它们可以通过查询以纯文本形式记录。例如，拥有 postgresql 慢查询日志可以从正在进行的登录查询中捕获并记录密码。

In Summary

总之

Use php bcrypt if you can, it'll lessen the time that the password remains unhashed. Try to ensure your linux system has bcrypt installed in it's crypt()so that is performant. Upgrade to at least php 5.3.7+ is highly recommended as php's implementation is slightly buggy from php 5.3.0 to 5.3.6.9, and inappropriately falls back to the broken DESwithout warning in php 5.2.9 and lower.

如果可以，请使用 php bcrypt，它会减少密码保持未散列的时间。尝试确保您的 linux 系统中安装了 bcrypt，crypt()以便提高性能。强烈建议至少升级到 php 5.3.7+，因为 php 的实现从 php 5.3.0 到 5.3.6.9 略有缺陷，并且DES在 php 5.2.9 及更低版本中不适当地退回到已损坏的版本而没有警告。

If you want/need in-postgres hashing, installing bcrypt is the way to go, as the default installed hashes are old and broken (md5, etc).

如果你想要/需要 in-postgres 散列，安装 bcrypt 是可行的方法，因为默认安装的散列是旧的和损坏的（md5 等）。

Here are references for more reading on the topic:

以下是有关该主题的更多阅读参考资料：

• http://codahale.com/how-to-safely-store-a-password/
• http://www.postgresql.org/docs/9.2/static/pgcrypto.html
• https://github.com/ircmaxell/password_compat

• http://codahale.com/how-to-safely-store-a-password/
• http://www.postgresql.org/docs/9.2/static/pgcrypto.html
• https://github.com/ircmaxell/password_compat

回答by rook

An application should hash its passwords using key derivation function like bcrypt or pbkdf2. Here is more information on secure password storage.

应用程序应使用密钥派生函数（如 bcrypt 或 pbkdf2）散列其密码。 以下是有关安全密码存储的更多信息。

... but sometimes you still need cryptogrpahic functions in a database.

...但有时您仍然需要数据库中的加密函数。

You can use pgcryptoto get access to sha256 which is a member of the sha2 family. Keep in mind sha0,sha1 md4, and md5 are very broken and should neverbe used for password hashes.

您可以使用pgcrypto访问 sha256，它是 sha2 家族的成员。请记住，sha0、sha1、md4 和 md5 非常损坏，不应用于密码哈希。

The following is an alright method of hashing passwords:

以下是散列密码的好方法：

digest("salt"||"password"||primary_key, "sha256")

The salt should be a large randomly generated value. This salt should be protected, because the hashes cannot be broken until the salt is recovered. If you are storing the salt in the database then it can be obtained along with the password hash using sql injection. Concatenating the primary key is used to prevent 2 people from having the same password hash even if they have the same password. Of course this system could be improved, but this is much better than most systems I have seen.

盐应该是一个很大的随机生成的值。这种盐应该受到保护，因为在盐恢复之前无法破坏散列。如果您将盐存储在数据库中，则可以使用 sql 注入与密码哈希一起获取。连接主键用于防止 2 个人即使密码相同也无法使用相同的密码哈希。当然这个系统可以改进，但这比我见过的大多数系统要好得多。

Generally it is best to do hashing in your application before it hits the database. This is because querys can show up in logs, and if the database server was owned then they could enable logging to get clear text passwords.

通常最好在应用程序访问数据库之前在应用程序中进行散列。这是因为查询可以显示在日志中，如果数据库服务器是拥有的，那么他们可以启用日志记录以获取明文密码。

回答by Matej Puntar

Examples and documentation on: http://www.postgresql.org/docs/8.3/static/pgcrypto.html

示例和文档位于：http: //www.postgresql.org/docs/8.3/static/pgcrypto.html

UPDATE ... SET pswhash = crypt('new password', gen_salt('md5'));

SELECT pswhash = crypt('entered password', pswhash) FROM ... ;