如何在 postgresql 中散列密码? [英] How can I hash passwords in postgresql?

问题描述

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

那么如何在 postgresql 中散列密码(使用一些盐)?

解决方案

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

>

推理

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

Debian/Ubuntu 安装包

sudo apt-get install postgresql//(当然)sudo apt-get install postgresql-contrib libpq-dev//(获取 bcrypt、crypt() 和 gen_salt())sudo apt-get install php5-pgsql//(如果你在 php 中使用 postgresql，则可选)

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

//首先创建你的数据库，然后:cd `pg_config --sharedir`//移动到保存这些脚本的 postgres 目录.echo "创建扩展 pgcrypto" |psql -d yOuRdATaBaSeNaMe//启用 pgcrypo 扩展

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

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

select * from accounts where password_hash = crypt(:pass, password_hash);//(注意现有散列如何用作其自己的个性化盐)

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

insert into accounts (password) values crypt(:password, gen_salt('bf', 8));//(8是工作系数)

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

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

注意登录

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

总结

如果可以，请使用 php bcrypt，它会减少密码保持未散列的时间.尝试确保您的 linux 系统在 crypt() 中安装了 bcrypt，以确保性能良好.强烈建议至少升级到 php 5.3.7+，因为 php 的实现从 php 5.3.0 到 5.3.6.9 有轻微的错误，并且在 php 5.2.9 中没有警告地不恰当地回退到损坏的 DES和更低.

如果你想要/需要 in-postgres 哈希，安装 bcrypt 是可行的方法，因为默认安装的哈希是旧的和损坏的(md5 等).

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

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

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.

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

解决方案

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.

Debian/Ubuntu install packages

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

// 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

Compare :pass to existing hash with:

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:

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

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. Generally that 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+)

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 plaintext from 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.

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 DES without warning in php 5.2.9 and lower.

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).

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

这篇关于如何在 postgresql 中散列密码?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！