Re: Authenticate with hash instead of plaintext password? - Mailing list pgsql-general

On Sun, Dec 16, 2012 at 07:42:40PM +0100, Murray Cumming wrote:
> On Sun, 2012-12-16 at 17:51 +0100, Peter Bex wrote:
> > Hashes (if properly salted and
> > stretched) are only useful if they are only ever checked against the
> > password itself.  Storing a hash of any kind and comparing that directly
> > with user input is equivalent to storing the password and comparing that
> > with user input.
>
> So PostgresQL, for instance, stores the actual plaintext password (or an
> encrypted, but not hashed) password? And compares that with the hash
> that it receives from libpq.

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.

> [snip]
> > The best solution I can come up with is not provide a web UI at all
> > but let the user connect directly to the database using a secure
> > method (e.g. SSL client certs, GSSAPI etc).
>
> That's not an option in this case. My system
> ( http://www.glom.org/wiki/index.php?title=Development/OnlineGlom )
> is meant to provide access to databases and I don't wish to implement all
> of it on the client side.)
>
> I do have the option of creating a different set of user/password logins
> for the web UI and then either
> - Using one username/password for all web users' databases, with no
>   PostgreSQL-level separation. But this would have to be in a config file
>   at least. I guess this is what most web systems do, though they
>   generally deal with only one database.

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.

> - Generating PostgreSQL username/passwords for each web user's database,
>   but never exposing these to the web user. But I'd have to store them
>   somewhere.

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.

Cheers,
Peter
--
http://sjamaan.ath.cx
--
"The process of preparing programs for a digital computer
 is especially attractive, not only because it can be economically
 and scientifically rewarding, but also because it can be an aesthetic
 experience much like composing poetry or music."
                            -- Donald Knuth