Thread: Generating unique session ids
Hi, I need to generate sessions for logged in users to my website which
uses pgsql. So i decided to write a function which is this:
-------------------------------
CREATE OR REPLACE FUNCTION session_createsession(int4, "varchar")
RETURNS text AS
$BODY$
DECLARE
sid TEXT;
BEGIN
sid := md5(random());
INSERT INTO sessions (id, accountid, ipaddress) VALUES (sid, $1, $2);
return sid;
EXCEPTION
WHEN unique_violation THEN
return session_createsession($1, $2);
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
-------------------------------
As the id field is primary key, it should generate a unique violation
if duplicate ids created, might be seen rarely but wanted to solve it
anyway. So i decided to check it by changing "sid := md5(random());" to
"sid := extract(minute from now());" When i run it, returns the minute
as session key and inserts an entryy for it, but if i call it again in
the same minute, it never ends execution. I expected it to return the
minute when system clock minute changes but it runs forever.
Am i doing something wrong? I mean, there might be some implemendation
to have now() to return same value in a trancastion or something but
could not be sure. If there is something like that, is that function
safe to create session ids? Because if it goes into an infinite loop
like it does with the minute thing, it'd drain all system resources.
Thanks.
"Antimon" <antimon@gmail.com> writes: > As the id field is primary key, it should generate a unique violation > if duplicate ids created, might be seen rarely but wanted to solve it > anyway. Why don't you just use a serial generator? So i decided to check it by changing "sid := md5(random());" to > "sid := extract(minute from now());" When i run it, returns the minute > as session key and inserts an entryy for it, but if i call it again in > the same minute, it never ends execution. I expected it to return the > minute when system clock minute changes but it runs forever. Yup, see http://www.postgresql.org/docs/8.1/static/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT regards, tom lane
On Wed, 26 Jul 2006, Tom Lane wrote:
> "Antimon" <antimon@gmail.com> writes:
> > As the id field is primary key, it should generate a unique violation
> > if duplicate ids created, might be seen rarely but wanted to solve it
> > anyway.
>
> Why don't you just use a serial generator?
If I may interrupt:
Session id's for web cannot be predictable because this will create a
security hole in application. md5(random()) is also a bad choise -
very much predictable.
Mr Antimon would definately better use another way of generating
session ID's - for example PHP sessions and session_id(). He can also
use system entropy source like /dev/urandom on POSIX systems.
Regards
Tometzky
--
...although Eating Honey was a very good thing to do, there was a
moment just before you began to eat it which was better than when you
were...
Winnie the Pooh
Tomasz Ostrowski wrote:
> On Wed, 26 Jul 2006, Tom Lane wrote:
>
>> "Antimon" <antimon@gmail.com> writes:
>>> As the id field is primary key, it should generate a unique violation
>>> if duplicate ids created, might be seen rarely but wanted to solve it
>>> anyway.
>> Why don't you just use a serial generator?
>
> If I may interrupt:
> Session id's for web cannot be predictable because this will create a
> security hole in application. md5(random()) is also a bad choise -
> very much predictable.
>
> Mr Antimon would definately better use another way of generating
> session ID's - for example PHP sessions and session_id(). He can also
> use system entropy source like /dev/urandom on POSIX systems.
>
> Regards
> Tometzky
Using a sequence does not mean it will be predictable.
In the past I have used something similar to this:
SELECT md5('secret_salt' || nextval('my_seq')::text)
Regards,
LL
On Thu, 27 Jul 2006, Lexington Luthor wrote:
> >Session id's for web cannot be predictable because this will create a
> >security hole in application.
>
> Using a sequence does not mean it will be predictable.
> In the past I have used something similar to this:
>
> SELECT md5('secret_salt' || nextval('my_seq')::text)
* When somebody knows md5('secret_salt' || '5') he will be able to
easily compute
md5('secret_salt' || '50')
md5('secret_salt' || '51')
md5('secret_salt' || '52')
...
md5('secret_salt' || '59')
md5('secret_salt' || '500')
md5('secret_salt' || '501')
...
md5('secret_salt' || '[any number starting from 5]').
Without knowledge of 'secret_salt'. So your proposal is totally
insecure.
* PostgreSQL integers (as returned by nextval()) are 4 bytes. This
means only 32 bit strength - much too low for today computers.
* Any database user is most of the time able to read function
bodies, so anybody who is able co connect to your database will be
able to get your 'secret_salt' and then predict session id's.
* If you think that nobody will connect to a database but
web-application frontend there's a high probability of SQL-injection
hole in frontend, which is sufficient.
So, basically, a very bad idea.
Regards
Tometzky
--
...although Eating Honey was a very good thing to do, there was a
moment just before you began to eat it which was better than when you
were...
Winnie the Pooh
> > SELECT md5('secret_salt' || nextval('my_seq')::text)
>
> * When somebody knows md5('secret_salt' || '5') he will be able to
> easily compute
> md5('secret_salt' || '50')
> md5('secret_salt' || '51')
> md5('secret_salt' || '52')
> ...
> md5('secret_salt' || '59')
> md5('secret_salt' || '500')
> md5('secret_salt' || '501')
> ...
> md5('secret_salt' || '[any number starting from 5]').
> Without knowledge of 'secret_salt'. So your proposal is totally
> insecure.
Challenge :)
chris=> select md5('******' || '5');
md5
----------------------------------
7b076f591070f6912e320b95782250ae
(1 row)
I won't tell what '******' was.
Can you send me what md5('******' || '50') will give?
Bye,
Chris.
Tomasz Ostrowski <tometzky@batory.org.pl> writes:
> * When somebody knows md5('secret_salt' || '5') he will be able to
> easily compute
> md5('secret_salt' || '50')
> md5('secret_salt' || '51')
Sure, but can't you fix that by putting the secret part at the end?
> * PostgreSQL integers (as returned by nextval()) are 4 bytes. This
> means only 32 bit strength - much too low for today computers.
Um, nextval returns int8.
> * Any database user is most of the time able to read function
> bodies, so anybody who is able co connect to your database will be
> able to get your 'secret_salt' and then predict session id's.
Yeah, it's not clear where to hide the secret.
regards, tom lane
Tom Lane wrote: > > * Any database user is most of the time able to read function > > bodies, so anybody who is able co connect to your database will be > > able to get your 'secret_salt' and then predict session id's. > > Yeah, it's not clear where to hide the secret. In a memfrob'ed (or something better probably) area in a C function? -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
I'm not an expert as you, but what about a small table where just one user can read and create the function with this same user and definer security? Excuse if I say something stupid Alvaro Herrera wrote: > Tom Lane wrote: > > >>> * Any database user is most of the time able to read function >>> bodies, so anybody who is able co connect to your database will be >>> able to get your 'secret_salt' and then predict session id's. >>> >> Yeah, it's not clear where to hide the secret. >> > > In a memfrob'ed (or something better probably) area in a C function? > >
On Thu, 27 Jul 2006, Tom Lane wrote:
> Tomasz Ostrowski <tometzky@batory.org.pl> writes:
> > * When somebody knows md5('secret_salt' || '5') he will be able to
> > easily compute
> > md5('secret_salt' || '50')
> > md5('secret_salt' || '51')
>
> Sure, but can't you fix that by putting the secret part at the end?
I'm not so sure anymore. I think I was wrong... Forget it.
> > * PostgreSQL integers (as returned by nextval()) are 4 bytes. This
> > means only 32 bit strength - much too low for today computers.
>
> Um, nextval returns int8.
OK. 64 bit should be enough.
> > * Any database user is most of the time able to read function
> > bodies, so anybody who is able co connect to your database will be
> > able to get your 'secret_salt' and then predict session id's.
>
> Yeah, it's not clear where to hide the secret.
As somebody said it would be possible with restricted table and
security definer function.
Regards
Tometzky
--
...although Eating Honey was a very good thing to do, there was a
moment just before you began to eat it which was better than when you
were...
Winnie the Pooh
Alvaro Herrera wrote:
> Tom Lane wrote:
>
>>> * Any database user is most of the time able to read function
>>> bodies, so anybody who is able co connect to your database will be
>>> able to get your 'secret_salt' and then predict session id's.
>> Yeah, it's not clear where to hide the secret.
>
> In a memfrob'ed (or something better probably) area in a C function?
You could also do it in a untrusted plperl or plpython function.
Joshua D. Drake
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
On Thu, Jul 27, 2006 at 15:15:32 +0200, Tomasz Ostrowski <tometzky@batory.org.pl> wrote: > > * PostgreSQL integers (as returned by nextval()) are 4 bytes. This > means only 32 bit strength - much too low for today computers. They are actually 8 bytes. Since session ids aren't valuable for very long you could actually make a usable system out of this if you rekeyed frequently. If the issue is how to cheaply prevent collisions that might occur from using random session ids, one might consider concatenating a random string with a sequence. As long as the sequence won't wrap around before a session id will expire, this will prevent collisions.