CODEWITHSUNDEEP
databasepostgresqltypes
Léo Léopold Hertz 준영
Léo Léopold Hertz 준영

Reputation: 141320

What is the datatype for a password in PostgreSQL?

I read that there are datatypes which do encryption so passwords are secured in your database.

I use at the moment varchar to 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.

However, the datatype in Perl suggests me that there are a better way in PostgreSQL than varchar.

What is the datatype for a password in PostgreSQL?

Upvotes: 89

Views: 122711

Answers (3)

Murali Mohan
Murali Mohan

Reputation: 759

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

Upvotes: 7

Nery Jr
Nery Jr

Reputation: 4009

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"

Upvotes: 10

Greg Hewgill
Greg Hewgill

Reputation: 993901

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.

Upvotes: 94

Related Questions