Generate random password PostgreSQL

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.

Category: plpgsql, postgresql, programming, stored procedures 7 comments »

7 Responses to “Generate random password PostgreSQL”

  1. Dennis

    ” 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.”

    A LOT SMALLER? It’s not even 1K of code. Come on, servers are fast enough these days and 1K of code is processed withing a couple of miliseconds, really. It’s good to let some things being generated by the database.

    A database should be a base for a consistent place for data, such as passwords.

  2. Berry Langerak

    > A LOT SMALLER? It’s not even 1K of code. Come on, servers are fast
    > enough these days and 1K of code is processed withing a couple of
    > miliseconds, really. It’s good to let some things being generated by the
    > database.

    I don’t really understand your point? I’m just saying the *application*-side of things is smaller than normal. By stuffing business logic in the database, you don’t have to do it in the application, which makes your application code a lot cleaner. In the “older” days, I had to fumble with arrays of data a lot, and those days are definitely over. The less code you have, the less bugs it can contain.

    > A database should be a base for a consistent place for data, such as
    > passwords.

    Not just that: a database can do a heck of a lot more than just simply storing data and keeping the data consistent: you can stuff the business logic in there, too.

  3. Richard

    Why not directly initialize your variables with the values you already initialize them with? ;)

    DECLARE _password VARCHAR ::= ”;

  4. Berry Langerak

    Hey Richard,

    > Why not directly initialize your variables with the values you
    > already initialize them with? ;)

    Because I didn’t know I could. FTFY though, thanks for the information!

  5. Marcus

    Hi…

    For me, advantages of placing your logic in the DB:
    1) No ORM.
    2) SQL is really powerful once you know it well. No marshalling needed.
    3) Views are an excellent way of encoding business logic (procs should be small).
    4) No ORM.
    5) Easier to tune.
    6) No ORM.

    Disadvantages of DB:
    1) Not everything can be done in a DB and the boundary hurts.
    2) Your schema design skills have to be good.
    3) Not everything can be done in a DB and the boundary hurts.
    4) You have to sync version control with the DB code on deployment.
    6) Not everything can be done in a DB and the boundary hurts.
    5) You don’t want to find your logic split between code and DB. It’s all or nothing.
    6) Not everything can be done in a DB and the boundary hurts.

    YMMV as they say.

    yours, Marcus

  6. Alex Smith

    This SQL generates a 6 chars random password (see last line if you want to generate a bigger/smaller one) using A table’s charset:

    SELECT ARRAY_TO_STRING(ARRAY_AGG(SUBSTR(A.chars, (RANDOM()*1000)::int%(LENGTH(A.chars))+1, 1)), ”)
    FROM (SELECT ‘ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789!@#$%*’::varchar AS chars) A,
    (SELECT generate_series(1, 6, 1) AS line) B

    I think it also solves the problem.

    Tested in postgresql 8.4.

  7. Simeó Reig

    If you want a more easy way:

    SELECT substr(md5(random()::text),1,10);

    even more easy

    SELECT md5(random()::text);

    Nice Entry


Leave a Reply



Back to top