Authenticate with hash instead of plaintext password?

[Murray Cumming]

libpq lets me open a connection by specifying a password:
http://www.postgresql.org/docs/9.2/static/libpq-connect.html#LIBPQ-PARAMKEYWORDS

Is there any way to specify a hash of the password when connecting, instead of
providing the password itself? My Web UI asks the user for a PostgreSQL
username and password, and I want to avoid asking the user for the
password again later, as long as they have the browser cookie that I set.

I've looked at the source of phpPgAdmin, which should deal with the same issue,
but that seems to store the plaintext password in the session, which might even
mean that the plaintext password ends up on disk, though I don't know enough about
PHP to be sure.

I understand that libpq already sends only an MD5 hash to the
PostgreSQL server, when it's configured to use MD5 authentication.
But I don't want to have to provide a plaintext password to libpq.
--
***@murrayc.com
www.murrayc.com
www.openismus.com

[Peter Bex]

Post by Murray Cumming
http://www.postgresql.org/docs/9.2/static/libpq-connect.html#LIBPQ-PARAMKEYWORDS
Is there any way to specify a hash of the password when connecting, instead of
providing the password itself?

What's the use of that? It won't buy you any extra protection if the
hash would be accepted as-is instead of the password. In fact, now
you would have *two* strings (instead of one) that are accepted as
equally valid passwords.

Post by Murray Cumming
My Web UI asks the user for a PostgreSQL
username and password, and I want to avoid asking the user for the
password again later, as long as they have the browser cookie that I set.

Perhaps you can encrypt it in the cookie, to prevent casual onlookers
from discovering the password. However, if anyone can obtain the cookie
(eg via the Firesheep plugin) they can still use that to login even if
they don't know the actual password - they can just reuse the encrypted
blob.

Post by Murray Cumming
I've looked at the source of phpPgAdmin, which should deal with the same issue,
but that seems to store the plaintext password in the session, which might even
mean that the plaintext password ends up on disk, though I don't know enough about
PHP to be sure.

The session is slightly safer than storing it directly in the cookie
because one would have to break into the server rather than access
the cookie store in the client. However, if anyone can recover the
session id, they can connect with this user's credentials as well.

Post by Murray Cumming
I understand that libpq already sends only an MD5 hash to the
PostgreSQL server, when it's configured to use MD5 authentication.

I would think that's challenge-response based, but I'm not sure. The
documentation seems to hint that it's just the password, but hashed.

Post by Murray Cumming
But I don't want to have to provide a plaintext password to libpq.

Why not? If the script lives on the server, there shouldn't be a way
for the user to recover the password even if it's put in the string.

Unfortunately, I'm not aware of any truly secure method of doing this.
Sorry! Maybe someone else knows of a good approach.

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

[Murray Cumming]

Post by Peter Bex

Post by Murray Cumming
http://www.postgresql.org/docs/9.2/static/libpq-connect.html#LIBPQ-PARAMKEYWORDS
Is there any way to specify a hash of the password when connecting, instead of
providing the password itself?

What's the use of that?

[snip]

I would not be storing the plaintext password anywhere. That makes it
harder for someone get the plaintext password if they break into the
server, and therefore harder for someone to use that password to break
into another account if the user has used the same password.

There have been plenty of high profile cases recently of password
databases being stolen, with those passwords being in plaintext, or
hashed without a salt, making user accounts on other systems vulnerable.
I'd like to avoid making the same embarrassing mistake.

***@murrayc.com
www.murrayc.com
www.openismus.com

[Peter Bex]

Post by Murray Cumming

Post by Peter Bex
What's the use of that?

[snip]
I would not be storing the plaintext password anywhere. That makes it
harder for someone get the plaintext password if they break into the
server, and therefore harder for someone to use that password to break
into another account if the user has used the same password.

If they do break in and are able to retrieve the password hash, they
can still break in with that hash. 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.

Post by Murray Cumming
There have been plenty of high profile cases recently of password
databases being stolen, with those passwords being in plaintext, or
hashed without a salt, making user accounts on other systems vulnerable.
I'd like to avoid making the same embarrassing mistake.

Please also avoid the mistake outlined above.

Unless I'm overlooking something, then if there's a way to directly
mediate between the browser client and the postgres server, you've
effectively created a man-in-the-middle. This shouldn't be possible
with a truly secure authentication mechanism.

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).

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

[Peter Bex]

Post by Peter Bex
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).

Speaking of which, a custom implementation of the GSSAPI might be
feasible. I don't have experience with the API, but presumably you
can accept a password once, generate a GSSAPI ticket and store *that*
in the user's session file or cookie. Any succesful attack will only
be able to get that one ticket. If the server is compromised you can
revoke all currently active tickets.

Assuming you're using HTTPS and store cookies with the "secure"
attribute, this could be a safe way to do things. It's probably not
easy or available out-of-the-box though!

I think the Postgres docs could use some improvement on how to
use GSSAPI; they're pretty terse! If I understand the Kerberos
section correctly, it's even possible to avoid passwords altogether
by using mod_auth_kerb and a browser extension that allows talking
to this module. That would be even better, but might not be acceptable
if you want to allow users to connect using vanilla webbrowsers.

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

[Tom Lane]

Post by Peter Bex

Post by Murray Cumming
I would not be storing the plaintext password anywhere. That makes it
harder for someone get the plaintext password if they break into the
server, and therefore harder for someone to use that password to break
into another account if the user has used the same password.

If they do break in and are able to retrieve the password hash, they
can still break in with that hash.

Right, they can break into *this account*. But Murray is worrying about
the all-too-common case where a user has used the same or similar
password for multiple sites. I think his goal of not having the
original password stored anywhere is laudable.

Perhaps I'm missing the context here, but it seems unlikely that the
users have any direct access to the database. If they can only get to
it through the website, then what about the idea of hashing the original
text, and then using that hashed string as the database password? IOW
I don't see why this must be implemented inside libpq rather than in
the website logic.

regards, tom lane

[Peter Bex]

Post by Tom Lane

Post by Peter Bex
If they do break in and are able to retrieve the password hash, they
can still break in with that hash.

Right, they can break into *this account*.

Not *just* this one, but any account on any service that uses this
same algorithm. Even if this is a completely custom algorithm
that no other service is going to use, there's still the risk that
when one of his servers is cracked, if a customer has multiple
accounts they are all compromised even if they are on servers that
weren't compromised. (of course, assuming they use the same password
- but that's a reality we'll have to face, hence his original
request)

Finally, if it's a naive implementation of calculating a single hash
run, the password can be easily retrieved. Either by brute-force
(with eg Hashcat or John the Ripper), or even common search engines.
See for example http://tools.benramsey.com/md5/

Post by Tom Lane
But Murray is worrying about the all-too-common case where a user
has used the same or similar password for multiple sites. I think
his goal of not having the original password stored anywhere is laudable.

Absolutely, I completely agree. I wasn't trying to put down this
important goal. There have been too many incidents of hacked password
databases. This has to end. That's why I'm so keen on trying to warn
against doing it in the way he originally proposed.

Post by Tom Lane
Perhaps I'm missing the context here, but it seems unlikely that the
users have any direct access to the database. If they can only get to
it through the website, then what about the idea of hashing the original
text, and then using that hashed string as the database password?

See above; the password can be easily retrieved.

Post by Tom Lane
IOW I don't see why this must be implemented inside libpq rather than
in the website logic.

I'm not sure this is necessary either.

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

[Tom Lane]

Post by Peter Bex

Post by Tom Lane
Right, they can break into *this account*.

Not *just* this one, but any account on any service that uses this
same algorithm.

That's easily fixed. I'd be inclined to make the "password" hash be a
hash of the actual password plus the user's name plus some
web-site-specific random salt string. All of these should be readily
available anytime you need to compute the hash, and the inclusion of the
latter two components will make it difficult to use precomputed rainbow
tables to extract the actual password. With a little more work, he
could also have a per-user random salt added to the hash input --- but
that would require an additional lookup step during login.

regards, tom lane

[Peter Bex]

Post by Tom Lane

Post by Peter Bex

Post by Tom Lane
Right, they can break into *this account*.

Not *just* this one, but any account on any service that uses this
same algorithm.

That's easily fixed. I'd be inclined to make the "password" hash be a
hash of the actual password plus the user's name plus some
web-site-specific random salt string.

That could work, provided the hashing algorithm is one of the stronger
varieties (eg bcrypt, scrypt, pbkdf2). If it's a simple hash, you
still run the risk of having the password recovered through one of the
methods pointed out in my other post.

However, if the hash is going to be stored as-is in postgres and
immediately used as the password, the user will also need to put the
hash in their web scripts (assuming it's an interface to manage shared
hosting accounts), and *if* the postgres port can be accessed directly
for client programs they'll need to use this hash there as well.
This may or may not be desirable.

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

[Murray Cumming]

Post by Peter Bex

Post by Murray Cumming

Post by Peter Bex
What's the use of that?

[snip]
I would not be storing the plaintext password anywhere. That makes it
harder for someone get the plaintext password if they break into the
server, and therefore harder for someone to use that password to break
into another account if the user has used the same password.

If they do break in and are able to retrieve the password hash, they
can still break in with that hash.

Yes, but in that case they've already broken in. And this is about
making it much harder to discover the plaintext password in that case.

Post by Peter Bex
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.

If so, then I just shouldn't be using that password for any kind of web
login.

[snip]

Post by Peter Bex
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.
- 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.


***@murrayc.com
www.murrayc.com
www.openismus.com

[Peter Bex]

Post by Murray Cumming

Post by Peter Bex
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.

Post by Murray Cumming
[snip]

Post by Peter Bex
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.

Post by Murray Cumming
- 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

[Adrian Klaver]

Post by Peter Bex

Post by Murray Cumming

Post by Peter Bex
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.

http://www.postgresql.org/docs/9.2/static/encryption-options.html
"
Password Storage Encryption
By default, database user passwords are stored as MD5 hashes, so the
administrator cannot determine the actual password assigned to the user.
If MD5 encryption is used for client authentication, the unencrypted
password is never even temporarily present on the server because the
client MD5-encrypts it before being sent across the network.
"

[Peter Bex]

Post by Adrian Klaver

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.

http://www.postgresql.org/docs/9.2/static/encryption-options.html

Thanks for the link; must've missed it somehow. I now also see
the topic of improving the password hashing has been discussed before.
For others reading along, I've found these two threads from this year:
http://archives.postgresql.org/pgsql-general/2012-02/msg00334.php
http://archives.postgresql.org/pgsql-hackers/2012-10/msg00462.php

Like the poster in the first URL says, password hashing is *not*
encryption. It's advisable not to refer to it as such - this
will only help increase the widespread confusion about the subject.

A good treatise on password hashing after the explosion of blog
posts about "rainbow tables" is this one:
http://chargen.matasano.com/chargen/2007/9/7/enough-with-the-rainbow-tables-what-you-need-to-know-about-s.html
Note how it deprecates (salted) MD5 as insecure because they can
be brute-forced too easily/quickly.

I understand that it would take a lot for a database to be compromised
and that the used passwords generally aren't going to be used on other
sites. On the other hand, there's no guarantee of either, and it's
not *that* complicated to improve the way passwords are stored.
IMO, the simplest way to implement a good hashing system that can be
easily upgraded in a backwards-compatible way when the need arises
is to use modular Unix crypt(). Modern libc implementations provide
a variety of decent ways of storing password hashes.

I've collected some more information about UNIX crypt() in the
documentation for a library I wrote for Chicken Scheme:
http://wiki.call-cc.org/eggref/4/crypt
All the fallback crypt() implementations this library provides are
public domain, so they could be used in Postgres. If public domain
is not acceptable for legal reasons, there are also plenty of
BSD-licensed implementations to be found elsewhere; bcrypt was first
implemented by OpenBSD.

I could try my hand at providing a patch to switch to, say, bcrypt,
but I'm pretty unfamiliar with the PostgreSQL source code. If
nobody else is interested in working on it I can give it a try
during the holidays.

I'm not sure how to deal with the md5 authentication method.
There is a good point in the -hackers thread above that eavesdroppers
are probably able to hijack existing connections, but there's no reason
to take any risks.

One solution would be to point out in the manual that it's not secure
and advise people to use SSL. Another would be to implement something
like SCRAM, as pointed out in the -hackers thread I posted above or
some other challenge-response system. However, this could be done
separately, either before or after the password storage itself has
been improved.

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

[Stephen Frost]

Post by Peter Bex
I could try my hand at providing a patch to switch to, say, bcrypt,
but I'm pretty unfamiliar with the PostgreSQL source code. If
nobody else is interested in working on it I can give it a try
during the holidays.

The code, in general, is very clean. The issues you're going to run
into are questions about protocol support (the hash, in some ways, is
currently part of our PG protocol and so changing that would be a break
in the protocol which would be frowned upon greatly...) and making sure
that things don't break internally. Note that not everything uses libpq
to talk to PG (the JDBC driver, for example, has a completely seperate
implementation of the protocol, as I recall). You'll also need to
address the upgrade path.

If this is implemented as an optional capability, that's more likely to
be acceptable but at the same time might not really 'fix' things.

I, for one, would love to see some work done in this area and would be
happy to help you with any questions you have regarding the code.

Post by Peter Bex
I'm not sure how to deal with the md5 authentication method.
There is a good point in the -hackers thread above that eavesdroppers
are probably able to hijack existing connections, but there's no reason
to take any risks.

We do support SSL also, of course, and we do encourage people to use it
whenever possible and definitely if going across untrusted networks.

Thanks,

Stephen

[Stephen Frost]

Post by Peter Bex
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.

Post by Peter Bex
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.

Post by Peter Bex
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

[Stephen Frost]

Post by Murray Cumming
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.

Actually, you could provide PG-level separation through the use of
roles. Create a role in the database which is 'noinherit' but which is
granted all the user roles. Then create a role for the web server to
log in as and then grant the 'noinherit' role to the web server role.
When the web server logs in and does whatever user verification it
needs, it can issue a 'set role userX;'.

Through that mechanism you could support client-side SSL certificates
(have Apache validate the user's client-side cert and then you can have
a mapping from DN to PG role for that user). With GSSAPI, as mentioned
up-thread, you could support Kerberos credentials and, with
mod_auth_kerb, could actually proxy those credentials to allow access to
the database as the user with GSSAPI. That requires setting up a
Kerberos environment, joining the client system to the Kerberos realm,
etc.

With the common web-server role method above, you could also implement
your own poor-man's GSSAPI using simple session ids, like a ton of web
sites out there do, and then have a mapping server-side between the
session ID and user role, along with first-issued time, last-used time,
etc, etc. If no session ID is present, go through the normal
authentication process and then have a security definer function be
used, upon successful login of that user, to set up the session.

Thanks,

Stephen