Berry Langerak

EDIT: it seems I’ve made a mistake in the previous versions of the script. I was multiplying the double precision I got from calling random( ) with the length( ) of the seed, and casting that to an integer. Unfortunately, I’ve found out that random can return a value < 0.1, which means that ( 0.09 * 4 ) = 0, which means that the 0th character was substring( )‘ed and appended. Alas, that doesn’t work. By taking the smallest integer not lower than the value returned by using ceil( ), this problem is fixed.

Also, I’ve managed to make the whole procedure a little quicker by appending the numeric values after instead of in the loop, and that has certainly sped things up.The code has been updated.

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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64

--
-- 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 := 'aeiou';
 _consonants VARCHAR := 'bcdfghjkmnpqrstvwxyz'; -- left out 'l', because it can appear as "I".
 _numbers VARCHAR := '23456789'; -- Left out 0 and 1 because they can appear as O and I.
 
BEGIN
    -- Create a loop for the length of the password (-2, as two numbers will be appended).
    FOR _counter IN 1..( _length - 2 ) LOOP
        _password = _password || CASE
           -- Add a vowel to the password for even values of _counter.
           WHEN ( _counter % 2 = 0 ) THEN SUBSTRING( _vowels, CEIL( RANDOM( ) * LENGTH( _vowels ) )::INT, 1 )
 
           -- Add a consonant to the password for odd values of _counter.
           ELSE SUBSTRING( _consonants, CEIL( RANDOM( ) * LENGTH( _consonants ) )::INT, 1 )
        END;
    END LOOP;
 
    -- Append two randomly selected numbers to the password.
    _password = _password || SUBSTRING( _numbers, CEIL( RANDOM( ) * LENGTH( _numbers ) )::INT, 1 );
    _password = _password || SUBSTRING( _numbers, CEIL( RANDOM( ) * LENGTH( _numbers ) )::INT, 1 );
 
    -- Finally, return the created password.
    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
 
    FOR _counter IN 1.._length LOOP
       _password = _password || SUBSTRING( _seed, CEIL( RANDOM( ) * LENGTH( _seed ) )::INT, 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.