Re: [HACKERS] WIP: SCRAM authentication

On Mon, Feb 15, 2016 at 11:05 AM, Michael Paquier wrote: > On Mon, Feb 15, 2016 at 10:51 AM, Stephen Frost wrote: >> * Tom Lane (t...@sss.pgh.pa.us) wrote: >>> Stephen Frost writes: >>> > Why do we need pg_shadow or pg_user or

Re: [HACKERS] WIP: SCRAM authentication

On Mon, Feb 15, 2016 at 10:51 AM, Stephen Frost wrote: > * Tom Lane (t...@sss.pgh.pa.us) wrote: >> Stephen Frost writes: >> > Why do we need pg_shadow or pg_user or related views at all..? >> >> A lot of code looks at those just to get usernames. I am not

Re: [HACKERS] WIP: SCRAM authentication

Stephen Frost writes: > * Tom Lane (t...@sss.pgh.pa.us) wrote: >> How about we just say that the password in these old views always reads >> out as '' even when there is a password, and we invent new views >> that carry real auth information? (Hopefully in an

Re: [HACKERS] WIP: SCRAM authentication

* Michael Paquier (michael.paqu...@gmail.com) wrote: > On Mon, Feb 15, 2016 at 10:23 AM, Stephen Frost wrote: > > I would start by pointing out that pg_user currently uses pg_shadow.. > > Why do we need pg_shadow or pg_user or related views at all..? > > pg_user/pg_shadow

Re: [HACKERS] WIP: SCRAM authentication

* Tom Lane (t...@sss.pgh.pa.us) wrote: > Stephen Frost writes: > > Why do we need pg_shadow or pg_user or related views at all..? > > A lot of code looks at those just to get usernames. I am not in favor of > breaking such stuff without need. Alright. > How about we just

Re: [HACKERS] WIP: SCRAM authentication

Stephen Frost writes: > Why do we need pg_shadow or pg_user or related views at all..? A lot of code looks at those just to get usernames. I am not in favor of breaking such stuff without need. How about we just say that the password in these old views always reads out as

Re: [HACKERS] WIP: SCRAM authentication

Michael, * Michael Paquier (michael.paqu...@gmail.com) wrote: > It seems to me that applications are going to need a refresh anyway... Indeed. > Among the other possibilities I can foresee: > - Add a column "protocol" to pg_shadow and produce one row per > protocol, so one user will be listed

Re: [HACKERS] WIP: SCRAM authentication

On Mon, Feb 15, 2016 at 9:56 AM, Stephen Frost wrote: > * Michael Paquier (michael.paqu...@gmail.com) wrote: >> We'd need as well to switch pg_shadow to have an array of elements >> made of protocol:identifier instead of a single password field. There >> can be only one valid

Re: [HACKERS] WIP: SCRAM authentication

Michael, * Michael Paquier (michael.paqu...@gmail.com) wrote: > On Mon, Feb 15, 2016 at 9:17 AM, Stephen Frost wrote: > > That said, per various discussions, we'd really want a more-or-less > > fully formed patch to land prior to the last CF, for this to have any > > chance.

Re: [HACKERS] WIP: SCRAM authentication

On Mon, Feb 15, 2016 at 9:17 AM, Stephen Frost wrote: > Michael, > > * Michael Paquier (michael.paqu...@gmail.com) wrote: >> On Sat, Feb 13, 2016 at 3:05 AM, David Steele wrote: >> > On 11/16/15 8:53 AM, Michael Paquier wrote: >> >> On Sat, Sep 5, 2015 at

Re: [HACKERS] WIP: SCRAM authentication

Michael, * Michael Paquier (michael.paqu...@gmail.com) wrote: > On Sat, Feb 13, 2016 at 3:05 AM, David Steele wrote: > > On 11/16/15 8:53 AM, Michael Paquier wrote: > >> On Sat, Sep 5, 2015 at 9:31 AM, Bruce Momjian wrote: > >>> On Fri, Sep 4, 2015 at 04:51:33PM -0400,

Re: [HACKERS] WIP: SCRAM authentication

On Sat, Feb 13, 2016 at 3:05 AM, David Steele wrote: > On 11/16/15 8:53 AM, Michael Paquier wrote: >> On Sat, Sep 5, 2015 at 9:31 AM, Bruce Momjian wrote: >>> On Fri, Sep 4, 2015 at 04:51:33PM -0400, Stephen Frost wrote: > Coming in late, but can you explain how multiple

Re: [HACKERS] WIP: SCRAM authentication

Hi Michael and Heikki, On 11/16/15 8:53 AM, Michael Paquier wrote: > On Sat, Sep 5, 2015 at 9:31 AM, Bruce Momjian wrote: >> On Fri, Sep 4, 2015 at 04:51:33PM -0400, Stephen Frost wrote: Coming in late, but can you explain how multiple passwords allow for easier automated credential

Re: [HACKERS] WIP: SCRAM authentication

On Mon, Nov 16, 2015 at 10:53 PM, Michael Paquier wrote: > Reviving an old thread for a patch still registered in this commit > fest to make the arguing move on. > > Supporting multiple verifiers for a single role has IMO clear advantages: > - help transition to new protocols and decommission of

Re: [HACKERS] WIP: SCRAM authentication

On Sat, Sep 5, 2015 at 9:31 AM, Bruce Momjian wrote: > On Fri, Sep 4, 2015 at 04:51:33PM -0400, Stephen Frost wrote: >> > Coming in late, but can you explain how multiple passwords allow for >> > easier automated credential rotation? If you have five applications >> > with stored passwords, I

Re: [HACKERS] WIP: SCRAM authentication

On Tue, Aug 18, 2015 at 09:30:39PM +0100, Greg Stark wrote: > > OK, that's an interesting argument. If SCRAM supports multiple > > password verifiers, and we support SCRAM, then I guess we should > > probably do that, too. I still don't like it all that much, though. > > I think it's absolutely

Re: [HACKERS] WIP: SCRAM authentication

Bruce, * Bruce Momjian (br...@momjian.us) wrote: > On Tue, Aug 18, 2015 at 09:30:39PM +0100, Greg Stark wrote: > > > OK, that's an interesting argument. If SCRAM supports multiple > > > password verifiers, and we support SCRAM, then I guess we should > > > probably do that, too. I still don't

Re: [HACKERS] WIP: SCRAM authentication

On Fri, Sep 4, 2015 at 04:51:33PM -0400, Stephen Frost wrote: > > Coming in late, but can you explain how multiple passwords allow for > > easier automated credential rotation? If you have five applications > > with stored passwords, I imagine you can't change them all at once, so > > with

Re: [HACKERS] WIP: SCRAM authentication

* Robert Haas (robertmh...@gmail.com) wrote: On Tue, Aug 18, 2015 at 2:07 PM, Stephen Frost sfr...@snowman.net wrote: SCRAM itself, as has been discussed, supports multiple password verifiers- that's a specific case all by itself, and it's done specifically to address the issue that one or

Re: [HACKERS] WIP: SCRAM authentication

On Tue, Aug 18, 2015 at 2:07 PM, Stephen Frost sfr...@snowman.net wrote: I would expect there to be people who would run into pg_upgrade complaining, that's why there would be the check. That's actually a much better situation than what happened around standard_conforming_strings. Further,

Re: [HACKERS] WIP: SCRAM authentication

Robert, * Robert Haas (robertmh...@gmail.com) wrote: On Tue, Aug 18, 2015 at 10:06 AM, Stephen Frost sfr...@snowman.net wrote: That was the imputus for my earlier suggestion that in a release or two, we actively make pg_upgrade error (or perhaps warn first, then error, across two releases)

Re: [HACKERS] WIP: SCRAM authentication

On Tue, Aug 18, 2015 at 7:19 PM, Robert Haas robertmh...@gmail.com wrote: Sorry, that's a completely bogus argument. We do not need to prevent people from upgrading if they haven't moved off of MD5 authentication; that's just an arbitrary - and IMHO extremely user-hostile - policy decision.

Re: [HACKERS] WIP: SCRAM authentication

On Wed, Aug 19, 2015 at 5:30 AM, Greg Stark st...@mit.edu wrote: On Tue, Aug 18, 2015 at 7:19 PM, Robert Haas robertmh...@gmail.com wrote: Sorry, that's a completely bogus argument. We do not need to prevent people from upgrading if they haven't moved off of MD5 authentication; that's just

Re: [HACKERS] WIP: SCRAM authentication

Josh, * Josh Berkus (j...@agliodbs.com) wrote: I don't feel like you've correctly assessed the risk inherent in the md5 auth method, which is that, having captured an md5auth string by whatever means, and attacker can reuse that md5 string on other databases in the network *without* cracking

Re: [HACKERS] WIP: SCRAM authentication

On Tue, Aug 18, 2015 at 10:06 AM, Stephen Frost sfr...@snowman.net wrote: That was the imputus for my earlier suggestion that in a release or two, we actively make pg_upgrade error (or perhaps warn first, then error, across two releases) if any of the old verifiers exist. I think there's

Re: [HACKERS] WIP: SCRAM authentication

* Robert Haas (robertmh...@gmail.com) wrote: On Wed, Aug 12, 2015 at 9:36 PM, Stephen Frost sfr...@snowman.net wrote: Yes, the SCRAM implementation could be buggy. But also, OpenSSL has repeatedly had security bugs that were due to forcing servers to downgrade to older protocols. I

Re: [HACKERS] WIP: SCRAM authentication

On 08/12/2015 06:36 PM, Stephen Frost wrote: I attempted to address that also by stating that, should an attacker compromise a system with the goal of gaining the cleartext password, they would attempt the following, in order: 1) attempt to compromise a superuser account, if not already

Re: [HACKERS] WIP: SCRAM authentication

On 8/11/15 5:18 PM, Robert Haas wrote: The thing we're actually debating here is whether enabling SCRAM authentication for a role should also mean disabling MD5 authentication for that same role, or whether you should be able to have two password verifiers stored for that role, one for SCRAM

Re: [HACKERS] WIP: SCRAM authentication

Robert, * Robert Haas (robertmh...@gmail.com) wrote: On Wed, Aug 12, 2015 at 4:09 PM, Stephen Frost sfr...@snowman.net wrote: As for the notion of dropping md5 from 9.6 or even forcing it to be one-or-the-other on a per-role basis, ... Please don't conflate those two things. They are

Re: [HACKERS] WIP: SCRAM authentication

On Wed, Aug 12, 2015 at 4:09 PM, Stephen Frost sfr...@snowman.net wrote: As for the notion of dropping md5 from 9.6 or even forcing it to be one-or-the-other on a per-role basis, ... Please don't conflate those two things. They are radically different in terms of the amount of upgrade pain

Re: [HACKERS] WIP: SCRAM authentication

On Wed, Aug 12, 2015 at 4:37 PM, Stephen Frost sfr...@snowman.net wrote: Please don't conflate those two things. They are radically different in terms of the amount of upgrade pain that they cause. The first one would be completely insane. Thanks for the clarification. I had gotten the

Re: [HACKERS] WIP: SCRAM authentication

On 08/12/2015 01:37 PM, Stephen Frost wrote: Would be great to get comments on the other comments, specifically that adding SCRAM's password verifier won't seriously change the security of a user's account or password based on an attack vector where the contents of pg_authid is compromised. I

Re: [HACKERS] WIP: SCRAM authentication

* Josh Berkus (j...@agliodbs.com) wrote: On 08/12/2015 01:37 PM, Stephen Frost wrote: Would be great to get comments on the other comments, specifically that adding SCRAM's password verifier won't seriously change the security of a user's account or password based on an attack vector where

Re: [HACKERS] WIP: SCRAM authentication

On Thu, Aug 13, 2015 at 10:22 AM, Stephen Frost wrote: The only case where I can see multiple verifiers per role making a real difference in migrations is for PGAAS hosting. But the folks from Heroku and AWS have been notably silent on this; lemme ping them. While their insight is certainly

Re: [HACKERS] WIP: SCRAM authentication

Robert, * Robert Haas (robertmh...@gmail.com) wrote: On Wed, Aug 12, 2015 at 4:37 PM, Stephen Frost sfr...@snowman.net wrote: Please don't conflate those two things. They are radically different in terms of the amount of upgrade pain that they cause. The first one would be completely

Re: [HACKERS] WIP: SCRAM authentication

On Wed, Aug 12, 2015 at 9:36 PM, Stephen Frost sfr...@snowman.net wrote: Yes, the SCRAM implementation could be buggy. But also, OpenSSL has repeatedly had security bugs that were due to forcing servers to downgrade to older protocols. I wouldn't like us to start growing similar

Re: [HACKERS] WIP: SCRAM authentication

* Michael Paquier (michael.paqu...@gmail.com) wrote: On Thu, Aug 13, 2015 at 10:22 AM, Stephen Frost wrote: The only case where I can see multiple verifiers per role making a real difference in migrations is for PGAAS hosting. But the folks from Heroku and AWS have been notably silent on

Re: [HACKERS] WIP: SCRAM authentication

On Thu, Aug 13, 2015 at 6:21 AM, Josh Berkus wrote: The only case where I can see multiple verifiers per role making a real difference in migrations is for PGAAS hosting. But the folks from Heroku and AWS have been notably silent on this; lemme ping them. Yes, I would be curious to hear from

Re: [HACKERS] WIP: SCRAM authentication

On Wed, Aug 12, 2015 at 10:50 AM, Peter Eisentraut pete...@gmx.net wrote: I understand this idea, but I think it's not practical for many uses. There is no way to find out, on the server, whether all current clients would support a switch to SCRAM. Let alone all not-current clients. The only

Re: [HACKERS] WIP: SCRAM authentication

On 8/12/15 12:19 PM, Robert Haas wrote: On Wed, Aug 12, 2015 at 10:50 AM, Peter Eisentraut pete...@gmx.net wrote: I understand this idea, but I think it's not practical for many uses. There is no way to find out, on the server, whether all current clients would support a switch to SCRAM. Let

Re: [HACKERS] WIP: SCRAM authentication

All, * Peter Eisentraut (pete...@gmx.net) wrote: On 8/12/15 12:19 PM, Robert Haas wrote: On Wed, Aug 12, 2015 at 10:50 AM, Peter Eisentraut pete...@gmx.net wrote: I understand this idea, but I think it's not practical for many uses. There is no way to find out, on the server, whether all

Re: [HACKERS] WIP: SCRAM authentication

On Tue, Aug 11, 2015 at 2:35 PM, Peter Eisentraut pete...@gmx.net wrote: On 8/11/15 10:28 AM, Robert Haas wrote: Right. So what? First, you upgrade all of the clients one by one to a new version of the connector that supports SCRAM. In my experience, upgrading clients is, in many settings,

Re: [HACKERS] WIP: SCRAM authentication

On Sun, Aug 9, 2015 at 2:42 PM, Josh Berkus j...@agliodbs.com wrote: On 08/09/2015 08:09 AM, Robert Haas wrote: Why do we need to be able to authenticate using more than one mechanism? If you have some clients that can't support SCRAM yet, you might as well continue using MD5 across the board

Re: [HACKERS] WIP: SCRAM authentication

On 08/11/2015 07:28 AM, Robert Haas wrote: There may be a good answer to this question, but I don't think I've seen it spelled out clearly. Please see my follow-up post about making by-login-role migration easier for users. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com --

Re: [HACKERS] WIP: SCRAM authentication

On 08/11/2015 09:35 AM, Robert Haas wrote: On Tue, Aug 11, 2015 at 12:29 PM, Josh Berkus j...@agliodbs.com wrote: On 08/11/2015 07:28 AM, Robert Haas wrote: There may be a good answer to this question, but I don't think I've seen it spelled out clearly. Please see my follow-up post about

Re: [HACKERS] WIP: SCRAM authentication

On Tue, Aug 11, 2015 at 12:29 PM, Josh Berkus j...@agliodbs.com wrote: On 08/11/2015 07:28 AM, Robert Haas wrote: There may be a good answer to this question, but I don't think I've seen it spelled out clearly. Please see my follow-up post about making by-login-role migration easier for

Re: [HACKERS] WIP: SCRAM authentication

On Tue, Aug 11, 2015 at 12:49 PM, Josh Berkus j...@agliodbs.com wrote: You're suggesting, then, that the switchover should be relatively easy, because drivers will support both MD5 and SCRAM, and once all drivers support both, the DBA can just swap verifiers? Yes, that's what I was imagining

Re: [HACKERS] WIP: SCRAM authentication

On 08/11/2015 10:06 AM, Robert Haas wrote: On Tue, Aug 11, 2015 at 12:49 PM, Josh Berkus j...@agliodbs.com wrote: That makes sense if drivers go that way. I'm concerned that some drivers will have a different call for a SCRAM connection than for an MD5 one; we'd want to exert our project

Re: [HACKERS] WIP: SCRAM authentication

On 8/11/15 10:28 AM, Robert Haas wrote: Right. So what? First, you upgrade all of the clients one by one to a new version of the connector that supports SCRAM. In my experience, upgrading clients is, in many settings, significantly harder than upgrading servers. So I think any plan to starts

Re: [HACKERS] WIP: SCRAM authentication

On 08/09/2015 07:19 PM, Michael Paquier wrote: ... during my exchange with Michael, I was thinking about the bug potential of taking the password field and multiplexing it in some way, which is significant. There is a definite risk of making this too complicated and we'll need to contrast

Re: [HACKERS] WIP: SCRAM authentication

* Robert Haas (robertmh...@gmail.com) wrote: On Sat, Aug 8, 2015 at 1:23 PM, Heikki Linnakangas hlinn...@iki.fi wrote: On 08/08/2015 04:27 PM, Robert Haas wrote: I don't see that there's any good reason to allow the same password to be stored in the catalog encrypted more than one way,

Re: [HACKERS] WIP: SCRAM authentication

On 08/09/2015 08:09 AM, Robert Haas wrote: Why do we need to be able to authenticate using more than one mechanism? If you have some clients that can't support SCRAM yet, you might as well continue using MD5 across the board until that changes. You're not going to get much real security out

Re: [HACKERS] WIP: SCRAM authentication

On Sat, Aug 8, 2015 at 1:23 PM, Heikki Linnakangas hlinn...@iki.fi wrote: On 08/08/2015 04:27 PM, Robert Haas wrote: I don't see that there's any good reason to allow the same password to be stored in the catalog encrypted more than one way, Sure there is. If you want to be able to

Re: [HACKERS] WIP: SCRAM authentication

* Sehrope Sarkuni (sehr...@jackdb.com) wrote: It'd be nice if the new auth mechanism supports multiple passwords in the same format as well (not just one per format). That way you could have two different passwords for a user that are active at the same time. This would simplify rolling

Re: [HACKERS] WIP: SCRAM authentication

On Mon, Aug 10, 2015 at 6:05 AM, Stephen Frost sfr...@snowman.net wrote: * Sehrope Sarkuni (sehr...@jackdb.com) wrote: It'd be nice if the new auth mechanism supports multiple passwords in the same format as well (not just one per format). That way you could have two different passwords for a

Re: [HACKERS] WIP: SCRAM authentication

On Mon, Aug 10, 2015 at 3:42 AM, Josh Berkus j...@agliodbs.com wrote: On 08/09/2015 08:09 AM, Robert Haas wrote: Why do we need to be able to authenticate using more than one mechanism? If you have some clients that can't support SCRAM yet, you might as well continue using MD5 across the

Re: [HACKERS] WIP: SCRAM authentication

On Fri, Aug 7, 2015 at 6:54 PM, Michael Paquier michael.paqu...@gmail.com wrote: This filtering machinery definitely looks like a GUC to me, something like password_forbidden_encryption that PASSWORD VERIFIERS looks at and discards the methods listed in there. This definitely needs to be

Re: [HACKERS] WIP: SCRAM authentication

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 08/08/2015 06:27 AM, Robert Haas wrote: terminology. I think we should store (1) your password, either encrypted or unencrypted; and (2) the method used to encrypt it. And that's it. A petty complaint, but it has always bothered me that we

Re: [HACKERS] WIP: SCRAM authentication

* Robert Haas (robertmh...@gmail.com) wrote: On Fri, Aug 7, 2015 at 6:54 PM, Michael Paquier michael.paqu...@gmail.com wrote: This filtering machinery definitely looks like a GUC to me, something like password_forbidden_encryption that PASSWORD VERIFIERS looks at and discards the methods

Re: [HACKERS] WIP: SCRAM authentication

On 08/08/2015 04:27 PM, Robert Haas wrote: I don't see that there's any good reason to allow the same password to be stored in the catalog encrypted more than one way, Sure there is. If you want to be able to authenticate using different mechanism, you need the same password encrypted in

Re: [HACKERS] WIP: SCRAM authentication

On Sat, Aug 8, 2015 at 6:23 PM, Heikki Linnakangas hlinn...@iki.fi wrote: Like Joe and Stephen, I actually find it highly confusing that we call the MD5 hash an encrypted password. The term password verifier is fairly common in the specifications of authentication mechanisms. I think we should

Re: [HACKERS] WIP: SCRAM authentication

On 08/08/2015 10:23 AM, Heikki Linnakangas wrote: On 08/08/2015 04:27 PM, Robert Haas wrote: I don't see that there's any good reason to allow the same password to be stored in the catalog encrypted more than one way, Sure there is. If you want to be able to authenticate using different

Re: [HACKERS] WIP: SCRAM authentication

On Sun, Aug 9, 2015 at 6:51 AM, Josh Berkus j...@agliodbs.com wrote: Obviously the backwards-compatibility issues are pretty major ... it'll be years before all drivers support SCRAM ... but we also want to provide a path forwards for secure installations in which no md5 hashes are stored.

Re: [HACKERS] WIP: SCRAM authentication

It'd be nice if the new auth mechanism supports multiple passwords in the same format as well (not just one per format). That way you could have two different passwords for a user that are active at the same time. This would simplify rolling database credentials as it wouldn't have to be done all

Re: [HACKERS] WIP: SCRAM authentication

On 08/08/2015 03:21 PM, Michael Paquier wrote: On Sun, Aug 9, 2015 at 6:51 AM, Josh Berkus j...@agliodbs.com wrote: 1. we drop the parameter password_encryption 2. we add the parameter password_storage, which takes a list: - plain : plain text - md5 : current md5 hashes - scram :

Re: [HACKERS] WIP: SCRAM authentication

On Sat, Aug 8, 2015 at 3:45 AM, Heikki Linnakangas hlinn...@iki.fi wrote: On 08/07/2015 09:26 PM, Robert Haas wrote: Maybe I'm chiming in too late here but I am sorta unimpressed by this. If the user's password is stored both MD5-hashed and hashed some other way in the system catalogs, that's

Re: [HACKERS] WIP: SCRAM authentication

On Fri, Aug 7, 2015 at 3:22 AM, Michael Paquier michael.paqu...@gmail.com wrote: On Tue, Aug 4, 2015 at 4:20 PM, Michael Paquier wrote: I have been looking more in depths at this one, which adds essential infrastructure to support multiple authentication hashes for more protocols. Here are

Re: [HACKERS] WIP: SCRAM authentication

On 08/07/2015 09:26 PM, Robert Haas wrote: Maybe I'm chiming in too late here but I am sorta unimpressed by this. If the user's password is stored both MD5-hashed and hashed some other way in the system catalogs, that's less secure than storing it in the least secure of those ways. And I'm

Re: [HACKERS] WIP: SCRAM authentication

On Mon, Mar 30, 2015 at 7:52 PM, Heikki Linnakangas hlinn...@iki.fi wrote: There have been numerous threads on replacing our MD5 authentication method, so I started hacking on that to see what it might look like. Just to be clear, this is 9.6 material. Attached is a WIP patch series that adds

Re: [HACKERS] WIP: SCRAM authentication

On 03/30/2015 06:46 PM, Stephen Frost wrote: * Heikki Linnakangas (hlinn...@iki.fi) wrote: * With CREATE USER PASSWORD 'foo', which hashes/verifiers should be generated by default? We currently have a boolean password_encryption setting for that. Needs to be a list. This generally sounds good

Re: [HACKERS] WIP: SCRAM authentication

Heikki, * Heikki Linnakangas (hlinn...@iki.fi) wrote: On 03/30/2015 06:46 PM, Stephen Frost wrote: Unfortunately, the first major release with this will certainly need to default to including md5 as we can't have a password update or change break clients right off the bat. What I think would

Re: [HACKERS] WIP: SCRAM authentication

* Heikki Linnakangas (hlinn...@iki.fi) wrote: There have been numerous threads on replacing our MD5 authentication method, so I started hacking on that to see what it might look like. Just to be clear, this is 9.6 material. Attached is a WIP patch series that adds support for SCRAM. There's no