database PostgreSQL 中密码的数据类型是什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1200326/
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
What is the datatype for a password in PostgreSQL?
提问by Léo Léopold Hertz ??
I readthat there are datatypes which do encryption so passwords are secured in your database.
我读到有数据类型可以加密，因此密码在您的数据库中是安全的。
I use at the moment varcharto store passwords. I have had the idea that I should somehow apply a SHA-512 function to the password and put that data somewhere such that the plain text password is removed.
我现在使用varchar来存储密码。我有一个想法，我应该以某种方式将 SHA-512 函数应用于密码并将该数据放在某处，以便删除纯文本密码。
However, the datatype in Perl suggests me that there are a better way in PostgreSQL than varchar.
但是，Perl 中的数据类型表明，PostgreSQL 中有比 varchar 更好的方法。
What is the datatype for a password in PostgreSQL?
PostgreSQL 中密码的数据类型是什么？
采纳答案by Greg Hewgill
Jeff has a good article titled You're Probably Storing Passwords Incorrectly. This article discusses various ways of storing passwords in databases, and some of the common pitfalls that you may run into. In particular, it discusses the use of hashing algorithms, rainbow tables, and the use of "salt" to reduce the risk of a compromised password file.
Jeff 有一篇很好的文章，标题为您可能错误地存储密码。本文讨论了在数据库中存储密码的各种方法，以及您可能遇到的一些常见陷阱。特别地，它讨论了散列算法、彩虹表的使用以及“盐”的使用来降低密码文件被盗的风险。
The use of the varchardata type is perfectly suitable for storing a properly hashed password. For example, here is part of my actual account record from a production database:
varchar数据类型的使用非常适合存储正确散列的密码。例如，这里是我来自生产数据库的实际帐户记录的一部分：
=> select account_id, email, salt, passhash from account where email = '[email protected]'; account_id | email | salt | passhash ------------+------------------+------------------+------------------------------------------ 1 | [email protected] | GFR9uT4N4Tzl3vnK | 2c2bf00079a6d49a8f7fb17cefb52fdb41a4b043 (1 row)
In this case, passhashis the hex representation of the SHA-1 of the salt concatenated with my password.
在这种情况下，passhash是与我的密码连接的盐的 SHA-1 的十六进制表示。
回答by Nery Jr
Install "chkpass module"
安装“chkpass 模块”
This module implements a data type chkpass that is designed for storing encrypted passwords. You need to install the postgresql contrib package and run CREATE EXTENSION command to install .
该模块实现了一种数据类型 chkpass，旨在存储加密密码。您需要安装 postgresql contrib 包并运行 CREATE EXTENSION 命令来安装 .
In Ubuntu 12.04 it would go like this:
在 Ubuntu 12.04 中，它会是这样的：
sudo apt-get install postgresql-contrib
Restart the postgresql server:
重启 postgresql 服务器：
sudo /etc/init.d/postgresql restart
All available extension are in:
所有可用的扩展名都在：
/opt/PostgreSQL/9.5/share/postgresql/extension/
Now you can run the CREATE EXTENSION command.
现在您可以运行 CREATE EXTENSION 命令。
Example:
例子：
CREATE EXTENSION chkpass;
CREATE TABLE accounts (username varchar(100), password chkpass);
INSERT INTO accounts(username, "password") VALUES ('user1', 'pass1');
INSERT INTO accounts(username, "password") VALUES ('user2', 'pass2');
select * from accounts where password='pass2';
Returns
退货
username | password
---------------------------
"user2" | ":Sy8pO3795PW/k"
回答by Murali Mohan
Postgres version 9.4+ can get this done in a smarter and securer way using pgcrypto extension as explained at: http://www.meetspaceapp.com/2016/04/12/passwords-postgresql-pgcrypto.html
Postgres 9.4+ 版可以使用 pgcrypto 扩展以更智能、更安全的方式完成此操作，如下所述：http: //www.meetspaceapp.com/2016/04/12/passwords-postgresql-pgcrypto.html
