Users and session ids

[C G]

Dear All,

I wonder if anyone can advise me with this problem.

1. A user logs into the database (through web, webservice, some other piece
of software) - connect(user="j oe",passwd="blo gs")
2. We generate a random session key which will expire in 1 hour. Put this in
table (user, SessKey, time).
3. Give key to user.
4. User wants to do something else, so passes us the session key.
5. How do we use this session key to log the user into the database, i.e.
how do we get the username and passwd to enable:
connect(user="j oe",passwd="blo gs").

Many thanks

Colin

_______________ _______________ _______________ _______________ _____
Tired of 56k? Get a FREE BT Broadband connection

http://www.msn.co.uk/specials/btbroadband

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

http://archives.postgresql.org

[Uwe C. Schroeder]

Re: Users and session ids

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


You've got to have some kind of "middleware ". Apache, custom, whatever.
Basically this piece of middleware gets the session key.
Have the middleware (using a common login) retrieve the ser (and password)
drom the database table and authenticate the user.


On Wednesday 10 December 2003 01:55 am, C G wrote:[color=blue]
> Dear All,
>
> I wonder if anyone can advise me with this problem.
>
> 1. A user logs into the database (through web, webservice, some other piece
> of software) - connect(user="j oe",passwd="blo gs")
> 2. We generate a random session key which will expire in 1 hour. Put this
> in table (user, SessKey, time).
> 3. Give key to user.
> 4. User wants to do something else, so passes us the session key.
> 5. How do we use this session key to log the user into the database, i.e.
> how do we get the username and passwd to enable:
> connect(user="j oe",passwd="blo gs").
>
> Many thanks
>
> Colin
>
> _______________ _______________ _______________ _______________ _____
> Tired of 56k? Get a FREE BT Broadband connection
> http://www.msn.co.uk/specials/btbroadband
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org[/color]

- --
UC

- --
Open Source Solutions 4U, LLC 2570 Fleetwood Drive
Phone: +1 650 872 2425 San Bruno, CA 94066
Cell: +1 650 302 2405 United States
Fax: +1 650 872 2417
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)

iD8DBQE/1vZ3jqGXBvRToM4 RAv+pAJ0bzCNwhs HxoCk36lXbppy8o Q7C6QCcD4H5
GKM2nyxIaOgp98l iPyjKk8w=
=qF5p
-----END PGP SIGNATURE-----


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postg resql.org so that your
message can get through to the mailing list cleanly

[Keith C. Perry]

Re: Users and session ids

Quoting C G <csgcsg39@hotma il.com>:
[color=blue]
> Dear All,
>
> I wonder if anyone can advise me with this problem.
>
> 1. A user logs into the database (through web, webservice, some other piece
> of software) - connect(user="j oe",passwd="blo gs")
> 2. We generate a random session key which will expire in 1 hour. Put this in
>
> table (user, SessKey, time).
> 3. Give key to user.
> 4. User wants to do something else, so passes us the session key.
> 5. How do we use this session key to log the user into the database, i.e.
> how do we get the username and passwd to enable:
> connect(user="j oe",passwd="blo gs").
>
> Many thanks
>
> Colin[/color]

Colin,

For the web, if you are running apache and mod_perl, take a look at the
PosgreSQL authentication modules on CPAN.org. In particular, anything that
deals with "cookie tracking" or authentication with cookies would be a start.

Of coures there are similar modules/methods for the other PG supported languages
as well.


--
Keith C. Perry, MS E.E.
Director of Networks & Applications
VCSN, Inc.

http://vcsn.com

_______________ _______________ ______
This email account is being host by:
VCSN, Inc : http://vcsn.com

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Shridhar Daithankar]

Re: Users and session ids

C G wrote:
[color=blue]
> Dear All,
>
> I wonder if anyone can advise me with this problem.
>
> 1. A user logs into the database (through web, webservice, some other
> piece of software) - connect(user="j oe",passwd="blo gs")
> 2. We generate a random session key which will expire in 1 hour. Put
> this in table (user, SessKey, time).
> 3. Give key to user.
> 4. User wants to do something else, so passes us the session key.
> 5. How do we use this session key to log the user into the database,
> i.e. how do we get the username and passwd to enable:
> connect(user="j oe",passwd="blo gs").[/color]

It is involved at multiple steps.

1. Use a connection pool, all connecting as superuser
2. Authenticate user with opening a new connection
3. Store a map of user session key v/s username/userid in application.
4. Use set session authorization after verifying the key.

It could have been good if postgresql could authenticate over an existing
connection or make set session authorisation accept username/password. But
anyways.. that is not such a big hassle except for the fact that each
authorisation costs starting/killing one connection

HTH

Shridhar

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster