Changing session ownership in a web app (or how to peel an onion)

[Greg Wickham]

Hi All,

Earlier this year there was a discussion between Tom and Ezra regarding extending 'set session authorization' to facilitate changing
the identity of a connection. A synopsis of the discussion is that Tom felt this was bad and the web application should have more
responsibility for handling session security.

I need to implement some session based authentication / authorization and would like to learn from others experience before
embarking too far down this path.

Some constraints:

1/ I'm not keen on embedding secret passwords in a web config file but if I have to I will (*sigh*).

2/ The user names used in the authentication credentials (from the perspective of the user) are _NOT_ the same as those internally
used in postgres. (Postgres has strict limitations on usernames which make using them for users impractical.)

3/ I want to use cookies and session based authentication (rather than continually use a username password tuple for each request).
(But then you could rationalize that the username / password could be reversed out of the session key so this may be a mute point -
it will be over a secure connection).

To meet these constraints it would appear necessary to:

1/ Run an external mapping of human usernames to postgres user names (or burn a connect / disconnect cycle to the db).

2/ Connect using the credentials (mapped username) and provided password

3/ Work as necessary (using connected uid)

4/ Disconnect

Is this the best (or only) technique?

If any one has any suggestions or experience in this then I'd appreciate hearing them.

Thanks in advance,

-Greg


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Tom Lane]

"Greg Wickham" <gr**********@g rangenet.net> writes:

... (Postgres has strict limitations on usernames which make using
them for users impractical.)

Er, which "strict limitations" would those be? You can put almost
anything into a double-quoted identifier.

regards, tom lane

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

[Greg Wickham]

Hi Tom,

I didn't know that double quotes around user names permitted much more variety (of user names).

As always - many many thanks.

-Greg

| -----Original Message-----
| From: pg************* ****@postgresql .org [mailto:pg****** ***********@pos tgresql.org] On Behalf Of Tom
| Lane
| Sent: Saturday, 16 October 2004 3:14 AM
| To: Greg Wickham
| Cc: pg***********@p ostgresql.org
| Subject: Re: [GENERAL] Changing session ownership in a web app (or how to peel an onion)
|
| "Greg Wickham" <gr**********@g rangenet.net> writes:
| > ... (Postgres has strict limitations on usernames which make using
| > them for users impractical.)
|
| Er, which "strict limitations" would those be? You can put almost
| anything into a double-quoted identifier.
|
| regards, tom lane
|
| ---------------------------(end of broadcast)---------------------------
| TIP 3: if posting/reading through Usenet, please send an appropriate
| subscribe-nomail command to ma*******@postg resql.org so that your
| message can get through to the mailing list cleanly

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match