Re: Random string generator function in SQL - better than random()::text ?

On Tue, Dec 25, 2012 at 3:29 AM, Erik Huelsmann <..hidden..> wrote:

Looks like a good candidate to replace the current functionality. Are there multiple places we can use this generator?

Not at present.

On the other hand: the current passwords will remain valid for only 24 hours. If there's no other place we can use the generator: does that warrant the extra complexity?

Honestly a better approach might be to create a stored procedure and just expire the passwords forcing a reset. At that point we accomplish what we want to more directly and the passwords themselves don't matter. We might be able to expire other user accounts this way as we find useful as well.

Best Wishes,
Chris Travers

Bye,

Erik.

On Tue, Dec 25, 2012 at 7:59 AM, Håvard Sørli <..hidden..> wrote:

for use in (and other places where we generate passwords)
branches/1.3/sql/upgrade/1.2-1.3.sql

CREATE OR REPLACE FUNCTION random_string(INTEGER)
RETURNS TEXT AS
$BODY$
SELECT array_to_string(
ARRAY (
SELECT substring(

'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz!#&()?[]'
FROM (ceil(random()*70))::int FOR 1
)
FROM generate_series(1, $1)
),
''
)
$BODY$
LANGUAGE sql VOLATILE;

Test:

$=> select random_string(12);
random_string
---------------
LAw)hNFnwciy
(1 row)

$=> select random_string(12) from generate_series(1,15);
random_string
---------------
6A[s[u]jAcNI
1NcZEJ#ZUpW3
4lr!GRrMcq7b
c6CnjZ?lxZC4
t5dFv1Jzn2s)
TcI[KQZxWmc7
EZt31?7v2l3x
nMpSOaOs4hrP
]IEW?qeBIPFK
MM8P0BFnY?8w
XXhb6Q!6j6cg
x8r8Y[TvLbCM
nS2DPA1whjPo
1IulOOJEX3M[
2qtOJ?k[QmBp

Can we replace random()::text with random_string ?
We could make the long '012345......' as a variable or make several
selectable variants. (by example exclude characters like I, 1 and l
because they look too similar or other variants).

--
Håvard Sørli - http:/www.anix.no

------------------------------------------------------------------------------
LogMeIn Rescue: Anywhere, Anytime Remote support for IT. Free Trial
Remotely access PCs and mobile devices and provide instant support
Improve your efficiency, and focus on delivering more value-add services
Discover what IT Professionals Know. Rescue delivers
http://p.sf.net/sfu/logmein_12329d2d
_______________________________________________
Ledger-smb-devel mailing list
..hidden..
https://lists.sourceforge.net/lists/listinfo/ledger-smb-devel

------------------------------------------------------------------------------
LogMeIn Rescue: Anywhere, Anytime Remote support for IT. Free Trial
Remotely access PCs and mobile devices and provide instant support
Improve your efficiency, and focus on delivering more value-add services
Discover what IT Professionals Know. Rescue delivers
http://p.sf.net/sfu/logmein_12329d2d
_______________________________________________
Ledger-smb-devel mailing list
..hidden..
https://lists.sourceforge.net/lists/listinfo/ledger-smb-devel