Generate random password

Robert Fitzpatrick wrote:

Can anyone suggest how one might be able to do this? I want to be able
to generate an 8 character random password for users in their password
field. Perhaps through the default setting of the field or a trigger
function. I found the following, but is there anything that can be used
on both Windows and *nix or can this be used on Windows somehow?

http://pgfoundry.org/forum/forum.php?forum_id=994

Here's a simple function I've used so I can do it in the database.
Written with help from this very list ;-)

CREATE OR REPLACE FUNCTION gen_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;

Then you can do stuff like:

update people set pp_password = gen_password() where pp_password is null;

Jeff

Robert Fitzpatrick wrote:

Can anyone suggest how one might be able to do this? I want to be able
to generate an 8 character random password for users in their password
field. Perhaps through the default setting of the field or a trigger
function. I found the following, but is there anything that can be used
on both Windows and *nix or can this be used on Windows somehow?

http://pgfoundry.org/forum/forum.php?forum_id=994

If you don't need something that's actually secure, you ca ndo it
trivially in PL/pgsql.Here's what I use, for example:

CREATE FUNCTION generate_random_password() RETURNS text
AS $$
DECLARE
j int4;
result text;
allowed text;
allowed_len int4;
BEGIN
allowed := '23456789abcdefghjkmnpqrstuvwxyzABCDEFGHJKMNPQRSTUVWXYZ&#%@';
allowed_len := length(allowed);
result := '';
WHILE length(result) < 16 LOOP
j := int4(random() * allowed_len);
result := result || substr(allowed, j+1, 1);
END LOOP;
RETURN result;
END;
$$
LANGUAGE plpgsql;

It's not fast (but how many thousands are you generating per second
anyway), it's not "really secure", but it works :)

(Note that the function explicitly excludes characters like I, 1 and l
because they look too similar)

//Magnus