Обсуждение: 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 limitingtoo. 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.albe@wien.gv.at> wrote: > Dear hackers, > > I have been thinking about ways to have PostgreSQL reject > weak passwords. > > I think the standard recommendation is "use PAM and LDAP", > but that requires the user to change the password outside > of PostgreSQL. And who would want to setup and maintain an > LDAP server just for this? > > Since everybody has different ideas what is a good password, > there should be some way to configure that. I've looked at > how Oracle does it, and they simply let you write a > stored procedure that throws an exception if it doesn't > like the password. > Since users are on cluster level and functions live in > databases, that won't work in PostgreSQL. > > I have come up with an idea or two and like to hear your > opinion. > > 1) One could have a set of GUCs like min_password_length, > min_password_nonchars and similar that everybody > could configure. This is not extremely flexible though. > 2) Another idea would be a GUC that contains a regular > expression that a password may *not* match. > Perhaps that's too limiting too. > 3) I have also considered a GUC that points to a loadable > module that performs the password check if set. > > Are there better ways? Isn't there some library we can link with and (conditionally) use? I believe windows exposes api function(s) to let you verify password complexity - I'm sure there is something similar available on unix, hopefully included on most common platforms? /Magnus
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
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/
Вложения
* Magnus Hagander (magnus@hagander.net) wrote: >> Are there better ways? > > Isn't there some library we can link with and (conditionally) use? I > believe windows exposes api function(s) to let you verify password > complexity - I'm sure there is something similar available on unix, > hopefully included on most common platforms? cracklib2. Stephen
In response to "Ing. Marcos L. Ortíz Valmaseda" <mlortiz@uci.cu>: > Andrew Dunstan escribió: > > > > Albe Laurenz wrote: > >> Dear hackers, > >> > >> I have been thinking about ways to have PostgreSQL reject > >> weak passwords. > >> > >> I think the standard recommendation is "use PAM and LDAP", > >> but that requires the user to change the password outside > >> of PostgreSQL. And who would want to setup and maintain an > >> LDAP server just for this? An option here is to have a way for PG to hook in to LDAP/PAM so that an ALTER ROLE actually changes the LDAP/PAM password. > >> Since everybody has different ideas what is a good password, > >> there should be some way to configure that. I've looked at > >> how Oracle does it, and they simply let you write a > >> stored procedure that throws an exception if it doesn't > >> like the password. [snip] > >> 3) I have also considered a GUC that points to a loadable > >> module that performs the password check if set. > > > You have to analyze all points before to do this. I vote too for the > third option, but you have to be clear that how do you ´ll check the > weakness of the password: > 1- For example: the length should be greater that 6 char.. > 2- The password should be have a combination fo numbers, letters and > others dots I think you've missed the point. If a loadable module is used, then it can do anything the DBAs need it to. You can write your own module if you're not happy with those provided. At that point, arguing about what makes a good password is pretty irrelevant. Note the paragraph that I didn't snip where Albe points this out. -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/
2009/9/28 Stephen Frost <sfrost@snowman.net>: > * Magnus Hagander (magnus@hagander.net) wrote: >>> Are there better ways? >> >> Isn't there some library we can link with and (conditionally) use? I >> believe windows exposes api function(s) to let you verify password >> complexity - I'm sure there is something similar available on unix, >> hopefully included on most common platforms? > > cracklib2. That sounds like the one I thought about :-) -- Magnus HaganderMe: 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@dunslane.net>: > > > Ing. Marcos L. Ortíz Valmaseda wrote: >>> >>> My vote is for #3, if anything. >>> >>> >> You have to analyze all points before to do this. I vote too for the third option, but you have to be clear that how doyou ´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 implementingwhatever rules they want to enforce. That makes a lot of sense. Then we could perhaps ship a cracklib2 provider in contrib. -- Magnus HaganderMe: http://www.hagander.net/Work: http://www.redpill-linpro.com/
Andrew Dunstan <andrew@dunslane.net> writes: > Albe Laurenz wrote: >> 1) One could have a set of GUCs like min_password_length, >> min_password_nonchars and similar that everybody >> could configure. This is not extremely flexible though. >> 2) Another idea would be a GUC that contains a regular >> expression that a password may *not* match. >> Perhaps that's too limiting too. >> 3) I have also considered a GUC that points to a loadable >> module that performs the password check if set. > My vote is for #3, if anything. Yeah. I think there is no chance of anything in this vein getting accepted into core Postgres, if only because everybody will have a different idea of what it needs to do. A hook function (no need for a GUC) would be a reasonable proposal. regards, tom lane
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
"Albe Laurenz" <laurenz.albe@wien.gv.at> writes: > Tom Lane wrote: >> Actually there's a much bigger problem with asking the backend to reject >> weak passwords: what ya gonna do with a pre-MD5'd string? Which is >> exactly what the backend is going to always get, in a security-conscious >> environment. > I'm thinking of the case where somebody changes his or her > password interactively on the command line, with pgAdmin III, > or similar. People would hardly use the above in that case, Really? If pgAdmin has a password-change function that doesn't use client-side password encryption then somebody should file a bug against it. Sending unencrypted passwords exposes the password at least to the postmaster logfile. createuser has been doing encryption, unless specifically commanded not to, for a long time. regards, tom lane
On Mon, Sep 28, 2009 at 4:38 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > "Albe Laurenz" <laurenz.albe@wien.gv.at> writes: >> Tom Lane wrote: >>> Actually there's a much bigger problem with asking the backend to reject >>> weak passwords: what ya gonna do with a pre-MD5'd string? Which is >>> exactly what the backend is going to always get, in a security-conscious >>> environment. > >> I'm thinking of the case where somebody changes his or her >> password interactively on the command line, with pgAdmin III, >> or similar. People would hardly use the above in that case, > > Really? If pgAdmin has a password-change function that doesn't use > client-side password encryption then somebody should file a bug against > it. Sending unencrypted passwords exposes the password at least to the > postmaster logfile. createuser has been doing encryption, unless > specifically commanded not to, for a long time. pgAdmin MD5's the passwords if you use the GUI to change them, or when add a user. It doesn't make any attempt to parse the SQL if you enter it yourself in the query tool though (nor is it going to). -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com
Dave Page <dpage@pgadmin.org> writes: > pgAdmin MD5's the passwords if you use the GUI to change them, or when > add a user. It doesn't make any attempt to parse the SQL if you enter > it yourself in the query tool though (nor is it going to). No, I wouldn't expect it to go that far. My point is just that pre-MD5'd passwords are a lot commoner than Albe seems to think. regards, tom lane
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
On 9/28/09, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Actually there's a much bigger problem with asking the backend to reject > weak passwords: what ya gonna do with a pre-MD5'd string? Which is > exactly what the backend is going to always get, in a security-conscious > environment. Hmm... Looking at the docs, I don't see anywhere mention that the MD5 hashed passwords can still be used to log in, that only thing MD5'ing protects from is actually knowing what the password was. Also, although it does protect from sniffing password at login time, it is as insecure as plaintext password against sniffing on CREATE/ALTER USER time. Thus only secure way to change password is over SSL, in which case it does not make much difference whether it's plaintext or not. Logfile may seem to be as argument against plaintext, but note that you still cannot allow unprivileged users to access logfile that may contain MD5 passwords, as they can use them to log in as another user. So promoting the ENCRYPTED 'foo' as "secure" may lure users into false sense of security, and be lax against sniffing and logfile protection. Better approach seems to be simply refuse to log the password value into logfile, whether it's MD5 or not, and promote SSL as only secure way of changing passwords. IOW, having plaintext password in CREATE/ALTER time which can then checked for weaknesses is better that MD5 password, which actually does not increase security. We just need to fix the logging. -- marko
Marko Kreen <markokr@gmail.com> writes: > So promoting the ENCRYPTED 'foo' as "secure" may lure users into > false sense of security, and be lax against sniffing and logfile > protection. This argument is entirely irrelevant to the point. Yes, ENCRYPTED doesn't fix everything, but it is still good practice to use it and most well-written tools will. So having a weak-password detector that can only work on non-encrypted passwords is going to not be very helpful. > IOW, having plaintext password in CREATE/ALTER time which can > then checked for weaknesses is better that MD5 password, which > actually does not increase security. This is not acceptable and will not happen. The case that ENCRYPTED protects against is database superusers finding out other users' original passwords, which is a security issue to the extent that those users have used the same/similar passwords for other systems. We're not going to give up protection for that in order to provide an option to do weak-password checking in a place that simply isn't the best place to do it anyway. regards, tom lane
On Mon, Sep 28, 2009 at 2:00 PM, Marko Kreen <markokr@gmail.com> wrote: > So promoting the ENCRYPTED 'foo' as "secure" may lure users into > false sense of security, and be lax against sniffing and logfile > protection. ENCRYPTED prevents the user's password from being stolen by a modified server. ...Robert
> 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@agliodbs.com> writes: > Hmmm, that would be a useful, easy (I think) security feature: add a GUC > for failed_logins_allowed. And the counts would be tracked and enforced where? regards, tom lane
marcin mank <marcin.mank@gmail.com> writes: >> The case that ENCRYPTED >> protects against is database superusers finding out other users' >> original passwords, which is a security issue to the extent that those >> users have used the same/similar passwords for other systems. > I just want to note that md5 is not much of a protection against this > case these days. Take a look at this: > http://www.golubev.com/hashgpu.htm > It takes about 32 hours to brute force all passwords from [a-zA-Z0-9] > of up to 8 chars in length. Yeah, but that will find you a password that hashes to the same thing. Not necessarily the same password. It'll get you into the Postgres DB just fine, which you don't care about because you're already a superuser there. It won't necessarily get you into the assumed third-party systems. regards, tom lane
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
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@sss.pgh.pa.us> wrote:
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,
-- Josh Berkus <josh@agliodbs.com> writes:And the counts would be tracked and enforced where?
> Hmmm, that would be a useful, easy (I think) security feature: add a GUC
> for failed_logins_allowed.
Combining this with other suggestion:
.) Provide a GUC failed_logins_allowed
.) Add MAX FAILED LOGINS option to ADD/ALTER USER, which defaults to the GUC if not provided in the command.
.) Track per-user failed attempt counts in shared catalog, and reset on a successful login.
Best regards,
Lets call it Postgres
EnterpriseDB http://www.enterprisedb.com
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com
Twitter: singh_gurjeet
Skype: singh_gurjeet
Mail sent from my BlackLaptop device
"Albe Laurenz" <laurenz.albe@wien.gv.at> writes: > I thought about it some more, and I think that a password checking > hook might still be somewhat useful even for MD5-encrypted passwords; > the function could guess and exclude at least that dreadful > all-too-frequent case of username = password. True. You could probably even run through a moderate-size dictionary of weak passwords, depending on how long you're willing to make the user wait. (CHECK_FOR_INTERRUPTS inside the loop would be polite ;-)) regards, tom lane
On Tue, Sep 29, 2009 at 9:48 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > "Albe Laurenz" <laurenz.albe@wien.gv.at> writes: >> I thought about it some more, and I think that a password checking >> hook might still be somewhat useful even for MD5-encrypted passwords; >> the function could guess and exclude at least that dreadful >> all-too-frequent case of username = password. > > True. You could probably even run through a moderate-size dictionary > of weak passwords, depending on how long you're willing to make the > user wait. (CHECK_FOR_INTERRUPTS inside the loop would be polite ;-)) But how much value is there in that? This whole thing seems like a dead end to me. No matter how long you're willing to wait, putting the checking on the client side will let you far more validation for the same price. ...Robert
Robert Haas wrote: > On Tue, Sep 29, 2009 at 9:48 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > >> "Albe Laurenz" <laurenz.albe@wien.gv.at> writes: >> >>> I thought about it some more, and I think that a password checking >>> hook might still be somewhat useful even for MD5-encrypted passwords; >>> the function could guess and exclude at least that dreadful >>> all-too-frequent case of username = password. >>> >> True. You could probably even run through a moderate-size dictionary >> of weak passwords, depending on how long you're willing to make the >> user wait. (CHECK_FOR_INTERRUPTS inside the loop would be polite ;-)) >> > > But how much value is there in that? This whole thing seems like a > dead end to me. No matter how long you're willing to wait, putting > the checking on the client side will let you far more validation for > the same price. > > > Why do we need to answer that question? If all we do is provide a hook, the cost is very low, and the decision on value is left to whoever is deploying some module to use the hook. And it will let people possibly implement some password security policy dictated by some PHB, and so check off a box on a form somewhere. Frankly, real security requires that you pretty much get out of the password game, but passwords will undoubtedly be around for a long time, since people will always trade security for convenience. cheers andrew
Robert Haas <robertmhaas@gmail.com> writes: > But how much value is there in that? This whole thing seems like a > dead end to me. No matter how long you're willing to wait, putting > the checking on the client side will let you far more validation for > the same price. No doubt, but ... The value of doing it on the server side is you only have to implement it once, and you don't have to hope that all your users are using the most up-to-date clients that will enforce a check. (The more troglodytic of them might be using a direct ALTER USER PASSWORD command, which will certainly not result in any client-side check happening.) Even if we encouraged client-side tool authors to implement password checking, the lack of consensus about what the checks should be would pretty much guarantee lack of uniformity about exactly what got checked in any real installation. Which is not the sort of thing that makes security auditors feel all warm and fuzzy. regards, tom lane
Andrew Dunstan <andrew@dunslane.net> writes: > Why do we need to answer that question? If all we do is provide a hook, > the cost is very low, and the decision on value is left to whoever is > deploying some module to use the hook. Right. As long as it's just a hook, it's not enough work to justify lots of debate. There is a plausible use-case, and that's enough. regards, tom lane
On 09/29/2009 09:07 AM, Gurjeet Singh wrote:
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
On Tue, Sep 29, 2009 at 4:49 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:Josh Berkus <josh@agliodbs.com> writes:And the counts would be tracked and enforced where?
> Hmmm, that would be a useful, easy (I think) security feature: add a GUC
> for failed_logins_allowed.
Combining this with other suggestion:
.) Provide a GUC failed_logins_allowed
.) Add MAX FAILED LOGINS option to ADD/ALTER USER, which defaults to the GUC if not provided in the command.
.) Track per-user failed attempt counts in shared catalog, and reset on a successful login.
Reset on successful works _against_ the goal of preventing brute force attacks. Brute force attacks try until successful.
I read Josh's original suggestion to eventually evolve to "if a particular user account from a particular IP address uses the wrong password more than N times in T minutes, than the IP address is locked out for U minutes." This is the *only* way of significantly reducing the ability of a client to guess the password using "brute force".
It works pretty successfully in other systems. I know when I forget my voice mail password and I try five times, my account gets locked out for 24+ hours unless I request a password reset from our support organization. It is a pain in the butt - but it means that somebody trying to guess my password either has to get it right in a few short guesses, or they are out of luck.
Still, all of this seems a little bit over thought out to me, as unless one goes to this extreme - of actually blocking connections from an IP for a period of time - the ability to brute force passwords is already limited by network capacity, network latency, and protocol restrictions. md5 might be "broken" from the perspective of a super user having access to the md5 and having access to hardware accelerators (GPU), but brute forcing from a client to a server is still limited to thousands of attempts or less per second. This particular aspect of PostgreSQL has not concerned me. I tend to think that anybody who exposes their PostgreSQL to the Internet directly is asking for trouble no matter how hard pgsql-hackers tries to protect them. On my own network, there are so many other ways of getting at the passwords - including the crypt() passwords being visible over NIS using "ypmatch", that this is really the least of my concerns. We have employee agreements in place that prevent the use of "hacking", and outsiders are not supposed to have access to our network.
Point being - if you want to really be effective compared to what we have today - you need to go all the way. Half way is useless.
Cheers,
mark
-- Mark Mielke <mark@mielke.cc>
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@mielke.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.albe@wien.gv.at> wrote: > In the discussion following > http://archives.postgresql.org/pgsql-hackers/2009-09/msg01766.php > the consensus was that a hook that allows you to implement > a password checking routine as a module "would not hurt". > > So here's the patch. > I don't think there is documentation required; > correct me if I am wrong. How will people know how to use it, or that it's even there without at least a note in the docs somewhere? -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com
Dave Page wrote: > On Thu, Oct 1, 2009 at 1:53 PM, Albe Laurenz <laurenz.albe@wien.gv.at> wrote: > >> In the discussion following >> http://archives.postgresql.org/pgsql-hackers/2009-09/msg01766.php >> the consensus was that a hook that allows you to implement >> a password checking routine as a module "would not hurt". >> >> So here's the patch. >> I don't think there is documentation required; >> correct me if I am wrong. >> > > How will people know how to use it, or that it's even there without at > least a note in the docs somewhere? > > I'd prefer to have an example as a contrib module, as well as docs. Quite apart from anything else, how the heck would we test it without such a thing? cheers andrew
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.albe@wien.gv.at> wrote: > Andrew Dunstan wrote: >>>> So here's the patch. >>>> I don't think there is documentation required; >>>> correct me if I am wrong. >>> >>> How will people know how to use it, or that it's even there without at >>> least a note in the docs somewhere? >> >> I'd prefer to have an example as a contrib module, as well as docs. >> Quite apart from anything else, how the heck would we test it without >> such a thing? > > I was not sure because no other hooks were documented anywhere else > than in the code. > > I could add a paragraph in the "auth-password" section of > client-auth.sgml. Or is there a better place? > > I could easily write a simple contrib that adds a check for > username = password if there is interest. I think it's better to have an actually *useful* contrib module for it, if there is one. Meaning perhaps something that links to that cracklib thing mentioned upthread. -- Magnus HaganderMe: 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.albe@wien.gv.at> wrote: > > Andrew Dunstan wrote: > >>>> So here's the patch. > >>>> I don't think there is documentation required; > >>>> correct me if I am wrong. > >>> > >>> How will people know how to use it, or that it's even there without at > >>> least a note in the docs somewhere? > >> > >> I'd prefer to have an example as a contrib module, as well as docs. > >> Quite apart from anything else, how the heck would we test it without > >> such a thing? > > > > I was not sure because no other hooks were documented anywhere else > > than in the code. > > > > I could add a paragraph in the "auth-password" section of > > client-auth.sgml. Or is there a better place? > > > > I could easily write a simple contrib that adds a check for > > username = password if there is interest. > > I think it's better to have an actually *useful* contrib module for > it, if there is one. Meaning perhaps something that links to that > cracklib thing mentioned upthread. > +1 for a sample module that will allow cracklib to drop in. Cheers, Ken
* Albe Laurenz <laurenz.albe@wien.gv.at> [091001 08:54]: > In the discussion following > http://archives.postgresql.org/pgsql-hackers/2009-09/msg01766.php > the consensus was that a hook that allows you to implement > a password checking routine as a module "would not hurt". > > So here's the patch. > I don't think there is documentation required; > correct me if I am wrong. Can we have the check password hook function called in both the plaintext/encrypted case, with a flag as to whether it's encrypted or not? a. -- Aidan Van Dyk Create like a god, aidan@highrise.ca command like a king, http://www.highrise.ca/ work like a slave.
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@pgadmin.org> writes: > How will people know how to use it, or that it's even there without at > least a note in the docs somewhere? We don't normally document function hooks anywhere but the source code. That's one of the things that makes them low overhead ;-) I agree with the subsequent comments suggesting a sample module that actually does something useful --- although if it's going to link to external code like cracklib, it probably is going to have to be on pgfoundry not in contrib. regards, tom lane
On Thu, Oct 1, 2009 at 17:24, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Dave Page <dpage@pgadmin.org> writes: >> How will people know how to use it, or that it's even there without at >> least a note in the docs somewhere? > > We don't normally document function hooks anywhere but the source code. > That's one of the things that makes them low overhead ;-) > > I agree with the subsequent comments suggesting a sample module that > actually does something useful --- although if it's going to link to > external code like cracklib, it probably is going to have to be on > pgfoundry not in contrib. Why is that? we have plenty of other things in contrib that rely on external code, for example the uuid, xml or ssl stuff. -- Magnus HaganderMe: http://www.hagander.net/Work: http://www.redpill-linpro.com/
Magnus Hagander <magnus@hagander.net> writes: > On Thu, Oct 1, 2009 at 17:24, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> I agree with the subsequent comments suggesting a sample module that >> actually does something useful --- although if it's going to link to >> external code like cracklib, it probably is going to have to be on >> pgfoundry not in contrib. > Why is that? we have plenty of other things in contrib that rely on > external code, for example the uuid, xml or ssl stuff. Well, maybe. I was concerned about availability, portability, license compatibility, and so on. The bar's a lot lower for pgfoundry projects on all those points ... regards, tom lane
Magnus Hagander escreveu: > On Thu, Oct 1, 2009 at 17:24, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> Dave Page <dpage@pgadmin.org> writes: >>> How will people know how to use it, or that it's even there without at >>> least a note in the docs somewhere? >> We don't normally document function hooks anywhere but the source code. >> That's one of the things that makes them low overhead ;-) >> >> I agree with the subsequent comments suggesting a sample module that >> actually does something useful --- although if it's going to link to >> external code like cracklib, it probably is going to have to be on >> pgfoundry not in contrib. > > Why is that? we have plenty of other things in contrib that rely on > external code, for example the uuid, xml or ssl stuff. > Because cracklib is GPL'ed. -- Euler Taveira de Oliveira http://www.timbira.com/
On Thu, Oct 1, 2009 at 19:07, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Magnus Hagander <magnus@hagander.net> writes: >> On Thu, Oct 1, 2009 at 17:24, Tom Lane <tgl@sss.pgh.pa.us> wrote: >>> I agree with the subsequent comments suggesting a sample module that >>> actually does something useful --- although if it's going to link to >>> external code like cracklib, it probably is going to have to be on >>> pgfoundry not in contrib. > >> Why is that? we have plenty of other things in contrib that rely on >> external code, for example the uuid, xml or ssl stuff. > > Well, maybe. I was concerned about availability, portability, license > compatibility, and so on. The bar's a lot lower for pgfoundry projects > on all those points ... Fair enough. And on the licensing issue. That said, it would still be good to have something actually *useful* in contrib. A bit more than just comparing userid and password. Perhaps at least being able to set the min length, and the requirement on having >1 "character class"? -- Magnus HaganderMe: 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@hagander.net> writes: > > On Thu, Oct 1, 2009 at 17:24, Tom Lane <tgl@sss.pgh.pa.us> wrote: > >> I agree with the subsequent comments suggesting a sample module that > >> actually does something useful --- although if it's going to link to > >> external code like cracklib, it probably is going to have to be on > >> pgfoundry not in contrib. > > > Why is that? we have plenty of other things in contrib that rely on > > external code, for example the uuid, xml or ssl stuff. > > Well, maybe. I was concerned about availability, portability, license > compatibility, and so on. The bar's a lot lower for pgfoundry projects > on all those points ... > > regards, tom lane > It has been a while since I last used cracklib, but the interface is generic enough that the sample we would ship in contrib could be trivially adapted to use cracklib. The version we include could just have the (username = password) check or something similar. Regards, Ken
Tom Lane wrote: > Magnus Hagander <magnus@hagander.net> writes: > >> On Thu, Oct 1, 2009 at 17:24, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> >>> I agree with the subsequent comments suggesting a sample module that >>> actually does something useful --- although if it's going to link to >>> external code like cracklib, it probably is going to have to be on >>> pgfoundry not in contrib. >>> > > >> Why is that? we have plenty of other things in contrib that rely on >> external code, for example the uuid, xml or ssl stuff. >> > > Well, maybe. I was concerned about availability, portability, license > compatibility, and so on. The bar's a lot lower for pgfoundry projects > on all those points ... > > Yeah. I don't want to add another external dependency if we can avoid it. A module that depends on another library needs configure support etc. Surely we could provide something at least mildly useful without it getting too complex or depending on an external library. Remember, this is an example, not meant to have all the bells and whistles anyone could want. cheers andrew
Magnus Hagander <magnus@hagander.net> writes: > That said, it would still be good to have something actually *useful* > in contrib. A bit more than just comparing userid and password. > Perhaps at least being able to set the min length, and the requirement > on having >1 "character class"? +1. There's still the issue of not being able to do much with a pre-MD5'd password, though. regards, tom lane
Tom Lane wrote: > Magnus Hagander <magnus@hagander.net> writes: > > That said, it would still be good to have something actually *useful* > > in contrib. A bit more than just comparing userid and password. > > Perhaps at least being able to set the min length, and the requirement > > on having >1 "character class"? > > +1. There's still the issue of not being able to do much with a > pre-MD5'd password, though. Agreed. I am still a little worried that people will think they are checking for weak passwords when, for MD5, they are not. I am also worried that people will unknowingly reduce their security (not use MD5) to allow weak password checking. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
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@sss.pgh.pa.us> wrote: >> IOW, having plaintext password in CREATE/ALTER time which can >> then checked for weaknesses is better that MD5 password, which >> actually does not increase security. > > This is not acceptable and will not happen. The case that ENCRYPTED > protects against is database superusers finding out other users' > original passwords, which is a security issue to the extent that those > users have used the same/similar passwords for other systems. > We're not going to give up protection for that in order to provide > an option to do weak-password checking in a place that simply isn't > the best place to do it anyway. This is an area in which we often get beaten up in in technical evaluations against other DBMSs. Password complexity checks are pretty much standard features in other products and it hurts our adoption not to be able to offer them, especially in large shops where the first phase of the eval may be a simple box-checking exercise. I would suggest that in addition to the proposed plugin, we add an suset GUC (defaulting to OFF) which rejects any use of WITH ENCRYPTED PASSWORD to ensure that the password complexity can be checked when roles are created or modified. In the default case, the current behaviour would not change. With the GUC turned on, passwords could be forced through the validation module. To allow dumps to remain secure, the GUC can be turned off by the DBA prior to loading, or in the dump itself. Logging of CREATE/ALTER users statements in this mode could be special-cased to prevent passwords going to the logs/stats (not sure what overhead that might incur though). In addition, the docs for the GUC would obviously point out that it should only be used in conjunction with SSL connections. This would allow us to remain secure-by-default, and yet offer an important option for many potential users. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com
Dave Page <dpage@pgadmin.org> writes: > I would suggest that in addition to the proposed plugin, we add an > suset GUC (defaulting to OFF) which rejects any use of WITH ENCRYPTED > PASSWORD to ensure that the password complexity can be checked when > roles are created or modified. That's going to stop us from being beat up? A GUC that forcibly *weakens* security? I can't see it. If you're really intent on making that happen, you can have your password checker plugin reject crypted passwords; we don't need such a questionable rule in core. regards, tom lane
Tom Lane wrote: > Dave Page <dpage@pgadmin.org> writes: > >> I would suggest that in addition to the proposed plugin, we add an >> suset GUC (defaulting to OFF) which rejects any use of WITH ENCRYPTED >> PASSWORD to ensure that the password complexity can be checked when >> roles are created or modified. >> > > That's going to stop us from being beat up? A GUC that forcibly > *weakens* security? I can't see it. > > If you're really intent on making that happen, you can have your > password checker plugin reject crypted passwords; we don't need > such a questionable rule in core. > > > And you could have the plugin do that depending on a custom GUC. cheers andrew
On Wed, Oct 14, 2009 at 4:11 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Dave Page <dpage@pgadmin.org> writes: >> I would suggest that in addition to the proposed plugin, we add an >> suset GUC (defaulting to OFF) which rejects any use of WITH ENCRYPTED >> PASSWORD to ensure that the password complexity can be checked when >> roles are created or modified. > > That's going to stop us from being beat up? A GUC that forcibly > *weakens* security? I can't see it. If the communications channel uses SSL, and passwords are prevented from hitting the logs then (assuming there are no other weaknesses I haven't thought of), then the net effect would surely be tighter overall security? In a very security-conscious shop, the DBA won't have access to the underlying system at all, so debugging tools etc would be out of the question. In most shops, he will have access and can already just set the auth method to 'password' and then break out the debugger (or even replace the executables), so I can't see that this option would open up any obvious new attack vectors. Users are almost always the biggest weak-point in any security system, so should naturally be the first hole we look at plugging, before the ones that are much harder to exploit effectively - especially when those are only open to exploit by people who already have superuser privileges! > If you're really intent on making that happen, you can have your > password checker plugin reject crypted passwords; we don't need > such a questionable rule in core. Client software would need to have a standard way to know when to use ENCRYPTED PASSWORD or not. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com
On 10/14/09, Dave Page <dpage@pgadmin.org> wrote: > On Mon, Sep 28, 2009 at 7:37 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > >> IOW, having plaintext password in CREATE/ALTER time which can > >> then checked for weaknesses is better that MD5 password, which > >> actually does not increase security. > > > > This is not acceptable and will not happen. The case that ENCRYPTED > > protects against is database superusers finding out other users' > > original passwords, which is a security issue to the extent that those > > users have used the same/similar passwords for other systems. > > We're not going to give up protection for that in order to provide > > an option to do weak-password checking in a place that simply isn't > > the best place to do it anyway. > > > This is an area in which we often get beaten up in in technical > evaluations against other DBMSs. Password complexity checks are pretty > much standard features in other products and it hurts our adoption not > to be able to offer them, especially in large shops where the first > phase of the eval may be a simple box-checking exercise. > > I would suggest that in addition to the proposed plugin, we add an > suset GUC (defaulting to OFF) which rejects any use of WITH ENCRYPTED > PASSWORD to ensure that the password complexity can be checked when > roles are created or modified. Checking 1M common weak combinations against the md5 hashed password will take < 1s. You cannot count the commas in password, but thats it. The usual problems: 'username', '1234' can be still detected. > This would allow us to remain secure-by-default, and yet offer an > important option for many potential users. -- marko
Dave Page <dpage@pgadmin.org> writes: > On Wed, Oct 14, 2009 at 4:11 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> If you're really intent on making that happen, you can have your >> password checker plugin reject crypted passwords; we don't need >> such a questionable rule in core. > Client software would need to have a standard way to know when to use > ENCRYPTED PASSWORD or not. Oh, so you want us to propagate extra support for this blatant security reduction all over the system too? No thank you. This whole line of discussion just proves the point that was made originally: it would be a lot better to do whatever checking you want done on the client side, rather than risk transmitting unencrypted passwords. If you are going to imagine that client-side software knows about such a GUC, you might as well imagine that they have cracklib built in. regards, tom lane
On Wed, Oct 14, 2009 at 4:30 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Dave Page <dpage@pgadmin.org> writes: >> On Wed, Oct 14, 2009 at 4:11 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >>> If you're really intent on making that happen, you can have your >>> password checker plugin reject crypted passwords; we don't need >>> such a questionable rule in core. > >> Client software would need to have a standard way to know when to use >> ENCRYPTED PASSWORD or not. > > Oh, so you want us to propagate extra support for this blatant security > reduction all over the system too? No thank you. You've twice asserted it's a reduction without providing any arguments to back that up. I argue that you *possibly* open a very hard to exploit hole, which is exploitable only by sysadmins/DBAs, in return for which you close a very large hole that allows users to reuse passwords or use common or easy to guess words. If I am incorrect or have missed an important point, please explain why or what. > This whole line of discussion just proves the point that was made > originally: it would be a lot better to do whatever checking you want > done on the client side, rather than risk transmitting unencrypted > passwords. If you are going to imagine that client-side software knows > about such a GUC, you might as well imagine that they have cracklib > built in. Surely you can see that it is *absolutely pointless* to put an password complexity checking in the client? All a user would need to do is grab a copy of psql to bypass it. If they can't do that, there's probably a scripting language or 12 that would make it similarly easy. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com
On Wed, Oct 14, 2009 at 11:44 AM, Dave Page <dpage@pgadmin.org> wrote: > On Wed, Oct 14, 2009 at 4:30 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> Dave Page <dpage@pgadmin.org> writes: >>> On Wed, Oct 14, 2009 at 4:11 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >>>> If you're really intent on making that happen, you can have your >>>> password checker plugin reject crypted passwords; we don't need >>>> such a questionable rule in core. >> >>> Client software would need to have a standard way to know when to use >>> ENCRYPTED PASSWORD or not. >> >> Oh, so you want us to propagate extra support for this blatant security >> reduction all over the system too? No thank you. > > You've twice asserted it's a reduction without providing any arguments > to back that up. I argue that you *possibly* open a very hard to > exploit hole, which is exploitable only by sysadmins/DBAs, in return > for which you close a very large hole that allows users to reuse > passwords or use common or easy to guess words. > > If I am incorrect or have missed an important point, please explain why or what. > >> This whole line of discussion just proves the point that was made >> originally: it would be a lot better to do whatever checking you want >> done on the client side, rather than risk transmitting unencrypted >> passwords. If you are going to imagine that client-side software knows >> about such a GUC, you might as well imagine that they have cracklib >> built in. > > Surely you can see that it is *absolutely pointless* to put an > password complexity checking in the client? All a user would need to > do is grab a copy of psql to bypass it. If they can't do that, there's > probably a scripting language or 12 that would make it similarly easy. To all of the above, +1. ...Robert
Dave Page <dpage@pgadmin.org> writes: > You've twice asserted it's a reduction without providing any arguments > to back that up. You quoted two good arguments why it's insecure in your original message, neither of which your proposed GUC does anything to protect against; and you also admitted that there might be other leakage paths we haven't thought of. That seems to me to be more than sufficient reason to not encourage people to go back to passing unencrypted passwords around. regards, tom lane
On Wed, Oct 14, 2009 at 5:08 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Dave Page <dpage@pgadmin.org> writes: >> You've twice asserted it's a reduction without providing any arguments >> to back that up. > > You quoted two good arguments why it's insecure in your original > message, neither of which your proposed GUC does anything to protect > against; I see one, and I proposed masking passwords in any relevant queries before they were written to the stats or logs to mitigate that. > and you also admitted that there might be other leakage paths > we haven't thought of. That seems to me to be more than sufficient > reason to not encourage people to go back to passing unencrypted > passwords around. Yes. Which is why I asked your opinion as there's a far greater chance you would know of any such paths than I, *and* whether they represent a greater risk than the complete lack of control over the effectiveness of users passwords that we currently have. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com
Dave Page <dpage@pgadmin.org> writes: > On Wed, Oct 14, 2009 at 5:08 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > I see one, and I proposed masking passwords in any relevant queries > before they were written to the stats or logs to mitigate that. Let's see you do that (hint: "CREATD USER ... PASSWORD" is going to throw a syntax error before you realize there's anything there that might need to be protected). And you ignored the question of insecure transmission pathways, anyway. By the time the backend has figured out that it's got a CREATE USER ... PASSWORD command, it's already way too late if the client sent it over a non-SSL connection. Marko has pointed out repeatedly that a plugin can catch the worst cases of insecure passwords even when given a pre-md5'd password. So you can use a plugin that does it that way, or if you want you can use a plugin that throws error on a pre-md5'd password. I do not see a reason for us to add a boatload of questionable logic that favors the latter approach. regards, tom lane
On Wed, Oct 14, 2009 at 5:25 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Dave Page <dpage@pgadmin.org> writes: >> On Wed, Oct 14, 2009 at 5:08 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> I see one, and I proposed masking passwords in any relevant queries >> before they were written to the stats or logs to mitigate that. > > Let's see you do that (hint: "CREATD USER ... PASSWORD" is going to > throw a syntax error before you realize there's anything there that > might need to be protected). > > And you ignored the question of insecure transmission pathways, anyway. > By the time the backend has figured out that it's got a CREATE USER > ... PASSWORD command, it's already way too late if the client sent it > over a non-SSL connection. These are exactly the sort of issues I've been trying to get you to comment on. Thank you. > Marko has pointed out repeatedly that a plugin can catch the worst > cases of insecure passwords even when given a pre-md5'd password. My issues with that approach is that you may have to maintain a huge library of passwords - which you're never going to do efficiently or effectively if you assume that users may use simple variations of their username for example - eg. t0mlane. > So you can use a plugin that does it that way, or if you want you > can use a plugin that throws error on a pre-md5'd password. My only concern with that idea is having some way for the client to know when to not hash the password. Figuring that out from a specific error code and then retrying would be inefficient, ugly, and in the case of pgAdmin, quite hard to do given the way that SQL is generated and then executed. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com
On Wed, Oct 14, 2009 at 18:25, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Dave Page <dpage@pgadmin.org> writes: >> On Wed, Oct 14, 2009 at 5:08 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> I see one, and I proposed masking passwords in any relevant queries >> before they were written to the stats or logs to mitigate that. > > Let's see you do that (hint: "CREATD USER ... PASSWORD" is going to > throw a syntax error before you realize there's anything there that > might need to be protected). I'm unsure if it's our responsibility to think about that. We can leak a *lot* of sensitive information to the logs through syntax errors, this is just one of them. We *do* need to worry about the statements when they are sent properly, of course. So throwing out a wild idea that's probably just wild enough to even consider, but one way to deal with the logging side of things would be to deprecate/remove ALTER USER/CREATE USER with password, and add a separate API call. With a separate wire protocol packet. That would certainly take care of the logging part ;) > And you ignored the question of insecure transmission pathways, anyway. > By the time the backend has figured out that it's got a CREATE USER > ... PASSWORD command, it's already way too late if the client sent it > over a non-SSL connection. Encryption policy of this type cannot be set at the server. Ever. And it shouldn't. If the sysadmin is worried someone might sniff the traffic, he should require SSL, or use a VPN, or similar things. If he's fine with letting the client decide, then it's not a problem, because it's up to the client not to send the password that way. And if the client did, well, the sysadmin just said it was ok to let the client decide, so we're ok. > Marko has pointed out repeatedly that a plugin can catch the worst > cases of insecure passwords even when given a pre-md5'd password. > So you can use a plugin that does it that way, or if you want you > can use a plugin that throws error on a pre-md5'd password. I do > not see a reason for us to add a boatload of questionable logic > that favors the latter approach. it lets you find some passwords, but it does not let you set a *policy*. As is often required by security policies etc. Does anybody know how other databases that *do* implement this solve the problem? -- Magnus HaganderMe: http://www.hagander.net/Work: http://www.redpill-linpro.com/
Dave Page <dpage@pgadmin.org> writes: > On Wed, Oct 14, 2009 at 5:25 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> So you can use a plugin that does it that way, or if you want you >> can use a plugin that throws error on a pre-md5'd password. > My only concern with that idea is having some way for the client to > know when to not hash the password. Figuring that out from a specific > error code and then retrying would be inefficient, ugly, and in the > case of pgAdmin, quite hard to do given the way that SQL is generated > and then executed. I don't actually *want* client software deciding on its own hook to not hash the password. The only reason we have special client-side support for this command at all is to pre-hash the password, and people are accustomed to that behavior. If psql or pgAdmin takes a password and then sends it in the clear without telling me, that's a breach of trust with potentially serious consequences. I might not trust the DBA, for example, or I might be less confident of the network infrastructure than he is. Fix pgAdmin so that this is a user-controlled switch and let the user read the plugin's error message and decide if he wants to retry with a non-encrypted password. regards, tom lane
Magnus Hagander <magnus@hagander.net> writes: > On Wed, Oct 14, 2009 at 18:25, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> Let's see you do that (hint: "CREATD USER ... PASSWORD" is going to >> throw a syntax error before you realize there's anything there that >> might need to be protected). > I'm unsure if it's our responsibility to think about that. We can leak > a *lot* of sensitive information to the logs through syntax errors, > this is just one of them. We *do* need to worry about the statements > when they are sent properly, of course. Even if they're "sent properly", this entire discussion misses the point. The reason to not want cleartext passwords in the logs is that the user doesn't trust the DBA. Why would a user who doesn't trust the DBA want to trust him to not be running a modified copy of the database with all this nice logic disabled? The real point of crypted passwords is to not let uncrypted passwords go anywhere outside the *user's* control. If the DBA wants to enforce a policy that is incompatible with that, it should be extremely obvious to all concerned that that's what he's doing. In particular it should be in the user's face that he's about to send an uncrypted password, so that he can think twice about the particular password he's choosing (and hopefully not use one that's also good for another service). For relatively smart clients like pgAdmin, there might also be an option to refuse to send such a command across an insecure connection, or at least nag the user about it. regards, tom lane
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/policies2) Password history - you can't reuse a password3) Account disable after X incorrectattempts #2 can be done by doing an MD5 on the server of the old password and comparing it to what the client sent --- no need for a client change there. #3 can be implemented in the server too. #1 could be implemented in the server without client change if you are willing to expand any wildcards in the password policy pattern, MD5 those, and check them against what was sent from the client, and this only happens during password change. However, that only can check for simplistic patterns, like trailing digit, etc, but not for things like upper/lower case usage, etc. If you have such a policy, aren't you already using LDAP or PAM, and can't those enforce it? -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
On Wed, Oct 14, 2009 at 12:25 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Dave Page <dpage@pgadmin.org> writes: >> On Wed, Oct 14, 2009 at 5:08 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> I see one, and I proposed masking passwords in any relevant queries >> before they were written to the stats or logs to mitigate that. > > Let's see you do that (hint: "CREATD USER ... PASSWORD" is going to > throw a syntax error before you realize there's anything there that > might need to be protected). It seems to me incredibly rare for anyone to issue a manual CREATE USER command with an encrypted password. And if it is generated by a script, it will presumably not have a trivial typographical error. > And you ignored the question of insecure transmission pathways, anyway. > By the time the backend has figured out that it's got a CREATE USER > ... PASSWORD command, it's already way too late if the client sent it > over a non-SSL connection. Using a non-SSL connection over an untrusted network is incredibly stupid to begin with. I'm not sure we should be basing our design decisions around that scenario. ...Robert
Robert Haas <robertmhaas@gmail.com> writes: > On Wed, Oct 14, 2009 at 12:25 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> Let's see you do that (hint: "CREATD USER ... PASSWORD" is going to >> throw a syntax error before you realize there's anything there that >> might need to be protected). > It seems to me incredibly rare for anyone to issue a manual CREATE > USER command with an encrypted password. And if it is generated by a > script, it will presumably not have a trivial typographical error. Uh, this discussion was about cleartext passwords? regards, tom lane
On Wed, Oct 14, 2009 at 10:28 AM, Bruce Momjian <bruce@momjian.us> wrote: > > I see three checks we are trying to do on passwords: > > 1) Password complexity enforcement/policies > 2) Password history - you can't reuse a password > 3) Account disable after X incorrect attempts This whole discussion seems very strange to me. Surely any organization with rules like this will want them to be system-wide and will have already implemented them in their PAM and LDAP systems (assuming their not using Kerberos or something like that anyways). There's not much point in reinventing the wheel in the database when a) we'll never be remotely as complete as the existing authentication systems -- the above requirements only barely scratch the surface and b) even if we were as complete as existing systems it would never be integrated so there would be nothing stopping people from reusing passwords from their login account or trying passwords a limited number of times against each system to get many attempts in total. Incidentally I'm extremely dubious of systems that implement your goal #3. It seems like more of an obvious DOS attack vector than a security improvement to me. There are better defense mechanisms for such attacks such as preauth. One more argument why we shouldn't be reimplementing the wheel in an area where don't have particularly good experience. -- greg
On Wed, Oct 14, 2009 at 7:42 PM, Greg Stark <gsstark@mit.edu> wrote: > On Wed, Oct 14, 2009 at 10:28 AM, Bruce Momjian <bruce@momjian.us> wrote: >> >> I see three checks we are trying to do on passwords: >> >> 1) Password complexity enforcement/policies >> 2) Password history - you can't reuse a password >> 3) Account disable after X incorrect attempts > > > This whole discussion seems very strange to me. Surely any > organization with rules like this will want them to be system-wide and > will have already implemented them in their PAM and LDAP systems > (assuming their not using Kerberos or something like that anyways). Because like it or not, this 'feature' is one that people *are* looking for in early stages of evaluations, and it counts against us and can hurt our adoption when we can't tick that box. As an example, after years of only offering password policy management via the NT domain/active directory authentication methods, even Microsoft finally gave in and added policy management for their SQL Server accounts with SQL 2k5. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com
On Wed, Oct 14, 2009 at 6:08 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Magnus Hagander <magnus@hagander.net> writes: >> On Wed, Oct 14, 2009 at 18:25, Tom Lane <tgl@sss.pgh.pa.us> wrote: >>> Let's see you do that (hint: "CREATD USER ... PASSWORD" is going to >>> throw a syntax error before you realize there's anything there that >>> might need to be protected). > >> I'm unsure if it's our responsibility to think about that. We can leak >> a *lot* of sensitive information to the logs through syntax errors, >> this is just one of them. We *do* need to worry about the statements >> when they are sent properly, of course. > > Even if they're "sent properly", this entire discussion misses the point. > The reason to not want cleartext passwords in the logs is that the user > doesn't trust the DBA. Why would a user who doesn't trust the DBA > want to trust him to not be running a modified copy of the database with > all this nice logic disabled? If you trust him that little, why would you use a password that you also use elsewhere? Besides, if he can run a modified version of the database, its game over anyway. Just set pg_hba.conf's auth method to password, and you don't even have to wait for the user to change their password - you can grab it the next time he logs in. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com
On Wed, Oct 14, 2009 at 1:48 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Robert Haas <robertmhaas@gmail.com> writes: >> On Wed, Oct 14, 2009 at 12:25 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >>> Let's see you do that (hint: "CREATD USER ... PASSWORD" is going to >>> throw a syntax error before you realize there's anything there that >>> might need to be protected). > >> It seems to me incredibly rare for anyone to issue a manual CREATE >> USER command with an encrypted password. And if it is generated by a >> script, it will presumably not have a trivial typographical error. > > Uh, this discussion was about cleartext passwords? I understand that. The point is, you seemed to be worried that log-obfuscation wouldn't work because someone might type "CREATD USER ... PASSWORD" rather than "CREATE USER ... PASSWORD". But this can happen today, too, can't it? The only difference is that today the password MIGHT be encrypted. But if the user is really entering the command manually, it's probably not. Sure, someone COULD pre-MD5 a string and then copy and paste it into a psql session, but I bet that's not too common. I suspect people using the pre-MD5 option are using a more sophisticated client of some sort anyway. ...Robert
On 10/14/2009 12:25 PM, Tom Lane wrote: > Let's see you do that (hint: "CREATD USER ... PASSWORD" is going to > throw a syntax error before you realize there's anything there that > might need to be protected). > > And you ignored the question of insecure transmission pathways, anyway. > By the time the backend has figured out that it's got a CREATE USER > ... PASSWORD command, it's already way too late if the client sent it > over a non-SSL connection. It seems like the architectural problem here is that people think of SQL as being a valid way to change one's password. I never thought it was valid? What if, like "createdb", or "createuser", there was a "pgpasswd" that did all of the appropriate checks and provided the proper security (MD5 today?) during transit and when storing to a log file? "createuser" already does password prompting - maybe it should allow checking the password from the "createuser" client side? I think if we looked at the SQL commands as being a 'base operation not intended for everyday users', this entire debate would seem frivolous? :-) Does Oracle really do password checks on the base SQL commands used to change an Oracle password? That sounds silly. I'm totally on Tom's side. Having the server check passwords is the wrong solution to the problem. It makes things worse - not better. That they're approving a plugin capability on the server at all is generous, as it does lead to a sense of protection that may not be justifiable. Cheers, mark -- Mark Mielke<mark@mielke.cc>
Dave Page <dpage@pgadmin.org> writes: > On Wed, Oct 14, 2009 at 7:42 PM, Greg Stark <gsstark@mit.edu> wrote: >> This whole discussion seems very strange to me. Surely any >> organization with rules like this will want them to be system-wide and >> will have already implemented them in their PAM and LDAP systems >> (assuming their not using Kerberos or something like that anyways). > Because like it or not, this 'feature' is one that people *are* > looking for in early stages of evaluations, and it counts against us > and can hurt our adoption when we can't tick that box. Okay, fine, so we're not looking for actual high-grade security, we're looking to tick off a checkbox in the minds of not terribly well-informed people. Then the plugin mechanism as currently proposed will do the job just fine. We do not need to put a whole bunch of dubious extra infrastructure in there, and we DEFINITELY do not need anything that can be painted as a backwards step security-wise. regards, tom lane
On Wed, Oct 14, 2009 at 9:00 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Okay, fine, so we're not looking for actual high-grade security, > we're looking to tick off a checkbox in the minds of not terribly > well-informed people. Then the plugin mechanism as currently proposed > will do the job just fine. We do not need to put a whole bunch of > dubious extra infrastructure in there, and we DEFINITELY do not need > anything that can be painted as a backwards step security-wise. Nice exit strategy :-) I said up front this was a box-ticking exercise for these folks, however, rather than just tick the box and move on (meh - who cares if we can store 2009-02-31 - it stores all the valid dates which are the ones that matter :-p ) I prefer to discuss the issue and do the best job we can to make it a practical, usable and useful feature - which is kinda what we usually pride ourselves in doing! -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com
Dave Page <dpage@pgadmin.org> writes: > I said up front this was a box-ticking exercise for these folks, > however, rather than just tick the box and move on (meh - who cares if > we can store 2009-02-31 - it stores all the valid dates which are the > ones that matter :-p ) I prefer to discuss the issue and do the best > job we can to make it a practical, usable and useful feature - which > is kinda what we usually pride ourselves in doing! Well, sure. I just don't want to move backwards on other dimensions in order to move forward on this one. It's fair to argue that support of pre-crypted passwords closes only some holes that can be closed in other ways, but it's equally fair to argue that the limited capability of a plugin that has to check pre-crypted passwords also represents a corner case that can be solved in other ways. regards, tom lane
Dave Page <dpage@pgadmin.org> wrote: > I said up front this was a box-ticking exercise for these folks, Can they check the box if the provided clients include password strength checking? I'm just wondering if we're going at this the hard way, if that really is the main goal. From the point of view of usefulness, wouldn't it be OK if clients enforced the strength (or at least warned of weakness) *and* sent the md5sum? And, perhaps slightly off topic: if the login password is sent over a non-encrypted stream, md5sum or not, can't someone use it to log in if they're generating their own stream to connect? Discussions of which is the more secure way to change passwords seems a little silly if you're only worried about environments where someone can sniff any login sequence and spoof the user anyway. > (meh - who cares if we can store 2009-02-31 - it stores all the > valid dates which are the ones that matter :-p ) Oh, now that's just trolling -- you really don't want to open that can of worms again, do you? :-p -Kevin
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes: > And, perhaps slightly off topic: if the login password is sent over a > non-encrypted stream, md5sum or not, can't someone use it to log in if > they're generating their own stream to connect? Not if they only capture a login exchange --- the password is doubly encrypted during that. If they see the md5'd password in a CREATE USER command, then yeah, they could pass a subsequent md5 challenge, using suitably modified client software that doesn't try to re-encrypt the given password. But the main point is to hide the cleartext password, in any case. regards, tom lane
On Wed, Oct 14, 2009 at 9:50 PM, Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote: > Dave Page <dpage@pgadmin.org> wrote: > >> I said up front this was a box-ticking exercise for these folks, > > Can they check the box if the provided clients include password > strength checking? I'm just wondering if we're going at this the hard > way, if that really is the main goal. No. Any checks at the client are worthless, as they can be bypassed by 10 minutes worth of simple coding in any of a dozen or more languages. > And, perhaps slightly off topic: if the login password is sent over a > non-encrypted stream, md5sum or not, can't someone use it to log in if > they're generating their own stream to connect? Discussions of which > is the more secure way to change passwords seems a little silly if > you're only worried about environments where someone can sniff any > login sequence and spoof the user anyway. No - see Tom's reply. >> (meh - who cares if we can store 2009-02-31 - it stores all the >> valid dates which are the ones that matter :-p ) > > Oh, now that's just trolling -- you really don't want to open that can > of worms again, do you? :-p Well, after 12+ years in these parts I figure anyone should get the privilege of a small dig once in a while :-) -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com
Tom Lane <tgl@sss.pgh.pa.us> wrote: > "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes: >> if the login password is sent over a non-encrypted stream, md5sum >> or not, can't someone use it to log in if they're generating their >> own stream to connect? > If they see the md5'd password in a CREATE USER command, then yeah So anyone in a truly security-conscious environment should only change the password through a trusted connection, whether or not it is md5'd? (Trusted here meaning that the connection has suitable encryption or it is local to the server, which is suitably secured.) > But the main point is to hide the cleartext password, in any case. Sure, sorry to drift off-topic, but the thought kept nagging at me. :-) Anyway, I think it helped crystallize something for me -- the client-side md5 mangling protects against malicious DBAs or database server admins, while the trusted connection protects against network snooping. The former is more about keeping the bad guys, who would have other ways of compromising the database, from gleaning information about your password choices that they may be able to apply to other resources. The latter protects the database itself. Did I miss any other forms of attack either addresses? -Kevin
Dave Page <dpage@pgadmin.org> wrote: > No. Any checks at the client are worthless, as they can be bypassed > by 10 minutes worth of simple coding in any of a dozen or more > languages. Well, sure, but we're talking about a client going out of their way to wrestle the point of the gun toward their own foot, aren't we? If we're worried about the user compromising their own password, we have bigger problems, like that slip of paper in their desk drawer with the password written on it. I mean, I know some of these checklists can be pretty brain-dead (I've been on both sides of the RFP process many times), but it would seem over the top to say that client-side password strength checks aren't OK for the reason you give. -Kevin
On Wed, Oct 14, 2009 at 10:51 PM, Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote: > Dave Page <dpage@pgadmin.org> wrote: > >> No. Any checks at the client are worthless, as they can be bypassed >> by 10 minutes worth of simple coding in any of a dozen or more >> languages. > > Well, sure, but we're talking about a client going out of their way to > wrestle the point of the gun toward their own foot, aren't we? If > we're worried about the user compromising their own password, we have > bigger problems, like that slip of paper in their desk drawer with the > password written on it. I mean, I know some of these checklists can > be pretty brain-dead (I've been on both sides of the RFP process many > times), but it would seem over the top to say that client-side > password strength checks aren't OK for the reason you give. See my previous comment about dates. Check-box items aside, I have absolutely no desire to try to give the illusion of a security feature, when in reality any user could easily bypass it. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com
Tom Lane wrote: > "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes: > > And, perhaps slightly off topic: if the login password is sent over a > > non-encrypted stream, md5sum or not, can't someone use it to log in if > > they're generating their own stream to connect? > > Not if they only capture a login exchange --- the password is doubly > encrypted during that. If they see the md5'd password in a CREATE USER > command, then yeah, they could pass a subsequent md5 challenge, using > suitably modified client software that doesn't try to re-encrypt the > given password. > > But the main point is to hide the cleartext password, in any case. What if we added a GUC that only allowed password changes via an SSL connection. You could say that is a security enhancement, and administrators could set up their systems to use 'password' authentication for SSL and check the password strength on the server because they come in clear-text. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
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.Grittner@wicourts.gov> wrote: > >> Can they check the box if the provided clients include password >> strength checking? I'm just wondering if we're going at this the hard >> way, if that really is the main goal. >> > No. Any checks at the client are worthless, as they can be bypassed by > 10 minutes worth of simple coding in any of a dozen or more languages. > Why care? If the client is purposefully disabling passwords checks to use a "weak" password - this is an entirely different problem from somebody trying a weak password and being allowed. Circumvention of process is always a risk, and should be dealt with as a human resources problem. Why not stop the admin from disabling the security check when they create their pgadmin password too? We can't trust anybody - right? PAM does security checking client-side I think? I'm sure others do too? I'm not saying server checks are worthless - but I think you are exaggerating to say that client checks are worthless. Sending the password in cleartext via SQL seems bad. Sending it encoded seems only marginally better. Sending it in MD5 is good but means that password strength needs to be done by the client. You are saying that it's worth the loss of security in one area, to improve security in another. Providing client checks in the "official" clients is probably sufficient for your checkbox that you think is so important. Unless you think it is impossible to circumvent process in any of these "other" databases that do such a better job? Personally, I don't think PostgreSQL is the best place to manage passwords at this level anyways, beyond the basic usage. PostgreSQL shouldn't need to know the password, and the password should still be required to as strong as the organization requires it. Lots of other solutions here - PAM, LDAP, Kerberos, ... How much of these solutions should PostgreSQL re-implement? Cheers, mark -- Mark Mielke<mark@mielke.cc>
Peter Eisentraut <peter_e@gmx.net> writes: > Well, you would lose anyway if the DBA switches the pg_hba.conf setting > from md5 to password without telling you. True :-(. Anybody for a zero-knowledge protocol? (Realistically, non-password-based auth methods are the only real solution here, I fear. We should probably be doing more to encourage people to use SSL-cert-based authentication in low-trust situations.) regards, tom lane
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@momjian.us> writes: > Tom Lane wrote: >> But the main point is to hide the cleartext password, in any case. > What if we added a GUC that only allowed password changes via an SSL > connection. How's that help? The user has already exposed their new choice of password to any hypothetical eavesdropper. Of course, if they're smart, they'll pick a different password before they try again on a secure connection ... but good luck hoping for that. (And, again, there is ABSOLUTELY NO NEED for us to put such debatable policies into the core. Anyone who thinks that's a good idea can have his password-check plugin enforce it.) regards, tom lane
On 10/14/2009 06:02 PM, Dave Page wrote: > On Wed, Oct 14, 2009 at 10:51 PM, Kevin Grittner > <Kevin.Grittner@wicourts.gov> wrote: > >> Well, sure, but we're talking about a client going out of their way to >> wrestle the point of the gun toward their own foot, aren't we? If >> we're worried about the user compromising their own password, we have >> bigger problems, like that slip of paper in their desk drawer with the >> password written on it. I mean, I know some of these checklists can >> be pretty brain-dead (I've been on both sides of the RFP process many >> times), but it would seem over the top to say that client-side >> password strength checks aren't OK for the reason you give. >> > See my previous comment about dates. Check-box items aside, I have > absolutely no desire to try to give the illusion of a security > feature, when in reality any user could easily bypass it. This is only true if you thing 'create/alter role ... password ...' is intended to be used by hand - and if you think this, are you not concerned that the password shows up on the screen as the user types this? It's not an illusion if the user requires effort to bypass it. Who are you protecting? If you are trying to protect the user from themselves - I'd start by disabling 'create/alter role ... password ...' altogether and requiring some other means of the user securely setting a password. At our company we have a single-sign on system where users must login to a central password server to change their password. The central password server provides the password strength test. The intent is for none of the applications to ever know or see the password. It's not about trust - it is about principle. The DBA and/or application has no need or requirement to know the password. They only need to know that it is valid. For web-based applications, the user authentication is accomplished via a forward to one of the central password authentication servers, which upon successful login, forwards the user back to their intended application with a token that proves they have authenticated. If you think security is such an important thing - why not go all the way? Disable the entry level security provided by PostgreSQL allowing SQL commands to 'set' a password entirely. Use a token-based system with a third party trusted authenticator. I'm not being facetious. I think it's silly to argue that 50% is insufficient, and that lots of effort should be spent on reaching 55%, when you are clearly nowhere near 100% in either case. Cheers, mark -- Mark Mielke<mark@mielke.cc>
On 10/14/2009 06:25 PM, Peter Eisentraut wrote: > On Wed, 2009-10-14 at 18:38 +0200, Magnus Hagander wrote: > >> So throwing out a wild idea that's probably just wild enough to even >> consider, but one way to deal with the logging side of things would be >> to deprecate/remove ALTER USER/CREATE USER with password, and add a >> separate API call. With a separate wire protocol packet. That would >> certainly take care of the logging part ;) >> > I think that would be the correct fix. > Yep. +1. If we are really so paranoid. Cheers, mark -- Mark Mielke<mark@mielke.cc>
* Tom Lane (tgl@sss.pgh.pa.us) wrote: > Peter Eisentraut <peter_e@gmx.net> writes: > > Well, you would lose anyway if the DBA switches the pg_hba.conf setting > > from md5 to password without telling you. > > True :-(. Anybody for a zero-knowledge protocol? > > (Realistically, non-password-based auth methods are the only real > solution here, I fear. We should probably be doing more to encourage > people to use SSL-cert-based authentication in low-trust situations.) Or GSSAPI.. Helping users understand how they can leverage their existing Kerberos or MS SSPI single-sign-on infrastructures to securely access PG would go a long way to reducing the password-based usage out there, imo. Of course, it'd be nice if we supported GSSAPI encrypted transport too. Separating the encryption into SSL is less than ideal. Stephen
On Wed, Oct 14, 2009 at 11:21 PM, Mark Mielke <mark@mark.mielke.cc> wrote: > On 10/14/2009 05:33 PM, Dave Page wrote: >> >> No. Any checks at the client are worthless, as they can be bypassed by >> 10 minutes worth of simple coding in any of a dozen or more languages. >> > > Why care? Because many large (and small for that matter) organisations also have security policies which mandate the enforcement of specific password policies. Just because you think it's worthless to try to prevent someone reusing a password, or using 'password' doesn't mean that everyone else does. Some organisations will use such a feature in a box-ticking exercise when evaluating, and others may actually decide to use the feature, and expect it to work effectively. Beside, we are not in the habit of putting half-arsed features in PostgreSQL. If we do something, we do it properly. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com
On Wed, Oct 14, 2009 at 11:44 PM, Stephen Frost <sfrost@snowman.net> wrote: > * Tom Lane (tgl@sss.pgh.pa.us) wrote: >> Peter Eisentraut <peter_e@gmx.net> writes: >> > Well, you would lose anyway if the DBA switches the pg_hba.conf setting >> > from md5 to password without telling you. >> >> True :-(. Anybody for a zero-knowledge protocol? >> >> (Realistically, non-password-based auth methods are the only real >> solution here, I fear. We should probably be doing more to encourage >> people to use SSL-cert-based authentication in low-trust situations.) > > Or GSSAPI.. Helping users understand how they can leverage their > existing Kerberos or MS SSPI single-sign-on infrastructures to securely > access PG would go a long way to reducing the password-based usage out > there, imo. Of course, it'd be nice if we supported GSSAPI encrypted > transport too. Separating the encryption into SSL is less than ideal. Such solutions are exactly what I'd expect to actually go into production in most places, but that doesn't mean that people don't pay attention to the basic features offered as part of the core database when they're early in the evaluation phase. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com
Dave Page <dpage@pgadmin.org> wrote: > On Wed, Oct 14, 2009 at 10:51 PM, Kevin Grittner >> bigger problems, like that slip of paper in their desk drawer with >> the password written on it. > See my previous comment about dates. Check-box items aside, I have > absolutely no desire to try to give the illusion of a security > feature, when in reality any user could easily bypass it. I think you missed my point -- if you want to try to block the user from compromising their *own* password, you can't. They can tell anybody they want, write it on a slip of paper stuck to their terminal (yes, I've seen that), let it loose any other way they want. Why focus on one (rather unlikely) way that a user could compromise their own password when there are so many other ways, much easier and more likely to actually happen, which are totally out of our control? If a simple client-side strength check would allow the box to be checked, and would protect any user who isn't going out of their way to let their password be abused, I'm not really understanding your objection. Now, if it fails to cover the checkbox because it can't check against the last three passwords used, that's another story, but the server-side plugin can easily cover things like that. And ultimately, if you really care about tight security rather than checking off a box, other posts address how that can actually be done. -Kevin
On Thu, Oct 15, 2009 at 2:49 PM, Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote: > Dave Page <dpage@pgadmin.org> wrote: >> On Wed, Oct 14, 2009 at 10:51 PM, Kevin Grittner > >>> bigger problems, like that slip of paper in their desk drawer with >>> the password written on it. > >> See my previous comment about dates. Check-box items aside, I have >> absolutely no desire to try to give the illusion of a security >> feature, when in reality any user could easily bypass it. > > I think you missed my point -- if you want to try to block the user > from compromising their *own* password, you can't. They can tell > anybody they want, write it on a slip of paper stuck to their terminal > (yes, I've seen that), let it loose any other way they want. Why > focus on one (rather unlikely) way that a user could compromise their > own password when there are so many other ways, much easier and more > likely to actually happen, which are totally out of our control? It's certainly true that there are other ways for users to compromise their passwords if they want. The fact remains though, that most other DBMSs (and all major operating systems I can think of) offer password policy features as non-client checks which are difficult, if not impossible for the user to bypass. Clearly other people think it's important to do this, and we are compared against their products on a daily basis, so if we want to compete with them on a level playing field we need at least a comparable feature set. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com
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@mark.mielke.cc> wrote: > >> On 10/14/2009 05:33 PM, Dave Page wrote: >> >>> No. Any checks at the client are worthless, as they can be bypassed by >>> 10 minutes worth of simple coding in any of a dozen or more languages. >>> >>> >> Why care? >> > Because many large (and small for that matter) organisations also have > security policies which mandate the enforcement of specific password > policies. Just because you think it's worthless to try to prevent > someone reusing a password, or using 'password' doesn't mean that > everyone else does. Some organisations will use such a feature in a > box-ticking exercise when evaluating, and others may actually decide > to use the feature, and expect it to work effectively. > > Beside, we are not in the habit of putting half-arsed features in > PostgreSQL. If we do something, we do it properly. > You miss my point (and conveniently cut it out). For users who accidentally break policy vs users who purposefully circumvent policy - the approaches must be different, and the risk management decision may be different. It's a lot easier to circumvent policy than most people (management specifically) realize. If your attempt it to absolutely prevent a determined competent individual from circumventing your policy - you need to do a LOT MORE than what you are suggesting. If you just want to prevent accidents - having the client software do the checks is fine. Cheers, mark -- Mark Mielke<mark@mielke.cc>
On 10/15/2009 10:08 AM, Dave Page wrote: > It's certainly true that there are other ways for users to compromise > their passwords if they want. The fact remains though, that most other > DBMSs (and all major operating systems I can think of) offer password > policy features as non-client checks which are difficult, if not > impossible for the user to bypass. Clearly other people think it's > important to do this, and we are compared against their products on a > daily basis, so if we want to compete with them on a level playing > field we need at least a comparable feature set. > Not so clear to me. If they're doing strong checks, this means they're sending passwords in the clear or only barely encoded, or using some OTHER method than 'alter role ... password ...' to change the password. Point being - if you think this is absolutely important to do - don't go +5% of the way - go 100% of the way. Then again, I'm not so concerned about what arbitrary criteria some person defines as "what makes a good database system". I'm more concerned with what makes the system better for *me*. I don't see how this entire thread helps *me* in any way - and I do understand the need for strong passwords - and my company *does* have policies that require strong passwords. Even if the plugin is provided - I'm not going to activate it. I already have a policy for setting strong passwords that I already follow. Cheers, mark -- Mark Mielke<mark@mielke.cc>
On 10/15/2009 10:38 AM, Albe Laurenz wrote: > Mark Mielke wrote: > >> Does Oracle really do password checks on the base SQL commands used to >> change an Oracle password? That sounds silly. >> > In Oracle you can write a stored procedure to check passwords; > it is invoked whenever a user is created or altered. > > No matter how you change the password, Oracle can always recover > the plaintext and feed it to the password checking function. > > So, unless you use the "Advanced Security" option (extra $$) that > enables you to encrypt network connections, any eavesdropper > with knowledge of Oracle's (secret) encryption algorithms can get > your new password when you change it. > > And the DBA can get your password with ease. Now I remember. Our "secure password server" used as single-sign on for most applications in the company, which normally avoids any applications ever having to see or authenticate the login, needs to send the passwords in plain or encoded form (not one-way encrypted) to a few "broken" systems, which include systems designed around Oracle user management, to allow people to login to these applications using their corporate wide password. I remember thinking one word when I learned this... "awesome" (sarcastic tone playing in head). Where is the check box that says "prevents password recovery"? For Dave Page: Understand that in a large company, as you are discussing, with policies that require strong passwords, the usual reason for requiring strong passwords is due to concerns over privilege escalation. Access to one system gives you access to others. In the case of single sign-on, which is a requirement for any business with dozens or more applications, where it is unreasonable for each employee to actually memorize dozens of distinct "strong" passwords, access to one system gives you access to all systems. Therefore, "trust the DBA" makes no sense. If the DBA can see my password, then they can login to my employee records and check out what my salary or contact information is, or they can login to one of the secure portals and authorize purchases as me. A *good* system, is not trusted with the password. This is why I say you are focusing on making PostgreSQL what you think is a tiny bit better, but the gain is minor or artificial. If PostgreSQL starts requiring strong passwords - the world is not necessarily a better place in any mind except the person doing the ignorant checkbox evaluation who believes advertising on face value. If you need security - you should know enough to know you need something better than per-application password strength checkers. Cheers, mark -- Mark Mielke<mark@mielke.cc>
On Thu, Oct 15, 2009 at 5:28 PM, Mark Mielke <mark@mark.mielke.cc> wrote: > > Not so clear to me. If they're doing strong checks, this means they're > sending passwords in the clear or only barely encoded, or using some OTHER > method than 'alter role ... password ...' to change the password. Some are sending them in the clear (though often over SSL connections). > Point being - if you think this is absolutely important to do - don't go +5% > of the way - go 100% of the way. Exactly - that's why I want to see a check in the server, not the client which should get to 95%. I also happen to agree with Magnus that the only really secure way to do this on outside of SQL, but I can't see us dropping ALTER USER ... WITH PASSWORD in a hurry. > Then again, I'm not so concerned about what arbitrary criteria some person > defines as "what makes a good database system". I'm more concerned with what > makes the system better for *me*. I don't see how this entire thread helps > *me* in any way - and I do understand the need for strong passwords - and my > company *does* have policies that require strong passwords. Even if the > plugin is provided - I'm not going to activate it. I already have a policy > for setting strong passwords that I already follow. That's an excellent point. It probably doesn't make any difference to you or many of the other people on this list who are concerned with running their own systems and may already use other techniques, such as LDAP, SSPI etc. A not-insignificant percentage of the people here are not concerned with running their own systems though. They are working to help new users adopt PostgreSQL, and make a living selling services or support to those users. Sometimes that can be for huge projects, where it is necessary to justify every difference in check-box items against other products to get past the early eval stages. Like it or not, that is a fact, and this hampers our adoption. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com
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@mark.mielke.cc> wrote: > You miss my point (and conveniently cut it out). For users who accidentally > break policy vs users who purposefully circumvent policy - the approaches > must be different, and the risk management decision may be different. > > It's a lot easier to circumvent policy than most people (management > specifically) realize. If your attempt it to absolutely prevent a determined > competent individual from circumventing your policy - you need to do a LOT > MORE than what you are suggesting. > > If you just want to prevent accidents - having the client software do the > checks is fine. I don't get it. It's easy to circumvent client checks by using a different client. Circumventing server checks is really hard. You have to be able to hack the server. It seems obvious to me that putting the checks on the server is raising the bar by at least one order of magnitude and more likely two or three. Now, it's true that server-side checks on plaintext passwords are a security risk - in paricular, even with SSL, they can be captured by a modified server. So from the point of view of the *employee*, sending plaintext passwords may be less secure, because they don't know where their password is going. But from the point of view of the *server administrator*, they are more secure, because the server administrator believes (likely entirely correctly) that the odds of an employee picking a bad password (perhaps by firing up psql, which is not exactly a difficult-to-obtain utility) are higher than the odds that someone will trojan his server and install a password capture tool. If we were using some kind of real public key system and someone suggested breaking it to add password complexity checking, I would understand the outrage here. But I don't understand why everyone is so worked up about having an *optional* *flag* to force plaintext instead of MD5. I might be wrong here, but can't a determined attacker brute-force an MD5 anyway? The very fact that people are suggesting that password checking might be feasible even on a pre-MD5'd password by using a dictionary suggests that we're not getting a whole lot of real security here. And even if not, dude, it's an *optional* *flag*. Document that using it has certain advantages and certain disadvantages, and let users make their own decision about whether to deploy it. If they make a bad decision and get hacked as a result, tell them it's their own darn fault for setting the flag. I reiterate my previous opposition to the idea that "I wouldn't use that feature" is a reason not to add it. ...Robert
Robert Haas <robertmhaas@gmail.com> writes: > If we were using some kind of real public key system and someone > suggested breaking it to add password complexity checking, I would > understand the outrage here. But I don't understand why everyone is > so worked up about having an *optional* *flag* to force plaintext > instead of MD5. I might be wrong here, but can't a determined > attacker brute-force an MD5 anyway? The very fact that people are > suggesting that password checking might be feasible even on a > pre-MD5'd password by using a dictionary suggests that we're not > getting a whole lot of real security here. And even if not, dude, > it's an *optional* *flag*. Yes, and it's an optional flag that could perfectly well be implemented in the plugin that I think we do have consensus to add a hook for. The argument is over why do we need to litter the core system with it. regards, tom lane
On Thu, Oct 15, 2009 at 6:17 PM, Josh Berkus <josh@agliodbs.com> wrote: > Enabling the inclusion of a password checker in the client *would* > improve things by preventing stupid users from setting their password > the same as their username, or to a 3-letter word, or anything equally > stupid which can be checked in a contextless way. This would be an > real, incremental improvement *without* breaking anything else. And > presumably would help our checkboxyness. What client? For the vast majority of users, what you're proposing is 'do it yourself'. Most people don't use pgAdmin or psql. > But I've seen nothing in Dave's other proposals which would *actually* Actually, I made just one proposal, to complement a patch that has already been submitted. > improve password security as opposed to doing exactly the opposite > Requiring passwords to be sent unhashed over the wire so that they can > be checked on the server is like making sure that your front door is > always locked by giving keys to everyone you meet. As Peter pointed out, it's already game-over if you're worried about the DBA. For other cases, I was clear that SSL should be expected. > In fact, given Dave's pursuit of a specific set of requirements, I think My only request was for server-side password policy enforcement, and a way to ensure users/DBAs could use pgAdmin to manage those passwords. > he has *one* specific client in mind rather than a generalized > requirement. For my part, I've not in 10 years had anyone ask me for > password checking in Postgres as an evaluation criteron. Encrypted > data, yes. I don't deal with prospective clients, which is where this comes from. I do deal with a team of (pre)sales engineers who complain about this, and maybe half-a-dozen other issues on a very regular basis. They tell me that PostgreSQL loses out in early stages of tech evals because of this issue, and I have no reason to disbelieve them. Sure it's almost certainly not the only reason, but they add up. And yes, data encryption is one of the other checkbox items that they bleat about. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com
On Thu, Oct 15, 2009 at 6:43 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Robert Haas <robertmhaas@gmail.com> writes: >> If we were using some kind of real public key system and someone >> suggested breaking it to add password complexity checking, I would >> understand the outrage here. But I don't understand why everyone is >> so worked up about having an *optional* *flag* to force plaintext >> instead of MD5. I might be wrong here, but can't a determined >> attacker brute-force an MD5 anyway? The very fact that people are >> suggesting that password checking might be feasible even on a >> pre-MD5'd password by using a dictionary suggests that we're not >> getting a whole lot of real security here. And even if not, dude, >> it's an *optional* *flag*. > > Yes, and it's an optional flag that could perfectly well be implemented > in the plugin that I think we do have consensus to add a hook for. > The argument is over why do we need to litter the core system with it. I already said that would suit me. The only other requirement I would have is a way for pgAdmin or other clients to figure out if that flag was set so they could construct queries appropriately (and yes, that could include refusing to send plain text passwords over non-SSL connections). -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com
On Thu, Oct 15, 2009 at 1:47 PM, Dave Page <dpage@pgadmin.org> wrote: > On Thu, Oct 15, 2009 at 6:43 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> Robert Haas <robertmhaas@gmail.com> writes: >>> If we were using some kind of real public key system and someone >>> suggested breaking it to add password complexity checking, I would >>> understand the outrage here. But I don't understand why everyone is >>> so worked up about having an *optional* *flag* to force plaintext >>> instead of MD5. I might be wrong here, but can't a determined >>> attacker brute-force an MD5 anyway? The very fact that people are >>> suggesting that password checking might be feasible even on a >>> pre-MD5'd password by using a dictionary suggests that we're not >>> getting a whole lot of real security here. And even if not, dude, >>> it's an *optional* *flag*. >> >> Yes, and it's an optional flag that could perfectly well be implemented >> in the plugin that I think we do have consensus to add a hook for. >> The argument is over why do we need to litter the core system with it. > > I already said that would suit me. The only other requirement I would > have is a way for pgAdmin or other clients to figure out if that flag > was set so they could construct queries appropriately (and yes, that > could include refusing to send plain text passwords over non-SSL > connections). OK, so we're in violent agreement here? Except for figuring out how an API for checking the flag? Could they just try it with MD5 first and then fall back if that say "no MD5"? ...Robert
On Thu, Oct 15, 2009 at 6:55 PM, Robert Haas <robertmhaas@gmail.com> wrote: > OK, so we're in violent agreement here? From a technical perspective I think we have been for a while. Though clearly some people disagree with my assertion that putting any form of policy enforcement in the client is not actually 'enforcement'. I wonder how many of those folks would implement their website's data sanitisation in the browser only - but I digress... :-) > Except for figuring out how > an API for checking the flag? Could they just try it with MD5 first > and then fall back if that say "no MD5"? That's what I was trying to avoid, as the architecture of pgAdmin makes that really hard. I know that's not PG's problem, but forcing a retry is quite an ugly solution anyway, so I was hoping we could come up with something better. I suppose in the worst case, I could just have pgAdmin throw the error, and then add a per-server option to disable password hashing in the relevant places, but I'd far rather have that automated so it can't be set unnecessarily. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com
On 10/15/2009 01:44 PM, Dave Page wrote: > I don't deal with prospective clients, which is where this comes from. > I do deal with a team of (pre)sales engineers who complain about this, > and maybe half-a-dozen other issues on a very regular basis. They tell > me that PostgreSQL loses out in early stages of tech evals because of > this issue, and I have no reason to disbelieve them. Sure it's almost > certainly not the only reason, but they add up. > A lot of evaluations are designed to fit exactly one product, and it's impossible to win here. In my own company, I recently saw the most ridiculous (to me) evaluations over a suite of products, that effectively listed an exact implementation as requirements. This resulted in a huge split between people who considered the evaluation fair and who went with their choice for exactly that one product, and the rest of the people who called the evaluation a sham and refused to participate, choosing to instead use their own choice of products not caring about the outcome of the evaluation. The evaluation, by the way, included other "silly" statements, like how a database instance costs $48k in license fees, even though everybody knew we were already using PostgreSQL for $0k or even if we chose to be supported by one of the many PostgreSQL support companies, it would not cost $48k. Where did they get that number? Because they presumed they would go with Oracle. The evaluation was a sham from start to finish. Perhaps you can see how little I value some arbitrary checkbox list on some "evaluation"? If people want to count PostgreSQL off the list from the start - they will, and there is not much you or I can do about it. Bowing to the pressure of fulfilling these checkboxes, when they'll just change them next time to something else that PostgreSQL doesn't quite do, is a waste of time. We should do what is right to do. We should not be focusing on checkboxes raised by other people who are not competent enough to understand the subject matter or who have already made their choice, and the evaluation is just a rubber stamp to pretend they have done due diligence about justifying their choice compared to alternatives. Cheers, mark -- Mark Mielke<mark@mielke.cc>
Dave Page <dpage@pgadmin.org> writes: > On Thu, Oct 15, 2009 at 6:43 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> Yes, and it's an optional flag that could perfectly well be implemented >> in the plugin that I think we do have consensus to add a hook for. >> The argument is over why do we need to litter the core system with it. > I already said that would suit me. The only other requirement I would > have is a way for pgAdmin or other clients to figure out if that flag > was set so they could construct queries appropriately (and yes, that > could include refusing to send plain text passwords over non-SSL > connections). Well, if it's a GUC implemented by a plugin, it's still a GUC. All you need is some side agreement between pgAdmin and potential GUC authors about what the GUC will be called. regards, tom lane
Dave Page <dpage@pgadmin.org> writes: > I suppose in the worst case, I could just have pgAdmin throw the > error, and then add a per-server option to disable password hashing in > the relevant places, but I'd far rather have that automated so it > can't be set unnecessarily. As I commented before, I think that automating this is actually a dangerous misfeature. regards, tom lane
On 10/15/2009 02:02 PM, Dave Page wrote: > On Thu, Oct 15, 2009 at 6:55 PM, Robert Haas<robertmhaas@gmail.com> wrote: > > >> OK, so we're in violent agreement here? >> > From a technical perspective I think we have been for a while. Though > clearly some people disagree with my assertion that putting any form > of policy enforcement in the client is not actually 'enforcement'. I > wonder how many of those folks would implement their website's data > sanitisation in the browser only - but I digress... :-) > It depends on what your goal is. If your goal is to treat users as monkeys that you do not trust, even with their own password, and the DBA as God, who you absolutely do trust, than you are correct. I don't know about your company - but in my company, the DBAs are in the IT department, and they really have no business knowing my password, which would give them access to my employee records, and my authorization capabilities. For any company that requires security, I do not accept that we can "trust the DBA". The database is just one small component in a much larger solution. The DBA is the monkey for a minor backend application, and the designers are the people earning money for the corporation. We have the exact opposite of what you are suggesting. A person can get access to much more data by logging in as the user on their *desktop* than by accessing some database directly. I think you are missing that security is a balance. Your dig at ignorant people who do JS-based browser side checks of input is not applicable. You are exchanging one type of security for another type of security. You think that your proposed type of security is more valid than my proposed type of security. It depends on the application. Sometimes you might be right. Other times, you have arguably made things worse. Any company that truly needs security of this sort - should not be using PostgreSQL based roles with passwords for authentication. The true value of your proposal is pretty limited. I'm not saying don't do it. I am saying that you are not truly achieving any improvement in security for the target audience you are saying that you are representing. I think your proposal might improve things for newbies running PostgreSQL on an open Internet port at home who pick username = password. Frankly, I don't think their data is worth protecting, and their choice to use username = password and make it accessible on an open Internet port confirms that they are either completely ignorant about security, or they also agree that their data is not worth protecting. Cheers, mark -- Mark Mielke<mark@mielke.cc>
On Thu, Oct 15, 2009 at 7:22 PM, Mark Mielke <mark@mark.mielke.cc> wrote: > It depends on what your goal is. If your goal is to treat users as monkeys > that you do not trust, even with their own password, and the DBA as God, who > you absolutely do trust, than you are correct. > > I don't know about your company - but in my company, the DBAs are in the IT > department, and they really have no business knowing my password, which > would give them access to my employee records, and my authorization > capabilities. For any company that requires security, I do not accept that > we can "trust the DBA". The database is just one small component in a much > larger solution. The DBA is the monkey for a minor backend application, and > the designers are the people earning money for the corporation. We have the > exact opposite of what you are suggesting. A person can get access to much > more data by logging in as the user on their *desktop* than by accessing > some database directly. You have no choice but to trust the DBA (or sysadmin) if you use PostgreSQL's built in authentication. Just set a pg_hba.conf line to 'password' and start harvesting passwords. > I think you are missing that security is a balance. Your dig at ignorant > people who do JS-based browser side checks of input is not applicable. You > are exchanging one type of security for another type of security. You think > that your proposed type of security is more valid than my proposed type of > security. It depends on the application. Sometimes you might be right. Other > times, you have arguably made things worse. Any company that truly needs > security of this sort - should not be using PostgreSQL based roles with > passwords for authentication. The true value of your proposal is pretty > limited. I never said it wasn't - in fact I said from the outset it was about box-checking, and that anyone doing things properly will use LDAP/SSPI/Kerberos etc. What I did say, was that my proposed GUC doesn't allow the DBA to do anything that he cannot already do in a much easier way, per the comment above. Anyway, as noted in the message you quoted, the current proposal will allow my colleagues to check boxes, and will be implemented in a sensible way on the server side. And it's entirely confined to a plugin, so if you trust all your users, there's no need for you to load it at all. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com
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@cheapcomplexdevices.com> wrote: > Dave Page wrote: >> I never said it wasn't - in fact I said from the outset it was about >> box-checking, and that anyone doing things properly will use >> LDAP/SSPI/Kerberos etc. > > I don't understand why the box-checkers can't already check that > box; with the explanation stating "Yes - by using LDAP or GSSAPI > or PAM configured accordingly". > > Or do checkbox-lists specifically say > "can postgres do XYZ with all OS security features disabled". Well I guess that's possible, but what I hear our SEs complaining about is having to justify features that require additional software or features that other DBMSs offer natively. Let me offer a couple of examples: Q. Does the product offer password policy enforcement? Oracle: Yes SQL Server: Yes PostgreSQL: Yes (using an external authentication provider with policy enforcement). Q. Does the product support external authentication providers? Oracle: Yes SQL Server: Yes PostgreSQL: Yes Q. Does the product offer transparent data encryption? Oracle: Yes SQL Server: Yes PostgreSQL: Yes (using an encrypted filesystem). Q. Does the product offer audit capabilities? Oracle: Yes SQL Server: Yes PostgreSQL: Yes (must be manually implemented using triggers and functions) Too many of those caveats, and it's easy to see how we can be discounted early in the evaluation phase. It's not helped that often these lists will be drawn up by people used to working with the commercial DBMSs, so we probably wouldn't get extra points for having a dozen procedural languages, or other features that are largely unique to PostgreSQL, no matter how cool and useful they are. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com
Tom Lane wrote: > Robert Haas <robertmhaas@gmail.com> writes: > > If we were using some kind of real public key system and someone > > suggested breaking it to add password complexity checking, I would > > understand the outrage here. But I don't understand why everyone is > > so worked up about having an *optional* *flag* to force plaintext > > instead of MD5. I might be wrong here, but can't a determined > > attacker brute-force an MD5 anyway? The very fact that people are > > suggesting that password checking might be feasible even on a > > pre-MD5'd password by using a dictionary suggests that we're not > > getting a whole lot of real security here. And even if not, dude, > > it's an *optional* *flag*. > > Yes, and it's an optional flag that could perfectly well be implemented > in the plugin that I think we do have consensus to add a hook for. > The argument is over why do we need to litter the core system with it. So, are we agreed to provide a hook on the server side, but to use it you have to configure your system with SSL and 'password'? I can add that to the TODO list. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Dave Page wrote: > Too many of those caveats, and it's easy to see how we can be > discounted early in the evaluation phase. It's not helped that often > these lists will be drawn up by people used to working with the > commercial DBMSs, so we probably wouldn't get extra points for having > a dozen procedural languages, or other features that are largely > unique to PostgreSQL, no matter how cool and useful they are. Yep, this is illustrating something that is pretty basic to open source --- that is open source often provides the tools for a solution, rather than a complete solution. I often think of open source as providing a calculator with wires sticking out, rather than calculator buttons; the wires allow more flexibility, but they are harder to use. Personally I think the calculator/wires approach is better from an engineering perspective, but it can be a handicap in the user experience and checkbox categories --- ease of use is perhaps not our strong point. Much of our open source value is being different, in both cost, reliability, and configurability. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Bruce Momjian <bruce@momjian.us> writes: > So, are we agreed to provide a hook on the server side, but to use it > you have to configure your system with SSL and 'password'? I can add > that to the TODO list. I think we're agreed to provide the hook. What restrictions the hook might enforce are not our concern. regards, tom lane
On 10/16/2009 11:28 AM, Bruce Momjian wrote: > Dave Page wrote: > >> Too many of those caveats, and it's easy to see how we can be >> discounted early in the evaluation phase. It's not helped that often >> these lists will be drawn up by people used to working with the >> commercial DBMSs, so we probably wouldn't get extra points for having >> a dozen procedural languages, or other features that are largely >> unique to PostgreSQL, no matter how cool and useful they are. >> > Yep, this is illustrating something that is pretty basic to open source > --- that is open source often provides the tools for a solution, rather > than a complete solution. I often think of open source as providing a > calculator with wires sticking out, rather than calculator buttons; the > wires allow more flexibility, but they are harder to use. > Although often true - I think this is selling PostgreSQL a little short. It is a self-contained solution for what it does best, and for those that need more - there are better frameworks designed to be integrated that PostgreSQL is able to integrate with. PostgreSQL isn't a calculator with wires - if anything, I think PostgreSQL is an easy-to-use full functioned calculator whereas Oracle might be some advanced HP calculator that requires special training to learn how to use right... :-) > Personally I think the calculator/wires approach is better from an > engineering perspective, but it can be a handicap in the user experience > and checkbox categories --- ease of use is perhaps not our strong point. > Much of our open source value is being different, in both cost, > reliability, and configurability. I found this true of a lot of tools. I still remember when the mutt developers argued against putting IMAP in their solution because they thought there might be a better "IMAP component" client out there. Eventually, such arguments are dropped, as the practical sense on the matter says that tight integration is a requirement. I don't see how PostgreSQL has really failed in this regard. Maybe Oracle comes out-of-box with more features - but this doesn't make it necessarily a more "complete" solution - it just means it has more bells and whistles. A bicycle doesn't need a ticking card mounted through the spokes for it to be considered a "complete solution". :-) Somebody might one day want that "feature" - but it's extra - it's not core. Cheers, mark -- Mark Mielke<mark@mielke.cc>
On Fri, Oct 16, 2009 at 4:28 PM, Bruce Momjian <bruce@momjian.us> wrote: > Personally I think the calculator/wires approach is better from an > engineering perspective, but it can be a handicap in the user experience > and checkbox categories --- ease of use is perhaps not our strong point. > Much of our open source value is being different, in both cost, > reliability, and configurability. I don't really see this as an either-or proposition. The calculator with buttons still has wires inside, someone had to hack on the wiring to get the functionality working. Someone else with an inclination toward marketing put shiny buttons on it. I'm pretty happy to be in the first group and since Postgres has a BSD license people in the second group are free to work on shiny buttons. The discussion at hand is an unusual situation where the priorities conflict because supporting a particular shiny button requires ripping out some useful wiring. In the long run I think having the wiring right is more valuable than some shiny buttons today. -- greg
Tom Lane wrote: > Bruce Momjian <bruce@momjian.us> writes: > > So, are we agreed to provide a hook on the server side, but to use it > > you have to configure your system with SSL and 'password'? I can add > > that to the TODO list. > > I think we're agreed to provide the hook. What restrictions the hook > might enforce are not our concern. Great, added to TODO: Allow server-side enforcement of password policies Password checks might include password complexity or non-reuse ofpasswords.This facility will require the client to send the password tothe server in plain-text, so SSL and 'password'authentication isnecessary to use this features. * http://archives.postgresql.org/pgsql-hackers/2009-09/msg01766.php * http://archives.postgresql.org/pgsql-hackers/2009-10/msg00025.php -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Mark Mielke wrote: > > Personally I think the calculator/wires approach is better from an > > engineering perspective, but it can be a handicap in the user experience > > and checkbox categories --- ease of use is perhaps not our strong point. > > Much of our open source value is being different, in both cost, > > reliability, and configurability. > > I found this true of a lot of tools. I still remember when the mutt > developers argued against putting IMAP in their solution because they > thought there might be a better "IMAP component" client out there. > Eventually, such arguments are dropped, as the practical sense on the > matter says that tight integration is a requirement. > > I don't see how PostgreSQL has really failed in this regard. Maybe > Oracle comes out-of-box with more features - but this doesn't make it > necessarily a more "complete" solution - it just means it has more bells > and whistles. A bicycle doesn't need a ticking card mounted through the > spokes for it to be considered a "complete solution". :-) Somebody might > one day want that "feature" - but it's extra - it's not core. Agreed. Many commercial database solutions start to look like Frankenstein with all the bolted-on components. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Bruce Momjian wrote: > Yep, this is illustrating something that is pretty basic to open source > --- that is open source often provides the tools for a solution, rather > than a complete solution. I often think of open source as providing a > calculator with wires sticking out, rather than calculator buttons; the > wires allow more flexibility, but they are harder to use. I disagree. Open source typically provides the complete solution too - just not from the developer who programs one component of the solution. Checklist writers intentionally use this to make straw-man arguments. People used to say "linux doesn't even have a GUI" - noting that X11 is a separate project. Now people have database checkboxes for: * a GUI admin tool (which we have, though it's a separate package)* GIS data types (which we have, though it's a separatepackage)* server-side password filters (which we have, though LDAP, etc)* replication (which we have, though manypackages)* clustering (which we have, though hadoopdb) The Linux guys successfully communicated that it isn't fair for checklists to compare an OS kernel against commercial application suites. Seems it'd be good for the postgres project to similarly communicate that the database kernel is the core of a platform that's broader than just a database kernel. Ron
Bruce Momjian wrote: > Great, added to TODO: > > Allow server-side enforcement of password policies > > Password checks might include password complexity or non-reuse of > passwords. This facility will require the client to send the password to > the server in plain-text, so SSL and 'password' authentication is > necessary to use this features. I don't get why you need 'password' authentication for that. The point where the password should be checked is not when the user uses it to logon, but when he or she changes it. So in my opinion that should be: This facility will require to send new and changed password to the server in plain-text, so it will require SSL, and the use of encrypted passwords in CREATE/ALTER ROLE will have to be disabled. Yours, Laurenz Albe
On Thu, 2009-10-15 at 13:19 -0400, Robert Haas wrote: > But I don't understand why everyone is > so worked up about having an *optional* *flag* to force plaintext > instead of MD5. It would be pretty bad usability. Users would be faced with the choice: you can have secure authentication or good passwords, but not both. (For some values of "secure" and "good".) I think most people would want both.
On Mon, 2009-10-19 at 09:14 +0200, Albe Laurenz wrote: > Bruce Momjian wrote: > > Great, added to TODO: > > > > Allow server-side enforcement of password policies > > > > Password checks might include password complexity or non-reuse of > > passwords. This facility will require the client to send the password to > > the server in plain-text, so SSL and 'password' authentication is > > necessary to use this features. > > I don't get why you need 'password' authentication for that. > The point where the password should be checked is not when > the user uses it to logon, but when he or she changes it. > > So in my opinion that should be: > This facility will require to send new and changed password to > the server in plain-text, so it will require SSL, and the use > of encrypted passwords in CREATE/ALTER ROLE will have to be > disabled. Note that this solution will still not satisfy the original checkbox requirement.
Peter Eisentraut wrote: >> I don't get why you need 'password' authentication for that. >> The point where the password should be checked is not when >> the user uses it to logon, but when he or she changes it. >> >> So in my opinion that should be: >> This facility will require to send new and changed password to >> the server in plain-text, so it will require SSL, and the use >> of encrypted passwords in CREATE/ALTER ROLE will have to be >> disabled. > > Note that this solution will still not satisfy the original checkbox > requirement. I guess I misunderstood something there, but I had assumed that the checkbox item read something like: "Does the product offer password policy enforcement?" (to quote Dave Page). I understood that to mean "does the server check if a new password complies with a certain set of rules". Yours, Laurenz Albe
On Mon, Oct 19, 2009 at 7:34 AM, Peter Eisentraut <peter_e@gmx.net> wrote: > On Thu, 2009-10-15 at 13:19 -0400, Robert Haas wrote: >> But I don't understand why everyone is >> so worked up about having an *optional* *flag* to force plaintext >> instead of MD5. > > It would be pretty bad usability. Users would be faced with the choice: > you can have secure authentication or good passwords, but not both. > (For some values of "secure" and "good".) I think most people would > want both. Unless you have the ability to entirely control the software that users use to access PostgreSQL, which is probably only true in super-high-security environments and is certainly false anywhere I've ever worked, you can only have one of those things. SSH keys or SSL certificates are great for defeating network attacks, but I know a lot of people who keep SSL certificates unencrypted on their laptops because there's no easy way to stop them. Those very same people can EASILY be forced to pick relatively good Windows logon passwords because AD can enforce password complexity requirements. Of course, they can't be forced not to write their Windows logon password on a napkin, but they also can't be forced not to run an unsecured FTP server on their laptop that provides access to their unencrypted SSH keys/SSL certificates. Now, we can argue all day about probabilities, but I don't see any reason to believe that we know for sure what the best trade-off is in every environment, which is why I favor providing options, documenting the trade-offs, and letting users make the final decision. ...Robert
"Albe Laurenz" <laurenz.albe@wien.gv.at> writes: > Bruce Momjian wrote: >> Password checks might include password complexity or non-reuse of >> passwords. This facility will require the client to send the password to >> the server in plain-text, so SSL and 'password' authentication is >> necessary to use this features. > So in my opinion that should be: > This facility will require to send new and changed password to > the server in plain-text, so it will require SSL, and the use > of encrypted passwords in CREATE/ALTER ROLE will have to be > disabled. Actually, not one word of *either* version should be in TODO. All of that is speculation about policies that a particular add-on module might or might not choose to enforce. regards, tom lane
On Mon, 2009-10-19 at 14:54 +0200, Albe Laurenz wrote: > Peter Eisentraut wrote: > > Note that this solution will still not satisfy the original checkbox > > requirement. > > I guess I misunderstood something there, but I had assumed that the > checkbox item read something like: "Does the product offer password > policy enforcement?" (to quote Dave Page). The answer to that is currently "Yes, with external tools". Using the plugin approach, the answer will remain "Yes, with external tools". So we wouldn't gain much.
Peter Eisentraut <peter_e@gmx.net> writes: > On Mon, 2009-10-19 at 14:54 +0200, Albe Laurenz wrote: >> I guess I misunderstood something there, but I had assumed that the >> checkbox item read something like: "Does the product offer password >> policy enforcement?" (to quote Dave Page). > The answer to that is currently "Yes, with external tools". Using the > plugin approach, the answer will remain "Yes, with external tools". So > we wouldn't gain much. Except that your first statement is false. It is not possible currently for any tool to prevent someone from doing ALTER USER joe PASSWORD joe. A server-side plugin can provide a guarantee that there are no bad passwords (for some value of bad, and with some possible adverse consequences). We don't have that today. regards, tom lane
Tom Lane escribió: > Peter Eisentraut <peter_e@gmx.net> writes: > > On Mon, 2009-10-19 at 14:54 +0200, Albe Laurenz wrote: > >> I guess I misunderstood something there, but I had assumed that the > >> checkbox item read something like: "Does the product offer password > >> policy enforcement?" (to quote Dave Page). > > > The answer to that is currently "Yes, with external tools". Using the > > plugin approach, the answer will remain "Yes, with external tools". So > > we wouldn't gain much. > > Except that your first statement is false. It is not possible currently > for any tool to prevent someone from doing ALTER USER joe PASSWORD joe. > A server-side plugin can provide a guarantee that there are no bad > passwords (for some value of bad, and with some possible adverse > consequences). We don't have that today. We do, if you have you server grabbing passwords from LDAP or whatever external auth service you use. That would be more secure than anything mentioned in this thread, because the password enforcement could work on unencrypted passwords without adverse consequences. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
Alvaro Herrera wrote: >> Except that your first statement is false. It is not possible currently >> for any tool to prevent someone from doing ALTER USER joe PASSWORD joe. >> A server-side plugin can provide a guarantee that there are no bad >> passwords (for some value of bad, and with some possible adverse >> consequences). We don't have that today. >> > > We do, if you have you server grabbing passwords from LDAP or whatever > external auth service you use. That would be more secure than anything > mentioned in this thread, because the password enforcement could work on > unencrypted passwords without adverse consequences. > We don't have it today for passwords that postgres manages. Unless we're going to rely on an external auth source completely, I think there's a good case for the hooks, but not for any of the other "adjustments" that people have suggested. cheers andrew
I wrote: > A server-side plugin can provide a guarantee that there are no bad > passwords (for some value of bad, and with some possible adverse > consequences). We don't have that today. BTW, it strikes me that ALTER USER RENAME introduces an interesting hazard for such a plugin. Consider CREATE USER joe; ALTER USER joe PASSWORD joe; -- presumably, plugin will reject this ALTER USER joe PASSWORD mumblefrotz; -- assume this is considered OK ALTER USER joe RENAME TO mumblefrotz; Now we have a user with name equal to password, which no sane security policy will think is a good thing, but the plugin had no chance to prevent it. In the case where the password is stored MD5-crypted, we clear it on RENAME because of the fact that the username is part of the hash. (We had always thought that was a bug^Wimplementation restriction, but now it looks like a feature.) So in normal practice the above hazard doesn't exist; but it would for cleartext passwords. One thing we could do is *always* clear the password on RENAME. Another is to keep the cleartext password, but pass the new name and password through the plugin before allowing the RENAME to succeed. Since the PW is cleartext, presumably the plugin won't have any problem checking it. The latter however seems like we are getting a security-critical behavior out of a chance combination of implementation artifacts, which doesn't make me feel comfortable. Thoughts? regards, tom lane
Andrew Dunstan <andrew@dunslane.net> writes: > Alvaro Herrera wrote: >> We do, if you have you server grabbing passwords from LDAP or whatever >> external auth service you use. That would be more secure than anything >> mentioned in this thread, because the password enforcement could work on >> unencrypted passwords without adverse consequences. > We don't have it today for passwords that postgres manages. Unless we're > going to rely on an external auth source completely, I think there's a > good case for the hooks, but not for any of the other "adjustments" that > people have suggested. Yeah. Installing LDAP or Kerberos or whatever is sensible if you have a need for a central auth server anyway. If you are just trying to run a database, it's a major additional investment of effort, and I can't quibble at all with people who think that it's unreasonable to have to do that just to have some modicum of a password policy. I also am of the opinion that it's reasonable to provide a hook or two for this purpose, but not to go further than that. regards, tom lane
Tom Lane wrote: > "Albe Laurenz" <laurenz.albe@wien.gv.at> writes: > > Bruce Momjian wrote: > >> Password checks might include password complexity or non-reuse of > >> passwords. This facility will require the client to send the password to > >> the server in plain-text, so SSL and 'password' authentication is > >> necessary to use this features. > > > So in my opinion that should be: > > This facility will require to send new and changed password to > > the server in plain-text, so it will require SSL, and the use > > of encrypted passwords in CREATE/ALTER ROLE will have to be > > disabled. > > Actually, not one word of *either* version should be in TODO. All of > that is speculation about policies that a particular add-on module > might or might not choose to enforce. Agreed, updated: |Allow server-side enforcement of password policies|Password checks might include password complexity or non-reuse ofpasswords. This facility will require the client to send passwordcreation/changes to the server in plain-text, not MD5. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
2009/10/19 Tom Lane <tgl@sss.pgh.pa.us>: > I wrote: >> A server-side plugin can provide a guarantee that there are no bad >> passwords (for some value of bad, and with some possible adverse >> consequences). We don't have that today. > > BTW, it strikes me that ALTER USER RENAME introduces an interesting > hazard for such a plugin. Consider > > CREATE USER joe; > ALTER USER joe PASSWORD joe; -- presumably, plugin will reject this > ALTER USER joe PASSWORD mumblefrotz; -- assume this is considered OK > ALTER USER joe RENAME TO mumblefrotz; > > Now we have a user with name equal to password, which no sane security > policy will think is a good thing, but the plugin had no chance to > prevent it. The big difference is that you need to be superuser to change the name of a user, but not to change your own password. I know for example the Windows password policy thing has the same issue - if you rename the user, it doesn't have the password around to check, but you are an administrator so that's considered ok. -- Magnus HaganderMe: http://www.hagander.net/Work: http://www.redpill-linpro.com/
Magnus Hagander <magnus@hagander.net> writes: > 2009/10/19 Tom Lane <tgl@sss.pgh.pa.us>: >> Now we have a user with name equal to password, which no sane security >> policy will think is a good thing, but the plugin had no chance to >> prevent it. > The big difference is that you need to be superuser to change the name > of a user, but not to change your own password. True, but the superuser doesn't necessarily know what the user has set his password to. regards, tom lane
On Tue, Oct 20, 2009 at 9:42 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Magnus Hagander <magnus@hagander.net> writes: >> 2009/10/19 Tom Lane <tgl@sss.pgh.pa.us>: >>> Now we have a user with name equal to password, which no sane security >>> policy will think is a good thing, but the plugin had no chance to >>> prevent it. > >> The big difference is that you need to be superuser to change the name >> of a user, but not to change your own password. > > True, but the superuser doesn't necessarily know what the user has > set his password to. Yeah, but I'm not sure this case is worth worrying about. People who actually care password security are likely to have checks that are substantially stronger than "!= username". ...Robert
"Albe Laurenz" <laurenz.albe@wien.gv.at> wrote: > I wrote: > > Following the discussions in > > http://archives.postgresql.org/pgsql-hackers/2009-09/msg01766.php > > and > > http://archives.postgresql.org/pgsql-hackers/2009-10/msg00025.php , > > here are patches for > > > > a) a hook in backend/commands/user.c that allows one to add > > password checking functions > > b) a contrib module that makes use of the hook and > > c) documentation for the contrib module. > > I found a small but embarrassing bug - here is another version. I've reviewed your patch. The rough approach looks fine, but I have some comments about function declarations and coding style. The hook in core is declared as: typedef int(*check_password_hook_type)(char * const username, char * const password); but result type is actually treated as a boolean. So, it also should declared as bool. Also, the type of arguments should be "const char *". There several comments in contrib/passwordcheck. - We don't need #ifdef PG_MODULE_MAGIC because the module works only on 8.5 or later; PG_MODULE_MAGIC is always definedthere. - _PG_fini() is never called in HEAD anymore. Please remove it. - The function declaration of _PG_init() should be _PG_init(void). - isalpha() should be called as isalpha((unsigned char) c) because it could be crashed by multi-byte characters. - "8 characters long" would be better if it is configurable easily. I don't think it is to be a GUC varable, but #defineshould be used. - The logic in "check if the password contains only letters" should be "check if the password contains both upper-case,lower-case and non-alphabet letters". Passwords like "12345678' are not enough. - Coding style should more follow postgres. For example, posisions of '{', usage of spaces and linebreaks. Regards, --- ITAGAKI Takahiro NTT Open Source Software Center
Itagaki Takahiro wrote: > I've reviewed your patch. The rough approach looks fine, > but I have some comments about function declarations and coding style. Thank you for taking the time! I think I addressed all the shortcomings you mentioned with the attached patches. I fixed the "{" alignment; I am not sure if there are any other spacing or alignment problems. Yours, Laurenz Albe
Вложения
I think it would better to add an explicit "isencrypted" parameter to the check_password_hook function, rather than require the module to do isMD5 on the password. Any imaginable check hook will need to know if the password is in MD5 format, and the backend already knows it (because it already did that check), it seems good to let the hook function know. Besides, if we introduce explicit syntax for saying that the supplied password is plaintext or md5 one day, calling isMD5 in the module will no longer be appropriate. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
Heikki Linnakangas wrote: > I think it would better to add an explicit "isencrypted" parameter to > the check_password_hook function, rather than require the module to do > isMD5 on the password. Any imaginable check hook will need to know if > the password is in MD5 format, and the backend already knows it (because > it already did that check), it seems good to let the hook function know. > Besides, if we introduce explicit syntax for saying that the supplied > password is plaintext or md5 one day, calling isMD5 in the module will > no longer be appropriate. I agree on the second point, and I changed the patch accordingly. Here's the latest version. Yours, Laurenz Albe
Вложения
"Albe Laurenz" <laurenz.albe@wien.gv.at> wrote: > Heikki Linnakangas wrote: > > I think it would better to add an explicit "isencrypted" parameter to > > the check_password_hook function, rather than require the module to do > > isMD5 on the password. > > I agree on the second point, and I changed the patch accordingly. > Here's the latest version. Looks good. I change status of the patch to "Ready for Committer". BTW, it might not be a work for this patch, we also need to reject too long "VALID UNTIL" setting. If the password is complex, we should not use the same password for a long time. Regards, --- ITAGAKI Takahiro NTT Open Source Software Center
Itagaki Takahiro wrote: > Looks good. I change status of the patch to "Ready for Committer". Thanks for the help! > BTW, it might not be a work for this patch, we also need to > reject too long "VALID UNTIL" setting. If the password is > complex, we should not use the same password for a long time. There are some cases, e.g. application servers logging into the database, where you cannot just let the password expire, so I think this would at best have to be a rule with exceptions. Another thing that makes VALID UNTIL inconvenient to use is that after expiration, logins simply fail, and the user is never prompted to change the password. But of course you are right, requiring a limited password lifetime is closely related to requiring a good password. Yours, Laurenz Albe
Itagaki Takahiro <itagaki.takahiro@oss.ntt.co.jp> writes: > BTW, it might not be a work for this patch, we also need to > reject too long "VALID UNTIL" setting. If the password is > complex, we should not use the same password for a long time. This is a good point --- people who have password strength policies tend to want a limited usage period as well. It's even conceivable that you could have different allowed lifespans depending on how strong the password is. I suggest we alter the hook signature to pass it the valuntil time along with the other parameters it's already getting, and let the one hook enforce policies for both. I'm reviewing the patch now, and barring objections will make this change before committing. regards, tom lane
Itagaki Takahiro <itagaki.takahiro@oss.ntt.co.jp> writes: > "Albe Laurenz" <laurenz.albe@wien.gv.at> wrote: >> I agree on the second point, and I changed the patch accordingly. >> Here's the latest version. > Looks good. I change status of the patch to "Ready for Committer". Applied with some minor modifications. Aside from the added valuntil parameter, I changed the "isencrypted" parameter to an int with some #define'd values. It seems easily foreseeable that we'll replace the MD5 encryption scheme someday, and it'd be good to ensure that this API is extendable when that happens. Also, I got rid of the bool return value and made the hook responsible for throwing its own errors. I don't know about you guys, but I would cheerfully kill anybody who tried to make me use a password checker that didn't tell me anything about why it thinks my password is too weak. (The CrackLib API we are using is lamentably badly designed on this score --- does it have another call that provides a more useful error report?) Even if you think "weak password" is adequate for that class of complaints, the single error message would certainly not do for complaints about the valuntil date being too far away. regards, tom lane
Tom Lane wrote: > Applied with some minor modifications. Aside from the added valuntil > parameter, I changed the "isencrypted" parameter to an int with some > #define'd values. It seems easily foreseeable that we'll replace the > MD5 encryption scheme someday, and it'd be good to ensure that this > API is extendable when that happens. Also, I got rid of the bool > return value and made the hook responsible for throwing its > own errors. > I don't know about you guys, but I would cheerfully kill anybody who > tried to make me use a password checker that didn't tell me anything > about why it thinks my password is too weak. (The CrackLib API we > are using is lamentably badly designed on this score --- does it have > another call that provides a more useful error report?) Even if you > think "weak password" is adequate for that class of complaints, the > single error message would certainly not do for complaints about the > valuntil date being too far away. Thank you. I agree on all points. I did not know that contrib modules get translated too, else I would have thrown the error messages there. Yours, Laurenz Albe
"Albe Laurenz" <laurenz.albe@wien.gv.at> writes: > I did not know that contrib modules get translated too, else I would > have thrown the error messages there. I'm not sure whether contrib is currently covered by the translation teams, but it could someday be. In any case, an inadequate error message is not made less inadequate by translating it ;-) regards, tom lane