* Peter Bex (Peter.Bex@xs4all.nl) wrote:
> Hm, that's a good point, I hadn't considered that. I don't know how
> Postgres stores its passwords internally or how its authentication works
> exactly. Maybe one of the developers can shine a light on this.
PG stores a hash which is salted with the username. The client takes
the username and password provided by the user and creates the same hash
to send to the server. There is a challenge/response mechanism used for
the communication between the client and server to avoid the hash
actually being seen on the wire.
> I've been wondering about how to do this correctly. I think I've asked
> before on this list. I think one way to do it is to create a "master"
> user which can do nothing but use "set role" to switch to each account.
>
> Then this user would have access to one table of its own which stores
> the usernames and password hashes you have made yourself. When a user
> logs in, you can create a one-time random value (a "ticket") that you
> store in a mappings table. Then you can check whether the user really
> is logged in before switching to their database role.
Right, I described this in another email just now, but you don't
necessairly need to have your own username/password hash table, as I
point out in my other email.
> I think the GSSAPI might really be a good way to do it, if rather
> difficult and labor-intensive. You could just get a ticket and store
> that in the cookie. No need to do any double bookkeeping.
GSSAPI isn't hard at all, really, if you have some notion of control
over the client (if this is an "Enterprise" solution, where your users
are members of the enterprise, they're probably already using Kerberos
through Microsoft Active Directory, and supporting that is very easily
done with PG...).
Thanks,
Stephen
