Error using PostgreSQL digest function trying to check password

Question

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?

hruske hruske · Accepted Answer · 2016-03-02T22:57:18

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.

Error using PostgreSQL digest function trying to check password

1 Answers