0

I'm using PostgreSQL 9.5 and trying to comprehend how to store passwords with digest and crypt functions within the pgcrypto module. I have a table like this:

CREATE TABLE "usuarios" (
"id" integer NOT NULL ,
"password" varchar(120),
CONSTRAINT usuarios_pk PRIMARY KEY ("id")
);

I succesfully saved the first row with this query:

INSERT INTO public.usuarios VALUES (DEFAULT,digest('somesalt' || 'mypass','sha256'));

I took the idea of the global salt and the digest using the sha256 algorithm reading this post and the official documentation following the example using crypt()

My problem is, when I try to compare the password in pgAdmin with the following query:

SELECT (usuarios.password = digest('somesalt' || 'mypass','sha256')) AS Match FROM usuarios;

I get an error mismatching password type( varchar) and digest type (bytea). I have been looking around for a workaround or how the cast would be but cannot find it or, maybe cannot understand what's under my nose. Any help?

Community
  • 1
  • 1
Edgar Sampere
  • 263
  • 4
  • 24

1 Answers1

0

You need to cast your digest, eg (note the ::varchar after digest):

SELECT (usuarios.password = digest('somesalt' || 'mypass','sha256')::varchar) AS Match FROM usuarios;

Also note that sha256 isn't really good for password hashing, and the PostgreSQL has better functions for password hashing. Also see this SO answer.

Community
  • 1
  • 1
hruske
  • 2,205
  • 19
  • 27
  • Edited. So it's better to use crypt than digest? Because obviously md5 is not an option so, if I use crypt, what could be a good algorithm to use? Or is it different postgres md5 than the original md5, is it ok to use it? – Edgar Sampere Mar 02 '16 at 22:59
  • Yes, if possible with bf algorithm. This table in documentation shows how resistant to bruteforcing the crypt algorithms are: http://www.postgresql.org/docs/current/static/pgcrypto.html#PGCRYPTO-HASH-SPEED-TABLE – hruske Mar 02 '16 at 23:01
  • Oh!, thanks, I was a little confused on which function to use – Edgar Sampere Mar 02 '16 at 23:02