Re: Rejecting weak passwords

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

On 28 sep 2009, at 11.54, "Albe Laurenz" <laurenz(dot)albe(at)wien(dot)gv(dot)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

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

* Magnus Hagander (magnus(at)hagander(dot)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

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/

In response to "Ing. Marcos L. Ortí­z Valmaseda" <mlortiz(at)uci(dot)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/

2009/9/28 Stephen Frost <sfrost(at)snowman(dot)net>:
> * Magnus Hagander (magnus(at)hagander(dot)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/

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

2009/9/28 Andrew Dunstan <andrew(at)dunslane(dot)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/

Andrew Dunstan <andrew(at)dunslane(dot)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

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

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

"Albe Laurenz" <laurenz(dot)albe(at)wien(dot)gv(dot)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

On Mon, Sep 28, 2009 at 4:38 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Albe Laurenz" <laurenz(dot)albe(at)wien(dot)gv(dot)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

Dave Page <dpage(at)pgadmin(dot)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

On 9/28/09, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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

Marko Kreen <markokr(at)gmail(dot)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

On Mon, Sep 28, 2009 at 2:00 PM, Marko Kreen <markokr(at)gmail(dot)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

> 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

> 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

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

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

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

Josh Berkus <josh(at)agliodbs(dot)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

marcin mank <marcin(dot)mank(at)gmail(dot)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

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

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

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

> Josh Berkus <josh(at)agliodbs(dot)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[(dot)singh](at)EnterpriseDB(dot)com

singh(dot)gurjeet(at){ gmail | hotmail | indiatimes | yahoo }.com
Twitter: singh_gurjeet
Skype: singh_gurjeet

Mail sent from my BlackLaptop device

"Albe Laurenz" <laurenz(dot)albe(at)wien(dot)gv(dot)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

On Tue, Sep 29, 2009 at 9:48 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Albe Laurenz" <laurenz(dot)albe(at)wien(dot)gv(dot)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

Robert Haas wrote:
> On Tue, Sep 29, 2009 at 9:48 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
>> "Albe Laurenz" <laurenz(dot)albe(at)wien(dot)gv(dot)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

Robert Haas <robertmhaas(at)gmail(dot)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

Andrew Dunstan <andrew(at)dunslane(dot)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

On 09/29/2009 09:07 AM, Gurjeet Singh wrote:
> On Tue, Sep 29, 2009 at 4:49 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us
> <mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us>> wrote:
>
> Josh Berkus <josh(at)agliodbs(dot)com <mailto:josh(at)agliodbs(dot)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(at)mielke(dot)cc>

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

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(at)mielke(dot)cc>

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

On Thu, Oct 1, 2009 at 1:53 PM, Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)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

Dave Page wrote:
> On Thu, Oct 1, 2009 at 1:53 PM, Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)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

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

On Thu, Oct 1, 2009 at 15:26, Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)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/

On Thu, Oct 01, 2009 at 03:54:37PM +0200, Magnus Hagander wrote:
> On Thu, Oct 1, 2009 at 15:26, Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)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

* Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)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(at)highrise(dot)ca command like a king,
http://www.highrise.ca/ work like a slave.

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

Dave Page <dpage(at)pgadmin(dot)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

On Thu, Oct 1, 2009 at 17:24, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Dave Page <dpage(at)pgadmin(dot)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/

Magnus Hagander <magnus(at)hagander(dot)net> writes:
> On Thu, Oct 1, 2009 at 17:24, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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

Magnus Hagander escreveu:
> On Thu, Oct 1, 2009 at 17:24, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Dave Page <dpage(at)pgadmin(dot)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/

On Thu, Oct 1, 2009 at 19:07, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Magnus Hagander <magnus(at)hagander(dot)net> writes:
>> On Thu, Oct 1, 2009 at 17:24, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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/

On Thu, Oct 01, 2009 at 01:07:04PM -0400, Tom Lane wrote:
> Magnus Hagander <magnus(at)hagander(dot)net> writes:
> > On Thu, Oct 1, 2009 at 17:24, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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

Tom Lane wrote:
> Magnus Hagander <magnus(at)hagander(dot)net> writes:
>
>> On Thu, Oct 1, 2009 at 17:24, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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

Magnus Hagander <magnus(at)hagander(dot)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

Tom Lane wrote:
> Magnus Hagander <magnus(at)hagander(dot)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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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

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

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

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

On Mon, Sep 28, 2009 at 7:37 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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

Dave Page <dpage(at)pgadmin(dot)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

Tom Lane wrote:
> Dave Page <dpage(at)pgadmin(dot)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

On Wed, Oct 14, 2009 at 4:11 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Dave Page <dpage(at)pgadmin(dot)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

Dave Page <dpage(at)pgadmin(dot)org> writes:
> On Wed, Oct 14, 2009 at 4:11 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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

On 10/14/09, Dave Page <dpage(at)pgadmin(dot)org> wrote:
> On Mon, Sep 28, 2009 at 7:37 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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

On Wed, Oct 14, 2009 at 4:30 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Dave Page <dpage(at)pgadmin(dot)org> writes:
>> On Wed, Oct 14, 2009 at 4:11 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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

On Wed, Oct 14, 2009 at 11:44 AM, Dave Page <dpage(at)pgadmin(dot)org> wrote:
> On Wed, Oct 14, 2009 at 4:30 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Dave Page <dpage(at)pgadmin(dot)org> writes:
>>> On Wed, Oct 14, 2009 at 4:11 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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

Dave Page <dpage(at)pgadmin(dot)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

On Wed, Oct 14, 2009 at 5:08 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Dave Page <dpage(at)pgadmin(dot)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

Dave Page <dpage(at)pgadmin(dot)org> writes:
> On Wed, Oct 14, 2009 at 5:08 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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

On Wed, Oct 14, 2009 at 5:25 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Dave Page <dpage(at)pgadmin(dot)org> writes:
>> On Wed, Oct 14, 2009 at 5:08 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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

On Wed, Oct 14, 2009 at 18:25, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Dave Page <dpage(at)pgadmin(dot)org> writes:
>> On Wed, Oct 14, 2009 at 5:08 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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/

Dave Page <dpage(at)pgadmin(dot)org> writes:
> On Wed, Oct 14, 2009 at 5:25 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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

Magnus Hagander <magnus(at)hagander(dot)net> writes:
> On Wed, Oct 14, 2009 at 18:25, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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

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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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

On Wed, Oct 14, 2009 at 12:25 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Dave Page <dpage(at)pgadmin(dot)org> writes:
>> On Wed, Oct 14, 2009 at 5:08 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Wed, Oct 14, 2009 at 12:25 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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

On Wed, Oct 14, 2009 at 10:28 AM, Bruce Momjian <bruce(at)momjian(dot)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

On Wed, Oct 14, 2009 at 7:42 PM, Greg Stark <gsstark(at)mit(dot)edu> wrote:
> On Wed, Oct 14, 2009 at 10:28 AM, Bruce Momjian <bruce(at)momjian(dot)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

On Wed, Oct 14, 2009 at 6:08 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Magnus Hagander <magnus(at)hagander(dot)net> writes:
>> On Wed, Oct 14, 2009 at 18:25, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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

On Wed, Oct 14, 2009 at 1:48 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> On Wed, Oct 14, 2009 at 12:25 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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

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(at)mielke(dot)cc>

Dave Page <dpage(at)pgadmin(dot)org> writes:
> On Wed, Oct 14, 2009 at 7:42 PM, Greg Stark <gsstark(at)mit(dot)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

On Wed, Oct 14, 2009 at 9:00 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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

Dave Page <dpage(at)pgadmin(dot)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

Dave Page <dpage(at)pgadmin(dot)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

"Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)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

On Wed, Oct 14, 2009 at 9:50 PM, Kevin Grittner
<Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
> Dave Page <dpage(at)pgadmin(dot)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

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)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

Dave Page <dpage(at)pgadmin(dot)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

On Wed, Oct 14, 2009 at 10:51 PM, Kevin Grittner
<Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
> Dave Page <dpage(at)pgadmin(dot)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

Tom Lane wrote:
> "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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

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.

On 10/14/2009 05:33 PM, Dave Page wrote:
> On Wed, Oct 14, 2009 at 9:50 PM, Kevin Grittner
> <Kevin(dot)Grittner(at)wicourts(dot)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(at)mielke(dot)cc>

Peter Eisentraut <peter_e(at)gmx(dot)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

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.

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.

Bruce Momjian <bruce(at)momjian(dot)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

On 10/14/2009 06:02 PM, Dave Page wrote:
> On Wed, Oct 14, 2009 at 10:51 PM, Kevin Grittner
> <Kevin(dot)Grittner(at)wicourts(dot)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(at)mielke(dot)cc>

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(at)mielke(dot)cc>

* Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
> Peter Eisentraut <peter_e(at)gmx(dot)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

On Wed, Oct 14, 2009 at 11:21 PM, Mark Mielke <mark(at)mark(dot)mielke(dot)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

On Wed, Oct 14, 2009 at 11:44 PM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> * Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
>> Peter Eisentraut <peter_e(at)gmx(dot)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

Dave Page <dpage(at)pgadmin(dot)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

On Thu, Oct 15, 2009 at 2:49 PM, Kevin Grittner
<Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
> Dave Page <dpage(at)pgadmin(dot)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

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

On 10/15/2009 03:54 AM, Dave Page wrote:
> On Wed, Oct 14, 2009 at 11:21 PM, Mark Mielke<mark(at)mark(dot)mielke(dot)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(at)mielke(dot)cc>

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(at)mielke(dot)cc>

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(at)mielke(dot)cc>

On Thu, Oct 15, 2009 at 5:28 PM, Mark Mielke <mark(at)mark(dot)mielke(dot)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

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.

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

On Thu, Oct 15, 2009 at 12:23 PM, Mark Mielke <mark(at)mark(dot)mielke(dot)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

Robert Haas <robertmhaas(at)gmail(dot)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

On Thu, Oct 15, 2009 at 6:17 PM, Josh Berkus <josh(at)agliodbs(dot)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

On Thu, Oct 15, 2009 at 6:43 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)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

On Thu, Oct 15, 2009 at 1:47 PM, Dave Page <dpage(at)pgadmin(dot)org> wrote:
> On Thu, Oct 15, 2009 at 6:43 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Robert Haas <robertmhaas(at)gmail(dot)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

On Thu, Oct 15, 2009 at 6:55 PM, Robert Haas <robertmhaas(at)gmail(dot)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

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(at)mielke(dot)cc>

Dave Page <dpage(at)pgadmin(dot)org> writes:
> On Thu, Oct 15, 2009 at 6:43 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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

Dave Page <dpage(at)pgadmin(dot)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

On 10/15/2009 02:02 PM, Dave Page wrote:
> On Thu, Oct 15, 2009 at 6:55 PM, Robert Haas<robertmhaas(at)gmail(dot)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(at)mielke(dot)cc>

On Thu, Oct 15, 2009 at 7:22 PM, Mark Mielke <mark(at)mark(dot)mielke(dot)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

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.

On Thu, Oct 15, 2009 at 10:40 PM, Ron Mayer
<rm_pg(at)cheapcomplexdevices(dot)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

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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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

Tom Lane wrote:
> Robert Haas <robertmhaas(at)gmail(dot)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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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

Bruce Momjian <bruce(at)momjian(dot)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

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(at)mielke(dot)cc>

On Fri, Oct 16, 2009 at 4:28 PM, Bruce Momjian <bruce(at)momjian(dot)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

Tom Lane wrote:
> Bruce Momjian <bruce(at)momjian(dot)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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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

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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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