Postgres: pg_hba.conf, md5, pg_shadow, encrypted passwords

Stephen Frost wrote:

The md5 hash which is generated for and stored in pg_shadow does not
use a random salt but instead uses the username which can generally be
determined ahead of time (especially for the 'postgres' superuser
account).

I noted that this was a problem back in August, 2002:

http://archives.postgresql.org/pgsql-admin/2002-08/msg00253.php

Then, as now, the developers weren't very concerned.

Regards,

David.

* David F. Skoll (dfs@roaringpenguin.com) wrote:

Stephen Frost wrote:

The md5 hash which is generated for and stored in pg_shadow does not
use a random salt but instead uses the username which can generally be
determined ahead of time (especially for the 'postgres' superuser
account).

I noted that this was a problem back in August, 2002:

http://archives.postgresql.org/pgsql-admin/2002-08/msg00253.php

Then, as now, the developers weren't very concerned.

I have some hopes that pointing out the rather large problem with the
md5 authentication mechanism in pg_hba.conf will lead them to discourage
it's use and thus reduce the occourances of the salt being made
available to the user giving more weight to the usefullness of having it
be a random salt. Additionally, it's been a few years, perhaps
viewpoints have changed.

Stephen

Stephen Frost <sfrost@snowman.net> writes:

The md5 hash which is generated for and stored in pg_shadow does not
use a random salt but instead uses the username which can generally be
determined ahead of time (especially for the 'postgres' superuser
account).

So?

The fact that we encrypt the contents of pg_shadow at all is not to
provide security against breakins by people who have managed to
obtain the contents of pg_shadow. Any such attacker knows as much as
the postmaster does, and so there isn't anything much the postmaster can
do to prevent a breakin. The reason we do it is to prevent such a
person (or a dishonest DBA) from obtaining the user's actual original
password. This doesn't improve the security of the database at all,
of course, but it does improve security globally if the user used the
same password for other systems.

This would allow for the pregeneration of the entire md5
keyspace using that 'salt' and then quick breakage of the hash once
it's retrieved by the attacker.

Considering the size of the possible keyspace, this is pretty silly.

Were a decent random salt of some
size used it would be difficult to guess and pregenerate the keyspace
for. Thus, keyspace generation would have to happen after pg_shadow
was compramised, giving the admin time to detect the compramise and
take corrective action.

Another large assumption: that the admin knows about the compromise
before the results are used.

. It is also not made clear that if you are
already handling transport-level security via SSL and/or IPSEC that
using md5 actually reduces security by not adding anything to the
transport-level security and defeating the on-disk security
effectivness of using md5 for pg_shadow.

That's simply false. The contents of pg_shadow are never sent over the
wire.

You're going to have to work a lot harder to convince us there's any
significant issue here.

regards, tom lane

Stephen Frost <sfrost@snowman.net> writes:

I have some hopes that pointing out the rather large problem with the
md5 authentication mechanism in pg_hba.conf will lead them to discourage
it's use and thus reduce the occourances of the salt being made
available to the user giving more weight to the usefullness of having it
be a random salt. Additionally, it's been a few years, perhaps
viewpoints have changed.

Salts are always given to the user, that's how they work. They're not secret.

The issue pointed out back then was that lots of hosts would have usernames
with the same name, namely "postgres". So a distributed attack would be able
to use a dictionary attack if it were targeting just the "postgres" user on
many hosts.

That was deemed not a threat model worth worrying about. It's pretty unlikely
someone would have access to the md5sums for many different hosts.

--
greg

On Wed, Apr 20, 2005 at 05:03:18PM -0400, Tom Lane wrote:

This would allow for the pregeneration of the entire md5
keyspace using that 'salt' and then quick breakage of the hash once
it's retrieved by the attacker.

Considering the size of the possible keyspace, this is pretty silly.

Actually, it's not as silly as you think. You can download rainbow
tables for Windows/LanMan passwords up to 14 or 15 characters in length.
Given the password hash and some code, you can determine the user's
password in a matter of minutes.

Simply put, MD5 is no longer strong enough for protecting secrets. It's
just too easy to brute-force. SHA1 is ok for now, but it's days are
numbered as well. I think it would be good to alter SHA1 (or something
stronger) as an alternative to MD5, and I see no reason not to use a
random salt instead of username.
--
Jim C. Nasby, Database Consultant decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

"Jim C. Nasby" <decibel@decibel.org> writes:

Simply put, MD5 is no longer strong enough for protecting secrets. It's
just too easy to brute-force. SHA1 is ok for now, but it's days are
numbered as well. I think it would be good to alter SHA1 (or something
stronger) as an alternative to MD5, and I see no reason not to use a
random salt instead of username.

Well, I have no particular problem with offering SHA1 as an alternative
hash method for those who find MD5 too weak ... but I still question the
value of putting any random salt in the table. AFAICS you would have to
send that salt as part of the initial password challenge, which means
any potential attacker could find it out even before trying to
compromise pg_shadow; so Stephen's argument that there is a useful
improvement in protection against precomputation of password hashes
still falls down.

BTW, one could also ask exactly what threat model Stephen is concerned
about. ISTM anyone who can obtain the contents of pg_shadow has
*already* broken your database security.

regards, tom lane

On Wed, Apr 20, 2005 at 06:03:18PM -0400, Tom Lane wrote:

Well, I have no particular problem with offering SHA1 as an alternative
hash method for those who find MD5 too weak ... but I still question the
value of putting any random salt in the table. AFAICS you would have to
send that salt as part of the initial password challenge, which means
any potential attacker could find it out even before trying to
compromise pg_shadow; so Stephen's argument that there is a useful
improvement in protection against precomputation of password hashes
still falls down.

BTW, one could also ask exactly what threat model Stephen is concerned
about. ISTM anyone who can obtain the contents of pg_shadow has
*already* broken your database security.

FWIW, I do think there's some benefit to not being able to pre-compute
an entire hash table for accounts such as 'postgres' and 'www'. But I
agree it would be useful to know the actual threat model.
--
Jim C. Nasby, Database Consultant decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

Tom Lane wrote:

"Jim C. Nasby" <decibel@decibel.org> writes:

Simply put, MD5 is no longer strong enough for protecting secrets. It's
just too easy to brute-force. SHA1 is ok for now, but it's days are
numbered as well. I think it would be good to alter SHA1 (or something
stronger) as an alternative to MD5, and I see no reason not to use a
random salt instead of username.

Well, I have no particular problem with offering SHA1 as an alternative
hash method for those who find MD5 too weak ... but I still question the
value of putting any random salt in the table. AFAICS you would have to
send that salt as part of the initial password challenge, which means
any potential attacker could find it out even before trying to
compromise pg_shadow; so Stephen's argument that there is a useful
improvement in protection against precomputation of password hashes
still falls down.

BTW, one could also ask exactly what threat model Stephen is concerned
about. ISTM anyone who can obtain the contents of pg_shadow has
*already* broken your database security.

That's what I told him. I think his concern about pre-computed hashes
is the only real issue, and give 'postgres' is usually the super-user, I
can see someone pre-computing md5 postgres hashes and doing quick
comparisons, perhaps as a root kit so you don't have to do the hashing
yourself. I personally don't find that very compelling either.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman@candle.pha.pa.us> writes:

That's what I told him. I think his concern about pre-computed hashes
is the only real issue, and give 'postgres' is usually the super-user, I
can see someone pre-computing md5 postgres hashes and doing quick
comparisons, perhaps as a root kit so you don't have to do the hashing
yourself. I personally don't find that very compelling either.

Lessee ... we'll include a complete password hash table in a root kit,
which will be used at a point where we've already managed to read
pg_shadow but are somehow still lacking the ability to do anything else
we could want to the database ... nope, not very compelling.

regards, tom lane

Bruce Momjian wrote:

BTW, one could also ask exactly what threat model Stephen is concerned
about. ISTM anyone who can obtain the contents of pg_shadow has
*already* broken your database security.

That's what I told him. I think his concern about pre-computed hashes
is the only real issue, and give 'postgres' is usually the super-user, I
can see someone pre-computing md5 postgres hashes and doing quick
comparisons, perhaps as a root kit so you don't have to do the hashing
yourself. I personally don't find that very compelling either.

The issue is that you should try your best to prevent dictionary attacks,
because often people use the same passwords for different things.
I know they shouldn't, but sometimes they do, so any measures you can
take to make a dictionary attack harder are worth doing, especially
when the random salt is so simple to implement.

--
David.

Tom Lane wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

That's what I told him. I think his concern about pre-computed hashes
is the only real issue, and give 'postgres' is usually the super-user, I
can see someone pre-computing md5 postgres hashes and doing quick
comparisons, perhaps as a root kit so you don't have to do the hashing
yourself. I personally don't find that very compelling either.

Lessee ... we'll include a complete password hash table in a root kit,
which will be used at a point where we've already managed to read
pg_shadow but are somehow still lacking the ability to do anything else
we could want to the database ... nope, not very compelling.

[bugtraq removed - I don't think this belongs there, at least at this stage]

/etc/shadow is supposedly only readable by root (or things that are
setuid root). If you have root you already own the box. Yet we store
passwords there hashed with random salt.

My understanding is that Stephen would like a system where clear
passwords are never stored. He's right in saying that our "encrypted"
passwords are in effect cleartext, as a malicious client would only ever
need to know the hashed pw to gain access, and not the original
cleartext. Of course, adding random salt won't change that, and you are
quite right in saying that the random salt would have to be sent as part
of the challenge.

There's a choice between protecting the password over the wire via a
challenge/response system, and protecting it in storage. Postgres has
quite reasonably chosen the former. Stephen says "Well, I can protect
the wire comms via ssh or IPSEC, and I'd like storage protection too."
That also seems reasonable, although I don't think the sky is really
falling in.

Lastly, I have seen Jan say several times (on IRC) that mission-critical
DBs should not be exposed to untrusted networks, but always protected by
appropriate middleware. I could not agree more.

cheers

andrew

Tom Lane wrote:

Lessee ... we'll include a complete password hash table in a root kit,
which will be used at a point where we've already managed to read
pg_shadow but are somehow still lacking the ability to do anything else
we could want to the database ... nope, not very compelling.

You are correct that the threat against the PostgreSQL installation itself
is not very compelling. However, take a look at the bigger picture:
People crack into systems and then try to use those systems to crack
into other systems. If you can make it harder to recover passwords
in the PostgreSQL system, then you've made it harder for attackers
to use those recovered passwords to attack other systems.

Think of the complete security environment, not just the security
of a particular PostgreSQL installation. Having random salts makes
it much harder for attackers to answer questions like "Does user X
have the same password in PostgreSQL installation 1 as he does in PostgreSQL
installation 2".

Regards,

David.

* Greg Stark (gsstark@mit.edu) wrote:

Stephen Frost <sfrost@snowman.net> writes:

I have some hopes that pointing out the rather large problem with the
md5 authentication mechanism in pg_hba.conf will lead them to discourage
it's use and thus reduce the occourances of the salt being made
available to the user giving more weight to the usefullness of having it
be a random salt. Additionally, it's been a few years, perhaps
viewpoints have changed.

Salts are always given to the user, that's how they work. They're not secret.

You're confusing the issues I'm afraid. If you're using md5 to secure
your transport then yes, you must provide the salt to the user since the
same salt must be used on both sides. That's not the salt under
discussion, however; the salt I'm referring to is the one which is used
to make it difficult to brute-force the password from a copy of the
resultant hash. That salt is not given to anyone because no one else
needs it- only the server needs to know that salt so that it can add it
to the password to compare against the hash in the database.

The issue pointed out back then was that lots of hosts would have usernames
with the same name, namely "postgres". So a distributed attack would be able
to use a dictionary attack if it were targeting just the "postgres" user on
many hosts.

That was deemed not a threat model worth worrying about. It's pretty unlikely
someone would have access to the md5sums for many different hosts.

I'm worried about them having access to the md5sums for my host..
If they did and I used 'md5' in pg_hba.conf they wouldn't need to brute
force anything, they'd have all they needed to connect as the postgres
users on my database.

Stephen

* Tom Lane (tgl@sss.pgh.pa.us) wrote:

Stephen Frost <sfrost@snowman.net> writes:

The md5 hash which is generated for and stored in pg_shadow does not
use a random salt but instead uses the username which can generally be
determined ahead of time (especially for the 'postgres' superuser
account).

So?

The fact that we encrypt the contents of pg_shadow at all is not to
provide security against breakins by people who have managed to
obtain the contents of pg_shadow. Any such attacker knows as much as
the postmaster does, and so there isn't anything much the postmaster can
do to prevent a breakin. The reason we do it is to prevent such a
person (or a dishonest DBA) from obtaining the user's actual original
password. This doesn't improve the security of the database at all,
of course, but it does improve security globally if the user used the
same password for other systems.

A dishonest DBA would have no trouble obtaining the user's actual
original password regardless through any number of means, both technical
and social. I can go into them if you'd like but the first one would
probably be just change pg_hba.conf to say 'password' instead of 'md5'
and you're basically done.

Additionally, a dishonest DBA has no need for the original password if
the user has the same username on multiple postgres databases (not
exactly unlikely) and those postgres systems use 'md5' in pg_hba.conf.
This is because when using the 'md5' mechanism in pg_hba.conf the
original password is irrelevant, all that matters is the
password+username hash, which is exactly what's stored in pg_shadow.

Were other systems such as ssh to use this same mechanism then again for
those you would not need the original password but only the hash in
order to authenticate yourself. Thus, claiming that you're protecting
the user from a dishonest DBA is, imv, at best a false sense of
security.

This would allow for the pregeneration of the entire md5
keyspace using that 'salt' and then quick breakage of the hash once
it's retrieved by the attacker.

Considering the size of the possible keyspace, this is pretty silly.

Not as silly as I wish it was. :/

Were a decent random salt of some
size used it would be difficult to guess and pregenerate the keyspace
for. Thus, keyspace generation would have to happen after pg_shadow
was compramised, giving the admin time to detect the compramise and
take corrective action.

Another large assumption: that the admin knows about the compromise
before the results are used.

It would be nice to give the admin some time to detect the compromise.
If a known salt is used then the admin gets essentially no time. If a
random salt is used then the admin will have at least some time while
the attacker generates the keyspace to find a cleartext version to pass
to the server- provided the server is using 'password' and *not* 'md5'
in pg_hba.conf. If the server is using 'md5' in pg_hba.conf then all is
lost as soon as pg_shadow is compromised.

. It is also not made clear that if you are
already handling transport-level security via SSL and/or IPSEC that
using md5 actually reduces security by not adding anything to the
transport-level security and defeating the on-disk security
effectivness of using md5 for pg_shadow.

That's simply false. The contents of pg_shadow are never sent over the
wire.

I didn't mean to suggest that they were. However, if you use 'md5' in
pg_hba.conf then what is stored in pg_shadow might as well be the
original password since it is all that is required to authenticate.
Sure, technically you also need the salt from the server for the
transport-md5-hash, but the server gives that to you and then all you
have to do is a simple md5hash.

You're going to have to work a lot harder to convince us there's any
significant issue here.

Happy to do my best. Hope this helps some. I strongly feel that using
'md5' in pg_hba.conf when using SSL or IPSEC should be strongly
discouraged. It adds nothing in that case and does some harm in the
event pg_shadow is compromised (through stealing of a backup tape, or a
partial compromise of the system whereby an attacker is able to gain
privledged access to read part of a file or similar).

Thanks,

Stephen

* Tom Lane (tgl@sss.pgh.pa.us) wrote:

"Jim C. Nasby" <decibel@decibel.org> writes:

Simply put, MD5 is no longer strong enough for protecting secrets. It's
just too easy to brute-force. SHA1 is ok for now, but it's days are
numbered as well. I think it would be good to alter SHA1 (or something
stronger) as an alternative to MD5, and I see no reason not to use a
random salt instead of username.

Well, I have no particular problem with offering SHA1 as an alternative
hash method for those who find MD5 too weak ... but I still question the

SHA2 would also be nice.

value of putting any random salt in the table. AFAICS you would have to
send that salt as part of the initial password challenge, which means
any potential attacker could find it out even before trying to
compromise pg_shadow; so Stephen's argument that there is a useful
improvement in protection against precomputation of password hashes
still falls down.

Only if you're using 'md5' in pg_hba.conf. In that case, yes, you would
have to send the salt as part of the password challenge. Personally, I
would discourage use of 'md5' in pg_hba.conf because of this and because
it changes the authentication token from the password to the hash which
is what is directly stored in the database.

BTW, one could also ask exactly what threat model Stephen is concerned
about. ISTM anyone who can obtain the contents of pg_shadow has
*already* broken your database security.

Just because they have access to pg_shadow does not necessairly mean
they have access to the database files directly or are able to write to
anything (such as to destroy data). It's possible they pulled
pg_shadow off a backup tape and are bent on destroying all the data in
your live database, and not in just reading it, or if your data is very
time sensitive then they want a more recent version for its value, etc.

There are other possibilities but my concern centers around a partial
system compromise where pg_shadow is obtained by the attacker, yes.

Thanks,

Stephen

* Andrew Dunstan (andrew@dunslane.net) wrote:

[bugtraq removed - I don't think this belongs there, at least at this stage]

Sure.

/etc/shadow is supposedly only readable by root (or things that are
setuid root). If you have root you already own the box. Yet we store
passwords there hashed with random salt.

Right, exactly.

My understanding is that Stephen would like a system where clear
passwords are never stored. He's right in saying that our "encrypted"
passwords are in effect cleartext, as a malicious client would only ever
need to know the hashed pw to gain access, and not the original
cleartext. Of course, adding random salt won't change that, and you are
quite right in saying that the random salt would have to be sent as part
of the challenge.

No, adding a random salt wouldn't change that, that's a direct flaw of
the 'md5' mechanism in pg_hba.conf. However, I can choose not to use
the 'md5' mechanism in pg_hba.conf and can use 'password' instead.
There's not currently an option to say "use a random salt instead of the
username as the salt" for those of us concerned both about people
sniffing the wire and compromising pg_shadow.

There's a choice between protecting the password over the wire via a
challenge/response system, and protecting it in storage. Postgres has
quite reasonably chosen the former. Stephen says "Well, I can protect
the wire comms via ssh or IPSEC, and I'd like storage protection too."
That also seems reasonable, although I don't think the sky is really
falling in.

Right.

Lastly, I have seen Jan say several times (on IRC) that mission-critical
DBs should not be exposed to untrusted networks, but always protected by
appropriate middleware. I could not agree more.

I agree with this also, of course, but there's only so much one can
do and security in layers...

Stephen

[snip]

The issue pointed out back then was that lots of hosts would have usernames
with the same name, namely "postgres". So a distributed attack would be able
to use a dictionary attack if it were targeting just the "postgres" user on
many hosts.

That was deemed not a threat model worth worrying about. It's pretty unlikely
someone would have access to the md5sums for many different hosts.

I'm worried about them having access to the md5sums for my host..
If they did and I used 'md5' in pg_hba.conf they wouldn't need to brute
force anything, they'd have all they needed to connect as the postgres
users on my database.

Lest anyone think that this is a theoretical attack, PHPBB suffered an
SQL injection last year that allowed to you to, in essence, learn the
truth or falsity of a boolean condition (nothing more) because you could
add conditions to the WHERE clause.

If the application is connecting to the database as superuser, then the
complete password can be read by testing each letter of the hash using
substring and subselects in an expression. For example,

(SELECT substring(password, 1, 1) = 'F' FROM pg_shadow WHERE
user='postgres')

tests if the first character of the password hash is an F.

By doing this, then the attacker can learn any user's password, even
though he can't perform any inserts, updates, deletes, etc., and he can
only extract data slowly.*

Of course, someone is asking to be 0wn3d if they set up PHPBB to connect
as superuser. However, given the amount of work done to prevent
foot-shooting in other areas (e.g., server refuses to run as root), it
seems inconsistent that using md5 as the connection method opens the
server to any attacker who knows the hashes.

Perhaps for 8.1 a new authentication method, say, "securemd5," ought to
be created in which remedies this deficiency?

Regards,
Paul Tillotson

*Interesting mental exercise: if all that your SQL injection allows is
to add conditions to a WHERE clause evaluated as superuser, how does one
execute arbitrary code? I can't think of how to do it offhand.

- You can't INSERT, UPDATE, DELETE, or perform any DDL (including
creating new functions)
- You can't use COPY to overwrite local files.
- There aren't any built-in functions that write files, change tables,
etc., are there?

Wouldn't you have to go for a buffer overflow in one of the built-in
functions? That's considerably more difficult than simply connecting as
superuser and using COPY to overwrite local files with arbitrary data.

Paul Tillotson <pntil@shentel.net> writes:

Of course, someone is asking to be 0wn3d if they set up PHPBB to connect
as superuser. However, given the amount of work done to prevent
foot-shooting in other areas (e.g., server refuses to run as root), it
seems inconsistent that using md5 as the connection method opens the
server to any attacker who knows the hashes.

Hm? Using md5 is certainly not any *more* dangerous than any of the
other possible password-based methods.

*Interesting mental exercise: if all that your SQL injection allows is
to add conditions to a WHERE clause evaluated as superuser, how does one
execute arbitrary code? I can't think of how to do it offhand.

If I found the correct reference:
http://www.phpbb.com/phpBB/viewtopic.php?f=14&amp;t=185180
then this wasn't any more circumscribed than any other SQL injection
attack. Consider injecting something like

... AND FALSE; CREATE USER trojan WITH PASSWORD 'trivial'; SELECT ... repeat original query text ...

It's worth pointing out also that adding a per-user-entry random salt
to the password protocol is not some kind of penalty-free magic bullet.
In particular it implies information leakage: I can tell from the
password challenge (or lack of one) whether the username I have offered
is valid. So rather than claiming "this is unconditionally a good thing
to do", you must actually provide a credible scenario that makes the
threat you are defending against more dangerous than the sorts of new
threats we'll be exposed to. So far I haven't seen a very credible
threat here.

regards, tom lane

On Wed, Apr 20, 2005 at 09:58:31PM -0400, Stephen Frost wrote:

* Greg Stark (gsstark@mit.edu) wrote:

Stephen Frost <sfrost@snowman.net> writes:

I have some hopes that pointing out the rather large problem with the
md5 authentication mechanism in pg_hba.conf will lead them to discourage
it's use and thus reduce the occourances of the salt being made
available to the user giving more weight to the usefullness of having it
be a random salt. Additionally, it's been a few years, perhaps
viewpoints have changed.

Salts are always given to the user, that's how they work. They're not secret.

You're confusing the issues I'm afraid. If you're using md5 to secure
your transport then yes, you must provide the salt to the user since the
same salt must be used on both sides. That's not the salt under
discussion, however; the salt I'm referring to is the one which is used
to make it difficult to brute-force the password from a copy of the
resultant hash. That salt is not given to anyone because no one else
needs it- only the server needs to know that salt so that it can add it
to the password to compare against the hash in the database.

Something that just occured to me... if you're using a random salt, you
can change it periodically without any disruption. So in the case of a
site that's worried about brute-forcing a password or hash you can
periodically update all the salts with new random values.

The protocol could also send a nonce as part of the key exchange. I
believe both techniques would add security.
--
Jim C. Nasby, Database Consultant decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"