How to encrypt passwords using PostgreSQL?

Question

I have some problems with coding passwords, how can I do this. Encoding Type MD5

digest(data text, type text) returns bytea; CREATE OR REPLACE FUNCTION md(bytea) returns text AS $$ SELECT encode(digest($1, 'sha1'), 'md5') $$ LANGUAGE SQL STRICT IMMUTABLE; INSERT INTO "login"(login, password, employee_id) VALUES ( 'email',crypt('password', md('md5')), 1); 

*** Error ***

 ERROR: syntax error at or near "digest" SQL state: 42601 Character: 1 

Answer 1

digest(data text, type text) returns bytea; Invalid syntax.

Instead, I recommend using bcrypt . No additional function definitions are required:

 INSERT into "login" (login, password, employee_id) VALUES ('email',crypt('password', gen_salt('bf')); 

Further...

 UPDATE table SET password = crypt('password',gen_salt('bf')) 

And password verification:

 SELECT ... FROM table WHERE password is NOT NULL AND password = crypt('password-to-test',password); 

Bcrypt is recommended by Crafted Software and Jeff Atwood . Pgcrypto white papers may also be of interest.

Answer 2

I know this question is old, but for those who have the same problem.

Step 1: first check if prcrypto is installed or not

 select e.extname, n.nspname from pg_catalog.pg_extension e left join pg_catalog.pg_namespace n on n.oid = e.extnamespace; 

Step 2: if it is not installed, create an extension

CREATE EXTENSION IF NOT EXISTS pgcrypto;

Step 3: Computes the binary hash of the given data.

  CREATE OR REPLACE FUNCTION sha1(bytea) returns text AS $$ SELECT encode(digest($1, 'sha1'), 'hex') $$ LANGUAGE SQL STRICT IMMUTABLE; 

Last step:

Also use the encoding function if you want a digest as a hexadecimal string

SELECT encode(digest('blue', 'sha1'), 'hex');

or

directly sha('blue')