0

How is it possible to create a function in EDB PostgreSQL to check the password?

The password should contain:

  • at least 1 upper case
  • at least 1 lower case
  • at least 1 digit
  • at least 8 characters long
  • at least 1 special character

So far I searched and got 

CREATE OR REPLACE FUNCTION verify_password(user_name varchar2, new_password varchar2, old_password varchar2)
  RETURN boolean IMMUTABLE
IS
BEGIN
  IF (length(new_password) < 5)
  THEN
    raise_application_error(-20001, 'too short');
  END IF;

  IF substring(new_password FROM old_password) IS NOT NULL
  THEN
    raise_application_error(-20002, 'includes old password');
  END IF;
  RETURN true;
END;

Above functions works fine but I am not able to add checks for uppercase, lowercase and special character.

If I add lines on checks for uppercase, lowercase and special character I am able to create the function but when password checks comes in it does not seem to work.

SQLDBA
  • 13
  • 5
  • `varchar2`, `raise_application_error`: this is rather an Oracle solution. And a poor one, because it assumes that you store passwords as plain text (see the `substring` part). In Postgres you can use f.ex. the [`pgcrypto`](https://www.postgresql.org/docs/current/static/pgcrypto.html#id-1.11.7.35.6) module to hash your passwords (you can still check if the new one matches with old one entirely, but no substring searches). – pozs Apr 11 '18 at 19:09
  • @pozs: EnterpriseDB does support Oracle's PL/SQL –  Apr 12 '18 at 05:56
  • You should show us the code that does not work (the one that checks for upper/lowercase and special characters). –  Apr 12 '18 at 05:57
  • @a_horse_with_no_name I see. Off: still I don't think storing passwords as plain text is a good idea. – pozs Apr 12 '18 at 17:16
  • any one having scripts to create function for password verification with suits below mentioned points The password should contain: at least 1 upper case at least 1 lower case at least 1 digit at least 8 characters long at least 1 special character – SQLDBA Apr 13 '18 at 11:11

0 Answers0