CODEWITHSUNDEEP
sqldatabasepostgresqlsecuritypasswords
Martin Svancar
Martin Svancar

Reputation: 47

PostgreSQL password generator

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;

Upvotes: 2

Views: 3982

Answers (2)

Martin Svancar
Martin Svancar

Reputation: 47

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;

Upvotes: 1

peter.petrov
peter.petrov

Reputation: 39457

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.

Upvotes: -1

Related Questions