Обсуждение: Generating random values.
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
> 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
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/
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.
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 > > > >
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 ===
On 8/17/05, A. Kretschmer <akretschmer@despammed.com> wrote: > select substring(md5(random()) from 5 for 15); Thanks everybody, this solution will fullfill my needs... ;) Sincerely, Fernando Lujan
jd@commandprompt.com ("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.
Edmund wrote:
>jd@commandprompt.com ("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
> 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
On Wed, Aug 17, 2005 at 15:54:40 -0600, Edmund <ebacon-xlii@onesystem.com> 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.
> 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
On 8/18/05, Mike Nolan <nolan@gw.tssi.com> 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
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 > > > >