Thread: Authenticate with hash instead of plaintext password?
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@murrayc.com www.murrayc.com www.openismus.com
On Sun, Dec 16, 2012 at 04:54:30PM +0100, Murray Cumming wrote: > 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? 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. > 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. > 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. > 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. > 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
On Sun, 2012-12-16 at 17:24 +0100, Peter Bex wrote: > On Sun, Dec 16, 2012 at 04:54:30PM +0100, Murray Cumming wrote: > > 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? > > 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@murrayc.com www.murrayc.com www.openismus.com
On Sun, Dec 16, 2012 at 05:38:37PM +0100, Murray Cumming wrote: > On Sun, 2012-12-16 at 17:24 +0100, Peter Bex wrote: > > 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. > 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
On Sun, Dec 16, 2012 at 05:51:23PM +0100, Peter Bex wrote: > 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
Peter Bex <Peter.Bex@xs4all.nl> writes:
> On Sun, Dec 16, 2012 at 05:38:37PM +0100, Murray Cumming wrote:
>> 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
On Sun, Dec 16, 2012 at 12:51:08PM -0500, Tom Lane wrote: > Peter Bex <Peter.Bex@xs4all.nl> writes: > > 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/ > 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. > 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. > 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
Peter Bex <Peter.Bex@xs4all.nl> writes:
> On Sun, Dec 16, 2012 at 12:51:08PM -0500, Tom Lane wrote:
>> 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
On Sun, 2012-12-16 at 17:51 +0100, Peter Bex wrote: > On Sun, Dec 16, 2012 at 05:38:37PM +0100, Murray Cumming wrote: > > On Sun, 2012-12-16 at 17:24 +0100, Peter Bex wrote: > > > 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. > 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] > 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@murrayc.com www.murrayc.com www.openismus.com
On Sun, Dec 16, 2012 at 01:30:29PM -0500, Tom Lane wrote: > Peter Bex <Peter.Bex@xs4all.nl> writes: > > On Sun, Dec 16, 2012 at 12:51:08PM -0500, Tom Lane wrote: > >> 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
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
On 12/16/2012 11:07 AM, Peter Bex wrote: > 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. > 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. " -- Adrian Klaver adrian.klaver@gmail.com
On Sun, Dec 16, 2012 at 11:17:25AM -0800, Adrian Klaver wrote: > On 12/16/2012 11:07 AM, Peter Bex wrote: > > 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
* Murray Cumming (murrayc@murrayc.com) wrote:
> 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
Attachment
* 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
Attachment
* Peter Bex (Peter.Bex@xs4all.nl) wrote:
> 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.
> 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