Generate random password PostgreSQL
While building our latest and greatest application, we needed to generate initial passwords for the users. This is not uncommon, and I’ve been generating passwords for quite some time using a PHP class called RandomString. However, in the last few months, I have been working with PostgreSQL more and more, and I started to write business logic into the database in the form of plpgsql stored procedures, instead of plain old PHP, as it turns out to make the application itself a lot smaller, leaner, cleaner and – most of all – more readable.
Adding a batch of users was easy: the usernames were derived from several other variables, so there was no need for PHP to get involved in creating the users and their passwords. So, I wanted to generate the passwords using a stored procedure and I don’t like doing the work myself, so I started to Google. Unfortunately, I couldn’t find even a snippet of code while searching the internet, and I’m not that well versed in plpgsql yet, so I decided to ask my geek-friends over at PFZ – PHP Community.
Vincent – also known as PgGuru – had a very nice solution which would allow you to create passwords based on a seed and a length you could pass to the procedure, but I wanted passwords in the format that he suggested earlier: “BABABAB11“. So I started to hack away at his example, and came up with two stored procedures that you can use to generate random, readable passwords straight in the database, no interference of the client required. Without further ado:
--
-- Generates a random, human readable password with the format "BABABA00".
-- It will alternate between a consonant or a vowel, and appends two numbers
-- at the end of the password and then return it.
--
CREATE OR REPLACE FUNCTION generatePassword (
_length INTEGER
) RETURNS varchar AS $$
DECLARE
_counter INTEGER;
_password VARCHAR;
_vowels VARCHAR;
_consonants VARCHAR;
_numbers VARCHAR;
BEGIN
_password = '';
_vowels = 'aeiou';
_consonants = 'bcdfghjkmnpqrstvwxyz'; -- left out 'l', because it can appear as "I".
_numbers = '23456789'; -- Left out 0 and 1 because they can appear as O and I.
FOR _counter IN 1.._length LOOP
_password = _password || CASE
WHEN _counter > ( _length - 2 ) THEN SUBSTRING( _numbers, CAST( RANDOM( ) * LENGTH( _numbers ) AS INTEGER ), 1 )
WHEN ( _counter % 2 = 0 ) THEN SUBSTRING( _vowels, CAST( RANDOM( ) * LENGTH( _vowels ) AS INTEGER ), 1 )
ELSE SUBSTRING( _consonants, CAST( RANDOM( ) * LENGTH( _consonants ) AS INTEGER ), 1 )
END;
END LOOP;
RETURN _password;
END;
$$ LANGUAGE 'plpgsql';
--
-- Generates a random password out of the seed passed to it.
--
CREATE OR REPLACE FUNCTION generatePassword (
_length INTEGER,
_seed VARCHAR
) RETURNS varchar AS $$
DECLARE
_counter INTEGER;
_password VARCHAR;
BEGIN
_password = '';
FOR _counter IN 1.._length LOOP
_password = _password || SUBSTRING( _seed, CAST( RANDOM( ) * LENGTH( _seed ) AS INTEGER ), 1 );
END LOOP;
RETURN _password;
END;
$$ LANGUAGE 'plpgsql';
--
-- Example of usage:
--
SELECT generatePassword( 8 );
SELECT generatePassword( 8, 'abc'::text );
So, there you have it. I hope someone else can benefit from these stored procedures, actually finding them instead of void while doing a Google search 
Cheers,
Berry.
2 comments » | plpgsql, postgresql, programming, stored procedures
