Support for SCRAM Authentication

[jkatz]

Support for SCRAM password authentication was introduced in PostgreSQL 10; PostgreSQL 14 defaults to using scram-sha-256 for password hashing by default:

Change the default of the password_encryption server parameter to scram-sha-256

While the md5 is still supported, it is gradually being phased out in favor of SCRAM.

If you need some reference implementations beyond the PostgreSQL source, here is a list of known PostgreSQL drivers + SCRAM status ( I also have a presentation on the topic that shows the workflow).

[lpil]

Hit into this today as PostgreSQL with SCRAM is used by https://fly.io/, a deployment platform that's pushing the BEAM quite hard at the moment.

Here's the epgsql implementation: https://github.com/epgsql/epgsql/pull/156/files

And the Elixir Postgrex one: https://github.com/elixir-ecto/postgrex/blob/master/lib/postgrex/scram.ex

[tsloughter]

Thanks @jkatz ! Wish I had not somehow missed this issue you raised 4 months ago and could have been on top of this for postgres 14!

[jkatz]

@tsloughter It's never too late to support SCRAM! 😉 I'd be happy to review your implementation.

[tsloughter]

@jkatz I've started on this but is it another case of postgres' "documentation" of the protocol only being the C code :) or is there some outline of the protocol somewhere?

[tsloughter]

Oh, I missed you have a presentation, maybe that'll be enough, I'll give it a look.

[jkatz]

It's implemented in accordance with the SCRAM RFC 5802 and because it uses SHA-256, RFC 7677.

The presentation you reference does cover some of the implementation specifics.

[tsloughter]

Update: I have it working (thanks to epgsql :) and just need to clean up the code a bit before opening a PR. Hopefully can get someone to review the code a bit (@lpil ?) so I don't just have to merge it.

[lpil]

I'm game

[tsloughter]

@lpil cool. I was able to do it over the weekend but can't figure out why there are broken tests completely unrelated to these changes :(. Realized I should just open the PR anyway so you can take a look, so will do that this morning.

[tsloughter]

Here it is: https://github.com/erleans/pgo/pull/58

I see I still need to move CI to github actions :(

[tsloughter]

Resolved by #58

[lpil]

Amazing! Thank you very much

[jkatz]

@tsloughter Thank you! I've updated the PG wiki to indicate pgo supports SCRAM.

[tsloughter]

Thanks!