I have some problems with encoding passwords,how can I do it. Type of encoding 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
digest(data text, type text) returns bytea; is not valid syntax.
I recommend using bcrypt instead. No additional function definitions are required:
INSERT into "login" (login, password, employee_id)
VALUES ('email',crypt('password', gen_salt('bf'));
Later...
UPDATE table SET password = crypt('password',gen_salt('bf'))
And checking the password:
SELECT ... FROM table
WHERE password is NOT NULL
AND password = crypt('password-to-test',password);
Bcrypt is recommended by Crafted Software and Jeff Atwood. The official pgcrypto docs may also be of interest.
I know this question is old but for those who having the same issue.
Step 1: first check whether 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 then create extension
CREATE EXTENSION IF NOT EXISTS pgcrypto;
Step 3: Computes a 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 encode function If you want the digest as a hexadecimal string
SELECT encode(digest('blue', 'sha1'), 'hex');
or
directly sha('blue')
