PostgreSQL password generator

Question

I need some help with sql pass generator. I already have a function which returns 8 random characters, but I have to be sure, that there are lowercase and uppercase characters and numbers. Any advice? Here is my old function.

CREATE FUNCTION f_generate_password() RETURNS text AS $$
DECLARE
   password text;
   chars text;
BEGIN
   password := '';
   chars := 
'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
   FOR i IN 1..8 LOOP
     password := password || SUBSTRING(chars, 
ceil(random()*LENGTH(chars))::integer, 1);
   END LOOP;
   return password;
END;
$$
LANGUAGE plpgsql;

Martin Svancar · Accepted Answer · 2016-05-22 22:10:42Z

1

Here is a solution for these with a same or similar problem :)

CREATE OR REPLACE FUNCTION f_generate_password()
  RETURNS text AS
$BODY$
DECLARE
   vPassword text;
   chars text;
BEGIN
   vPassword := ''; 
   chars := 
'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
  WHILE((select COALESCE(substring(vPassword from '.*[a-z]+.*'),'')) = '' OR (select COALESCE(substring(vPassword from '.*[A-Z]+.*'),'')) = '' OR (select COALESCE(substring(vPassword from '.*[0-9]+.*'),'')) = '') LOOP
  vPassword := '';
   FOR i IN 1..8 LOOP
     vPassword := vPassword || SUBSTRING(chars, ceil(random()*LENGTH(chars))::integer, 1);
  END LOOP;
   END LOOP;
   return vPassword;
END;
$BODY$
  LANGUAGE plpgsql;

answered May 22, 2016 at 22:10

Martin Svancar

471 silver badge4 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

peter.petrov · Accepted Answer · 2016-05-22 20:24:51Z

-1

If you wonder about an algorithm... I don't know PostgreSQL syntax/dialect but you can e.g.:

1) pick 3 random positions (1 to 8) and put 3 random lower-case letters there
2) pick 3 random positions (from the remaining ones) and put 3 random upper-case letters there
3) put 2 random digits in the remaining two positions.

answered May 22, 2016 at 20:24

peter.petrov

39.6k23 gold badges114 silver badges193 bronze badges

1 Comment

Martin Svancar Over a year ago

Yop i knew that, i just wanted that in SQL syntax :) But i already did it...i paste here that code just to help other people with same problem :)

Collectives™ on Stack Overflow

PostgreSQL password generator

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related