Thread

Rejecting weak passwords

Albe Laurenz <laurenz.albe@wien.gv.at> — 2009-09-28T09:54:17Z

Dear hackers,

I have been thinking about ways to have PostgreSQL reject
weak passwords.

I think the standard recommendation is "use PAM and LDAP",
but that requires the user to change the password outside
of PostgreSQL. And who would want to setup and maintain an
LDAP server just for this?

Since everybody has different ideas what is a good password,
there should be some way to configure that. I've looked at
how Oracle does it, and they simply let you write a
stored procedure that throws an exception if it doesn't
like the password.
Since users are on cluster level and functions live in
databases, that won't work in PostgreSQL.

I have come up with an idea or two and like to hear your
opinion.

1) One could have a set of GUCs like min_password_length,
   min_password_nonchars and similar that everybody
   could configure. This is not extremely flexible though.
2) Another idea would be a GUC that contains a regular
   expression that a password may *not* match.
   Perhaps that's too limiting too.
3) I have also considered a GUC that points to a loadable
   module that performs the password check if set.

Are there better ways?

Yours,
Laurenz Albe

Re: Rejecting weak passwords

Magnus Hagander <magnus@hagander.net> — 2009-09-28T10:11:50Z

On 28 sep 2009, at 11.54, "Albe Laurenz" <laurenz.albe@wien.gv.at>  
wrote:

> Dear hackers,
>
> I have been thinking about ways to have PostgreSQL reject
> weak passwords.
>
> I think the standard recommendation is "use PAM and LDAP",
> but that requires the user to change the password outside
> of PostgreSQL. And who would want to setup and maintain an
> LDAP server just for this?
>
> Since everybody has different ideas what is a good password,
> there should be some way to configure that. I've looked at
> how Oracle does it, and they simply let you write a
> stored procedure that throws an exception if it doesn't
> like the password.
> Since users are on cluster level and functions live in
> databases, that won't work in PostgreSQL.
>
> I have come up with an idea or two and like to hear your
> opinion.
>
> 1) One could have a set of GUCs like min_password_length,
>   min_password_nonchars and similar that everybody
>   could configure. This is not extremely flexible though.
> 2) Another idea would be a GUC that contains a regular
>   expression that a password may *not* match.
>   Perhaps that's too limiting too.
> 3) I have also considered a GUC that points to a loadable
>   module that performs the password check if set.
>
> Are there better ways?

Isn't there some library we can link with and (conditionally) use? I  
believe windows exposes api function(s) to let you verify password  
complexity - I'm sure there is something similar available on unix,  
hopefully included on most common platforms?

/Magnus

Re: Rejecting weak passwords

Andrew Dunstan <andrew@dunslane.net> — 2009-09-28T11:35:03Z


Albe Laurenz wrote:
> Dear hackers,
>
> I have been thinking about ways to have PostgreSQL reject
> weak passwords.
>
> I think the standard recommendation is "use PAM and LDAP",
> but that requires the user to change the password outside
> of PostgreSQL. And who would want to setup and maintain an
> LDAP server just for this?
>
> Since everybody has different ideas what is a good password,
> there should be some way to configure that. I've looked at
> how Oracle does it, and they simply let you write a
> stored procedure that throws an exception if it doesn't
> like the password.
> Since users are on cluster level and functions live in
> databases, that won't work in PostgreSQL.
>
> I have come up with an idea or two and like to hear your
> opinion.
>
> 1) One could have a set of GUCs like min_password_length,
>    min_password_nonchars and similar that everybody
>    could configure. This is not extremely flexible though.
> 2) Another idea would be a GUC that contains a regular
>    expression that a password may *not* match.
>    Perhaps that's too limiting too.
> 3) I have also considered a GUC that points to a loadable
>    module that performs the password check if set.
>
>
>   

My vote is for #3, if anything.

cheers

andrew

Re: Rejecting weak passwords

Stephen Frost <sfrost@snowman.net> — 2009-09-28T13:16:42Z

* Magnus Hagander (magnus@hagander.net) wrote:
>> Are there better ways?
>
> Isn't there some library we can link with and (conditionally) use? I  
> believe windows exposes api function(s) to let you verify password  
> complexity - I'm sure there is something similar available on unix,  
> hopefully included on most common platforms?

cracklib2.

	Stephen

Re: Rejecting weak passwords

Ing. Marcos Ortiz Valmaseda <mlortiz@uci.cu> — 2009-09-28T13:18:16Z

Andrew Dunstan escribió:
>
>
> Albe Laurenz wrote:
>> Dear hackers,
>>
>> I have been thinking about ways to have PostgreSQL reject
>> weak passwords.
>>
>> I think the standard recommendation is "use PAM and LDAP",
>> but that requires the user to change the password outside
>> of PostgreSQL. And who would want to setup and maintain an
>> LDAP server just for this?
>>
>> Since everybody has different ideas what is a good password,
>> there should be some way to configure that. I've looked at
>> how Oracle does it, and they simply let you write a
>> stored procedure that throws an exception if it doesn't
>> like the password.
>> Since users are on cluster level and functions live in
>> databases, that won't work in PostgreSQL.
>>
>> I have come up with an idea or two and like to hear your
>> opinion.
>>
>> 1) One could have a set of GUCs like min_password_length,
>>    min_password_nonchars and similar that everybody
>>    could configure. This is not extremely flexible though.
>> 2) Another idea would be a GUC that contains a regular
>>    expression that a password may *not* match.
>>    Perhaps that's too limiting too.
>> 3) I have also considered a GUC that points to a loadable
>>    module that performs the password check if set.
>>
>>
>>   
>
> My vote is for #3, if anything.
>
> cheers
>
> andrew
>
You have to analyze all points before to do this. I vote too for the 
third option, but you have to be clear that how do you ´ll check the 
weakness of the password:
1- For example: the length should be greater that 6 char..
2- The password should be have  a combination fo numbers, letters and 
others dots

Things like that you have to think very well, or to do a question to the 
list asking which are the best options.

I think the same about the PAM and LDAP auth

Regards

-- 
"DBAs must implements decisions based on the best fit of the application,DBMS, and platform
 ......for that reason.......I use PostgreSQL + Linux"

 Ing. Marcos L. Ortiz Valmaseda
 Línea Soporte y Despliegue
 Centro de Tecnologías de Almacenamiento y Análisis de Datos (CENTALAD)

 Linux User # 418229
 PostgreSQL User
 http://www.postgresql.org
 http://www.planetpostgresql.org/
 http://www.postgresql-es.org/

Re: Rejecting weak passwords

PT <wmoran@potentialtech.com> — 2009-09-28T13:24:06Z

In response to "Ing. Marcos L. Ortíz Valmaseda" <mlortiz@uci.cu>:

> Andrew Dunstan escribió:
> >
> > Albe Laurenz wrote:
> >> Dear hackers,
> >>
> >> I have been thinking about ways to have PostgreSQL reject
> >> weak passwords.
> >>
> >> I think the standard recommendation is "use PAM and LDAP",
> >> but that requires the user to change the password outside
> >> of PostgreSQL. And who would want to setup and maintain an
> >> LDAP server just for this?

An option here is to have a way for PG to hook in to LDAP/PAM so
that an ALTER ROLE actually changes the LDAP/PAM password.

> >> Since everybody has different ideas what is a good password,
> >> there should be some way to configure that. I've looked at
> >> how Oracle does it, and they simply let you write a
> >> stored procedure that throws an exception if it doesn't
> >> like the password.

[snip]

> >> 3) I have also considered a GUC that points to a loadable
> >>    module that performs the password check if set.
> >
> You have to analyze all points before to do this. I vote too for the 
> third option, but you have to be clear that how do you ´ll check the 
> weakness of the password:
> 1- For example: the length should be greater that 6 char..
> 2- The password should be have  a combination fo numbers, letters and 
> others dots

I think you've missed the point.  If a loadable module is used, then
it can do anything the DBAs need it to.  You can write your own module
if you're not happy with those provided.  At that point, arguing about
what makes a good password is pretty irrelevant.  Note the paragraph
that I didn't snip where Albe points this out.

-- 
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/

Re: Rejecting weak passwords

Magnus Hagander <magnus@hagander.net> — 2009-09-28T13:34:17Z

2009/9/28 Stephen Frost <sfrost@snowman.net>:
> * Magnus Hagander (magnus@hagander.net) wrote:
>>> Are there better ways?
>>
>> Isn't there some library we can link with and (conditionally) use? I
>> believe windows exposes api function(s) to let you verify password
>> complexity - I'm sure there is something similar available on unix,
>> hopefully included on most common platforms?
>
> cracklib2.

That sounds like the one I thought about :-)

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: Rejecting weak passwords

Andrew Dunstan <andrew@dunslane.net> — 2009-09-28T13:54:56Z


Ing. Marcos L. Ortíz Valmaseda wrote: 
>>
>> My vote is for #3, if anything.
>>
>>
> You have to analyze all points before to do this. I vote too for the 
> third option, but you have to be clear that how do you ´ll check the 
> weakness of the password:
> 1- For example: the length should be greater that 6 char..
> 2- The password should be have  a combination fo numbers, letters and 
> others dots
>
> Things like that you have to think very well, or to do a question to 
> the list asking which are the best options.
>
> I think the same about the PAM and LDAP auth
>
>

I'm voting for #3 precisely so postgres doesn't have to think about it, 
and the module author will do all the work implementing whatever rules 
they want to enforce.

cheers

andrew

Re: Rejecting weak passwords

Magnus Hagander <magnus@hagander.net> — 2009-09-28T14:24:17Z

2009/9/28 Andrew Dunstan <andrew@dunslane.net>:
>
>
> Ing. Marcos L. Ortíz Valmaseda wrote:
>>>
>>> My vote is for #3, if anything.
>>>
>>>
>> You have to analyze all points before to do this. I vote too for the third option, but you have to be clear that how do you ´ll check the weakness of the password:
>> 1- For example: the length should be greater that 6 char..
>> 2- The password should be have  a combination fo numbers, letters and others dots
>>
>> Things like that you have to think very well, or to do a question to the list asking which are the best options.
>>
>> I think the same about the PAM and LDAP auth
>>
>>
>
> I'm voting for #3 precisely so postgres doesn't have to think about it, and the module author will do all the work implementing whatever rules they want to enforce.

That makes a lot of sense. Then we could perhaps ship a cracklib2
provider in contrib.

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: Rejecting weak passwords

Tom Lane <tgl@sss.pgh.pa.us> — 2009-09-28T14:46:07Z

Andrew Dunstan <andrew@dunslane.net> writes:
> Albe Laurenz wrote:
>> 1) One could have a set of GUCs like min_password_length,
>> min_password_nonchars and similar that everybody
>> could configure. This is not extremely flexible though.
>> 2) Another idea would be a GUC that contains a regular
>> expression that a password may *not* match.
>> Perhaps that's too limiting too.
>> 3) I have also considered a GUC that points to a loadable
>> module that performs the password check if set.

> My vote is for #3, if anything.

Yeah.  I think there is no chance of anything in this vein getting
accepted into core Postgres, if only because everybody will have a
different idea of what it needs to do.  A hook function (no need
for a GUC) would be a reasonable proposal.

			regards, tom lane

Re: Rejecting weak passwords

Tom Lane <tgl@sss.pgh.pa.us> — 2009-09-28T15:06:15Z

Actually there's a much bigger problem with asking the backend to reject
weak passwords: what ya gonna do with a pre-MD5'd string?  Which is
exactly what the backend is going to always get, in a security-conscious
environment.

			regards, tom lane

Re: Rejecting weak passwords

Albe Laurenz <laurenz.albe@wien.gv.at> — 2009-09-28T15:31:37Z

Tom Lane wrote:
> Actually there's a much bigger problem with asking the backend to reject
> weak passwords: what ya gonna do with a pre-MD5'd string?  Which is
> exactly what the backend is going to always get, in a security-conscious
> environment.

You mean if the password is set with
CREATE/ALTER ROLE x ENCRYPTED PASSWORD 'md5string' ?
That could not be checked of course...

I'm thinking of the case where somebody changes his or her
password interactively on the command line, with pgAdmin III,
or similar. People would hardly use the above in that case,
and you can do it safely over SSL encrypted connections.

Given the feedback by everybody (thanks!) I will investigate
the option of a loadable module or some other kind of hook.

Is there code in PostgreSQL that uses something like that
to get me started?

Yours,
Laurenz Albe

Re: Rejecting weak passwords

Tom Lane <tgl@sss.pgh.pa.us> — 2009-09-28T15:38:45Z

"Albe Laurenz" <laurenz.albe@wien.gv.at> writes:
> Tom Lane wrote:
>> Actually there's a much bigger problem with asking the backend to reject
>> weak passwords: what ya gonna do with a pre-MD5'd string?  Which is
>> exactly what the backend is going to always get, in a security-conscious
>> environment.

> I'm thinking of the case where somebody changes his or her
> password interactively on the command line, with pgAdmin III,
> or similar. People would hardly use the above in that case,

Really?  If pgAdmin has a password-change function that doesn't use
client-side password encryption then somebody should file a bug against
it.  Sending unencrypted passwords exposes the password at least to the
postmaster logfile.  createuser has been doing encryption, unless
specifically commanded not to, for a long time.

			regards, tom lane

Re: Rejecting weak passwords

Dave Page <dpage@pgadmin.org> — 2009-09-28T15:48:21Z

On Mon, Sep 28, 2009 at 4:38 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Albe Laurenz" <laurenz.albe@wien.gv.at> writes:
>> Tom Lane wrote:
>>> Actually there's a much bigger problem with asking the backend to reject
>>> weak passwords: what ya gonna do with a pre-MD5'd string?  Which is
>>> exactly what the backend is going to always get, in a security-conscious
>>> environment.
>
>> I'm thinking of the case where somebody changes his or her
>> password interactively on the command line, with pgAdmin III,
>> or similar. People would hardly use the above in that case,
>
> Really?  If pgAdmin has a password-change function that doesn't use
> client-side password encryption then somebody should file a bug against
> it.  Sending unencrypted passwords exposes the password at least to the
> postmaster logfile.  createuser has been doing encryption, unless
> specifically commanded not to, for a long time.

pgAdmin MD5's the passwords if you use the GUI to change them, or when
add a user. It doesn't make any attempt to parse the SQL if you enter
it yourself in the query tool though (nor is it going to).

-- 
Dave Page
EnterpriseDB UK:   http://www.enterprisedb.com

Re: Rejecting weak passwords

Tom Lane <tgl@sss.pgh.pa.us> — 2009-09-28T15:50:24Z

Dave Page <dpage@pgadmin.org> writes:
> pgAdmin MD5's the passwords if you use the GUI to change them, or when
> add a user. It doesn't make any attempt to parse the SQL if you enter
> it yourself in the query tool though (nor is it going to).

No, I wouldn't expect it to go that far.  My point is just that
pre-MD5'd passwords are a lot commoner than Albe seems to think.

			regards, tom lane

Re: Rejecting weak passwords

Marko Kreen <markokr@gmail.com> — 2009-09-28T18:00:14Z

On 9/28/09, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Actually there's a much bigger problem with asking the backend to reject
> weak passwords: what ya gonna do with a pre-MD5'd string?  Which is
> exactly what the backend is going to always get, in a security-conscious
> environment.

Hmm...  Looking at the docs, I don't see anywhere mention that the MD5
hashed passwords can still be used to log in, that only thing MD5'ing
protects from is actually knowing what the password was.

Also, although it does protect from sniffing password at login time,
it is as insecure as plaintext password against sniffing on
CREATE/ALTER USER time.

Thus only secure way to change password is over SSL, in which case
it does not make much difference whether it's plaintext or not.
Logfile may seem to be as argument against plaintext, but note
that you still cannot allow unprivileged users to access logfile
that may contain MD5 passwords, as they can use them to log in
as another user.

So promoting the ENCRYPTED 'foo' as "secure" may lure users into
false sense of security, and be lax against sniffing and logfile
protection.

Better approach seems to be simply refuse to log the password
value into logfile, whether it's MD5 or not, and promote SSL
as only secure way of changing passwords.

IOW, having plaintext password in CREATE/ALTER time which can
then checked for weaknesses is better that MD5 password, which
actually does not increase security.

We just need to fix the logging.

-- 
marko

Re: Rejecting weak passwords

Tom Lane <tgl@sss.pgh.pa.us> — 2009-09-28T18:37:11Z

Marko Kreen <markokr@gmail.com> writes:
> So promoting the ENCRYPTED 'foo' as "secure" may lure users into
> false sense of security, and be lax against sniffing and logfile
> protection.

This argument is entirely irrelevant to the point.  Yes, ENCRYPTED
doesn't fix everything, but it is still good practice to use it
and most well-written tools will.  So having a weak-password detector
that can only work on non-encrypted passwords is going to not be
very helpful.

> IOW, having plaintext password in CREATE/ALTER time which can
> then checked for weaknesses is better that MD5 password, which
> actually does not increase security.

This is not acceptable and will not happen.  The case that ENCRYPTED
protects against is database superusers finding out other users'
original passwords, which is a security issue to the extent that those
users have used the same/similar passwords for other systems.
We're not going to give up protection for that in order to provide
an option to do weak-password checking in a place that simply isn't
the best place to do it anyway.

			regards, tom lane

Re: Rejecting weak passwords

Robert Haas <robertmhaas@gmail.com> — 2009-09-28T18:40:15Z

On Mon, Sep 28, 2009 at 2:00 PM, Marko Kreen <markokr@gmail.com> wrote:
> So promoting the ENCRYPTED 'foo' as "secure" may lure users into
> false sense of security, and be lax against sniffing and logfile
> protection.

ENCRYPTED prevents the user's password from being stolen by a modified server.

...Robert

Re: Rejecting weak passwords

Marcin Mańk <marcin.mank@gmail.com> — 2009-09-28T21:49:06Z

> The case that ENCRYPTED
> protects against is database superusers finding out other users'
> original passwords, which is a security issue to the extent that those
> users have used the same/similar passwords for other systems.

I just want to note that md5 is not much of a protection against this
case these days. Take a look at this:
http://www.golubev.com/hashgpu.htm

It takes about 32 hours to brute force all passwords from [a-zA-Z0-9]
of up to 8 chars in length.

Maybe it is time to look at something like bcrypt.
http://chargen.matasano.com/chargen/2007/9/7/enough-with-the-rainbow-tables-what-you-need-to-know-about-s.html

Greetings
Marcin

Re: Rejecting weak passwords

Josh Berkus <josh@agliodbs.com> — 2009-09-28T22:52:56Z

> It takes about 32 hours to brute force all passwords from [a-zA-Z0-9]
> of up to 8 chars in length.

That would be a reason to limit the number of failed connection attempts
from a single source, then, rather than a reason to change the hash
function.

Hmmm, that would be a useful, easy (I think) security feature: add a GUC
for failed_logins_allowed.

-- 
Josh Berkus
PostgreSQL Experts Inc.
www.pgexperts.com

Re: Rejecting weak passwords

Joshua D. Drake <jd@commandprompt.com> — 2009-09-28T22:59:45Z

On Mon, 2009-09-28 at 15:52 -0700, Josh Berkus wrote:
> > It takes about 32 hours to brute force all passwords from [a-zA-Z0-9]
> > of up to 8 chars in length.
> 
> That would be a reason to limit the number of failed connection attempts
> from a single source, then, rather than a reason to change the hash
> function.
> 
> Hmmm, that would be a useful, easy (I think) security feature: add a GUC
> for failed_logins_allowed.

Why a GUC, can't we just use ALTER ROLE (or ALTER DATABASE)?

Joshua D. Drake


-- 
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564
Consulting, Training, Support, Custom Development, Engineering
If the world pushes look it in the eye and GRR. Then push back harder. - Salamander

Re: Rejecting weak passwords

Jeff Davis <pgsql@j-davis.com> — 2009-09-28T23:01:57Z

On Mon, 2009-09-28 at 15:52 -0700, Josh Berkus wrote:
> > It takes about 32 hours to brute force all passwords from [a-zA-Z0-9]
> > of up to 8 chars in length.
> 
> That would be a reason to limit the number of failed connection attempts
> from a single source, then, rather than a reason to change the hash
> function.

That doesn't solve the problem of an administrator brute-forcing your password.

Regards,
	Jeff Davis

Re: Rejecting weak passwords

Andrew Dunstan <andrew@dunslane.net> — 2009-09-28T23:10:13Z


Jeff Davis wrote:
> On Mon, 2009-09-28 at 15:52 -0700, Josh Berkus wrote:
>   
>>> It takes about 32 hours to brute force all passwords from [a-zA-Z0-9]
>>> of up to 8 chars in length.
>>>       
>> That would be a reason to limit the number of failed connection attempts
>> from a single source, then, rather than a reason to change the hash
>> function.
>>     
>
> That doesn't solve the problem of an administrator brute-forcing your password.
>
>
>
>   

Indeed. These brute force checkers aren't checking them by actually 
trying the connection.

cheers

andrew

Re: Rejecting weak passwords

Tom Lane <tgl@sss.pgh.pa.us> — 2009-09-28T23:19:35Z

Josh Berkus <josh@agliodbs.com> writes:
> Hmmm, that would be a useful, easy (I think) security feature: add a GUC
> for failed_logins_allowed.

And the counts would be tracked and enforced where?

			regards, tom lane

Re: Rejecting weak passwords

Tom Lane <tgl@sss.pgh.pa.us> — 2009-09-28T23:26:52Z

marcin mank <marcin.mank@gmail.com> writes:
>> The case that ENCRYPTED
>> protects against is database superusers finding out other users'
>> original passwords, which is a security issue to the extent that those
>> users have used the same/similar passwords for other systems.

> I just want to note that md5 is not much of a protection against this
> case these days. Take a look at this:
> http://www.golubev.com/hashgpu.htm

> It takes about 32 hours to brute force all passwords from [a-zA-Z0-9]
> of up to 8 chars in length.

Yeah, but that will find you a password that hashes to the same thing.
Not necessarily the same password.  It'll get you into the Postgres
DB just fine, which you don't care about because you're already a
superuser there.  It won't necessarily get you into the assumed
third-party systems.

			regards, tom lane

Re: Rejecting weak passwords

Albe Laurenz <laurenz.albe@wien.gv.at> — 2009-09-29T06:46:07Z

Tom Lane wrote:
> > pgAdmin MD5's the passwords if you use the GUI to change them, or when
> > add a user. It doesn't make any attempt to parse the SQL if you enter
> > it yourself in the query tool though (nor is it going to).
> 
> No, I wouldn't expect it to go that far.  My point is just that
> pre-MD5'd passwords are a lot commoner than Albe seems to think.

Point taken.

I thought about it some more, and I think that a password checking
hook might still be somewhat useful even for MD5-encrypted passwords;
the function could guess and exclude at least that dreadful
all-too-frequent case of username = password.

Yours,
Laurenz Albe

Re: Rejecting weak passwords

Peter Eisentraut <peter_e@gmx.net> — 2009-09-29T11:43:05Z

On Mon, 2009-09-28 at 15:59 -0700, Joshua D. Drake wrote:
> On Mon, 2009-09-28 at 15:52 -0700, Josh Berkus wrote:
> > > It takes about 32 hours to brute force all passwords from [a-zA-Z0-9]
> > > of up to 8 chars in length.
> > 
> > That would be a reason to limit the number of failed connection attempts
> > from a single source, then, rather than a reason to change the hash
> > function.
> > 
> > Hmmm, that would be a useful, easy (I think) security feature: add a GUC
> > for failed_logins_allowed.
> 
> Why a GUC, can't we just use ALTER ROLE (or ALTER DATABASE)?

If you make it a GUC, you get those for free.  (That's what the "U"
means.)

Re: Rejecting weak passwords

Gurjeet Singh <singh.gurjeet@gmail.com> — 2009-09-29T13:07:40Z

On Tue, Sep 29, 2009 at 4:49 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Josh Berkus <josh@agliodbs.com> writes:
> > Hmmm, that would be a useful, easy (I think) security feature: add a GUC
> > for failed_logins_allowed.
>
> And the counts would be tracked and enforced where?
>
>
Combining this with other suggestion:

.) Provide a GUC failed_logins_allowed
.) Add MAX FAILED LOGINS option to ADD/ALTER USER, which defaults to the GUC
if not provided in the command.
.) Track per-user failed attempt counts in shared catalog, and reset on a
successful login.

Best regards,
-- 
Lets call it Postgres

EnterpriseDB      http://www.enterprisedb.com

gurjeet[.singh]@EnterpriseDB.com

singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com
Twitter: singh_gurjeet
Skype: singh_gurjeet

Mail sent from my BlackLaptop device

Re: Rejecting weak passwords

Tom Lane <tgl@sss.pgh.pa.us> — 2009-09-29T13:48:46Z

"Albe Laurenz" <laurenz.albe@wien.gv.at> writes:
> I thought about it some more, and I think that a password checking
> hook might still be somewhat useful even for MD5-encrypted passwords;
> the function could guess and exclude at least that dreadful
> all-too-frequent case of username = password.

True.  You could probably even run through a moderate-size dictionary
of weak passwords, depending on how long you're willing to make the
user wait.  (CHECK_FOR_INTERRUPTS inside the loop would be polite ;-))

			regards, tom lane

Re: Rejecting weak passwords

Robert Haas <robertmhaas@gmail.com> — 2009-09-29T14:18:34Z

On Tue, Sep 29, 2009 at 9:48 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Albe Laurenz" <laurenz.albe@wien.gv.at> writes:
>> I thought about it some more, and I think that a password checking
>> hook might still be somewhat useful even for MD5-encrypted passwords;
>> the function could guess and exclude at least that dreadful
>> all-too-frequent case of username = password.
>
> True.  You could probably even run through a moderate-size dictionary
> of weak passwords, depending on how long you're willing to make the
> user wait.  (CHECK_FOR_INTERRUPTS inside the loop would be polite ;-))

But how much value is there in that?  This whole thing seems like a
dead end to me.  No matter how long you're willing to wait, putting
the checking on the client side will let you far more validation for
the same price.

...Robert

Re: Rejecting weak passwords

Andrew Dunstan <andrew@dunslane.net> — 2009-09-29T14:47:08Z

Robert Haas wrote:
> On Tue, Sep 29, 2009 at 9:48 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>   
>> "Albe Laurenz" <laurenz.albe@wien.gv.at> writes:
>>     
>>> I thought about it some more, and I think that a password checking
>>> hook might still be somewhat useful even for MD5-encrypted passwords;
>>> the function could guess and exclude at least that dreadful
>>> all-too-frequent case of username = password.
>>>       
>> True.  You could probably even run through a moderate-size dictionary
>> of weak passwords, depending on how long you're willing to make the
>> user wait.  (CHECK_FOR_INTERRUPTS inside the loop would be polite ;-))
>>     
>
> But how much value is there in that?  This whole thing seems like a
> dead end to me.  No matter how long you're willing to wait, putting
> the checking on the client side will let you far more validation for
> the same price.
>
>
>   

Why do we need to answer that question? If all we do is provide a hook, 
the cost is very low, and the decision on value is left to whoever is 
deploying some module to use the hook. And it will let people possibly 
implement some password security policy dictated by some PHB, and so 
check off a box on a form somewhere. Frankly, real security requires 
that you pretty much get out of the password game, but passwords will 
undoubtedly be around for a long time, since people will always trade 
security for convenience.

cheers

andrew

Re: Rejecting weak passwords

Tom Lane <tgl@sss.pgh.pa.us> — 2009-09-29T14:50:03Z

Robert Haas <robertmhaas@gmail.com> writes:
> But how much value is there in that?  This whole thing seems like a
> dead end to me.  No matter how long you're willing to wait, putting
> the checking on the client side will let you far more validation for
> the same price.

No doubt, but ...

The value of doing it on the server side is you only have to implement
it once, and you don't have to hope that all your users are using the
most up-to-date clients that will enforce a check.  (The more troglodytic
of them might be using a direct ALTER USER PASSWORD command, which will
certainly not result in any client-side check happening.)

Even if we encouraged client-side tool authors to implement password
checking, the lack of consensus about what the checks should be would
pretty much guarantee lack of uniformity about exactly what got checked
in any real installation.  Which is not the sort of thing that makes
security auditors feel all warm and fuzzy.

			regards, tom lane

Re: Rejecting weak passwords

Tom Lane <tgl@sss.pgh.pa.us> — 2009-09-29T15:02:29Z

Andrew Dunstan <andrew@dunslane.net> writes:
> Why do we need to answer that question? If all we do is provide a hook, 
> the cost is very low, and the decision on value is left to whoever is 
> deploying some module to use the hook.

Right.  As long as it's just a hook, it's not enough work to justify
lots of debate.  There is a plausible use-case, and that's enough.

			regards, tom lane

Re: Rejecting weak passwords

mark@mark.mielke.cc — 2009-09-29T16:23:05Z

On 09/29/2009 09:07 AM, Gurjeet Singh wrote:
> On Tue, Sep 29, 2009 at 4:49 AM, Tom Lane <tgl@sss.pgh.pa.us 
> <mailto:tgl@sss.pgh.pa.us>> wrote:
>
>     Josh Berkus <josh@agliodbs.com <mailto:josh@agliodbs.com>> writes:
>     > Hmmm, that would be a useful, easy (I think) security feature:
>     add a GUC
>     > for failed_logins_allowed.
>     And the counts would be tracked and enforced where?
>
>
> Combining this with other suggestion:
> .) Provide a GUC failed_logins_allowed
> .) Add MAX FAILED LOGINS option to ADD/ALTER USER, which defaults to 
> the GUC if not provided in the command.
> .) Track per-user failed attempt counts in shared catalog, and reset 
> on a successful login.
>

Reset on successful works _against_ the goal of preventing brute force 
attacks. Brute force attacks try until successful.

I read Josh's original suggestion to eventually evolve to "if a 
particular user account from a particular IP address uses the wrong 
password more than N times in T minutes, than the IP address is locked 
out for U minutes." This is the *only* way of significantly reducing the 
ability of a client to guess the password using "brute force".

It works pretty successfully in other systems. I know when I forget my 
voice mail password and I try five times, my account gets locked out for 
24+ hours unless I request a password reset from our support 
organization. It is a pain in the butt - but it means that somebody 
trying to guess my password either has to get it right in a few short 
guesses, or they are out of luck.

Still, all of this seems a little bit over thought out to me, as unless 
one goes to this extreme - of actually blocking connections from an IP 
for a period of time - the ability to brute force passwords is already 
limited by network capacity, network latency, and protocol restrictions. 
md5 might be "broken" from the perspective of a super user having access 
to the md5 and having access to hardware accelerators (GPU), but brute 
forcing from a client to a server is still limited to thousands of 
attempts or less per second. This particular aspect of PostgreSQL has 
not concerned me. I tend to think that anybody who exposes their 
PostgreSQL to the Internet directly is asking for trouble no matter how 
hard pgsql-hackers tries to protect them. On my own network, there are 
so many other ways of getting at the passwords - including the crypt() 
passwords being visible over NIS using "ypmatch", that this is really 
the least of my concerns. We have employee agreements in place that 
prevent the use of "hacking", and outsiders are not supposed to have 
access to our network.

Point being - if you want to really be effective compared to what we 
have today - you need to go all the way. Half way is useless.

Cheers,
mark

-- 
Mark Mielke<mark@mielke.cc>

Re: Rejecting weak passwords

Josh Berkus <josh@agliodbs.com> — 2009-09-29T16:54:24Z

Mark,

> I read Josh's original suggestion to eventually evolve to "if a
> particular user account from a particular IP address uses the wrong
> password more than N times in T minutes, than the IP address is locked
> out for U minutes." This is the *only* way of significantly reducing the
> ability of a client to guess the password using "brute force".

As pointed out by others, that was a false assertion.  Most
sophisticated attackers sniff the MD5 password over the network or by
other means, and then brute force match it without trying to connect to
the DB.

-- 
Josh Berkus
PostgreSQL Experts Inc.
www.pgexperts.com

Re: Rejecting weak passwords

mark@mark.mielke.cc — 2009-09-29T18:58:44Z

On 09/29/2009 12:54 PM, Josh Berkus wrote:
>> I read Josh's original suggestion to eventually evolve to "if a
>> particular user account from a particular IP address uses the wrong
>> password more than N times in T minutes, than the IP address is locked
>> out for U minutes." This is the *only* way of significantly reducing the
>> ability of a client to guess the password using "brute force".
>>      
> As pointed out by others, that was a false assertion.  Most
> sophisticated attackers sniff the MD5 password over the network or by
> other means, and then brute force match it without trying to connect to
> the DB.
>    

I don't know about most. Sniffing requires an inside track. I cannot 
sniff your network traffic from my notebook in Ottawa. Somebody so 
inclined would have to first break into your network to see the password 
you are sending. Then, if they can sniff your traffic, they can do all 
sorts of other things. It's a bit of a wash in the grand scheme of things.

In practice, for the last decade, I have seen peaks of tens of thousands 
of attempts a day to brute force into my machine from the Internet from 
locations all over the world. It is not limited to telnet or SSH either 
- they come in on IMAP ports, VNC ports, SMB ports, or anything else 
that is widely used and exposed. Brute forcing through remote login is a 
well used method of cracking. Still, their ability to guess is limited 
by network capacity and network latency. So, it is on the order of 
thousands, not millions, and basic password precautions such as "don't 
use a word" are still quite effective.

I don't think knowing the MD5 is an attack on its own. It might be a 
component in an escalation vector whereby the first get access to one 
resource, and then sniff the MD5 or see it in the backend database 
storage, to break into another resource. In any case - if they get the 
MD5, PostgreSQL is already compromised, and the next attack is more 
likely to affect something else - not PostgreSQL itself.

Within our company, the crypt() passwords are available to all employees 
via NIS. Technically, this is a problem - but in practice, how much 
effort is this worth resolving? If they can get onto our network to get 
access to the crypt() password, they probably already have access to 
other intellectual property.

Mostly - I'm saying that PostgreSQL using MD5 is a minor issue - 
switching to SHA-1 is not going to eliminate the problem, it will just 
make things a tiny bit harder for a would be attacker. To actually close 
the window, as opposed to push it closed a little tighter, would take a 
lot more effort.

Cheers,
mark

-- 
Mark Mielke<mark@mielke.cc>

Re: Rejecting weak passwords

Albe Laurenz <laurenz.albe@wien.gv.at> — 2009-10-01T12:53:57Z

In the discussion following
http://archives.postgresql.org/pgsql-hackers/2009-09/msg01766.php
the consensus was that a hook that allows you to implement
a password checking routine as a module "would not hurt".

So here's the patch.
I don't think there is documentation required;
correct me if I am wrong.

Yours,
Laurenz Albe

Re: Rejecting weak passwords

Dave Page <dpage@pgadmin.org> — 2009-10-01T12:57:23Z

On Thu, Oct 1, 2009 at 1:53 PM, Albe Laurenz <laurenz.albe@wien.gv.at> wrote:
> In the discussion following
> http://archives.postgresql.org/pgsql-hackers/2009-09/msg01766.php
> the consensus was that a hook that allows you to implement
> a password checking routine as a module "would not hurt".
>
> So here's the patch.
> I don't think there is documentation required;
> correct me if I am wrong.

How will people know how to use it, or that it's even there without at
least a note in the docs somewhere?

-- 
Dave Page
EnterpriseDB UK:   http://www.enterprisedb.com

Re: Rejecting weak passwords

Andrew Dunstan <andrew@dunslane.net> — 2009-10-01T13:04:30Z


Dave Page wrote:
> On Thu, Oct 1, 2009 at 1:53 PM, Albe Laurenz <laurenz.albe@wien.gv.at> wrote:
>   
>> In the discussion following
>> http://archives.postgresql.org/pgsql-hackers/2009-09/msg01766.php
>> the consensus was that a hook that allows you to implement
>> a password checking routine as a module "would not hurt".
>>
>> So here's the patch.
>> I don't think there is documentation required;
>> correct me if I am wrong.
>>     
>
> How will people know how to use it, or that it's even there without at
> least a note in the docs somewhere?
>
>   

I'd prefer to have an example as a contrib module, as well as docs. 
Quite apart from anything else, how the heck would we test it without 
such a thing?

cheers

andrew

Re: Rejecting weak passwords

Albe Laurenz <laurenz.albe@wien.gv.at> — 2009-10-01T13:26:59Z

Andrew Dunstan wrote:
>>> So here's the patch.
>>> I don't think there is documentation required;
>>> correct me if I am wrong.
>>
>> How will people know how to use it, or that it's even there without at
>> least a note in the docs somewhere?
> 
> I'd prefer to have an example as a contrib module, as well as docs. 
> Quite apart from anything else, how the heck would we test it without 
> such a thing?

I was not sure because no other hooks were documented anywhere else
than in the code.

I could add a paragraph in the "auth-password" section of
client-auth.sgml. Or is there a better place?

I could easily write a simple contrib that adds a check for
username = password if there is interest.

Yours,
Laurenz Albe

Re: Rejecting weak passwords

Magnus Hagander <magnus@hagander.net> — 2009-10-01T13:54:37Z

On Thu, Oct 1, 2009 at 15:26, Albe Laurenz <laurenz.albe@wien.gv.at> wrote:
> Andrew Dunstan wrote:
>>>> So here's the patch.
>>>> I don't think there is documentation required;
>>>> correct me if I am wrong.
>>>
>>> How will people know how to use it, or that it's even there without at
>>> least a note in the docs somewhere?
>>
>> I'd prefer to have an example as a contrib module, as well as docs.
>> Quite apart from anything else, how the heck would we test it without
>> such a thing?
>
> I was not sure because no other hooks were documented anywhere else
> than in the code.
>
> I could add a paragraph in the "auth-password" section of
> client-auth.sgml. Or is there a better place?
>
> I could easily write a simple contrib that adds a check for
> username = password if there is interest.

I think it's better to have an actually *useful* contrib module for
it, if there is one. Meaning perhaps something that links to that
cracklib thing mentioned upthread.

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: Rejecting weak passwords

ktm@rice.edu <ktm@rice.edu> — 2009-10-01T14:04:31Z

On Thu, Oct 01, 2009 at 03:54:37PM +0200, Magnus Hagander wrote:
> On Thu, Oct 1, 2009 at 15:26, Albe Laurenz <laurenz.albe@wien.gv.at> wrote:
> > Andrew Dunstan wrote:
> >>>> So here's the patch.
> >>>> I don't think there is documentation required;
> >>>> correct me if I am wrong.
> >>>
> >>> How will people know how to use it, or that it's even there without at
> >>> least a note in the docs somewhere?
> >>
> >> I'd prefer to have an example as a contrib module, as well as docs.
> >> Quite apart from anything else, how the heck would we test it without
> >> such a thing?
> >
> > I was not sure because no other hooks were documented anywhere else
> > than in the code.
> >
> > I could add a paragraph in the "auth-password" section of
> > client-auth.sgml. Or is there a better place?
> >
> > I could easily write a simple contrib that adds a check for
> > username = password if there is interest.
> 
> I think it's better to have an actually *useful* contrib module for
> it, if there is one. Meaning perhaps something that links to that
> cracklib thing mentioned upthread.
> 

+1 for a sample module that will allow cracklib to drop in.

Cheers,
Ken

Re: Rejecting weak passwords

Aidan Van Dyk <aidan@highrise.ca> — 2009-10-01T14:08:57Z

* Albe Laurenz <laurenz.albe@wien.gv.at> [091001 08:54]:
> In the discussion following
> http://archives.postgresql.org/pgsql-hackers/2009-09/msg01766.php
> the consensus was that a hook that allows you to implement
> a password checking routine as a module "would not hurt".
> 
> So here's the patch.
> I don't think there is documentation required;
> correct me if I am wrong.

Can we have the check password hook function called in both the
plaintext/encrypted case, with a flag as to whether it's encrypted or
not?

a.

-- 
Aidan Van Dyk                                             Create like a god,
aidan@highrise.ca                                       command like a king,
http://www.highrise.ca/                                   work like a slave.

Re: Rejecting weak passwords

Albe Laurenz <laurenz.albe@wien.gv.at> — 2009-10-01T15:07:26Z

Aidan Van Dyk wrote:
> Can we have the check password hook function called in both the
> plaintext/encrypted case, with a flag as to whether it's encrypted or
> not?

It will be called in both cases, and I figured that you can
check yourself the same way that PostgreSQL does:
If isMD5(password), then it is treated as an encrypted password.

Yours,
Laurenz Albe

Re: Rejecting weak passwords

Tom Lane <tgl@sss.pgh.pa.us> — 2009-10-01T15:24:02Z

Dave Page <dpage@pgadmin.org> writes:
> How will people know how to use it, or that it's even there without at
> least a note in the docs somewhere?

We don't normally document function hooks anywhere but the source code.
That's one of the things that makes them low overhead ;-)

I agree with the subsequent comments suggesting a sample module that
actually does something useful --- although if it's going to link to
external code like cracklib, it probably is going to have to be on
pgfoundry not in contrib.

			regards, tom lane

Re: Rejecting weak passwords

Magnus Hagander <magnus@hagander.net> — 2009-10-01T16:56:00Z

On Thu, Oct 1, 2009 at 17:24, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Dave Page <dpage@pgadmin.org> writes:
>> How will people know how to use it, or that it's even there without at
>> least a note in the docs somewhere?
>
> We don't normally document function hooks anywhere but the source code.
> That's one of the things that makes them low overhead ;-)
>
> I agree with the subsequent comments suggesting a sample module that
> actually does something useful --- although if it's going to link to
> external code like cracklib, it probably is going to have to be on
> pgfoundry not in contrib.

Why is that? we have plenty of other things in contrib that rely on
external code, for example the uuid, xml or ssl stuff.


-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: Rejecting weak passwords

Tom Lane <tgl@sss.pgh.pa.us> — 2009-10-01T17:07:04Z

Magnus Hagander <magnus@hagander.net> writes:
> On Thu, Oct 1, 2009 at 17:24, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> I agree with the subsequent comments suggesting a sample module that
>> actually does something useful --- although if it's going to link to
>> external code like cracklib, it probably is going to have to be on
>> pgfoundry not in contrib.

> Why is that? we have plenty of other things in contrib that rely on
> external code, for example the uuid, xml or ssl stuff.

Well, maybe.  I was concerned about availability, portability, license
compatibility, and so on.  The bar's a lot lower for pgfoundry projects
on all those points ...

			regards, tom lane

Re: Rejecting weak passwords

Euler Taveira <euler@timbira.com> — 2009-10-01T17:07:48Z

Magnus Hagander escreveu:
> On Thu, Oct 1, 2009 at 17:24, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Dave Page <dpage@pgadmin.org> writes:
>>> How will people know how to use it, or that it's even there without at
>>> least a note in the docs somewhere?
>> We don't normally document function hooks anywhere but the source code.
>> That's one of the things that makes them low overhead ;-)
>>
>> I agree with the subsequent comments suggesting a sample module that
>> actually does something useful --- although if it's going to link to
>> external code like cracklib, it probably is going to have to be on
>> pgfoundry not in contrib.
> 
> Why is that? we have plenty of other things in contrib that rely on
> external code, for example the uuid, xml or ssl stuff.
> 
Because cracklib is GPL'ed.


-- 
  Euler Taveira de Oliveira
  http://www.timbira.com/

Re: Rejecting weak passwords

Magnus Hagander <magnus@hagander.net> — 2009-10-01T17:12:32Z

On Thu, Oct 1, 2009 at 19:07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Magnus Hagander <magnus@hagander.net> writes:
>> On Thu, Oct 1, 2009 at 17:24, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> I agree with the subsequent comments suggesting a sample module that
>>> actually does something useful --- although if it's going to link to
>>> external code like cracklib, it probably is going to have to be on
>>> pgfoundry not in contrib.
>
>> Why is that? we have plenty of other things in contrib that rely on
>> external code, for example the uuid, xml or ssl stuff.
>
> Well, maybe.  I was concerned about availability, portability, license
> compatibility, and so on.  The bar's a lot lower for pgfoundry projects
> on all those points ...

Fair enough. And on the licensing issue.

That said, it would still be good to have something actually *useful*
in contrib. A bit more than just comparing userid and password.
Perhaps at least being able to set the min length, and the requirement
on having >1 "character class"?


-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: Rejecting weak passwords

ktm@rice.edu <ktm@rice.edu> — 2009-10-01T17:19:40Z

On Thu, Oct 01, 2009 at 01:07:04PM -0400, Tom Lane wrote:
> Magnus Hagander <magnus@hagander.net> writes:
> > On Thu, Oct 1, 2009 at 17:24, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >> I agree with the subsequent comments suggesting a sample module that
> >> actually does something useful --- although if it's going to link to
> >> external code like cracklib, it probably is going to have to be on
> >> pgfoundry not in contrib.
> 
> > Why is that? we have plenty of other things in contrib that rely on
> > external code, for example the uuid, xml or ssl stuff.
> 
> Well, maybe.  I was concerned about availability, portability, license
> compatibility, and so on.  The bar's a lot lower for pgfoundry projects
> on all those points ...
> 
> 			regards, tom lane
> 
It has been a while since I last used cracklib, but the interface
is generic enough that the sample we would ship in contrib could
be trivially adapted to use cracklib. The version we include could
just have the (username = password) check or something similar.

Regards,
Ken

Re: Rejecting weak passwords

Andrew Dunstan <andrew@dunslane.net> — 2009-10-01T17:34:27Z

Tom Lane wrote:
> Magnus Hagander <magnus@hagander.net> writes:
>   
>> On Thu, Oct 1, 2009 at 17:24, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>     
>>> I agree with the subsequent comments suggesting a sample module that
>>> actually does something useful --- although if it's going to link to
>>> external code like cracklib, it probably is going to have to be on
>>> pgfoundry not in contrib.
>>>       
>
>   
>> Why is that? we have plenty of other things in contrib that rely on
>> external code, for example the uuid, xml or ssl stuff.
>>     
>
> Well, maybe.  I was concerned about availability, portability, license
> compatibility, and so on.  The bar's a lot lower for pgfoundry projects
> on all those points ...
>
>   

Yeah. I don't want to add another external dependency if we can avoid 
it. A module that depends on another library needs configure support 
etc. Surely we could  provide something at least mildly useful without 
it getting too complex or depending on an external library.

Remember, this is an example, not meant to have all the bells and 
whistles anyone could want.

cheers

andrew

Re: Rejecting weak passwords

Tom Lane <tgl@sss.pgh.pa.us> — 2009-10-01T17:46:01Z

Magnus Hagander <magnus@hagander.net> writes:
> That said, it would still be good to have something actually *useful*
> in contrib. A bit more than just comparing userid and password.
> Perhaps at least being able to set the min length, and the requirement
> on having >1 "character class"?

+1.  There's still the issue of not being able to do much with a
pre-MD5'd password, though.

			regards, tom lane

Re: Rejecting weak passwords

Bruce Momjian <bruce@momjian.us> — 2009-10-02T22:56:09Z

Tom Lane wrote:
> Magnus Hagander <magnus@hagander.net> writes:
> > That said, it would still be good to have something actually *useful*
> > in contrib. A bit more than just comparing userid and password.
> > Perhaps at least being able to set the min length, and the requirement
> > on having >1 "character class"?
> 
> +1.  There's still the issue of not being able to do much with a
> pre-MD5'd password, though.

Agreed.  I am still a little worried that people will think they are
checking for weak passwords when, for MD5, they are not.  I am also
worried that people will unknowingly reduce their security (not use MD5)
to allow weak password checking.

-- 
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

Re: Rejecting weak passwords

Albe Laurenz <laurenz.albe@wien.gv.at> — 2009-10-03T09:03:53Z

Bruce Momjian wrote:
>Tom Lane wrote:
>> > That said, it would still be good to have something actually *useful*
>> > in contrib. A bit more than just comparing userid and password.
>> > Perhaps at least being able to set the min length, and the requirement
>> > on having >1 "character class"?
>>
>> +1.  There's still the issue of not being able to do much with a
>> pre-MD5'd password, though.
>
> Agreed.  I am still a little worried that people will think they are
> checking for weak passwords when, for MD5, they are not.  I am also
> worried that people will unknowingly reduce their security (not use MD5)
> to allow weak password checking.

I will write a contrib that does some password checking.

I'll add comments to the code to show where and how to add cracklib
if you want to.

I will write a conspicuous noisy warning into the README that
warns about the limits with respect to MD5 and states that for really
good security you should consider external authentication.

Due to time constraints at work I won't be able to start working on
it before end of next week.

Yours,
Laurenz Albe

Re: Rejecting weak passwords

Albe Laurenz <laurenz.albe@wien.gv.at> — 2009-10-09T11:47:59Z

Following the discussions in
http://archives.postgresql.org/pgsql-hackers/2009-09/msg01766.php
and
http://archives.postgresql.org/pgsql-hackers/2009-10/msg00025.php ,
here are patches for

a) a hook in backend/commands/user.c that allows one to add
   password checking functions
b) a contrib module that makes use of the hook and
c) documentation for the contrib module.

I'll add links to the November commitfest page.

Yours,
Laurenz Albe

Re: Rejecting weak passwords

Albe Laurenz <laurenz.albe@wien.gv.at> — 2009-10-12T06:22:52Z

I wrote:
> Following the discussions in
> http://archives.postgresql.org/pgsql-hackers/2009-09/msg01766.php
> and
> http://archives.postgresql.org/pgsql-hackers/2009-10/msg00025.php ,
> here are patches for
> 
> a) a hook in backend/commands/user.c that allows one to add
>    password checking functions
> b) a contrib module that makes use of the hook and
> c) documentation for the contrib module.

I found a small but embarrassing bug - here is another version.

Yours,
Laurenz Albe

Re: Rejecting weak passwords

Dave Page <dpage@pgadmin.org> — 2009-10-14T14:27:34Z

On Mon, Sep 28, 2009 at 7:37 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

>> IOW, having plaintext password in CREATE/ALTER time which can
>> then checked for weaknesses is better that MD5 password, which
>> actually does not increase security.
>
> This is not acceptable and will not happen.  The case that ENCRYPTED
> protects against is database superusers finding out other users'
> original passwords, which is a security issue to the extent that those
> users have used the same/similar passwords for other systems.
> We're not going to give up protection for that in order to provide
> an option to do weak-password checking in a place that simply isn't
> the best place to do it anyway.

This is an area in which we often get beaten up in in technical
evaluations against other DBMSs. Password complexity checks are pretty
much standard features in other products and it hurts our adoption not
to be able to offer them, especially in large shops where the first
phase of the eval may be a simple box-checking exercise.

I would suggest that in addition to the proposed plugin, we add an
suset GUC (defaulting to OFF) which rejects any use of WITH ENCRYPTED
PASSWORD to ensure that the password complexity can be checked when
roles are created or modified.

In the default case, the current behaviour would not change.

With the GUC turned on, passwords could be forced through the
validation module. To allow dumps to remain secure, the GUC can be
turned off by the DBA prior to loading, or in the dump itself. Logging
of CREATE/ALTER users statements in this mode could be special-cased
to prevent passwords going to the logs/stats (not sure what overhead
that might incur though). In addition, the docs for the GUC would
obviously point out that it should only be used in conjunction with
SSL connections.

This would allow us to remain secure-by-default, and yet offer an
important option for many potential users.

-- 
Dave Page
EnterpriseDB UK:   http://www.enterprisedb.com

Re: Rejecting weak passwords

Tom Lane <tgl@sss.pgh.pa.us> — 2009-10-14T15:11:55Z

Dave Page <dpage@pgadmin.org> writes:
> I would suggest that in addition to the proposed plugin, we add an
> suset GUC (defaulting to OFF) which rejects any use of WITH ENCRYPTED
> PASSWORD to ensure that the password complexity can be checked when
> roles are created or modified.

That's going to stop us from being beat up?  A GUC that forcibly
*weakens* security?  I can't see it.

If you're really intent on making that happen, you can have your
password checker plugin reject crypted passwords; we don't need
such a questionable rule in core.

			regards, tom lane

Re: Rejecting weak passwords

Andrew Dunstan <andrew@dunslane.net> — 2009-10-14T15:22:43Z


Tom Lane wrote:
> Dave Page <dpage@pgadmin.org> writes:
>   
>> I would suggest that in addition to the proposed plugin, we add an
>> suset GUC (defaulting to OFF) which rejects any use of WITH ENCRYPTED
>> PASSWORD to ensure that the password complexity can be checked when
>> roles are created or modified.
>>     
>
> That's going to stop us from being beat up?  A GUC that forcibly
> *weakens* security?  I can't see it.
>
> If you're really intent on making that happen, you can have your
> password checker plugin reject crypted passwords; we don't need
> such a questionable rule in core.
>
> 			
>   

And you could have the plugin do that depending on a custom GUC.

cheers

andrew

Re: Rejecting weak passwords

Dave Page <dpage@pgadmin.org> — 2009-10-14T15:25:49Z

On Wed, Oct 14, 2009 at 4:11 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Dave Page <dpage@pgadmin.org> writes:
>> I would suggest that in addition to the proposed plugin, we add an
>> suset GUC (defaulting to OFF) which rejects any use of WITH ENCRYPTED
>> PASSWORD to ensure that the password complexity can be checked when
>> roles are created or modified.
>
> That's going to stop us from being beat up?  A GUC that forcibly
> *weakens* security?  I can't see it.

If the communications channel uses SSL, and passwords are prevented
from hitting the logs then (assuming there are no other weaknesses I
haven't thought of), then the net effect would surely be tighter
overall security?

In a very security-conscious shop, the DBA won't have access to the
underlying system at all, so debugging tools etc would be out of the
question. In most shops, he will have access and can already just set
the auth method to 'password' and then break out the debugger (or even
replace the executables), so I can't see that this option would open
up any obvious new attack vectors.

Users are almost always the biggest weak-point in any security system,
so should naturally be the first hole we look at plugging, before the
ones that are much harder to exploit effectively - especially when
those are only open to exploit by people who already have superuser
privileges!

> If you're really intent on making that happen, you can have your
> password checker plugin reject crypted passwords; we don't need
> such a questionable rule in core.

Client software would need to have a standard way to know when to use
ENCRYPTED PASSWORD or not.

-- 
Dave Page
EnterpriseDB UK:   http://www.enterprisedb.com

Re: Rejecting weak passwords

Tom Lane <tgl@sss.pgh.pa.us> — 2009-10-14T15:30:49Z

Dave Page <dpage@pgadmin.org> writes:
> On Wed, Oct 14, 2009 at 4:11 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> If you're really intent on making that happen, you can have your
>> password checker plugin reject crypted passwords; we don't need
>> such a questionable rule in core.

> Client software would need to have a standard way to know when to use
> ENCRYPTED PASSWORD or not.

Oh, so you want us to propagate extra support for this blatant security
reduction all over the system too?  No thank you.

This whole line of discussion just proves the point that was made
originally: it would be a lot better to do whatever checking you want
done on the client side, rather than risk transmitting unencrypted
passwords.  If you are going to imagine that client-side software knows
about such a GUC, you might as well imagine that they have cracklib
built in.

			regards, tom lane

Re: Rejecting weak passwords

Marko Kreen <markokr@gmail.com> — 2009-10-14T15:32:08Z

On 10/14/09, Dave Page <dpage@pgadmin.org> wrote:
> On Mon, Sep 28, 2009 at 7:37 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
>  >> IOW, having plaintext password in CREATE/ALTER time which can
>  >> then checked for weaknesses is better that MD5 password, which
>  >> actually does not increase security.
>  >
>  > This is not acceptable and will not happen.  The case that ENCRYPTED
>  > protects against is database superusers finding out other users'
>  > original passwords, which is a security issue to the extent that those
>  > users have used the same/similar passwords for other systems.
>  > We're not going to give up protection for that in order to provide
>  > an option to do weak-password checking in a place that simply isn't
>  > the best place to do it anyway.
>
>
> This is an area in which we often get beaten up in in technical
>  evaluations against other DBMSs. Password complexity checks are pretty
>  much standard features in other products and it hurts our adoption not
>  to be able to offer them, especially in large shops where the first
>  phase of the eval may be a simple box-checking exercise.
>
>  I would suggest that in addition to the proposed plugin, we add an
>  suset GUC (defaulting to OFF) which rejects any use of WITH ENCRYPTED
>  PASSWORD to ensure that the password complexity can be checked when
>  roles are created or modified.

Checking 1M common weak combinations against the md5 hashed password
will take < 1s.  You cannot count the commas in password, but thats it.
The usual problems: 'username', '1234' can be still detected.

>  This would allow us to remain secure-by-default, and yet offer an
>  important option for many potential users.

-- 
marko

Re: Rejecting weak passwords

Dave Page <dpage@pgadmin.org> — 2009-10-14T15:44:27Z

On Wed, Oct 14, 2009 at 4:30 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Dave Page <dpage@pgadmin.org> writes:
>> On Wed, Oct 14, 2009 at 4:11 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> If you're really intent on making that happen, you can have your
>>> password checker plugin reject crypted passwords; we don't need
>>> such a questionable rule in core.
>
>> Client software would need to have a standard way to know when to use
>> ENCRYPTED PASSWORD or not.
>
> Oh, so you want us to propagate extra support for this blatant security
> reduction all over the system too?  No thank you.

You've twice asserted it's a reduction without providing any arguments
to back that up. I argue that you *possibly* open a very hard to
exploit hole, which is exploitable only by sysadmins/DBAs, in return
for which you close a very large hole that allows users to reuse
passwords or use common or easy to guess words.

If I am incorrect or have missed an important point, please explain why or what.

> This whole line of discussion just proves the point that was made
> originally: it would be a lot better to do whatever checking you want
> done on the client side, rather than risk transmitting unencrypted
> passwords.  If you are going to imagine that client-side software knows
> about such a GUC, you might as well imagine that they have cracklib
> built in.

Surely you can see that it is *absolutely pointless* to put an
password complexity checking in the client? All a user would need to
do is grab a copy of psql to bypass it. If they can't do that, there's
probably a scripting language or 12 that would make it similarly easy.

-- 
Dave Page
EnterpriseDB UK:   http://www.enterprisedb.com

Re: Rejecting weak passwords

Robert Haas <robertmhaas@gmail.com> — 2009-10-14T15:53:06Z

On Wed, Oct 14, 2009 at 11:44 AM, Dave Page <dpage@pgadmin.org> wrote:
> On Wed, Oct 14, 2009 at 4:30 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Dave Page <dpage@pgadmin.org> writes:
>>> On Wed, Oct 14, 2009 at 4:11 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>>> If you're really intent on making that happen, you can have your
>>>> password checker plugin reject crypted passwords; we don't need
>>>> such a questionable rule in core.
>>
>>> Client software would need to have a standard way to know when to use
>>> ENCRYPTED PASSWORD or not.
>>
>> Oh, so you want us to propagate extra support for this blatant security
>> reduction all over the system too?  No thank you.
>
> You've twice asserted it's a reduction without providing any arguments
> to back that up. I argue that you *possibly* open a very hard to
> exploit hole, which is exploitable only by sysadmins/DBAs, in return
> for which you close a very large hole that allows users to reuse
> passwords or use common or easy to guess words.
>
> If I am incorrect or have missed an important point, please explain why or what.
>
>> This whole line of discussion just proves the point that was made
>> originally: it would be a lot better to do whatever checking you want
>> done on the client side, rather than risk transmitting unencrypted
>> passwords.  If you are going to imagine that client-side software knows
>> about such a GUC, you might as well imagine that they have cracklib
>> built in.
>
> Surely you can see that it is *absolutely pointless* to put an
> password complexity checking in the client? All a user would need to
> do is grab a copy of psql to bypass it. If they can't do that, there's
> probably a scripting language or 12 that would make it similarly easy.

To all of the above, +1.

...Robert

Re: Rejecting weak passwords

Tom Lane <tgl@sss.pgh.pa.us> — 2009-10-14T16:08:34Z

Dave Page <dpage@pgadmin.org> writes:
> You've twice asserted it's a reduction without providing any arguments
> to back that up.

You quoted two good arguments why it's insecure in your original
message, neither of which your proposed GUC does anything to protect
against; and you also admitted that there might be other leakage paths
we haven't thought of.  That seems to me to be more than sufficient
reason to not encourage people to go back to passing unencrypted
passwords around.

			regards, tom lane

Re: Rejecting weak passwords

Dave Page <dpage@pgadmin.org> — 2009-10-14T16:16:49Z

On Wed, Oct 14, 2009 at 5:08 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Dave Page <dpage@pgadmin.org> writes:
>> You've twice asserted it's a reduction without providing any arguments
>> to back that up.
>
> You quoted two good arguments why it's insecure in your original
> message, neither of which your proposed GUC does anything to protect
> against;

I see one, and I proposed masking passwords in any relevant queries
before they were written to the stats or logs to mitigate that.

> and you also admitted that there might be other leakage paths
> we haven't thought of.  That seems to me to be more than sufficient
> reason to not encourage people to go back to passing unencrypted
> passwords around.

Yes. Which is why I asked your opinion as there's a far greater chance
you would know of any such paths than I, *and* whether they represent
a greater risk than the complete lack of control over the
effectiveness of users passwords that we currently have.

-- 
Dave Page
EnterpriseDB UK:   http://www.enterprisedb.com

Re: Rejecting weak passwords

Tom Lane <tgl@sss.pgh.pa.us> — 2009-10-14T16:25:25Z

Dave Page <dpage@pgadmin.org> writes:
> On Wed, Oct 14, 2009 at 5:08 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> I see one, and I proposed masking passwords in any relevant queries
> before they were written to the stats or logs to mitigate that.

Let's see you do that (hint: "CREATD USER ... PASSWORD" is going to
throw a syntax error before you realize there's anything there that
might need to be protected).

And you ignored the question of insecure transmission pathways, anyway.
By the time the backend has figured out that it's got a CREATE USER
... PASSWORD command, it's already way too late if the client sent it
over a non-SSL connection.

Marko has pointed out repeatedly that a plugin can catch the worst
cases of insecure passwords even when given a pre-md5'd password.
So you can use a plugin that does it that way, or if you want you
can use a plugin that throws error on a pre-md5'd password.  I do
not see a reason for us to add a boatload of questionable logic
that favors the latter approach.

			regards, tom lane

Re: Rejecting weak passwords

Dave Page <dpage@pgadmin.org> — 2009-10-14T16:37:49Z

On Wed, Oct 14, 2009 at 5:25 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Dave Page <dpage@pgadmin.org> writes:
>> On Wed, Oct 14, 2009 at 5:08 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> I see one, and I proposed masking passwords in any relevant queries
>> before they were written to the stats or logs to mitigate that.
>
> Let's see you do that (hint: "CREATD USER ... PASSWORD" is going to
> throw a syntax error before you realize there's anything there that
> might need to be protected).
>
> And you ignored the question of insecure transmission pathways, anyway.
> By the time the backend has figured out that it's got a CREATE USER
> ... PASSWORD command, it's already way too late if the client sent it
> over a non-SSL connection.

These are exactly the sort of issues I've been trying to get you to
comment on. Thank you.

> Marko has pointed out repeatedly that a plugin can catch the worst
> cases of insecure passwords even when given a pre-md5'd password.

My issues with that approach is that you may have to maintain a huge
library of passwords - which you're never going to do efficiently or
effectively if you assume that users may use simple variations of
their username for example - eg. t0mlane.

> So you can use a plugin that does it that way, or if you want you
> can use a plugin that throws error on a pre-md5'd password.

My only concern with that idea is having some way for the client to
know when to not hash the password. Figuring that out from a specific
error code and then retrying would be inefficient, ugly, and in the
case of pgAdmin, quite hard to do given the way that SQL is generated
and then executed.

-- 
Dave Page
EnterpriseDB UK:   http://www.enterprisedb.com

Re: Rejecting weak passwords

Magnus Hagander <magnus@hagander.net> — 2009-10-14T16:38:39Z

On Wed, Oct 14, 2009 at 18:25, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Dave Page <dpage@pgadmin.org> writes:
>> On Wed, Oct 14, 2009 at 5:08 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> I see one, and I proposed masking passwords in any relevant queries
>> before they were written to the stats or logs to mitigate that.
>
> Let's see you do that (hint: "CREATD USER ... PASSWORD" is going to
> throw a syntax error before you realize there's anything there that
> might need to be protected).

I'm unsure if it's our responsibility to think about that. We can leak
a *lot* of sensitive information to the logs through syntax errors,
this is just one of them. We *do* need to worry about the statements
when they are sent properly, of course.

So throwing out a wild idea that's probably just wild enough to even
consider, but one way to deal with the logging side of things would be
to deprecate/remove ALTER USER/CREATE USER with password, and add a
separate API call. With a separate wire protocol packet. That would
certainly take care of the logging part ;)

> And you ignored the question of insecure transmission pathways, anyway.
> By the time the backend has figured out that it's got a CREATE USER
> ... PASSWORD command, it's already way too late if the client sent it
> over a non-SSL connection.

Encryption policy of this type cannot be set at the server. Ever. And
it shouldn't.

If the sysadmin is worried someone might sniff the traffic, he should
require SSL, or use a VPN, or similar things.

If he's fine with letting the client decide, then it's not a problem,
because it's up to the client not to send the password that way. And
if the client did, well, the sysadmin just said it was ok to let the
client decide, so we're ok.

> Marko has pointed out repeatedly that a plugin can catch the worst
> cases of insecure passwords even when given a pre-md5'd password.
> So you can use a plugin that does it that way, or if you want you
> can use a plugin that throws error on a pre-md5'd password.  I do
> not see a reason for us to add a boatload of questionable logic
> that favors the latter approach.

it lets you find some passwords, but it does not let you set a
*policy*. As is often required by security policies etc.

Does anybody know how other databases that *do* implement this solve
the problem?

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: Rejecting weak passwords

Tom Lane <tgl@sss.pgh.pa.us> — 2009-10-14T16:59:24Z

Dave Page <dpage@pgadmin.org> writes:
> On Wed, Oct 14, 2009 at 5:25 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> So you can use a plugin that does it that way, or if you want you
>> can use a plugin that throws error on a pre-md5'd password.

> My only concern with that idea is having some way for the client to
> know when to not hash the password. Figuring that out from a specific
> error code and then retrying would be inefficient, ugly, and in the
> case of pgAdmin, quite hard to do given the way that SQL is generated
> and then executed.

I don't actually *want* client software deciding on its own hook to not
hash the password.  The only reason we have special client-side support
for this command at all is to pre-hash the password, and people are
accustomed to that behavior.  If psql or pgAdmin takes a password and
then sends it in the clear without telling me, that's a breach of trust
with potentially serious consequences.  I might not trust the DBA, for
example, or I might be less confident of the network infrastructure
than he is.

Fix pgAdmin so that this is a user-controlled switch and let the user
read the plugin's error message and decide if he wants to retry with a
non-encrypted password.

			regards, tom lane

Re: Rejecting weak passwords

Tom Lane <tgl@sss.pgh.pa.us> — 2009-10-14T17:08:34Z

Magnus Hagander <magnus@hagander.net> writes:
> On Wed, Oct 14, 2009 at 18:25, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Let's see you do that (hint: "CREATD USER ... PASSWORD" is going to
>> throw a syntax error before you realize there's anything there that
>> might need to be protected).

> I'm unsure if it's our responsibility to think about that. We can leak
> a *lot* of sensitive information to the logs through syntax errors,
> this is just one of them. We *do* need to worry about the statements
> when they are sent properly, of course.

Even if they're "sent properly", this entire discussion misses the point.
The reason to not want cleartext passwords in the logs is that the user
doesn't trust the DBA.  Why would a user who doesn't trust the DBA
want to trust him to not be running a modified copy of the database with
all this nice logic disabled?

The real point of crypted passwords is to not let uncrypted passwords
go anywhere outside the *user's* control.  If the DBA wants to enforce
a policy that is incompatible with that, it should be extremely obvious
to all concerned that that's what he's doing.  In particular it should
be in the user's face that he's about to send an uncrypted password,
so that he can think twice about the particular password he's choosing
(and hopefully not use one that's also good for another service).  For
relatively smart clients like pgAdmin, there might also be an option
to refuse to send such a command across an insecure connection, or at
least nag the user about it.

			regards, tom lane

Re: Rejecting weak passwords

Bruce Momjian <bruce@momjian.us> — 2009-10-14T17:28:28Z

nMagnus Hagander wrote:
> > Marko has pointed out repeatedly that a plugin can catch the worst
> > cases of insecure passwords even when given a pre-md5'd password.
> > So you can use a plugin that does it that way, or if you want you
> > can use a plugin that throws error on a pre-md5'd password. ?I do
> > not see a reason for us to add a boatload of questionable logic
> > that favors the latter approach.
> 
> it lets you find some passwords, but it does not let you set a
> *policy*. As is often required by security policies etc.

I see three checks we are trying to do on passwords:

	1) Password complexity enforcement/policies
	2) Password history - you can't reuse a password
	3) Account disable after X incorrect attempts

#2 can be done by doing an MD5 on the server of the old password and
comparing it to what the client sent --- no need for a client change
there.  #3 can be implemented in the server too.

#1 could be implemented in the server without client change if you are
willing to expand any wildcards in the password policy pattern, MD5
those, and check them against what was sent from the client, and this
only happens during password change.  However, that only can check for
simplistic patterns, like trailing digit, etc, but not for things like
upper/lower case usage, etc.  If you have such a policy, aren't you
already using LDAP or PAM, and can't those enforce it?

-- 
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

Re: Rejecting weak passwords

Robert Haas <robertmhaas@gmail.com> — 2009-10-14T17:38:25Z

On Wed, Oct 14, 2009 at 12:25 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Dave Page <dpage@pgadmin.org> writes:
>> On Wed, Oct 14, 2009 at 5:08 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> I see one, and I proposed masking passwords in any relevant queries
>> before they were written to the stats or logs to mitigate that.
>
> Let's see you do that (hint: "CREATD USER ... PASSWORD" is going to
> throw a syntax error before you realize there's anything there that
> might need to be protected).

It seems to me incredibly rare for anyone to issue a manual CREATE
USER command with an encrypted password.  And if it is generated by a
script, it will presumably not have a trivial typographical error.

> And you ignored the question of insecure transmission pathways, anyway.
> By the time the backend has figured out that it's got a CREATE USER
> ... PASSWORD command, it's already way too late if the client sent it
> over a non-SSL connection.

Using a non-SSL connection over an untrusted network is incredibly
stupid to begin with.  I'm not sure we should be basing our design
decisions around that scenario.

...Robert

Re: Rejecting weak passwords

Tom Lane <tgl@sss.pgh.pa.us> — 2009-10-14T17:48:45Z

Robert Haas <robertmhaas@gmail.com> writes:
> On Wed, Oct 14, 2009 at 12:25 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Let's see you do that (hint: "CREATD USER ... PASSWORD" is going to
>> throw a syntax error before you realize there's anything there that
>> might need to be protected).

> It seems to me incredibly rare for anyone to issue a manual CREATE
> USER command with an encrypted password.  And if it is generated by a
> script, it will presumably not have a trivial typographical error.

Uh, this discussion was about cleartext passwords?

			regards, tom lane

Re: Rejecting weak passwords

Greg Stark <gsstark@mit.edu> — 2009-10-14T18:42:04Z

On Wed, Oct 14, 2009 at 10:28 AM, Bruce Momjian <bruce@momjian.us> wrote:
>
> I see three checks we are trying to do on passwords:
>
>        1) Password complexity enforcement/policies
>        2) Password history - you can't reuse a password
>        3) Account disable after X incorrect attempts

This whole discussion seems very strange to me. Surely any
organization with rules like this will want them to be system-wide and
will have already implemented them in their PAM and LDAP systems
(assuming their not using Kerberos or something like that anyways).
There's not much point in reinventing the wheel in the database when
a) we'll never be remotely as complete as the existing authentication
systems -- the above requirements only barely scratch the surface and
b) even if we were as complete as existing systems it would never be
integrated so there would be nothing stopping people from reusing
passwords from their login account or trying passwords a limited
number of times against each system to get many attempts in total.

Incidentally I'm extremely dubious of systems that implement your goal
#3. It seems like more of an obvious DOS attack vector than a security
improvement to me. There are better defense mechanisms for such
attacks such as preauth. One more argument why we shouldn't be
reimplementing the wheel in an area where don't have particularly good
experience.

-- 
greg

Re: Rejecting weak passwords

Dave Page <dpage@pgadmin.org> — 2009-10-14T18:50:23Z

On Wed, Oct 14, 2009 at 7:42 PM, Greg Stark <gsstark@mit.edu> wrote:
> On Wed, Oct 14, 2009 at 10:28 AM, Bruce Momjian <bruce@momjian.us> wrote:
>>
>> I see three checks we are trying to do on passwords:
>>
>>        1) Password complexity enforcement/policies
>>        2) Password history - you can't reuse a password
>>        3) Account disable after X incorrect attempts
>
>
> This whole discussion seems very strange to me. Surely any
> organization with rules like this will want them to be system-wide and
> will have already implemented them in their PAM and LDAP systems
> (assuming their not using Kerberos or something like that anyways).

Because like it or not, this 'feature' is one that people *are*
looking for in early stages of evaluations, and it counts against us
and can hurt our adoption when we can't tick that box.

As an example, after years of only offering password policy management
via the NT domain/active directory authentication methods, even
Microsoft finally gave in and added policy management for their SQL
Server accounts with SQL 2k5.

-- 
Dave Page
EnterpriseDB UK:   http://www.enterprisedb.com

Re: Rejecting weak passwords

Dave Page <dpage@pgadmin.org> — 2009-10-14T18:56:35Z

On Wed, Oct 14, 2009 at 6:08 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Magnus Hagander <magnus@hagander.net> writes:
>> On Wed, Oct 14, 2009 at 18:25, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> Let's see you do that (hint: "CREATD USER ... PASSWORD" is going to
>>> throw a syntax error before you realize there's anything there that
>>> might need to be protected).
>
>> I'm unsure if it's our responsibility to think about that. We can leak
>> a *lot* of sensitive information to the logs through syntax errors,
>> this is just one of them. We *do* need to worry about the statements
>> when they are sent properly, of course.
>
> Even if they're "sent properly", this entire discussion misses the point.
> The reason to not want cleartext passwords in the logs is that the user
> doesn't trust the DBA.  Why would a user who doesn't trust the DBA
> want to trust him to not be running a modified copy of the database with
> all this nice logic disabled?

If you trust him that little, why would you use a password that you
also use elsewhere?

Besides, if he can run a modified version of the database, its game
over anyway. Just set pg_hba.conf's auth method to password, and you
don't even have to wait for the user to change their password - you
can grab it the next time he logs in.


-- 
Dave Page
EnterpriseDB UK:   http://www.enterprisedb.com

Re: Rejecting weak passwords

Robert Haas <robertmhaas@gmail.com> — 2009-10-14T19:17:00Z

On Wed, Oct 14, 2009 at 1:48 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>> On Wed, Oct 14, 2009 at 12:25 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> Let's see you do that (hint: "CREATD USER ... PASSWORD" is going to
>>> throw a syntax error before you realize there's anything there that
>>> might need to be protected).
>
>> It seems to me incredibly rare for anyone to issue a manual CREATE
>> USER command with an encrypted password.  And if it is generated by a
>> script, it will presumably not have a trivial typographical error.
>
> Uh, this discussion was about cleartext passwords?

I understand that.  The point is, you seemed to be worried that
log-obfuscation wouldn't work because someone might type "CREATD USER
... PASSWORD" rather than "CREATE USER ... PASSWORD".  But this can
happen today, too, can't it?  The only difference is that today the
password MIGHT be encrypted.  But if the user is really entering the
command manually, it's probably not.  Sure, someone COULD pre-MD5 a
string and then copy and paste it into a psql session, but I bet
that's not too common.  I suspect people using the pre-MD5 option are
using a more sophisticated client of some sort anyway.

...Robert

Re: Rejecting weak passwords

mark@mark.mielke.cc — 2009-10-14T19:20:37Z

On 10/14/2009 12:25 PM, Tom Lane wrote:
> Let's see you do that (hint: "CREATD USER ... PASSWORD" is going to
> throw a syntax error before you realize there's anything there that
> might need to be protected).
>
> And you ignored the question of insecure transmission pathways, anyway.
> By the time the backend has figured out that it's got a CREATE USER
> ... PASSWORD command, it's already way too late if the client sent it
> over a non-SSL connection.

It seems like the architectural problem here is that people think of SQL 
as being a valid way to change one's password.

I never thought it was valid?

What if, like "createdb", or "createuser", there was a "pgpasswd" that 
did all of the appropriate checks and provided the proper security (MD5 
today?) during transit and when storing to a log file? "createuser" 
already does password prompting - maybe it should allow checking the 
password from the "createuser" client side?

I think if we looked at the SQL commands as being a 'base operation not 
intended for everyday users', this entire debate would seem frivolous? :-)

Does Oracle really do password checks on the base SQL commands used to 
change an Oracle password? That sounds silly.

I'm totally on Tom's side. Having the server check passwords is the 
wrong solution to the problem. It makes things worse - not better. That 
they're approving a plugin capability on the server at all is generous, 
as it does lead to a sense of protection that may not be justifiable.

Cheers,
mark

-- 
Mark Mielke<mark@mielke.cc>

Re: Rejecting weak passwords

Tom Lane <tgl@sss.pgh.pa.us> — 2009-10-14T20:00:47Z

Dave Page <dpage@pgadmin.org> writes:
> On Wed, Oct 14, 2009 at 7:42 PM, Greg Stark <gsstark@mit.edu> wrote:
>> This whole discussion seems very strange to me. Surely any
>> organization with rules like this will want them to be system-wide and
>> will have already implemented them in their PAM and LDAP systems
>> (assuming their not using Kerberos or something like that anyways).

> Because like it or not, this 'feature' is one that people *are*
> looking for in early stages of evaluations, and it counts against us
> and can hurt our adoption when we can't tick that box.

Okay, fine, so we're not looking for actual high-grade security,
we're looking to tick off a checkbox in the minds of not terribly
well-informed people.  Then the plugin mechanism as currently proposed
will do the job just fine.  We do not need to put a whole bunch of
dubious extra infrastructure in there, and we DEFINITELY do not need
anything that can be painted as a backwards step security-wise.

			regards, tom lane

Re: Rejecting weak passwords

Dave Page <dpage@pgadmin.org> — 2009-10-14T20:10:38Z

On Wed, Oct 14, 2009 at 9:00 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Okay, fine, so we're not looking for actual high-grade security,
> we're looking to tick off a checkbox in the minds of not terribly
> well-informed people.  Then the plugin mechanism as currently proposed
> will do the job just fine.  We do not need to put a whole bunch of
> dubious extra infrastructure in there, and we DEFINITELY do not need
> anything that can be painted as a backwards step security-wise.

Nice exit strategy :-)

I said up front this was a box-ticking exercise for these folks,
however, rather than just tick the box and move on (meh - who cares if
we can store 2009-02-31 - it stores all the valid dates which are the
ones that matter :-p ) I prefer to discuss the issue and do the best
job we can to make it a practical, usable and useful feature - which
is kinda what we usually pride ourselves in doing!

-- 
Dave Page
EnterpriseDB UK:   http://www.enterprisedb.com

Re: Rejecting weak passwords

Tom Lane <tgl@sss.pgh.pa.us> — 2009-10-14T20:25:55Z

Dave Page <dpage@pgadmin.org> writes:
> I said up front this was a box-ticking exercise for these folks,
> however, rather than just tick the box and move on (meh - who cares if
> we can store 2009-02-31 - it stores all the valid dates which are the
> ones that matter :-p ) I prefer to discuss the issue and do the best
> job we can to make it a practical, usable and useful feature - which
> is kinda what we usually pride ourselves in doing!

Well, sure.  I just don't want to move backwards on other dimensions
in order to move forward on this one.  It's fair to argue that support
of pre-crypted passwords closes only some holes that can be closed in
other ways, but it's equally fair to argue that the limited capability
of a plugin that has to check pre-crypted passwords also represents a
corner case that can be solved in other ways.

			regards, tom lane

Re: Rejecting weak passwords

Kevin Grittner <kevin.grittner@wicourts.gov> — 2009-10-14T20:50:46Z

Dave Page <dpage@pgadmin.org> wrote:

> I said up front this was a box-ticking exercise for these folks,

Can they check the box if the provided clients include password
strength checking?  I'm just wondering if we're going at this the hard
way, if that really is the main goal.

>From the point of view of usefulness, wouldn't it be OK if clients
enforced the strength (or at least warned of weakness) *and* sent the
md5sum?

And, perhaps slightly off topic: if the login password is sent over a
non-encrypted stream, md5sum or not, can't someone use it to log in if
they're generating their own stream to connect?  Discussions of which
is the more secure way to change passwords seems a little silly if
you're only worried about environments where someone can sniff any
login sequence and spoof the user anyway.

> (meh - who cares if we can store 2009-02-31 - it stores all the
> valid dates which are the ones that matter :-p )

Oh, now that's just trolling -- you really don't want to open that can
of worms again, do you?   :-p

-Kevin

Re: Rejecting weak passwords

Tom Lane <tgl@sss.pgh.pa.us> — 2009-10-14T21:17:53Z

"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> And, perhaps slightly off topic: if the login password is sent over a
> non-encrypted stream, md5sum or not, can't someone use it to log in if
> they're generating their own stream to connect?

Not if they only capture a login exchange --- the password is doubly
encrypted during that.  If they see the md5'd password in a CREATE USER
command, then yeah, they could pass a subsequent md5 challenge, using
suitably modified client software that doesn't try to re-encrypt the
given password.

But the main point is to hide the cleartext password, in any case.

			regards, tom lane

Re: Rejecting weak passwords

Dave Page <dpage@pgadmin.org> — 2009-10-14T21:33:43Z

On Wed, Oct 14, 2009 at 9:50 PM, Kevin Grittner
<Kevin.Grittner@wicourts.gov> wrote:
> Dave Page <dpage@pgadmin.org> wrote:
>
>> I said up front this was a box-ticking exercise for these folks,
>
> Can they check the box if the provided clients include password
> strength checking?  I'm just wondering if we're going at this the hard
> way, if that really is the main goal.

No. Any checks at the client are worthless, as they can be bypassed by
10 minutes worth of simple coding in any of a dozen or more languages.

> And, perhaps slightly off topic: if the login password is sent over a
> non-encrypted stream, md5sum or not, can't someone use it to log in if
> they're generating their own stream to connect?  Discussions of which
> is the more secure way to change passwords seems a little silly if
> you're only worried about environments where someone can sniff any
> login sequence and spoof the user anyway.

No - see Tom's reply.

>> (meh - who cares if we can store 2009-02-31 - it stores all the
>> valid dates which are the ones that matter :-p )
>
> Oh, now that's just trolling -- you really don't want to open that can
> of worms again, do you?   :-p

Well, after 12+ years in these parts I figure anyone should get the
privilege of a small dig once in a while :-)

-- 
Dave Page
EnterpriseDB UK:   http://www.enterprisedb.com

Re: Rejecting weak passwords

Kevin Grittner <kevin.grittner@wicourts.gov> — 2009-10-14T21:45:51Z

Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:

>> if the login password is sent over a non-encrypted stream, md5sum
>> or not, can't someone use it to log in if they're generating their
>> own stream to connect?

> If they see the md5'd password in a CREATE USER command, then yeah

So anyone in a truly security-conscious environment should only change
the password through a trusted connection, whether or not it is md5'd?
(Trusted here meaning that the connection has suitable encryption or
it is local to the server, which is suitably secured.)

> But the main point is to hide the cleartext password, in any case.

Sure, sorry to drift off-topic, but the thought kept nagging at me. 
:-)  Anyway, I think it helped crystallize something for me -- the
client-side md5 mangling protects against malicious DBAs or database
server admins, while the trusted connection protects against network
snooping.  The former is more about keeping the bad guys, who would
have other ways of compromising the database, from gleaning
information about your password choices that they may be able to apply
to other resources.  The latter protects the database itself.

Did I miss any other forms of attack either addresses?

-Kevin

Re: Rejecting weak passwords

Kevin Grittner <kevin.grittner@wicourts.gov> — 2009-10-14T21:51:19Z

Dave Page <dpage@pgadmin.org> wrote:

> No. Any checks at the client are worthless, as they can be bypassed
> by 10 minutes worth of simple coding in any of a dozen or more
> languages.

Well, sure, but we're talking about a client going out of their way to
wrestle the point of the gun toward their own foot, aren't we?  If
we're worried about the user compromising their own password, we have
bigger problems, like that slip of paper in their desk drawer with the
password written on it.  I mean, I know some of these checklists can
be pretty brain-dead (I've been on both sides of the RFP process many
times), but it would seem over the top to say that client-side
password strength checks aren't OK for the reason you give.

-Kevin

Re: Rejecting weak passwords

Dave Page <dpage@pgadmin.org> — 2009-10-14T22:02:24Z

On Wed, Oct 14, 2009 at 10:51 PM, Kevin Grittner
<Kevin.Grittner@wicourts.gov> wrote:
> Dave Page <dpage@pgadmin.org> wrote:
>
>> No. Any checks at the client are worthless, as they can be bypassed
>> by 10 minutes worth of simple coding in any of a dozen or more
>> languages.
>
> Well, sure, but we're talking about a client going out of their way to
> wrestle the point of the gun toward their own foot, aren't we?  If
> we're worried about the user compromising their own password, we have
> bigger problems, like that slip of paper in their desk drawer with the
> password written on it.  I mean, I know some of these checklists can
> be pretty brain-dead (I've been on both sides of the RFP process many
> times), but it would seem over the top to say that client-side
> password strength checks aren't OK for the reason you give.

See my previous comment about dates. Check-box items aside, I have
absolutely no desire to try to give the illusion of a security
feature, when in reality any user could easily bypass it.


-- 
Dave Page
EnterpriseDB UK:   http://www.enterprisedb.com

Re: Rejecting weak passwords

Bruce Momjian <bruce@momjian.us> — 2009-10-14T22:07:45Z

Tom Lane wrote:
> "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> > And, perhaps slightly off topic: if the login password is sent over a
> > non-encrypted stream, md5sum or not, can't someone use it to log in if
> > they're generating their own stream to connect?
> 
> Not if they only capture a login exchange --- the password is doubly
> encrypted during that.  If they see the md5'd password in a CREATE USER
> command, then yeah, they could pass a subsequent md5 challenge, using
> suitably modified client software that doesn't try to re-encrypt the
> given password.
> 
> But the main point is to hide the cleartext password, in any case.

What if we added a GUC that only allowed password changes via an SSL
connection.  You could say that is a security enhancement, and
administrators could set up their systems to use 'password'
authentication for SSL and check the password strength on the server
because they come in clear-text.

-- 
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

Re: Rejecting weak passwords

Peter Eisentraut <peter_e@gmx.net> — 2009-10-14T22:12:50Z

On Wed, 2009-10-14 at 12:59 -0400, Tom Lane wrote:
> If psql or pgAdmin takes a password and
> then sends it in the clear without telling me, that's a breach of
> trust
> with potentially serious consequences.  I might not trust the DBA, for
> example, or I might be less confident of the network infrastructure
> than he is.

Well, you would lose anyway if the DBA switches the pg_hba.conf setting
from md5 to password without telling you.  There is usually no
straightforward way in client applications to guard against that.
Something to think about.

Re: Rejecting weak passwords

mark@mark.mielke.cc — 2009-10-14T22:21:15Z

On 10/14/2009 05:33 PM, Dave Page wrote:
> On Wed, Oct 14, 2009 at 9:50 PM, Kevin Grittner
> <Kevin.Grittner@wicourts.gov>  wrote:
>    
>> Can they check the box if the provided clients include password
>> strength checking?  I'm just wondering if we're going at this the hard
>> way, if that really is the main goal.
>>      
> No. Any checks at the client are worthless, as they can be bypassed by
> 10 minutes worth of simple coding in any of a dozen or more languages.
>    

Why care? If the client is purposefully disabling passwords checks to 
use a "weak" password - this is an entirely different problem from 
somebody trying a weak password and being allowed. Circumvention of 
process is always a risk, and should be dealt with as a human resources 
problem. Why not stop the admin from disabling the security check when 
they create their pgadmin password too? We can't trust anybody - right?

PAM does security checking client-side I think? I'm sure others do too?

I'm not saying server checks are worthless - but I think you are 
exaggerating to say that client checks are worthless. Sending the 
password in cleartext via SQL seems bad. Sending it encoded seems only 
marginally better. Sending it in MD5 is good but means that password 
strength needs to be done by the client. You are saying that it's worth 
the loss of security in one area, to improve security in another. 
Providing client checks in the "official" clients is probably sufficient 
for your checkbox that you think is so important. Unless you think it is 
impossible to circumvent process in any of these "other" databases that 
do such a better job?

Personally, I don't think PostgreSQL is the best place to manage 
passwords at this level anyways, beyond the basic usage. PostgreSQL 
shouldn't need to know the password, and the password should still be 
required to as strong as the organization requires it. Lots of other 
solutions here - PAM, LDAP, Kerberos, ... How much of these solutions 
should PostgreSQL re-implement?

Cheers,
mark

-- 
Mark Mielke<mark@mielke.cc>

Re: Rejecting weak passwords

Tom Lane <tgl@sss.pgh.pa.us> — 2009-10-14T22:23:19Z

Peter Eisentraut <peter_e@gmx.net> writes:
> Well, you would lose anyway if the DBA switches the pg_hba.conf setting
> from md5 to password without telling you.

True :-(.  Anybody for a zero-knowledge protocol?

(Realistically, non-password-based auth methods are the only real
solution here, I fear.  We should probably be doing more to encourage
people to use SSL-cert-based authentication in low-trust situations.)

			regards, tom lane

Re: Rejecting weak passwords

Peter Eisentraut <peter_e@gmx.net> — 2009-10-14T22:24:38Z

On Wed, 2009-10-14 at 13:08 -0400, Tom Lane wrote:
> The reason to not want cleartext passwords in the logs is that the
> user doesn't trust the DBA.

I originally implemented the encrypt-on-the-client logic because the
previous way of using ALTER USER ... PASSWORD would promiscuously leave
the clear-text password all over the place, including the psql history,
where it accidentally pops up again when you scroll up, or possibly the
pgAdmin log, and the server log where the DBA can't avoid reading it
even if he doesn't really want to, from where it goes through PgFouine
and onto a web server.

While guarding against a DBA you don't trust is a side-effect of this,
it should not really be a goal.  Most password systems that I can think
of pass the cleartext password into address space controlled by the
administrator at some point.  The goal should be to keep it there for as
little as possible and not spread it around randomly.  But users should
know that the password they enter into any system can be seen by the
administrator of the system.

The canonical solution for a situation where you don't trust anyone is
SSL client certificates.

Re: Rejecting weak passwords

Peter Eisentraut <peter_e@gmx.net> — 2009-10-14T22:25:21Z

On Wed, 2009-10-14 at 18:38 +0200, Magnus Hagander wrote:
> So throwing out a wild idea that's probably just wild enough to even
> consider, but one way to deal with the logging side of things would be
> to deprecate/remove ALTER USER/CREATE USER with password, and add a
> separate API call. With a separate wire protocol packet. That would
> certainly take care of the logging part ;)

I think that would be the correct fix.

Re: Rejecting weak passwords

Tom Lane <tgl@sss.pgh.pa.us> — 2009-10-14T22:28:12Z

Bruce Momjian <bruce@momjian.us> writes:
> Tom Lane wrote:
>> But the main point is to hide the cleartext password, in any case.

> What if we added a GUC that only allowed password changes via an SSL
> connection.

How's that help?  The user has already exposed their new choice of
password to any hypothetical eavesdropper.  Of course, if they're smart,
they'll pick a different password before they try again on a secure
connection ... but good luck hoping for that.

(And, again, there is ABSOLUTELY NO NEED for us to put such debatable
policies into the core.  Anyone who thinks that's a good idea can have
his password-check plugin enforce it.)

			regards, tom lane

Re: Rejecting weak passwords

mark@mark.mielke.cc — 2009-10-14T22:29:00Z

On 10/14/2009 06:02 PM, Dave Page wrote:
> On Wed, Oct 14, 2009 at 10:51 PM, Kevin Grittner
> <Kevin.Grittner@wicourts.gov>  wrote:
>    
>> Well, sure, but we're talking about a client going out of their way to
>> wrestle the point of the gun toward their own foot, aren't we?  If
>> we're worried about the user compromising their own password, we have
>> bigger problems, like that slip of paper in their desk drawer with the
>> password written on it.  I mean, I know some of these checklists can
>> be pretty brain-dead (I've been on both sides of the RFP process many
>> times), but it would seem over the top to say that client-side
>> password strength checks aren't OK for the reason you give.
>>      
> See my previous comment about dates. Check-box items aside, I have
> absolutely no desire to try to give the illusion of a security
> feature, when in reality any user could easily bypass it.

This is only true if you thing 'create/alter role ... password ...' is 
intended to be used by hand - and if you think this, are you not 
concerned that the password shows up on the screen as the user types this?

It's not an illusion if the user requires effort to bypass it.

Who are you protecting?

If you are trying to protect the user from themselves - I'd start by 
disabling 'create/alter role ... password ...' altogether and requiring 
some other means of the user securely setting a password. At our company 
we have a single-sign on system where users must login to a central 
password server to change their password. The central password server 
provides the password strength test. The intent is for none of the 
applications to ever know or see the password. It's not about trust - it 
is about principle. The DBA and/or application has no need or 
requirement to know the password. They only need to know that it is 
valid. For web-based applications, the user authentication is 
accomplished via a forward to one of the central password authentication 
servers, which upon successful login, forwards the user back to their 
intended application with a token that proves they have authenticated.

If you think security is such an important thing - why not go all the 
way? Disable the entry level security provided by PostgreSQL allowing 
SQL commands to 'set' a password entirely. Use a token-based system with 
a third party trusted authenticator.

I'm not being facetious. I think it's silly to argue that 50% is 
insufficient, and that lots of effort should be spent on reaching 55%, 
when you are clearly nowhere near 100% in either case.

Cheers,
mark

-- 
Mark Mielke<mark@mielke.cc>

Re: Rejecting weak passwords

mark@mark.mielke.cc — 2009-10-14T22:30:16Z

On 10/14/2009 06:25 PM, Peter Eisentraut wrote:
> On Wed, 2009-10-14 at 18:38 +0200, Magnus Hagander wrote:
>    
>> So throwing out a wild idea that's probably just wild enough to even
>> consider, but one way to deal with the logging side of things would be
>> to deprecate/remove ALTER USER/CREATE USER with password, and add a
>> separate API call. With a separate wire protocol packet. That would
>> certainly take care of the logging part ;)
>>      
> I think that would be the correct fix.
>    

Yep. +1. If we are really so paranoid.

Cheers,
mark

-- 
Mark Mielke<mark@mielke.cc>

Re: Rejecting weak passwords

Stephen Frost <sfrost@snowman.net> — 2009-10-14T22:44:47Z

* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Peter Eisentraut <peter_e@gmx.net> writes:
> > Well, you would lose anyway if the DBA switches the pg_hba.conf setting
> > from md5 to password without telling you.
> 
> True :-(.  Anybody for a zero-knowledge protocol?
> 
> (Realistically, non-password-based auth methods are the only real
> solution here, I fear.  We should probably be doing more to encourage
> people to use SSL-cert-based authentication in low-trust situations.)

Or GSSAPI..  Helping users understand how they can leverage their
existing Kerberos or MS SSPI single-sign-on infrastructures to securely
access PG would go a long way to reducing the password-based usage out
there, imo.  Of course, it'd be nice if we supported GSSAPI encrypted
transport too.  Separating the encryption into SSL is less than ideal.

	Stephen

Re: Rejecting weak passwords

Dave Page <dpage@pgadmin.org> — 2009-10-15T07:54:09Z

On Wed, Oct 14, 2009 at 11:21 PM, Mark Mielke <mark@mark.mielke.cc> wrote:
> On 10/14/2009 05:33 PM, Dave Page wrote:
>>
>> No. Any checks at the client are worthless, as they can be bypassed by
>> 10 minutes worth of simple coding in any of a dozen or more languages.
>>
>
> Why care?

Because many large (and small for that matter) organisations also have
security policies which mandate the enforcement of specific password
policies. Just because you think it's worthless to try to prevent
someone reusing a password, or using 'password' doesn't mean that
everyone else does. Some organisations will use such a feature in a
box-ticking exercise when evaluating, and others may actually decide
to use the feature, and expect it to work effectively.

Beside, we are not in the habit of putting half-arsed features in
PostgreSQL. If we do something, we do it properly.

-- 
Dave Page
EnterpriseDB UK:   http://www.enterprisedb.com

Re: Rejecting weak passwords

Dave Page <dpage@pgadmin.org> — 2009-10-15T08:00:30Z

On Wed, Oct 14, 2009 at 11:44 PM, Stephen Frost <sfrost@snowman.net> wrote:
> * Tom Lane (tgl@sss.pgh.pa.us) wrote:
>> Peter Eisentraut <peter_e@gmx.net> writes:
>> > Well, you would lose anyway if the DBA switches the pg_hba.conf setting
>> > from md5 to password without telling you.
>>
>> True :-(.  Anybody for a zero-knowledge protocol?
>>
>> (Realistically, non-password-based auth methods are the only real
>> solution here, I fear.  We should probably be doing more to encourage
>> people to use SSL-cert-based authentication in low-trust situations.)
>
> Or GSSAPI..  Helping users understand how they can leverage their
> existing Kerberos or MS SSPI single-sign-on infrastructures to securely
> access PG would go a long way to reducing the password-based usage out
> there, imo.  Of course, it'd be nice if we supported GSSAPI encrypted
> transport too.  Separating the encryption into SSL is less than ideal.

Such solutions are exactly what I'd expect to actually go into
production in most places, but that doesn't mean that people don't pay
attention to the basic features offered as part of the core database
when they're early in the evaluation phase.

-- 
Dave Page
EnterpriseDB UK:   http://www.enterprisedb.com

Re: Rejecting weak passwords

Kevin Grittner <kevin.grittner@wicourts.gov> — 2009-10-15T13:49:29Z

Dave Page <dpage@pgadmin.org> wrote:
> On Wed, Oct 14, 2009 at 10:51 PM, Kevin Grittner

>> bigger problems, like that slip of paper in their desk drawer with
>> the password written on it.

> See my previous comment about dates. Check-box items aside, I have
> absolutely no desire to try to give the illusion of a security
> feature, when in reality any user could easily bypass it.

I think you missed my point -- if you want to try to block the user
from compromising their *own* password, you can't.  They can tell
anybody they want, write it on a slip of paper stuck to their terminal
(yes, I've seen that), let it loose any other way they want.  Why
focus on one (rather unlikely) way that a user could compromise their
own password when there are so many other ways, much easier and more
likely to actually happen, which are totally out of our control?

If a simple client-side strength check would allow the box to be
checked, and would protect any user who isn't going out of their way
to let their password be abused, I'm not really understanding your
objection.  Now, if it fails to cover the checkbox because it can't
check against the last three passwords used, that's another story, but
the server-side plugin can easily cover things like that.

And ultimately, if you really care about tight security rather than
checking off a box, other posts address how that can actually be done.

-Kevin

Re: Rejecting weak passwords

Dave Page <dpage@pgadmin.org> — 2009-10-15T14:08:32Z

On Thu, Oct 15, 2009 at 2:49 PM, Kevin Grittner
<Kevin.Grittner@wicourts.gov> wrote:
> Dave Page <dpage@pgadmin.org> wrote:
>> On Wed, Oct 14, 2009 at 10:51 PM, Kevin Grittner
>
>>> bigger problems, like that slip of paper in their desk drawer with
>>> the password written on it.
>
>> See my previous comment about dates. Check-box items aside, I have
>> absolutely no desire to try to give the illusion of a security
>> feature, when in reality any user could easily bypass it.
>
> I think you missed my point -- if you want to try to block the user
> from compromising their *own* password, you can't.  They can tell
> anybody they want, write it on a slip of paper stuck to their terminal
> (yes, I've seen that), let it loose any other way they want.  Why
> focus on one (rather unlikely) way that a user could compromise their
> own password when there are so many other ways, much easier and more
> likely to actually happen, which are totally out of our control?

It's certainly true that there are other ways for users to compromise
their passwords if they want. The fact remains though, that most other
DBMSs (and all major operating systems I can think of) offer password
policy features as non-client checks which are difficult, if not
impossible for the user to bypass. Clearly other people think it's
important to do this, and we are compared against their products on a
daily basis, so if we want to compete with them on a level playing
field we need at least a comparable feature set.

-- 
Dave Page
EnterpriseDB UK:   http://www.enterprisedb.com

Re: Rejecting weak passwords

Albe Laurenz <laurenz.albe@wien.gv.at> — 2009-10-15T14:38:00Z

Mark Mielke wrote:
> Does Oracle really do password checks on the base SQL commands used to 
> change an Oracle password? That sounds silly.

In Oracle you can write a stored procedure to check passwords;
it is invoked whenever a user is created or altered.

No matter how you change the password, Oracle can always recover
the plaintext and feed it to the password checking function.

So, unless you use the "Advanced Security" option (extra $$) that
enables you to encrypt network connections, any eavesdropper
with knowledge of Oracle's (secret) encryption algorithms can get
your new password when you change it.

And the DBA can get your password with ease.

Yours,
Laurenz Albe

Re: Rejecting weak passwords

mark@mark.mielke.cc — 2009-10-15T16:23:31Z

On 10/15/2009 03:54 AM, Dave Page wrote:
> On Wed, Oct 14, 2009 at 11:21 PM, Mark Mielke<mark@mark.mielke.cc>  wrote:
>    
>> On 10/14/2009 05:33 PM, Dave Page wrote:
>>      
>>> No. Any checks at the client are worthless, as they can be bypassed by
>>> 10 minutes worth of simple coding in any of a dozen or more languages.
>>>
>>>        
>> Why care?
>>      
> Because many large (and small for that matter) organisations also have
> security policies which mandate the enforcement of specific password
> policies. Just because you think it's worthless to try to prevent
> someone reusing a password, or using 'password' doesn't mean that
> everyone else does. Some organisations will use such a feature in a
> box-ticking exercise when evaluating, and others may actually decide
> to use the feature, and expect it to work effectively.
>
> Beside, we are not in the habit of putting half-arsed features in
> PostgreSQL. If we do something, we do it properly.
>    

You miss my point (and conveniently cut it out). For users who 
accidentally break policy vs users who purposefully circumvent policy - 
the approaches must be different, and the risk management decision may 
be different.

It's a lot easier to circumvent policy than most people (management 
specifically) realize. If your attempt it to absolutely prevent a 
determined competent individual from circumventing your policy - you 
need to do a LOT MORE than what you are suggesting.

If you just want to prevent accidents - having the client software do 
the checks is fine.

Cheers,
mark

-- 
Mark Mielke<mark@mielke.cc>

Re: Rejecting weak passwords

mark@mark.mielke.cc — 2009-10-15T16:28:45Z

On 10/15/2009 10:08 AM, Dave Page wrote:
> It's certainly true that there are other ways for users to compromise
> their passwords if they want. The fact remains though, that most other
> DBMSs (and all major operating systems I can think of) offer password
> policy features as non-client checks which are difficult, if not
> impossible for the user to bypass. Clearly other people think it's
> important to do this, and we are compared against their products on a
> daily basis, so if we want to compete with them on a level playing
> field we need at least a comparable feature set.
>    

Not so clear to me. If they're doing strong checks, this means they're 
sending passwords in the clear or only barely encoded, or using some 
OTHER method than 'alter role ... password ...' to change the password.

Point being - if you think this is absolutely important to do - don't go 
+5% of the way - go 100% of the way.

Then again, I'm not so concerned about what arbitrary criteria some 
person defines as "what makes a good database system". I'm more 
concerned with what makes the system better for *me*. I don't see how 
this entire thread helps *me* in any way - and I do understand the need 
for strong passwords - and my company *does* have policies that require 
strong passwords. Even if the plugin is provided - I'm not going to 
activate it. I already have a policy for setting strong passwords that I 
already follow.

Cheers,
mark

-- 
Mark Mielke<mark@mielke.cc>

Re: Rejecting weak passwords

mark@mark.mielke.cc — 2009-10-15T16:38:14Z

On 10/15/2009 10:38 AM, Albe Laurenz wrote:
> Mark Mielke wrote:
>    
>> Does Oracle really do password checks on the base SQL commands used to
>> change an Oracle password? That sounds silly.
>>      
> In Oracle you can write a stored procedure to check passwords;
> it is invoked whenever a user is created or altered.
>
> No matter how you change the password, Oracle can always recover
> the plaintext and feed it to the password checking function.
>
> So, unless you use the "Advanced Security" option (extra $$) that
> enables you to encrypt network connections, any eavesdropper
> with knowledge of Oracle's (secret) encryption algorithms can get
> your new password when you change it.
>
> And the DBA can get your password with ease.

Now I remember. Our "secure password server" used as single-sign on for 
most applications in the company, which normally avoids any applications 
ever having to see or authenticate the login, needs to send the 
passwords in plain or encoded form (not one-way encrypted) to a few 
"broken" systems, which include systems designed around Oracle user 
management, to allow people to login to these applications using their 
corporate wide password. I remember thinking one word when I learned 
this... "awesome" (sarcastic tone playing in head).

Where is the check box that says "prevents password recovery"?

For Dave Page: Understand that in a large company, as you are 
discussing, with policies that require strong passwords, the usual 
reason for requiring strong passwords is due to concerns over privilege 
escalation. Access to one system gives you access to others. In the case 
of single sign-on, which is a requirement for any business with dozens 
or more applications, where it is unreasonable for each employee to 
actually memorize dozens of distinct "strong" passwords, access to one 
system gives you access to all systems. Therefore, "trust the DBA" makes 
no sense. If the DBA can see my password, then they can login to my 
employee records and check out what my salary or contact information is, 
or they can login to one of the secure portals and authorize purchases 
as me. A *good* system, is not trusted with the password.

This is why I say you are focusing on making PostgreSQL what you think 
is a tiny bit better, but the gain is minor or artificial. If PostgreSQL 
starts requiring strong passwords - the world is not necessarily a 
better place in any mind except the person doing the ignorant checkbox 
evaluation who believes advertising on face value.

If you need security - you should know enough to know you need something 
better than per-application password strength checkers.

Cheers,
mark

-- 
Mark Mielke<mark@mielke.cc>

Re: Rejecting weak passwords

Dave Page <dpage@pgadmin.org> — 2009-10-15T16:41:34Z

On Thu, Oct 15, 2009 at 5:28 PM, Mark Mielke <mark@mark.mielke.cc> wrote:
>
> Not so clear to me. If they're doing strong checks, this means they're
> sending passwords in the clear or only barely encoded, or using some OTHER
> method than 'alter role ... password ...' to change the password.

Some are sending them in the clear (though often over SSL connections).

> Point being - if you think this is absolutely important to do - don't go +5%
> of the way - go 100% of the way.

Exactly - that's why I want to see a check in the server, not the
client which should get to 95%. I also happen to agree with Magnus
that the only really secure way to do this on outside of SQL, but I
can't see us dropping ALTER USER ... WITH PASSWORD in a hurry.

> Then again, I'm not so concerned about what arbitrary criteria some person
> defines as "what makes a good database system". I'm more concerned with what
> makes the system better for *me*. I don't see how this entire thread helps
> *me* in any way - and I do understand the need for strong passwords - and my
> company *does* have policies that require strong passwords. Even if the
> plugin is provided - I'm not going to activate it. I already have a policy
> for setting strong passwords that I already follow.

That's an excellent point. It probably doesn't make any difference to
you or many of the other people on this list who are concerned with
running their own systems and may already use other techniques, such
as LDAP, SSPI etc.

A not-insignificant percentage of the people here are not concerned
with running their own systems though. They are working to help new
users adopt PostgreSQL, and make a living selling services or support
to those users. Sometimes that can be for huge projects, where it is
necessary to justify every difference in check-box items against other
products to get past the early eval stages. Like it or not, that is a
fact, and this hampers our adoption.

-- 
Dave Page
EnterpriseDB UK:   http://www.enterprisedb.com

Re: Rejecting weak passwords

Ron Mayer <rm_pg@cheapcomplexdevices.com> — 2009-10-15T16:59:50Z

Mark Mielke wrote:
> On 10/15/2009 10:08 AM, Dave Page wrote:
>> ...other
>> DBMSs (and all major operating systems I can think of) offer password
>> policy features as non-client checks...we are compared ...
> 
> Not so clear to me. If they're doing strong checks, this means they're
> sending passwords in the clear or only barely encoded, or using some
> OTHER method than 'alter role ... password ...' to change the password.

This makes it sounds like a documentation problem to me.

We need to educate the security-feature-checklist writers.

It seems we need to clearly spell out the security risks of sending
plain text passwords in the section where we would state the reason
why the checks are done in the client - and then hopefully the
security checklists writers will include "only sends encrypted
passwords" as a checkbox on the product comparison charts.

And if server-side checks are that important, perhaps the wiki needs
an example of how to enable server-side check for popular GSSAPI
or LDAP or PAM configurations and describe how to make postgres
use those.

Re: Rejecting weak passwords

Josh Berkus <josh@agliodbs.com> — 2009-10-15T17:17:06Z

On 10/15/09 9:41 AM, Dave Page wrote:
> Sometimes that can be for huge projects, where it is
> necessary to justify every difference in check-box items against other
> products to get past the early eval stages. Like it or not, that is a
> fact, and this hampers our adoption.

Precisely, and I think you've gotten away from that in your proposal.

Any company who wants to institute a *truly* secure password policy is
going to use LDAP, Kerberos, SSPI or GASSPI.  Therefore what you're
proposing is enabling band-aiding for the companies who don't want to
really implement secure password control, but want to *feel* like they have.

Why does this sound like a misfeature?

Enabling the inclusion of a password checker in the client *would*
improve things by preventing stupid users from setting their password
the same as their username, or to a 3-letter word, or anything equally
stupid which can be checked in a contextless way.  This would be an
real, incremental improvement *without* breaking anything else.  And
presumably would help our checkboxyness.

I also think that it would be minimally intrusive to allow the
PostgreSQL server to refuse connections from clients which didn't send a
signal that they had the password-checker enabled.

I *don't* think that guarding against users who are skilled enough to
fake the password checker signal is worth *anyone's* time.  First, a
user that skilled presumably knows enough to pick secure passwords in
the first place.  Second, nothing Postgres can realistcally do in the
way of password checking is going to protect us against a hacker with a
knowledge of postgres internals.  And, again, companies worrying about
this are going to be using LDAP or GSSPI.

Now, this thread has identified a number of areas where we could
realistically improve password security:

* Implement GASSPI encryption
* Allow superusers to disable ALTER USER SET PASSWORD for normal users.
* After the above, create a new createuser which works with ALTER USER
disabled and uses a password checklib.
* Implement the rest of the above suggestion.

But I've seen nothing in Dave's other proposals which would *actually*
improve password security as opposed to doing exactly the opposite.
Requiring passwords to be sent unhashed over the wire so that they can
be checked on the server is like making sure that your front door is
always locked by giving keys to everyone you meet.

In fact, given Dave's pursuit of a specific set of requirements, I think
he has *one* specific client in mind rather than a generalized
requirement.  For my part, I've not in 10 years had anyone ask me for
password checking in Postgres as an evaluation criteron.  Encrypted
data, yes.

--Josh Berkus

Re: Rejecting weak passwords

Robert Haas <robertmhaas@gmail.com> — 2009-10-15T17:19:10Z

On Thu, Oct 15, 2009 at 12:23 PM, Mark Mielke <mark@mark.mielke.cc> wrote:
> You miss my point (and conveniently cut it out). For users who accidentally
> break policy vs users who purposefully circumvent policy - the approaches
> must be different, and the risk management decision may be different.
>
> It's a lot easier to circumvent policy than most people (management
> specifically) realize. If your attempt it to absolutely prevent a determined
> competent individual from circumventing your policy - you need to do a LOT
> MORE than what you are suggesting.
>
> If you just want to prevent accidents - having the client software do the
> checks is fine.

I don't get it.  It's easy to circumvent client checks by using a
different client.  Circumventing server checks is really hard.  You
have to be able to hack the server.  It seems obvious to me that
putting the checks on the server is raising the bar by at least one
order of magnitude and more likely two or three.

Now, it's true that server-side checks on plaintext passwords are a
security risk - in paricular, even with SSL, they can be captured by a
modified server.  So from the point of view of the *employee*, sending
plaintext passwords may be less secure, because they don't know where
their password is going.  But from the point of view of the *server
administrator*, they are more secure, because the server administrator
believes (likely entirely correctly) that the odds of an employee
picking a bad password (perhaps by firing up psql, which is not
exactly a difficult-to-obtain utility) are higher than the odds that
someone will trojan his server and install a password capture tool.

If we were using some kind of real public key system and someone
suggested breaking it to add password complexity checking, I would
understand the outrage here.  But I don't understand why everyone is
so worked up about having an *optional* *flag* to force plaintext
instead of MD5.  I might be wrong here, but can't a determined
attacker brute-force an MD5 anyway?  The very fact that people are
suggesting that password checking might be feasible even on a
pre-MD5'd password by using a dictionary suggests that we're not
getting a whole lot of real security here.  And even if not, dude,
it's an *optional* *flag*.  Document that using it has certain
advantages and certain disadvantages, and let users make their own
decision about whether to deploy it.  If they make a bad decision and
get hacked as a result, tell them it's their own darn fault for
setting the flag.

I reiterate my previous opposition to the idea that "I wouldn't use
that feature" is a reason not to add it.

...Robert

Re: Rejecting weak passwords

Tom Lane <tgl@sss.pgh.pa.us> — 2009-10-15T17:43:51Z

Robert Haas <robertmhaas@gmail.com> writes:
> If we were using some kind of real public key system and someone
> suggested breaking it to add password complexity checking, I would
> understand the outrage here.  But I don't understand why everyone is
> so worked up about having an *optional* *flag* to force plaintext
> instead of MD5.  I might be wrong here, but can't a determined
> attacker brute-force an MD5 anyway?  The very fact that people are
> suggesting that password checking might be feasible even on a
> pre-MD5'd password by using a dictionary suggests that we're not
> getting a whole lot of real security here.  And even if not, dude,
> it's an *optional* *flag*.

Yes, and it's an optional flag that could perfectly well be implemented
in the plugin that I think we do have consensus to add a hook for.
The argument is over why do we need to litter the core system with it.

			regards, tom lane

Re: Rejecting weak passwords

Dave Page <dpage@pgadmin.org> — 2009-10-15T17:44:40Z

On Thu, Oct 15, 2009 at 6:17 PM, Josh Berkus <josh@agliodbs.com> wrote:

> Enabling the inclusion of a password checker in the client *would*
> improve things by preventing stupid users from setting their password
> the same as their username, or to a 3-letter word, or anything equally
> stupid which can be checked in a contextless way.  This would be an
> real, incremental improvement *without* breaking anything else.  And
> presumably would help our checkboxyness.

What client? For the vast majority of users, what you're proposing is
'do it yourself'. Most people don't use pgAdmin or psql.

> But I've seen nothing in Dave's other proposals which would *actually*

Actually, I made just one proposal, to complement a patch that has
already been submitted.

> improve password security as opposed to doing exactly the opposite
> Requiring passwords to be sent unhashed over the wire so that they can
> be checked on the server is like making sure that your front door is
> always locked by giving keys to everyone you meet.

As Peter pointed out, it's already game-over if you're worried about
the DBA. For other cases, I was clear that SSL should be expected.

> In fact, given Dave's pursuit of a specific set of requirements, I think

My only request was for server-side password policy enforcement, and a
way to ensure users/DBAs could use pgAdmin to manage those passwords.

> he has *one* specific client in mind rather than a generalized
> requirement.  For my part, I've not in 10 years had anyone ask me for
> password checking in Postgres as an evaluation criteron.  Encrypted
> data, yes.

I don't deal with prospective clients, which is where this comes from.
I do deal with a team of (pre)sales engineers who complain about this,
and maybe half-a-dozen other issues on a very regular basis. They tell
me that PostgreSQL loses out in early stages of tech evals because of
this issue, and I have no reason to disbelieve them. Sure it's almost
certainly not the only reason, but they add up.

And yes, data encryption is one of the other checkbox items that they
bleat about.

-- 
Dave Page
EnterpriseDB UK:   http://www.enterprisedb.com

Re: Rejecting weak passwords

Dave Page <dpage@pgadmin.org> — 2009-10-15T17:47:16Z

On Thu, Oct 15, 2009 at 6:43 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>> If we were using some kind of real public key system and someone
>> suggested breaking it to add password complexity checking, I would
>> understand the outrage here.  But I don't understand why everyone is
>> so worked up about having an *optional* *flag* to force plaintext
>> instead of MD5.  I might be wrong here, but can't a determined
>> attacker brute-force an MD5 anyway?  The very fact that people are
>> suggesting that password checking might be feasible even on a
>> pre-MD5'd password by using a dictionary suggests that we're not
>> getting a whole lot of real security here.  And even if not, dude,
>> it's an *optional* *flag*.
>
> Yes, and it's an optional flag that could perfectly well be implemented
> in the plugin that I think we do have consensus to add a hook for.
> The argument is over why do we need to litter the core system with it.

I already said that would suit me. The only other requirement I would
have is a way for pgAdmin or other clients to figure out if that flag
was set so they could construct queries appropriately (and yes, that
could include refusing to send plain text passwords over non-SSL
connections).

-- 
Dave Page
EnterpriseDB UK:   http://www.enterprisedb.com

Re: Rejecting weak passwords

Robert Haas <robertmhaas@gmail.com> — 2009-10-15T17:55:30Z

On Thu, Oct 15, 2009 at 1:47 PM, Dave Page <dpage@pgadmin.org> wrote:
> On Thu, Oct 15, 2009 at 6:43 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Robert Haas <robertmhaas@gmail.com> writes:
>>> If we were using some kind of real public key system and someone
>>> suggested breaking it to add password complexity checking, I would
>>> understand the outrage here.  But I don't understand why everyone is
>>> so worked up about having an *optional* *flag* to force plaintext
>>> instead of MD5.  I might be wrong here, but can't a determined
>>> attacker brute-force an MD5 anyway?  The very fact that people are
>>> suggesting that password checking might be feasible even on a
>>> pre-MD5'd password by using a dictionary suggests that we're not
>>> getting a whole lot of real security here.  And even if not, dude,
>>> it's an *optional* *flag*.
>>
>> Yes, and it's an optional flag that could perfectly well be implemented
>> in the plugin that I think we do have consensus to add a hook for.
>> The argument is over why do we need to litter the core system with it.
>
> I already said that would suit me. The only other requirement I would
> have is a way for pgAdmin or other clients to figure out if that flag
> was set so they could construct queries appropriately (and yes, that
> could include refusing to send plain text passwords over non-SSL
> connections).

OK, so we're in violent agreement here?  Except for figuring out how
an API for checking the flag?  Could they just try it with MD5 first
and then fall back if that say "no MD5"?

...Robert

Re: Rejecting weak passwords

Dave Page <dpage@pgadmin.org> — 2009-10-15T18:02:55Z

On Thu, Oct 15, 2009 at 6:55 PM, Robert Haas <robertmhaas@gmail.com> wrote:

> OK, so we're in violent agreement here?

From a technical perspective I think we have been for a while. Though
clearly some people disagree with my assertion that putting any form
of policy enforcement in the client is not actually 'enforcement'. I
wonder how many of those folks would implement their website's data
sanitisation in the browser only - but I digress... :-)

> Except for figuring out how
> an API for checking the flag?  Could they just try it with MD5 first
> and then fall back if that say "no MD5"?

That's what I was trying to avoid, as the architecture of pgAdmin
makes that really hard. I know that's not PG's problem, but forcing a
retry is quite an ugly solution anyway, so I was hoping we could come
up with something better.

I suppose in the worst case, I could just have pgAdmin throw the
error, and then add a per-server option to disable password hashing in
the relevant places, but I'd far rather have that automated so it
can't be set unnecessarily.

-- 
Dave Page
EnterpriseDB UK:   http://www.enterprisedb.com

Re: Rejecting weak passwords

mark@mark.mielke.cc — 2009-10-15T18:11:18Z

On 10/15/2009 01:44 PM, Dave Page wrote:
> I don't deal with prospective clients, which is where this comes from.
> I do deal with a team of (pre)sales engineers who complain about this,
> and maybe half-a-dozen other issues on a very regular basis. They tell
> me that PostgreSQL loses out in early stages of tech evals because of
> this issue, and I have no reason to disbelieve them. Sure it's almost
> certainly not the only reason, but they add up.
>    

A lot of evaluations are designed to fit exactly one product, and it's 
impossible to win here.

In my own company, I recently saw the most ridiculous (to me) 
evaluations over a suite of products, that effectively listed an exact 
implementation as requirements. This resulted in a huge split between 
people who considered the evaluation fair and who went with their choice 
for exactly that one product, and the rest of the people who called the 
evaluation a sham and refused to participate, choosing to instead use 
their own choice of products not caring about the outcome of the 
evaluation. The evaluation, by the way, included other "silly" 
statements, like how a database instance costs $48k in license fees, 
even though everybody knew we were already using PostgreSQL for $0k or 
even if we chose to be supported by one of the many PostgreSQL support 
companies, it would not cost $48k. Where did they get that number? 
Because they presumed they would go with Oracle. The evaluation was a 
sham from start to finish.

Perhaps you can see how little I value some arbitrary checkbox list on 
some "evaluation"? If people want to count PostgreSQL off the list from 
the start - they will, and there is not much you or I can do about it. 
Bowing to the pressure of fulfilling these checkboxes, when they'll just 
change them next time to something else that PostgreSQL doesn't quite 
do, is a waste of time.

We should do what is right to do. We should not be focusing on 
checkboxes raised by other people who are not competent enough to 
understand the subject matter or who have already made their choice, and 
the evaluation is just a rubber stamp to pretend they have done due 
diligence about justifying their choice compared to alternatives.

Cheers,
mark

-- 
Mark Mielke<mark@mielke.cc>

Re: Rejecting weak passwords

Tom Lane <tgl@sss.pgh.pa.us> — 2009-10-15T18:12:06Z

Dave Page <dpage@pgadmin.org> writes:
> On Thu, Oct 15, 2009 at 6:43 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Yes, and it's an optional flag that could perfectly well be implemented
>> in the plugin that I think we do have consensus to add a hook for.
>> The argument is over why do we need to litter the core system with it.

> I already said that would suit me. The only other requirement I would
> have is a way for pgAdmin or other clients to figure out if that flag
> was set so they could construct queries appropriately (and yes, that
> could include refusing to send plain text passwords over non-SSL
> connections).

Well, if it's a GUC implemented by a plugin, it's still a GUC.  All you
need is some side agreement between pgAdmin and potential GUC authors
about what the GUC will be called.

			regards, tom lane

Re: Rejecting weak passwords

Tom Lane <tgl@sss.pgh.pa.us> — 2009-10-15T18:16:31Z

Dave Page <dpage@pgadmin.org> writes:
> I suppose in the worst case, I could just have pgAdmin throw the
> error, and then add a per-server option to disable password hashing in
> the relevant places, but I'd far rather have that automated so it
> can't be set unnecessarily.

As I commented before, I think that automating this is actually a
dangerous misfeature.

			regards, tom lane

Re: Rejecting weak passwords

mark@mark.mielke.cc — 2009-10-15T18:22:20Z

On 10/15/2009 02:02 PM, Dave Page wrote:
> On Thu, Oct 15, 2009 at 6:55 PM, Robert Haas<robertmhaas@gmail.com>  wrote:
>
>    
>> OK, so we're in violent agreement here?
>>      
>  From a technical perspective I think we have been for a while. Though
> clearly some people disagree with my assertion that putting any form
> of policy enforcement in the client is not actually 'enforcement'. I
> wonder how many of those folks would implement their website's data
> sanitisation in the browser only - but I digress... :-)
>    

It depends on what your goal is. If your goal is to treat users as 
monkeys that you do not trust, even with their own password, and the DBA 
as God, who you absolutely do trust, than you are correct.

I don't know about your company - but in my company, the DBAs are in the 
IT department, and they really have no business knowing my password, 
which would give them access to my employee records, and my 
authorization capabilities. For any company that requires security, I do 
not accept that we can "trust the DBA". The database is just one small 
component in a much larger solution. The DBA is the monkey for a minor 
backend application, and the designers are the people earning money for 
the corporation. We have the exact opposite of what you are suggesting. 
A person can get access to much more data by logging in as the user on 
their *desktop* than by accessing some database directly.

I think you are missing that security is a balance. Your dig at ignorant 
people who do JS-based browser side checks of input is not applicable. 
You are exchanging one type of security for another type of security. 
You think that your proposed type of security is more valid than my 
proposed type of security. It depends on the application. Sometimes you 
might be right. Other times, you have arguably made things worse. Any 
company that truly needs security of this sort - should not be using 
PostgreSQL based roles with passwords for authentication. The true value 
of your proposal is pretty limited.

I'm not saying don't do it. I am saying that you are not truly achieving 
any improvement in security for the target audience you are saying that 
you are representing.

I think your proposal might improve things for newbies running 
PostgreSQL on an open Internet port at home who pick username = 
password. Frankly, I don't think their data is worth protecting, and 
their choice to use username = password and make it accessible on an 
open Internet port confirms that they are either completely ignorant 
about security, or they also agree that their data is not worth protecting.

Cheers,
mark

-- 
Mark Mielke<mark@mielke.cc>

Re: Rejecting weak passwords

Dave Page <dpage@pgadmin.org> — 2009-10-15T18:54:24Z

On Thu, Oct 15, 2009 at 7:22 PM, Mark Mielke <mark@mark.mielke.cc> wrote:
> It depends on what your goal is. If your goal is to treat users as monkeys
> that you do not trust, even with their own password, and the DBA as God, who
> you absolutely do trust, than you are correct.
>
> I don't know about your company - but in my company, the DBAs are in the IT
> department, and they really have no business knowing my password, which
> would give them access to my employee records, and my authorization
> capabilities. For any company that requires security, I do not accept that
> we can "trust the DBA". The database is just one small component in a much
> larger solution. The DBA is the monkey for a minor backend application, and
> the designers are the people earning money for the corporation. We have the
> exact opposite of what you are suggesting. A person can get access to much
> more data by logging in as the user on their *desktop* than by accessing
> some database directly.

You have no choice but to trust the DBA (or sysadmin) if you use
PostgreSQL's built in authentication. Just set a pg_hba.conf line to
'password' and start harvesting passwords.

> I think you are missing that security is a balance. Your dig at ignorant
> people who do JS-based browser side checks of input is not applicable. You
> are exchanging one type of security for another type of security. You think
> that your proposed type of security is more valid than my proposed type of
> security. It depends on the application. Sometimes you might be right. Other
> times, you have arguably made things worse. Any company that truly needs
> security of this sort - should not be using PostgreSQL based roles with
> passwords for authentication. The true value of your proposal is pretty
> limited.

I never said it wasn't - in fact I said from the outset it was about
box-checking, and that anyone doing things properly will use
LDAP/SSPI/Kerberos etc.

What I did say, was that my proposed GUC doesn't allow the DBA to do
anything that he cannot already do in a much easier way, per the
comment above.

Anyway, as noted in the message you quoted, the current proposal will
allow my colleagues to check boxes, and will be implemented in a
sensible way on the server side. And it's entirely confined to a
plugin, so if you trust all your users, there's no need for you to
load it at all.

-- 
Dave Page
EnterpriseDB UK:   http://www.enterprisedb.com

Re: Rejecting weak passwords

Ron Mayer <rm_pg@cheapcomplexdevices.com> — 2009-10-15T21:40:16Z

Dave Page wrote:
> I never said it wasn't - in fact I said from the outset it was about
> box-checking, and that anyone doing things properly will use
> LDAP/SSPI/Kerberos etc.

I don't understand why the box-checkers can't already check that
box; with the explanation stating "Yes - by using LDAP or GSSAPI
or PAM configured accordingly".

Or do checkbox-lists specifically say
"can postgres do XYZ with all OS security features disabled".

> Anyway, as noted in the message you quoted, the current proposal will
> allow my colleagues to check boxes, and will be implemented in a
> sensible way on the server side. And it's entirely confined to a
> plugin, so if you trust all your users, there's no need for you to
> load it at all.

Note that I'm not horribly against the feature (though I wouldn't
use it) --- just that ISTM we're checkbox-compliant already by
working with the OS, and it's perhaps more a documentation issue
than coding issue to get those boxes checked.

Re: Rejecting weak passwords

Dave Page <dpage@pgadmin.org> — 2009-10-15T22:13:43Z

On Thu, Oct 15, 2009 at 10:40 PM, Ron Mayer
<rm_pg@cheapcomplexdevices.com> wrote:
> Dave Page wrote:
>> I never said it wasn't - in fact I said from the outset it was about
>> box-checking, and that anyone doing things properly will use
>> LDAP/SSPI/Kerberos etc.
>
> I don't understand why the box-checkers can't already check that
> box; with the explanation stating "Yes - by using LDAP or GSSAPI
> or PAM configured accordingly".
>
> Or do checkbox-lists specifically say
> "can postgres do XYZ with all OS security features disabled".

Well I guess that's possible, but what I hear our SEs complaining
about is having to justify features that require additional software
or features that other DBMSs offer natively. Let me offer a couple of
examples:

Q. Does the product offer password policy enforcement?
Oracle: Yes
SQL Server: Yes
PostgreSQL: Yes (using an external authentication provider with policy
enforcement).

Q. Does the product support external authentication providers?
Oracle: Yes
SQL Server: Yes
PostgreSQL: Yes

Q. Does the product offer transparent data encryption?
Oracle: Yes
SQL Server: Yes
PostgreSQL: Yes (using an encrypted filesystem).

Q. Does the product offer audit capabilities?
Oracle: Yes
SQL Server: Yes
PostgreSQL: Yes (must be manually implemented using triggers and functions)

Too many of those caveats, and it's easy to see how we can be
discounted early in the evaluation phase. It's not helped that often
these lists will be drawn up by people used to working with the
commercial DBMSs, so we probably wouldn't get extra points for having
a dozen procedural languages, or other features that are largely
unique to PostgreSQL, no matter how cool and useful they are.

-- 
Dave Page
EnterpriseDB UK:   http://www.enterprisedb.com

Re: Rejecting weak passwords

Bruce Momjian <bruce@momjian.us> — 2009-10-16T15:28:51Z

Dave Page wrote:
> Too many of those caveats, and it's easy to see how we can be
> discounted early in the evaluation phase. It's not helped that often
> these lists will be drawn up by people used to working with the
> commercial DBMSs, so we probably wouldn't get extra points for having
> a dozen procedural languages, or other features that are largely
> unique to PostgreSQL, no matter how cool and useful they are.

Yep, this is illustrating something that is pretty basic to open source
--- that is open source often provides the tools for a solution, rather
than a complete solution.  I often think of open source as providing a
calculator with wires sticking out, rather than calculator buttons;  the
wires allow more flexibility, but they are harder to use.

Personally I think the calculator/wires approach is better from an
engineering perspective, but it can be a handicap in the user experience
and checkbox categories --- ease of use is perhaps not our strong point.
Much of our open source value is being different, in both cost,
reliability, and configurability.

-- 
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

Re: Rejecting weak passwords

Bruce Momjian <bruce@momjian.us> — 2009-10-16T15:29:50Z

Tom Lane wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
> > If we were using some kind of real public key system and someone
> > suggested breaking it to add password complexity checking, I would
> > understand the outrage here.  But I don't understand why everyone is
> > so worked up about having an *optional* *flag* to force plaintext
> > instead of MD5.  I might be wrong here, but can't a determined
> > attacker brute-force an MD5 anyway?  The very fact that people are
> > suggesting that password checking might be feasible even on a
> > pre-MD5'd password by using a dictionary suggests that we're not
> > getting a whole lot of real security here.  And even if not, dude,
> > it's an *optional* *flag*.
> 
> Yes, and it's an optional flag that could perfectly well be implemented
> in the plugin that I think we do have consensus to add a hook for.
> The argument is over why do we need to litter the core system with it.

So, are we agreed to provide a hook on the server side, but to use it
you have to configure your system with SSL and 'password'?  I can add
that to the TODO list.

-- 
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

Re: Rejecting weak passwords

Tom Lane <tgl@sss.pgh.pa.us> — 2009-10-16T15:58:20Z

Bruce Momjian <bruce@momjian.us> writes:
> So, are we agreed to provide a hook on the server side, but to use it
> you have to configure your system with SSL and 'password'?  I can add
> that to the TODO list.

I think we're agreed to provide the hook.  What restrictions the hook
might enforce are not our concern.

			regards, tom lane

Re: Rejecting weak passwords

mark@mark.mielke.cc — 2009-10-16T16:40:09Z

On 10/16/2009 11:28 AM, Bruce Momjian wrote:
> Dave Page wrote:
>    
>> Too many of those caveats, and it's easy to see how we can be
>> discounted early in the evaluation phase. It's not helped that often
>> these lists will be drawn up by people used to working with the
>> commercial DBMSs, so we probably wouldn't get extra points for having
>> a dozen procedural languages, or other features that are largely
>> unique to PostgreSQL, no matter how cool and useful they are.
>>      
> Yep, this is illustrating something that is pretty basic to open source
> --- that is open source often provides the tools for a solution, rather
> than a complete solution.  I often think of open source as providing a
> calculator with wires sticking out, rather than calculator buttons;  the
> wires allow more flexibility, but they are harder to use.
>    

Although often true - I think this is selling PostgreSQL a little short. 
It is a self-contained solution for what it does best, and for those 
that need more - there are better frameworks designed to be integrated 
that PostgreSQL is able to integrate with. PostgreSQL isn't a calculator 
with wires - if anything, I think PostgreSQL is an easy-to-use full 
functioned calculator whereas Oracle might be some advanced HP 
calculator that requires special training to learn how to use right... :-)

> Personally I think the calculator/wires approach is better from an
> engineering perspective, but it can be a handicap in the user experience
> and checkbox categories --- ease of use is perhaps not our strong point.
> Much of our open source value is being different, in both cost,
> reliability, and configurability.

I found this true of a lot of tools. I still remember when the mutt 
developers argued against putting IMAP in their solution because they 
thought there might be a better "IMAP component" client out there. 
Eventually, such arguments are dropped, as the practical sense on the 
matter says that tight integration is a requirement.

I don't see how PostgreSQL has really failed in this regard. Maybe 
Oracle comes out-of-box with more features - but this doesn't make it 
necessarily a more "complete" solution - it just means it has more bells 
and whistles. A bicycle doesn't need a ticking card mounted through the 
spokes for it to be considered a "complete solution". :-) Somebody might 
one day want that "feature" - but it's extra - it's not core.

Cheers,
mark

-- 
Mark Mielke<mark@mielke.cc>

Re: Rejecting weak passwords

Greg Stark <gsstark@mit.edu> — 2009-10-16T17:06:59Z

On Fri, Oct 16, 2009 at 4:28 PM, Bruce Momjian <bruce@momjian.us> wrote:
> Personally I think the calculator/wires approach is better from an
> engineering perspective, but it can be a handicap in the user experience
> and checkbox categories --- ease of use is perhaps not our strong point.
> Much of our open source value is being different, in both cost,
> reliability, and configurability.

I don't really see this as an either-or proposition. The calculator
with buttons still has wires inside, someone had to hack on the wiring
to get the functionality working. Someone else with an inclination
toward marketing put shiny buttons on it. I'm pretty happy to be in
the first group and since Postgres has a BSD license people in the
second group are free to work on shiny buttons.

The discussion at hand is an unusual situation where the priorities
conflict because supporting a particular shiny button requires ripping
out some useful wiring. In the long run I think having the wiring
right is more valuable than some shiny buttons today.

-- 
greg

Re: Rejecting weak passwords

Bruce Momjian <bruce@momjian.us> — 2009-10-16T17:34:44Z

Tom Lane wrote:
> Bruce Momjian <bruce@momjian.us> writes:
> > So, are we agreed to provide a hook on the server side, but to use it
> > you have to configure your system with SSL and 'password'?  I can add
> > that to the TODO list.
> 
> I think we're agreed to provide the hook.  What restrictions the hook
> might enforce are not our concern.

Great, added to TODO:

	Allow server-side enforcement of password policies
	
	    Password checks might include password complexity or non-reuse of
	passwords. This facility will require the client to send the password to
	the server in plain-text, so SSL and 'password' authentication is
	necessary to use this features.
	
	        * http://archives.postgresql.org/pgsql-hackers/2009-09/msg01766.php
	        * http://archives.postgresql.org/pgsql-hackers/2009-10/msg00025.php 

-- 
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

Re: Rejecting weak passwords

Bruce Momjian <bruce@momjian.us> — 2009-10-16T17:40:24Z

Mark Mielke wrote:
> > Personally I think the calculator/wires approach is better from an
> > engineering perspective, but it can be a handicap in the user experience
> > and checkbox categories --- ease of use is perhaps not our strong point.
> > Much of our open source value is being different, in both cost,
> > reliability, and configurability.
> 
> I found this true of a lot of tools. I still remember when the mutt 
> developers argued against putting IMAP in their solution because they 
> thought there might be a better "IMAP component" client out there. 
> Eventually, such arguments are dropped, as the practical sense on the 
> matter says that tight integration is a requirement.
> 
> I don't see how PostgreSQL has really failed in this regard. Maybe 
> Oracle comes out-of-box with more features - but this doesn't make it 
> necessarily a more "complete" solution - it just means it has more bells 
> and whistles. A bicycle doesn't need a ticking card mounted through the 
> spokes for it to be considered a "complete solution". :-) Somebody might 
> one day want that "feature" - but it's extra - it's not core.

Agreed.  Many commercial database solutions start to look like
Frankenstein with all the bolted-on components.

-- 
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

Re: Rejecting weak passwords

Ron Mayer <rm_pg@cheapcomplexdevices.com> — 2009-10-18T16:17:27Z

Bruce Momjian wrote:
> Yep, this is illustrating something that is pretty basic to open source
> --- that is open source often provides the tools for a solution, rather
> than a complete solution.  I often think of open source as providing a
> calculator with wires sticking out, rather than calculator buttons;  the
> wires allow more flexibility, but they are harder to use.

I disagree.    Open source typically provides the complete solution
too - just not from the developer who programs one component of the
solution.

Checklist writers intentionally use this to make straw-man arguments.
People used to say "linux doesn't even have a GUI" - noting that
X11 is a separate project.

Now people have database checkboxes for:

 * a GUI admin tool (which we have, though it's a separate package)
 * GIS data types (which we have, though it's a separate package)
 * server-side password filters (which we have, though LDAP, etc)
 * replication (which we have, though many packages)
 * clustering (which we have, though hadoopdb)

The Linux guys successfully communicated that it isn't fair
for checklists to compare an OS kernel against commercial
application suites.   Seems it'd be good for the postgres
project to similarly communicate that the database kernel
is the core of a platform that's broader than just a
database kernel.

   Ron

Re: Rejecting weak passwords

Albe Laurenz <laurenz.albe@wien.gv.at> — 2009-10-19T07:14:07Z

Bruce Momjian wrote:
> Great, added to TODO:
> 
> 	Allow server-side enforcement of password policies
> 	
> 	    Password checks might include password complexity or non-reuse of
> 	passwords. This facility will require the client to send the password to
> 	the server in plain-text, so SSL and 'password' authentication is
> 	necessary to use this features.

I don't get why you need 'password' authentication for that.
The point where the password should be checked is not when
the user uses it to logon, but when he or she changes it.

So in my opinion that should be:
This facility will require to send new and changed password to
the server in plain-text, so it will require SSL, and the use
of encrypted passwords in CREATE/ALTER ROLE will have to be
disabled.

Yours,
Laurenz Albe

Re: Rejecting weak passwords

Peter Eisentraut <peter_e@gmx.net> — 2009-10-19T11:34:55Z

On Thu, 2009-10-15 at 13:19 -0400, Robert Haas wrote:
> But I don't understand why everyone is
> so worked up about having an *optional* *flag* to force plaintext
> instead of MD5.

It would be pretty bad usability.  Users would be faced with the choice:
you can have secure authentication or good passwords, but not both.
(For some values of "secure" and "good".)  I think most people would
want both.

Re: Rejecting weak passwords

Peter Eisentraut <peter_e@gmx.net> — 2009-10-19T11:51:15Z

On Mon, 2009-10-19 at 09:14 +0200, Albe Laurenz wrote:
> Bruce Momjian wrote:
> > Great, added to TODO:
> > 
> > 	Allow server-side enforcement of password policies
> > 	
> > 	    Password checks might include password complexity or non-reuse of
> > 	passwords. This facility will require the client to send the password to
> > 	the server in plain-text, so SSL and 'password' authentication is
> > 	necessary to use this features.
> 
> I don't get why you need 'password' authentication for that.
> The point where the password should be checked is not when
> the user uses it to logon, but when he or she changes it.
> 
> So in my opinion that should be:
> This facility will require to send new and changed password to
> the server in plain-text, so it will require SSL, and the use
> of encrypted passwords in CREATE/ALTER ROLE will have to be
> disabled.

Note that this solution will still not satisfy the original checkbox
requirement.

Re: Rejecting weak passwords

Albe Laurenz <laurenz.albe@wien.gv.at> — 2009-10-19T12:54:38Z

Peter Eisentraut wrote:
>> I don't get why you need 'password' authentication for that.
>> The point where the password should be checked is not when
>> the user uses it to logon, but when he or she changes it.
>> 
>> So in my opinion that should be:
>> This facility will require to send new and changed password to
>> the server in plain-text, so it will require SSL, and the use
>> of encrypted passwords in CREATE/ALTER ROLE will have to be
>> disabled.
> 
> Note that this solution will still not satisfy the original checkbox
> requirement.

I guess I misunderstood something there, but I had assumed that the
checkbox item read something like: "Does the product offer password
policy enforcement?" (to quote Dave Page).

I understood that to mean "does the server check if a new password
complies with a certain set of rules".

Yours,
Laurenz Albe

Re: Rejecting weak passwords

Robert Haas <robertmhaas@gmail.com> — 2009-10-19T14:07:25Z

On Mon, Oct 19, 2009 at 7:34 AM, Peter Eisentraut <peter_e@gmx.net> wrote:
> On Thu, 2009-10-15 at 13:19 -0400, Robert Haas wrote:
>> But I don't understand why everyone is
>> so worked up about having an *optional* *flag* to force plaintext
>> instead of MD5.
>
> It would be pretty bad usability.  Users would be faced with the choice:
> you can have secure authentication or good passwords, but not both.
> (For some values of "secure" and "good".)  I think most people would
> want both.

Unless you have the ability to entirely control the software that
users use to access PostgreSQL, which is probably only true in
super-high-security environments and is certainly false anywhere I've
ever worked, you can only have one of those things.

SSH keys or SSL certificates are great for defeating network attacks,
but I know a lot of people who keep SSL certificates unencrypted on
their laptops because there's no easy way to stop them.  Those very
same people can EASILY be forced to pick relatively good Windows logon
passwords because AD can enforce password complexity requirements.  Of
course, they can't be forced not to write their Windows logon password
on a napkin, but they also can't be forced not to run an unsecured FTP
server on their laptop that provides access to their unencrypted SSH
keys/SSL certificates.

Now, we can argue all day about probabilities, but I don't see any
reason to believe that we know for sure what the best trade-off is in
every environment, which is why I favor providing options, documenting
the trade-offs, and letting users make the final decision.

...Robert

Re: Rejecting weak passwords

Tom Lane <tgl@sss.pgh.pa.us> — 2009-10-19T14:30:05Z

"Albe Laurenz" <laurenz.albe@wien.gv.at> writes:
> Bruce Momjian wrote:
>> Password checks might include password complexity or non-reuse of
>> passwords. This facility will require the client to send the password to
>> the server in plain-text, so SSL and 'password' authentication is
>> necessary to use this features.

> So in my opinion that should be:
> This facility will require to send new and changed password to
> the server in plain-text, so it will require SSL, and the use
> of encrypted passwords in CREATE/ALTER ROLE will have to be
> disabled.

Actually, not one word of *either* version should be in TODO.  All of
that is speculation about policies that a particular add-on module
might or might not choose to enforce.

			regards, tom lane

Re: Rejecting weak passwords

Peter Eisentraut <peter_e@gmx.net> — 2009-10-19T16:08:08Z

On Mon, 2009-10-19 at 14:54 +0200, Albe Laurenz wrote:
> Peter Eisentraut wrote:
> > Note that this solution will still not satisfy the original checkbox
> > requirement.
> 
> I guess I misunderstood something there, but I had assumed that the
> checkbox item read something like: "Does the product offer password
> policy enforcement?" (to quote Dave Page).

The answer to that is currently "Yes, with external tools".  Using the
plugin approach, the answer will remain "Yes, with external tools".  So
we wouldn't gain much.

Re: Rejecting weak passwords

Tom Lane <tgl@sss.pgh.pa.us> — 2009-10-19T16:12:07Z

Peter Eisentraut <peter_e@gmx.net> writes:
> On Mon, 2009-10-19 at 14:54 +0200, Albe Laurenz wrote:
>> I guess I misunderstood something there, but I had assumed that the
>> checkbox item read something like: "Does the product offer password
>> policy enforcement?" (to quote Dave Page).

> The answer to that is currently "Yes, with external tools".  Using the
> plugin approach, the answer will remain "Yes, with external tools".  So
> we wouldn't gain much.

Except that your first statement is false.  It is not possible currently
for any tool to prevent someone from doing ALTER USER joe PASSWORD joe.
A server-side plugin can provide a guarantee that there are no bad
passwords (for some value of bad, and with some possible adverse
consequences).  We don't have that today.

			regards, tom lane

Re: Rejecting weak passwords

Alvaro Herrera <alvherre@commandprompt.com> — 2009-10-19T16:23:00Z

Tom Lane escribió:
> Peter Eisentraut <peter_e@gmx.net> writes:
> > On Mon, 2009-10-19 at 14:54 +0200, Albe Laurenz wrote:
> >> I guess I misunderstood something there, but I had assumed that the
> >> checkbox item read something like: "Does the product offer password
> >> policy enforcement?" (to quote Dave Page).
> 
> > The answer to that is currently "Yes, with external tools".  Using the
> > plugin approach, the answer will remain "Yes, with external tools".  So
> > we wouldn't gain much.
> 
> Except that your first statement is false.  It is not possible currently
> for any tool to prevent someone from doing ALTER USER joe PASSWORD joe.
> A server-side plugin can provide a guarantee that there are no bad
> passwords (for some value of bad, and with some possible adverse
> consequences).  We don't have that today.

We do, if you have you server grabbing passwords from LDAP or whatever
external auth service you use.  That would be more secure than anything
mentioned in this thread, because the password enforcement could work on
unencrypted passwords without adverse consequences.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Re: Rejecting weak passwords

Andrew Dunstan <andrew@dunslane.net> — 2009-10-19T16:37:19Z


Alvaro Herrera wrote:
>> Except that your first statement is false.  It is not possible currently
>> for any tool to prevent someone from doing ALTER USER joe PASSWORD joe.
>> A server-side plugin can provide a guarantee that there are no bad
>> passwords (for some value of bad, and with some possible adverse
>> consequences).  We don't have that today.
>>     
>
> We do, if you have you server grabbing passwords from LDAP or whatever
> external auth service you use.  That would be more secure than anything
> mentioned in this thread, because the password enforcement could work on
> unencrypted passwords without adverse consequences.
>   

We don't have it today for passwords that postgres manages. Unless we're 
going to rely on an external auth source completely, I think there's a 
good case for the hooks, but not for any of the other "adjustments" that 
people have suggested.


cheers

andrew

Re: Rejecting weak passwords

Tom Lane <tgl@sss.pgh.pa.us> — 2009-10-19T16:37:54Z

I wrote:
> A server-side plugin can provide a guarantee that there are no bad
> passwords (for some value of bad, and with some possible adverse
> consequences).  We don't have that today.

BTW, it strikes me that ALTER USER RENAME introduces an interesting
hazard for such a plugin.  Consider

CREATE USER joe;
ALTER USER joe PASSWORD joe;  -- presumably, plugin will reject this
ALTER USER joe PASSWORD mumblefrotz;  -- assume this is considered OK
ALTER USER joe RENAME TO mumblefrotz;

Now we have a user with name equal to password, which no sane security
policy will think is a good thing, but the plugin had no chance to
prevent it.

In the case where the password is stored MD5-crypted, we clear it on
RENAME because of the fact that the username is part of the hash.
(We had always thought that was a bug^Wimplementation restriction,
but now it looks like a feature.)  So in normal practice the above
hazard doesn't exist; but it would for cleartext passwords.

One thing we could do is *always* clear the password on RENAME.
Another is to keep the cleartext password, but pass the new name
and password through the plugin before allowing the RENAME to succeed.
Since the PW is cleartext, presumably the plugin won't have any problem
checking it.  The latter however seems like we are getting a
security-critical behavior out of a chance combination of implementation
artifacts, which doesn't make me feel comfortable.

Thoughts?

			regards, tom lane

Re: Rejecting weak passwords

Tom Lane <tgl@sss.pgh.pa.us> — 2009-10-19T17:16:36Z

Andrew Dunstan <andrew@dunslane.net> writes:
> Alvaro Herrera wrote:
>> We do, if you have you server grabbing passwords from LDAP or whatever
>> external auth service you use.  That would be more secure than anything
>> mentioned in this thread, because the password enforcement could work on
>> unencrypted passwords without adverse consequences.

> We don't have it today for passwords that postgres manages. Unless we're 
> going to rely on an external auth source completely, I think there's a 
> good case for the hooks, but not for any of the other "adjustments" that 
> people have suggested.

Yeah.  Installing LDAP or Kerberos or whatever is sensible if you have
a need for a central auth server anyway.  If you are just trying to run a
database, it's a major additional investment of effort, and I can't
quibble at all with people who think that it's unreasonable to have to
do that just to have some modicum of a password policy.

I also am of the opinion that it's reasonable to provide a hook or two
for this purpose, but not to go further than that.

			regards, tom lane

Re: Rejecting weak passwords

Bruce Momjian <bruce@momjian.us> — 2009-10-20T04:08:32Z

Tom Lane wrote:
> "Albe Laurenz" <laurenz.albe@wien.gv.at> writes:
> > Bruce Momjian wrote:
> >> Password checks might include password complexity or non-reuse of
> >> passwords. This facility will require the client to send the password to
> >> the server in plain-text, so SSL and 'password' authentication is
> >> necessary to use this features.
> 
> > So in my opinion that should be:
> > This facility will require to send new and changed password to
> > the server in plain-text, so it will require SSL, and the use
> > of encrypted passwords in CREATE/ALTER ROLE will have to be
> > disabled.
> 
> Actually, not one word of *either* version should be in TODO.  All of
> that is speculation about policies that a particular add-on module
> might or might not choose to enforce.

Agreed, updated:

	|Allow server-side enforcement of password policies
	|Password checks might include password complexity or non-reuse of
	passwords.  This facility will require the client to send password
	creation/changes to the server in plain-text, not MD5.

-- 
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

Re: Rejecting weak passwords

Magnus Hagander <magnus@hagander.net> — 2009-10-20T07:11:24Z

2009/10/19 Tom Lane <tgl@sss.pgh.pa.us>:
> I wrote:
>> A server-side plugin can provide a guarantee that there are no bad
>> passwords (for some value of bad, and with some possible adverse
>> consequences).  We don't have that today.
>
> BTW, it strikes me that ALTER USER RENAME introduces an interesting
> hazard for such a plugin.  Consider
>
> CREATE USER joe;
> ALTER USER joe PASSWORD joe;  -- presumably, plugin will reject this
> ALTER USER joe PASSWORD mumblefrotz;  -- assume this is considered OK
> ALTER USER joe RENAME TO mumblefrotz;
>
> Now we have a user with name equal to password, which no sane security
> policy will think is a good thing, but the plugin had no chance to
> prevent it.

The big difference is that you need to be superuser to change the name
of a user, but not to change your own password.

I know for example the Windows password policy thing has the same
issue - if you rename the user, it doesn't have the password around to
check, but you are an administrator so that's considered ok.


-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: Rejecting weak passwords

Tom Lane <tgl@sss.pgh.pa.us> — 2009-10-20T13:42:36Z

Magnus Hagander <magnus@hagander.net> writes:
> 2009/10/19 Tom Lane <tgl@sss.pgh.pa.us>:
>> Now we have a user with name equal to password, which no sane security
>> policy will think is a good thing, but the plugin had no chance to
>> prevent it.

> The big difference is that you need to be superuser to change the name
> of a user, but not to change your own password.

True, but the superuser doesn't necessarily know what the user has
set his password to.

			regards, tom lane

Re: Rejecting weak passwords

Robert Haas <robertmhaas@gmail.com> — 2009-10-20T14:07:31Z

On Tue, Oct 20, 2009 at 9:42 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Magnus Hagander <magnus@hagander.net> writes:
>> 2009/10/19 Tom Lane <tgl@sss.pgh.pa.us>:
>>> Now we have a user with name equal to password, which no sane security
>>> policy will think is a good thing, but the plugin had no chance to
>>> prevent it.
>
>> The big difference is that you need to be superuser to change the name
>> of a user, but not to change your own password.
>
> True, but the superuser doesn't necessarily know what the user has
> set his password to.

Yeah, but I'm not sure this case is worth worrying about.  People who
actually care password security are likely to have checks that are
substantially stronger than "!= username".

...Robert

Re: Rejecting weak passwords

Takahiro Itagaki <itagaki.takahiro@oss.ntt.co.jp> — 2009-11-16T04:24:14Z

"Albe Laurenz" <laurenz.albe@wien.gv.at> wrote:

> I wrote:
> > Following the discussions in
> > http://archives.postgresql.org/pgsql-hackers/2009-09/msg01766.php
> > and
> > http://archives.postgresql.org/pgsql-hackers/2009-10/msg00025.php ,
> > here are patches for
> > 
> > a) a hook in backend/commands/user.c that allows one to add
> >    password checking functions
> > b) a contrib module that makes use of the hook and
> > c) documentation for the contrib module.
> 
> I found a small but embarrassing bug - here is another version.

I've reviewed your patch. The rough approach looks fine,
but I have some comments about function declarations and coding style.

The hook in core is declared as:
  typedef int(*check_password_hook_type)(char * const username, char * const password);

but result type is actually  treated as a boolean. So, it also should
declared as bool. Also, the type of arguments should be "const char *".


There several comments in contrib/passwordcheck.

 - We don't need #ifdef PG_MODULE_MAGIC because the module works
   only on 8.5 or later; PG_MODULE_MAGIC is always defined there.

 - _PG_fini() is never called in HEAD anymore. Please remove it.

 - The function declaration of _PG_init() should be _PG_init(void).

 - isalpha() should be called as isalpha((unsigned char) c)
   because it could be crashed by multi-byte characters.

 - "8 characters long" would be better if it is configurable easily.
   I don't think it is to be a GUC varable, but #define should be used.

 - The logic in "check if the password contains only letters" should be
   "check if the password contains both upper-case, lower-case and
   non-alphabet letters". Passwords like "12345678' are not enough.

 - Coding style should more follow postgres. For example, posisions
   of '{', usage of spaces and linebreaks.


Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center

Re: Rejecting weak passwords

Albe Laurenz <laurenz.albe@wien.gv.at> — 2009-11-17T10:32:00Z

Itagaki Takahiro wrote:
> I've reviewed your patch. The rough approach looks fine,
> but I have some comments about function declarations and coding style.

Thank you for taking the time!

I think I addressed all the shortcomings you mentioned with
the attached patches. I fixed the "{" alignment; I am not sure
if there are any other spacing or alignment problems.

Yours,
Laurenz Albe

Re: Rejecting weak passwords

Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> — 2009-11-17T11:41:14Z

I think it would better to add an explicit "isencrypted" parameter to
the check_password_hook function, rather than require the module to do
isMD5 on the password. Any imaginable check hook will need to know if
the password is in MD5 format, and the backend already knows it (because
it already did that check), it seems good to let the hook function know.
Besides, if we introduce explicit syntax for saying that the supplied
password is plaintext or md5 one day, calling isMD5 in the module will
no longer be appropriate.

-- 
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com

Re: Rejecting weak passwords

Albe Laurenz <laurenz.albe@wien.gv.at> — 2009-11-17T12:28:01Z

Heikki Linnakangas wrote:
> I think it would better to add an explicit "isencrypted" parameter to
> the check_password_hook function, rather than require the module to do
> isMD5 on the password. Any imaginable check hook will need to know if
> the password is in MD5 format, and the backend already knows it (because
> it already did that check), it seems good to let the hook function know.
> Besides, if we introduce explicit syntax for saying that the supplied
> password is plaintext or md5 one day, calling isMD5 in the module will
> no longer be appropriate.

I agree on the second point, and I changed the patch accordingly.

Here's the latest version.

Yours,
Laurenz Albe

Re: Rejecting weak passwords

Takahiro Itagaki <itagaki.takahiro@oss.ntt.co.jp> — 2009-11-18T05:51:09Z

"Albe Laurenz" <laurenz.albe@wien.gv.at> wrote:

> Heikki Linnakangas wrote:
> > I think it would better to add an explicit "isencrypted" parameter to
> > the check_password_hook function, rather than require the module to do
> > isMD5 on the password.
> 
> I agree on the second point, and I changed the patch accordingly.
> Here's the latest version.

Looks good. I change status of the patch to "Ready for Committer".

BTW, it might not be a work for this patch, we also need to
reject too long "VALID UNTIL" setting. If the password is
complex, we should not use the same password for a long time.

Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center

Re: Rejecting weak passwords

Albe Laurenz <laurenz.albe@wien.gv.at> — 2009-11-18T10:35:22Z

Itagaki Takahiro wrote:
> Looks good. I change status of the patch to "Ready for Committer".

Thanks for the help!

> BTW, it might not be a work for this patch, we also need to
> reject too long "VALID UNTIL" setting. If the password is
> complex, we should not use the same password for a long time.

There are some cases, e.g. application servers logging into the
database, where you cannot just let the password expire, so I
think this would at best have to be a rule with exceptions.

Another thing that makes VALID UNTIL inconvenient to use is
that after expiration, logins simply fail, and the user
is never prompted to change the password.

But of course you are right, requiring a limited password
lifetime is closely related to requiring a good password.

Yours,
Laurenz Albe

Re: Rejecting weak passwords

Tom Lane <tgl@sss.pgh.pa.us> — 2009-11-18T20:05:34Z

Itagaki Takahiro <itagaki.takahiro@oss.ntt.co.jp> writes:
> BTW, it might not be a work for this patch, we also need to
> reject too long "VALID UNTIL" setting. If the password is
> complex, we should not use the same password for a long time.

This is a good point --- people who have password strength policies
tend to want a limited usage period as well.  It's even conceivable
that you could have different allowed lifespans depending on how
strong the password is.  I suggest we alter the hook signature to pass
it the valuntil time along with the other parameters it's already
getting, and let the one hook enforce policies for both.

I'm reviewing the patch now, and barring objections will make this
change before committing.

			regards, tom lane

Re: Rejecting weak passwords

Tom Lane <tgl@sss.pgh.pa.us> — 2009-11-18T22:05:34Z

Itagaki Takahiro <itagaki.takahiro@oss.ntt.co.jp> writes:
> "Albe Laurenz" <laurenz.albe@wien.gv.at> wrote:
>> I agree on the second point, and I changed the patch accordingly.
>> Here's the latest version.

> Looks good. I change status of the patch to "Ready for Committer".

Applied with some minor modifications.  Aside from the added valuntil
parameter, I changed the "isencrypted" parameter to an int with some
#define'd values.  It seems easily foreseeable that we'll replace the
MD5 encryption scheme someday, and it'd be good to ensure that this
API is extendable when that happens.  Also, I got rid of the bool
return value and made the hook responsible for throwing its own errors.
I don't know about you guys, but I would cheerfully kill anybody who
tried to make me use a password checker that didn't tell me anything
about why it thinks my password is too weak.  (The CrackLib API we
are using is lamentably badly designed on this score --- does it have
another call that provides a more useful error report?)  Even if you
think "weak password" is adequate for that class of complaints, the
single error message would certainly not do for complaints about the
valuntil date being too far away.

			regards, tom lane

Re: Rejecting weak passwords

Albe Laurenz <laurenz.albe@wien.gv.at> — 2009-11-19T07:59:10Z

Tom Lane wrote:
> Applied with some minor modifications.  Aside from the added valuntil
> parameter, I changed the "isencrypted" parameter to an int with some
> #define'd values.  It seems easily foreseeable that we'll replace the
> MD5 encryption scheme someday, and it'd be good to ensure that this
> API is extendable when that happens.  Also, I got rid of the bool
> return value and made the hook responsible for throwing its 
> own errors.
> I don't know about you guys, but I would cheerfully kill anybody who
> tried to make me use a password checker that didn't tell me anything
> about why it thinks my password is too weak.  (The CrackLib API we
> are using is lamentably badly designed on this score --- does it have
> another call that provides a more useful error report?)  Even if you
> think "weak password" is adequate for that class of complaints, the
> single error message would certainly not do for complaints about the
> valuntil date being too far away.

Thank you.

I agree on all points.
I did not know that contrib modules get translated too, else I would
have thrown the error messages there.

Yours,
Laurenz Albe

Re: Rejecting weak passwords

Tom Lane <tgl@sss.pgh.pa.us> — 2009-11-19T15:21:16Z

"Albe Laurenz" <laurenz.albe@wien.gv.at> writes:
> I did not know that contrib modules get translated too, else I would
> have thrown the error messages there.

I'm not sure whether contrib is currently covered by the translation
teams, but it could someday be.  In any case, an inadequate error
message is not made less inadequate by translating it ;-)

			regards, tom lane