What is the datatype for a password in PostgreSQL?
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.
The use of the varchar data type is perfectly suitable for storing a properly hashed password. For example, here is part of my actual account record from a production database:
=> 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, passhash is the hex representation of the SHA-1 of the salt concatenated with my password.
Install "chkpass module"
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 .
In Ubuntu 12.04 it would go like this:
sudo apt-get install postgresql-contrib
Restart the postgresql server:
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.
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"
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