Re: [HACKERS] Bad error message on valuntil
On 6/7/13 2:57 PM, Tom Lane wrote: Joshua D. Drake j...@commandprompt.com writes: I had a customer pulling their hair out today because they couldn't login to their system. The error was consistently: 2013-06-07 08:42:44 MST postgres 10.1.11.67 27440 FATAL: password authentication failed for user user However the problem had nothing to do with password authentication. It was because the valuntil on the user had been set till a date in the past. Now technically if we just removed the word password from the error it would be accurate but it seems it would be better to say, FATAL: the user user has expired. I think it's intentional that we don't tell the *client* that level of detail. I could see emitting a log message about it, but it's not clear whether that will help an unsophisticated user. Usually, when I log in somewhere and the password is expired, it tells me that the password is expired. I don't think we gain anything by hiding that from the user. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Bad error message on valuntil
On 06/19/2013 08:24 AM, Peter Eisentraut wrote: I think it's intentional that we don't tell the *client* that level of detail. I could see emitting a log message about it, but it's not clear whether that will help an unsophisticated user. Usually, when I log in somewhere and the password is expired, it tells me that the password is expired. I don't think we gain anything by hiding that from the user. FTR: there is an actual patch for this sitting over at the, Change authentication error message thread. JD -- Command Prompt, Inc. - http://www.commandprompt.com/ 509-416-6579 PostgreSQL Support, Training, Professional Services and Development High Availability, Oracle Conversion, Postgres-XC, @cmdpromptinc For my dreams of your image that blossoms a rose in the deeps of my heart. - W.B. Yeats -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Bad error message on valuntil
* Tom Lane wrote: it supposes that rolvaliduntil represents an expiration date for the user, but really it's only an expiration date for the password.) Does anyone think the docs for CREATE ROLE/VALID UNTIL should mention this more clearly? Currently, it is described as The VALID UNTIL clause sets a date and time after which the role's password is no longer valid. If this clause is omitted the password will be valid for all time. This is entirely correct, but I think it could be made clearer by adding a sentence like This clause does not apply to authentication methods that do not involve a password, such as trust, ident, and GSSAPI. And at the top of section 19.3 (Authentication Methods): Time restrictions for the logon of users controlled by an external authentication service, such as GSSAPI or PAM, can be imposed by that service only, not by PostgreSQL itself. -- Christian -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Bad error message on valuntil
On 06/08/2013 04:07 AM, Joshua D. Drake wrote: FATAL: Authentication failed: Check server log for specifics And then we make sure we log proper info? FATAL: Authentication using method 'password' failed, possible reasons are no/wrong password sent, account expired; see server log for details ? We can tell them what they would already know (they tried the 'password' method) and a little about what might be wrong, as well as where to go for more info. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Bad error message on valuntil
On 06/07/2013 12:31 PM, Tom Lane wrote: Joshua D. Drake j...@commandprompt.com writes: On 06/07/2013 11:57 AM, Tom Lane wrote: I think it's intentional that we don't tell the *client* that level of detail. Why? That seems rather silly. The general policy on authentication failure reports is that we don't tell the client anything it doesn't know already about what the auth method is. We can log additional info into the postmaster log if it I was looking at the code and I saw this catchall: default: errstr = gettext_noop(authentication failed for user \%s\: invalid authentication method); break; I think we could make the argument that if valuntil is expired that the authentication method is invalid. Thoughts? Else I am trying to come up with some decent wording... something like: Authentication failed: not all authentication tokens were met ? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Bad error message on valuntil
Hello, I had a customer pulling their hair out today because they couldn't login to their system. The error was consistently: 2013-06-07 08:42:44 MST postgres 10.1.11.67 27440 FATAL: password authentication failed for user user However the problem had nothing to do with password authentication. It was because the valuntil on the user had been set till a date in the past. Now technically if we just removed the word password from the error it would be accurate but it seems it would be better to say, FATAL: the user user has expired. Sincerely, Joshua D. Drake -- Command Prompt, Inc. - http://www.commandprompt.com/ 509-416-6579 PostgreSQL Support, Training, Professional Services and Development High Availability, Oracle Conversion, Postgres-XC, @cmdpromptinc For my dreams of your image that blossoms a rose in the deeps of my heart. - W.B. Yeats -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Bad error message on valuntil
Joshua D. Drake j...@commandprompt.com writes: I had a customer pulling their hair out today because they couldn't login to their system. The error was consistently: 2013-06-07 08:42:44 MST postgres 10.1.11.67 27440 FATAL: password authentication failed for user user However the problem had nothing to do with password authentication. It was because the valuntil on the user had been set till a date in the past. Now technically if we just removed the word password from the error it would be accurate but it seems it would be better to say, FATAL: the user user has expired. I think it's intentional that we don't tell the *client* that level of detail. I could see emitting a log message about it, but it's not clear whether that will help an unsophisticated user. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Bad error message on valuntil
On 06/07/2013 11:57 AM, Tom Lane wrote: Joshua D. Drake j...@commandprompt.com writes: I had a customer pulling their hair out today because they couldn't login to their system. The error was consistently: 2013-06-07 08:42:44 MST postgres 10.1.11.67 27440 FATAL: password authentication failed for user user However the problem had nothing to do with password authentication. It was because the valuntil on the user had been set till a date in the past. Now technically if we just removed the word password from the error it would be accurate but it seems it would be better to say, FATAL: the user user has expired. I think it's intentional that we don't tell the *client* that level of detail. Why? That seems rather silly. I could see emitting a log message about it, but it's not clear whether that will help an unsophisticated user. This is not an unsophisticated user. They tried resetting the password, even changing the username to lowercase in case it was some weird folding issue. Granted they didn't check pg_user but then again, I didn't at first either because, well the error was rather obvious until it wasn't. Sincerely, JD regards, tom lane -- Command Prompt, Inc. - http://www.commandprompt.com/ 509-416-6579 PostgreSQL Support, Training, Professional Services and Development High Availability, Oracle Conversion, Postgres-XC, @cmdpromptinc For my dreams of your image that blossoms a rose in the deeps of my heart. - W.B. Yeats -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Bad error message on valuntil
Tom Lane-2 wrote Joshua D. Drake lt; jd@ gt; writes: I had a customer pulling their hair out today because they couldn't login to their system. The error was consistently: 2013-06-07 08:42:44 MST postgres 10.1.11.67 27440 FATAL: password authentication failed for user user However the problem had nothing to do with password authentication. It was because the valuntil on the user had been set till a date in the past. Now technically if we just removed the word password from the error it would be accurate but it seems it would be better to say, FATAL: the user user has expired. I think it's intentional that we don't tell the *client* that level of detail. I could see emitting a log message about it, but it's not clear whether that will help an unsophisticated user. regards, tom lane I presume that password in this context refers to the method by which identity is checked; some alternatives being trust and ident? Using the same logic of why you would not expose the fact that the user is expired versus the user has provided invalid credentials exposing password is a security leak as well. And then, to top it off, provides a red herring to the user trying to figure out why their username/password combination isn't working. Something like: 'Authentication for user user failed. Update and try again or contact the administrator to confirm user is authorized to log onto the system.' David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Bad-error-message-on-valuntil-tp5758369p5758383.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Bad error message on valuntil
Joshua D. Drake j...@commandprompt.com writes: On 06/07/2013 11:57 AM, Tom Lane wrote: I think it's intentional that we don't tell the *client* that level of detail. Why? That seems rather silly. The general policy on authentication failure reports is that we don't tell the client anything it doesn't know already about what the auth method is. We can log additional info into the postmaster log if it seems useful to do so, but the more you tell a client, the more you risk undesirable info leakage to a bad guy. As an example here, reporting the valuntil condition would be acking to an attacker that he had the right password. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Bad error message on valuntil
David Johnston pol...@yahoo.com writes: I presume that password in this context refers to the method by which identity is checked; some alternatives being trust and ident? Right. Using the same logic of why you would not expose the fact that the user is expired versus the user has provided invalid credentials exposing password is a security leak as well. No; the client side already knows that password auth is in use, because it received a password challenge message. I suppose you could construct some argument about how the textual report might be exposed to higher code levels that didn't know that, but we haven't chosen to theorize about what happens on the client side to that extent. And then, to top it off, provides a red herring to the user trying to figure out why their username/password combination isn't working. It's not really a red herring, because in fact the password was what failed. (Joshua's wording proposal has a conceptual flaw, because it supposes that rolvaliduntil represents an expiration date for the user, but really it's only an expiration date for the password.) regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Bad error message on valuntil
On 06/07/2013 12:31 PM, Tom Lane wrote: Joshua D. Drake j...@commandprompt.com writes: On 06/07/2013 11:57 AM, Tom Lane wrote: I think it's intentional that we don't tell the *client* that level of detail. Why? That seems rather silly. The general policy on authentication failure reports is that we don't tell the client anything it doesn't know already about what the auth method is. We can log additional info into the postmaster log if it seems useful to do so, but the more you tell a client, the more you risk undesirable info leakage to a bad guy. As an example here, reporting the valuntil condition would be acking to an attacker that he had the right password. So security by obscurity? Alright, without getting into that argument how about we change the error message to: FATAL: Authentication failed: Check server log for specifics And then we make sure we log proper info? Sincerely, Joshua D. Drake regards, tom lane -- Command Prompt, Inc. - http://www.commandprompt.com/ 509-416-6579 PostgreSQL Support, Training, Professional Services and Development High Availability, Oracle Conversion, Postgres-XC, @cmdpromptinc For my dreams of your image that blossoms a rose in the deeps of my heart. - W.B. Yeats -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Bad error message on valuntil
On Fri, 07 Jun 2013 13:07:21 -0700 Joshua D. Drake j...@commandprompt.com wrote: On 06/07/2013 12:31 PM, Tom Lane wrote: Joshua D. Drake j...@commandprompt.com writes: On 06/07/2013 11:57 AM, Tom Lane wrote: I think it's intentional that we don't tell the *client* that level of detail. Why? That seems rather silly. The general policy on authentication failure reports is that we don't tell the client anything it doesn't know already about what the auth method is. We can log additional info into the postmaster log if it seems useful to do so, but the more you tell a client, the more you risk undesirable info leakage to a bad guy. As an example here, reporting the valuntil condition would be acking to an attacker that he had the right password. So security by obscurity? Alright, without getting into that argument how about we change the error message to: FATAL: Authentication failed: Check server log for specifics And then we make sure we log proper info? +1 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Bad error message on valuntil
Joshua D. Drake wrote On 06/07/2013 12:31 PM, Tom Lane wrote: Joshua D. Drake lt; jd@ gt; writes: On 06/07/2013 11:57 AM, Tom Lane wrote: I think it's intentional that we don't tell the *client* that level of detail. Why? That seems rather silly. The general policy on authentication failure reports is that we don't tell the client anything it doesn't know already about what the auth method is. We can log additional info into the postmaster log if it seems useful to do so, but the more you tell a client, the more you risk undesirable info leakage to a bad guy. As an example here, reporting the valuntil condition would be acking to an attacker that he had the right password. So security by obscurity? Alright, without getting into that argument how about we change the error message to: FATAL: Authentication failed: Check server log for specifics And then we make sure we log proper info? Sincerely, Joshua D. Drake regards, tom lane In a password login situation you should not indicate to the client why the login attempt failed. If you say that the password expired they know the username supplied has to be correct (otherwise how would you know the password is expired). However, echoing back the supplied user identifier (without otherwise implying that it exists or does not exist on the server) provides a quick verification spot for the user to see whether the expected user name was being sent - especially since the location of the error message is probably significantly removed from the location of the user name string on the client. Please check server log for specifics is not a good message for something sent to a client that in many normal situation would have no access to said logs. I'd suggest: Authentication Failed: the user (role_name) password combination was not found or is expired. How a particular user is to go about resolving the issue is an organizational (and individual) policy best ignored in the error message. For a stressed-out, administrator-capable, user who sees this message they at least are reminded that even if the combination exists it is possible that it is has somehow been disabled. Hopefully they will then remember that password expiration is possible and will check that along with the presence of the role/user. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Bad-error-message-on-valuntil-tp5758369p5758398.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Bad error message on valuntil
On 06/07/2013 01:41 PM, David Johnston wrote: Please check server log for specifics is not a good message for something sent to a client that in many normal situation would have no access to said logs. I don't agree. The user doesn't need access to the logs. If they get that error they report it to their support staff. We don't need to tell them any more than that. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Bad error message
On Oct 1, 2008, at 12:12 AM, Gurjeet Singh wrote: On Wed, Oct 1, 2008 at 9:39 AM, Tom Lane [EMAIL PROTECTED] wrote: Gurjeet Singh [EMAIL PROTECTED] writes: On Wed, Oct 1, 2008 at 3:07 AM, Decibel! [EMAIL PROTECTED] wrote: ERROR: aggregates not allowed in WHERE clause No, the real issue is that you are referencing the outer table's column's max() in the inner query (correlated sub-query). Yeah. It's not easy to see how the software could guess your real intentions here. We could maybe offer a vaguely-worded HINT but I'm not able to think of wording that would be very helpful. Can we do something like this in the code: if( level of the referenced column's relation != level of the (sub)query being processed ) errhint( The subquery may be unintentionally referencing an outer query's column! ); Yeah, something like that would be very helpful. Mail sent from my BlackLaptop device Haha. +1 -- Decibel!, aka Jim C. Nasby, Database Architect [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 smime.p7s Description: S/MIME cryptographic signature
[HACKERS] Bad error message
From -HEAD: ERROR: aggregates not allowed in WHERE clause STATEMENT: SELECT * FROM loans l WHERE id IN ( SELECT max(l.id) FROM loans JOIN customers c ON c.id = l.customer_id JOIN people p ON p.id = c.person_id WHERE p.first_name = 'Test person' GROUP BY l.loan_type_cd ) ; The real issue is this: ERROR: missing FROM-clause entry for table l at character 132 STATEMENT: SELECT max(l.id) FROM loans JOIN customers c ON c.id = l.customer_id JOIN people p ON p.id = c.person_id WHERE p.first_name = 'Test person' GROUP BY l.loan_type_cd; And if I change the FROM loans to be FROM loans l, the original select does work fine. Let me know if I need to create a full test case for this... -- Decibel!, aka Jim C. Nasby, Database Architect [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 smime.p7s Description: S/MIME cryptographic signature
Re: [HACKERS] Bad error message
On Wed, Oct 1, 2008 at 3:07 AM, Decibel! [EMAIL PROTECTED] wrote: From -HEAD: ERROR: aggregates not allowed in WHERE clause STATEMENT: SELECT * FROM loans l WHERE id IN ( SELECT max(l.id) FROM loans JOIN customers c ON c.id = l.customer_id JOIN people p ON p.id = c.person_id WHERE p.first_name = 'Test person' GROUP BY l.loan_type_cd ) ; The real issue is this: ERROR: missing FROM-clause entry for table l at character 132 STATEMENT: SELECT max(l.id) FROM loans JOIN customers c ON c.id = l.customer_id JOIN people p ON p.id = c.person_id WHERE p.first_name = 'Test person' GROUP BY l.loan_type_cd; And if I change the FROM loans to be FROM loans l, the original select does work fine. Let me know if I need to create a full test case for this... No, the real issue is that you are referencing the outer table's column's max() in the inner query (correlated sub-query). The table in the outer query is aliased 'l' and the sub-query is trying to aggregate that, which is in the outer query's WHERE clause; and that is not allowed. Renaming the outer query's alias to something other than 'l' would show you the real error (which again would be 'missing FROM-clause entry). Best regards, -- [EMAIL PROTECTED] [EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com EnterpriseDB http://www.enterprisedb.com Mail sent from my BlackLaptop device
Re: [HACKERS] Bad error message
Gurjeet Singh [EMAIL PROTECTED] writes: On Wed, Oct 1, 2008 at 3:07 AM, Decibel! [EMAIL PROTECTED] wrote: ERROR: aggregates not allowed in WHERE clause No, the real issue is that you are referencing the outer table's column's max() in the inner query (correlated sub-query). Yeah. It's not easy to see how the software could guess your real intentions here. We could maybe offer a vaguely-worded HINT but I'm not able to think of wording that would be very helpful. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Bad error message
On Wed, Oct 1, 2008 at 9:39 AM, Tom Lane [EMAIL PROTECTED] wrote: Gurjeet Singh [EMAIL PROTECTED] writes: On Wed, Oct 1, 2008 at 3:07 AM, Decibel! [EMAIL PROTECTED] wrote: ERROR: aggregates not allowed in WHERE clause No, the real issue is that you are referencing the outer table's column's max() in the inner query (correlated sub-query). Yeah. It's not easy to see how the software could guess your real intentions here. We could maybe offer a vaguely-worded HINT but I'm not able to think of wording that would be very helpful. Can we do something like this in the code: if( level of the referenced column's relation != level of the (sub)query being processed ) errhint( The subquery may be unintentionally referencing an outer query's column! ); Best regards, -- [EMAIL PROTECTED] [EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com EnterpriseDB http://www.enterprisedb.com Mail sent from my BlackLaptop device