Generating random values.

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
14 messages Options
Reply | Threaded
Open this post in threaded view
|

Generating random values.

Fernando Lujan
Hi folks,

I have a table wich contains my users... I want to insert to each user
a random password, so I need a random function. Is there such function
in Postgres? I just found the RANDOM which generates values between
0.0 and 1.0.

Any help or suggestion will be appreciated. :)

Fernando Lujan

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match
Reply | Threaded
Open this post in threaded view
|

Re: Generating random values.

Joshua D. Drake
Fernando Lujan wrote:
> Hi folks,
>
> I have a table wich contains my users... I want to insert to each user
> a random password, so I need a random function. Is there such function
> in Postgres? I just found the RANDOM which generates values between
> 0.0 and 1.0.
>
> Any help or suggestion will be appreciated. :)

I would do someting like:

select substring(md5(random() || random()), 5, 8);

Sincerely,

Joshua D. Drkae




>
> Fernando Lujan
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match


--
Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240
PostgreSQL Replication, Consulting, Custom Programming, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to [hidden email] so that your
       message can get through to the mailing list cleanly
Reply | Threaded
Open this post in threaded view
|

Re: Generating random values.

Mike Nolan
In reply to this post by Fernando Lujan
> I have a table wich contains my users... I want to insert to each user
> a random password, so I need a random function. Is there such function
> in Postgres? I just found the RANDOM which generates values between
> 0.0 and 1.0.

If you multiply that random number by a large integer and then truncate
or round the result, you will get a random integer between 0 and
whatever you use as a multiplier.

For example, 'select round(random() * 999999)' will generate a six digit
random integer.  

Whether that's a good password generator is a completely different subject,
one for which there is no 'best' answer.  

The more arbitrary the password, the more likely the user is to write it
down or have it saved in a password file on their computer, both of which
tend to defeat the purpose of having passwords in the first place.

I find some rather silly password 'standards' out there.  For example,
one company I've done business with requires that their passwords be
EXACTLY six characters long, of which two must be UPPER CASE letters,
two must be lower case letters and two must be numbers.

I have a short PHP program which generates (IMHO) better random passwords,
using several random numbers to select two short words (2-4 characters)
from a dictionary file and adding in a number.  

Here are a few passwords generated by it just now:

caps270nods
egopegs326
mast659quip
semi607it
rots505hot

I usually generate 3 or 4 passwords then let the user pick one.  I often
screen the output so that I don't get passwords like this one:

pissbum560
--
Mike Nolan

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
Reply | Threaded
Open this post in threaded view
|

Re: Generating random values.

Sebastian Hennebrueder
In reply to this post by Fernando Lujan
Fernando Lujan schrieb:

>Hi folks,
>
>I have a table wich contains my users... I want to insert to each user
>a random password, so I need a random function. Is there such function
>in Postgres? I just found the RANDOM which generates values between
>0.0 and 1.0.
>
>Any help or suggestion will be appreciated. :)
>
>Fernando Lujan
>
>---------------------------(end of broadcast)---------------------------
>TIP 9: In versions below 8.0, the planner will ignore your desire to
>       choose an index scan if your joining column's datatypes do not
>       match
>
>
>  
>
Create an array of characters and numbers.
Estimating the array size at 30
Calculate something like round(random * 30) and fetch a character from
the array.
Repeat this for each character

--
Best Regards / Viele Grüße

Sebastian Hennebrueder

----

http://www.laliluna.de

Tutorials for JSP, JavaServer Faces, Struts, Hibernate and EJB

Get support, education and consulting for these technologies - uncomplicated and cheap.


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq
Reply | Threaded
Open this post in threaded view
|

Re: [despammed] Generating random values.

Andreas Kretschmer
In reply to this post by Fernando Lujan
am  17.08.2005, um 13:48:38 -0300 mailte Fernando Lujan folgendes:
> Hi folks,
>
> I have a table wich contains my users... I want to insert to each user
> a random password, so I need a random function. Is there such function
> in Postgres? I just found the RANDOM which generates values between
> 0.0 and 1.0.
>
> Any help or suggestion will be appreciated. :)

select substring(md5(random()) from 5 for 15);


Regards, Andreas
--
Andreas Kretschmer    (Kontakt: siehe Header)
Heynitz:  035242/47212,      D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
 ===    Schollglas Unternehmensgruppe    ===

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org
Reply | Threaded
Open this post in threaded view
|

Re: Generating random values.

Chris Travers-2
In reply to this post by Fernando Lujan
Hi Fernando;

I think that PL/Perl would be the easiest language to use in this case.  
However, you could use PL/PGSQL and do something like:
1)  Generate a random number (RANDOM()) and multiply it by a base value,
and add something to it to bring it within a certain range.
2)  Look up the ASCII character associated with the random number.  I
forget the function name, but it is listed, I think, under string
functions in the docs.
3)  Concatenate this onto the end of your string.  The operator is ||.

Doing this with a fixed-length password would be extremely easy.  If you
have to do it with a variable length password, then the logic will need
to be a loop.  THis is probably the cleanest way to do it.  You could
probably even do this with ANSI SQL functions with a clever case
statement (I am assuming that a function is allowed to call itself).

Something like:

create function random_string(int, varchar) returns varchar AS '
select
CASE WHEN length($2) < $1  THEN random_string($2 || chr((random() *
(ascii_max - ascii_min))::int + ascii_min), $1)
ELSE $2
END
' LANGUAGE SQL;

Of course replace ascii_max and ascii_min with the maximum and minimum
ascii values you want it to use.

You can then create another function like this:
CREATE FUNCTION random_string(int) returns varchar AS '
SELECT random_string($1, '''');
' LANGUAGE SQL;

This becomes much harder when working with Unicode, I think....

Best Wishes,
Chris Travers
Metatron Technology Consulting

Fernando Lujan wrote:

>Hi folks,
>
>I have a table wich contains my users... I want to insert to each user
>a random password, so I need a random function. Is there such function
>in Postgres? I just found the RANDOM which generates values between
>0.0 and 1.0.
>
>Any help or suggestion will be appreciated. :)
>
>Fernando Lujan
>
>---------------------------(end of broadcast)---------------------------
>TIP 9: In versions below 8.0, the planner will ignore your desire to
>       choose an index scan if your joining column's datatypes do not
>       match
>
>
>  
>


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org
Reply | Threaded
Open this post in threaded view
|

Re: [despammed] Generating random values.

Fernando Lujan
In reply to this post by Andreas Kretschmer
On 8/17/05, A. Kretschmer <[hidden email]> wrote:
 
> select substring(md5(random()) from 5 for 15);

Thanks everybody, this solution will fullfill my needs... ;)

Sincerely,

Fernando Lujan

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match
Reply | Threaded
Open this post in threaded view
|

Re: Generating random values.

Edmund Bacon
In reply to this post by Joshua D. Drake
[hidden email] ("Joshua D. Drake") writes:

> Fernando Lujan wrote:
> > Hi folks,
> > I have a table wich contains my users... I want to insert to each
> > user
> > a random password, so I need a random function. Is there such function
> > in Postgres? I just found the RANDOM which generates values between
> > 0.0 and 1.0.
> > Any help or suggestion will be appreciated. :)
>
> I would do someting like:
>
> select substring(md5(random() || random()), 5, 8);
>
> Sincerely,
>
> Joshua D. Drkae

Great! a simple, dumb program can generate all your passwords in very
quickly.  My 2.4 Ghz Pentium 4 did it in under 10 minutes.  A token set of
16 characters, and a fixed length of 8 charachters just isnt a very big
search space.


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
Reply | Threaded
Open this post in threaded view
|

Re: Generating random values.

Chris Travers-2
Edmund wrote:

>[hidden email] ("Joshua D. Drake") writes:
>
>  
>
>>Fernando Lujan wrote:
>>    
>>
>>>Hi folks,
>>>I have a table wich contains my users... I want to insert to each
>>>user
>>>a random password, so I need a random function. Is there such function
>>>in Postgres? I just found the RANDOM which generates values between
>>>0.0 and 1.0.
>>>Any help or suggestion will be appreciated. :)
>>>      
>>>
>>I would do someting like:
>>
>>select substring(md5(random() || random()), 5, 8);
>>
>>Sincerely,
>>
>>Joshua D. Drkae
>>    
>>
>
>Great! a simple, dumb program can generate all your passwords in very
>quickly.  My 2.4 Ghz Pentium 4 did it in under 10 minutes.  A token set of
>16 characters, and a fixed length of 8 charachters just isnt a very big
>search space.
>
>
>  
>
This is why I suggested a variable-length random string function.  But
this not as trivial to impliment.

Best Wishes,
Chris Travers


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match
Reply | Threaded
Open this post in threaded view
|

Re: Generating random values.

Mike Nolan
In reply to this post by Edmund Bacon
> Great! a simple, dumb program can generate all your passwords in very
> quickly.  My 2.4 Ghz Pentium 4 did it in under 10 minutes.  A token set of
> 16 characters, and a fixed length of 8 charachters just isnt a very big
> search space.

Your new password is 87&3jiwkjIJiwkjikmkq,^^2v12hqIwLbvCQQQi18152

Do not write it down or save it in a password manager, as doing so
creates security problems.
--
Mike Nolan

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
Reply | Threaded
Open this post in threaded view
|

Re: Generating random values.

Bruno Wolff III
In reply to this post by Edmund Bacon
On Wed, Aug 17, 2005 at 15:54:40 -0600,
  Edmund <[hidden email]> wrote:
>
> Great! a simple, dumb program can generate all your passwords in very
> quickly.  My 2.4 Ghz Pentium 4 did it in under 10 minutes.  A token set of
> 16 characters, and a fixed length of 8 charachters just isnt a very big
> search space.

If you are worried about that you shouldn't be using any old random number
generator either. I doubt the plain random function is cryptographicly
secure. You want want to use something like /dev/random as a source.

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to [hidden email] so that your
       message can get through to the mailing list cleanly
Reply | Threaded
Open this post in threaded view
|

Re: Generating random values.

Chris Travers
In reply to this post by Mike Nolan
Mike Nolan wrote:

> Your new password is 87&3jiwkjIJiwkjikmkq,^^2v12hqIwLbvCQQQi18152
>
>Do not write it down or save it in a password manager, as doing so
>creates security problems.
>  
>
There is a solution here.

Initialize passwords with a random string.  Flag these accounts as
"Password Temporary."  When the user logs in (via the app), prompt
him/her to change his/her password.  You can do this latter one by
creating a change_password() function as such....

CREATE FUNCTION change_password(varchar) returns bool as '
DECLARE
passwd ALIAS FOR $1;
query VARCHAR;
BEGIN
query := '' ALTER USER '' || SESSION_USER || ''WITH ENCRYPTED PASSWORD
'' || passwd;
EXECUTE  query;
RETURN TRUE;
END;
' LANGUAGE PLPGSQL SECURITY DEFINER;

This is off the top of my head, so something might need to be reordered.

This way you can let users choose their own passwords :-)

If you like you can put other checks in it to make sure you have any
three of uppercase/lowercase/numbers/other characters or whatever else
you like.

Word of caution.  DO NOT USE CURRENT_USER instead of SESSION_USER of
else everyone will have access to the superuser account which created
this function :-)

Best Wishes,
Chris Travers
Metatron Technology Consulting

>--
>Mike Nolan
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: Don't 'kill -9' the postmaster
>
>
>  
>



---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to [hidden email] so that your
       message can get through to the mailing list cleanly

chris.vcf (135 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Generating random values.

Mike Nolan
> This way you can let users choose their own passwords :-)
>
> If you like you can put other checks in it to make sure you have any
> three of uppercase/lowercase/numbers/other characters or whatever else
> you like.

Allowing users to choose their own permanent passwords does not make them
any more secure, though it would hopefully make them easier to remember.  

Users tend to choose passwords that are easy to guess, and they tend to
use the same password for multiple accounts.  

As I indicated in my original response, there is no best answer to the
issue of password choices, though there are probably a few 'worst'
answers.  :-)

Once someone has established a password scheme, either randomly generated
or user selected, it should not be that difficult to write routines to
generate acceptable passwords or to enforce standards for user-generated
passwords.
--
Mike Nolan


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
Reply | Threaded
Open this post in threaded view
|

Re: Generating random values.

Fernando Lujan
On 8/18/05, Mike Nolan <[hidden email]> wrote:

> As I indicated in my original response, there is no best answer to the
> issue of password choices, though there are probably a few 'worst'
> answers.  :-)
>
> Once someone has established a password scheme, either randomly generated
> or user selected, it should not be that difficult to write routines to
> generate acceptable passwords or to enforce standards for user-generated
> passwords.

Good point Mike. In my case, for instance, the users will have the
opportunity to chance their password. There's no problems with
passwords which a user could remember. At least, the user will not
trouble you with a password reset requirement. :D

Thanks for all replies and suggestions.

Fernando Lujan

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to [hidden email] so that your
       message can get through to the mailing list cleanly