Skip site navigation (1) Skip section navigation (2)

Re: Table design for basic user management

From: Michael Wood <esiotrot(at)gmail(dot)com>
To: Jasen Betts <jasen(at)xnet(dot)co(dot)nz>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Table design for basic user management
Date: 2009-11-08 14:32:32
Message-ID: 5a8aa6680911080632h2692c108q4ac6d546d6f502b0@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
2009/11/7 Jasen Betts <jasen(at)xnet(dot)co(dot)nz>:
> On 2009-11-06, Thomas Løcke <thomas(dot)granvej6(at)gmail(dot)com> wrote:
[...]
>> I've come up with an initial design for this database:
>> http://pastebin.com/f5255453e
[...]
> CREATE TABLE log (
>    userid integer PRIMARY KEY REFERENCES REFERENCES user(id) ON DELETE CASCADE,
>    registered timestamp  DEFAULT now() NOT NULL,
>    lastvisit  timestamp  NOT NULL,
>    visits integer DEFAULT 0 NOT NULL
> );
>
> then you can log-in with a single query,
>
> update user set lastvisit=default where username='fred' and
> password=md5('salt'||'password') returning id;
[...]

I agree with using a salt, but you appear to be advocating a fixed
salt for everyone?  Normally the salt is stored along with the
password hash, so you'd need one query to retrieve the salt and
another query to calculate the hash and compare it to the stored hash.
 Something like this:

select salt from "user" where username='fred';
update "user" set lastvisit=default where username='fred' and
password=md5(salt||'password');

>> As far as I can see, PostgreSQL handles indexes automatically on primary and
>> unique columns. Is this assumption correct?
[...]

Yes, but foreign keys are not automatically indexed.  So, e.g. your
log table has a userid column that references "user"(id).  You would
have to create the index on log.userid yourself if that's what you
wanted.

-- 
Michael Wood <esiotrot(at)gmail(dot)com>

In response to

Responses

pgsql-novice by date

Next:From: Michael LushDate: 2009-11-09 09:58:44
Subject: Re: FATAL: the database system is starting up
Previous:From: raghu ramDate: 2009-11-08 06:48:33
Subject: Re: FATAL: the database system is starting up

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group