PostgreSQL SCRAM-SHA-256 authentication

From version 10 PostgreSQL supports RFC 7677.

This is a password transmission scheme that prevents password sniffing on untrusted connections and supports storage in a cryptographic hash form, much more secure than the dashed method on MD5.

The following is an example of code in Perl that can be used from the command line, providing the password using PIPE:

~$ echo 'my password' | ./scram_sha_256.pl
SCRAM-SHA-256$4096:NWxiS29VNlUwMkVaYTlqYw==$bvMFuhI6qZJLywX5SxB1JrdbdAiJQ+elsLDa+E7FFOQ=:BJJ1E8GqCtQMvyPRvYi83nlL6oqqNYlPnYUslMDwbYo=
~$

Thus, it is possible to change the password in the database without exchanging the password (in plain text) in the SQL code exchanged between client and server:

ALTER USER "my user" ENCRYPTED PASSWORD 'SCRAM-SHA-256$4096:NWxiS29VNlUwMkVaYTlqYw==$bvMFuhI6qZJLywX5SxB1JrdbdAiJQ+elsLDa+E7FFOQ=:BJJ1E8GqCtQMvyPRvYi83nlL6oqqNYlPnYUslMDwbYo=';

---

Perl code scram_sha_256.pl :

#!/usr/bin/perl
# Copyright: 2024 - Guido Brugnara <gdo@leader.it>
# Licence: This program is free software; you can redistribute it and/or modify it under the same terms as Perl itself.
use Crypt::Salt;
use Crypt::KeyDerivation qw(pbkdf2);
use Crypt::Mac::HMAC qw(hmac hmac_b64);
use Crypt::Digest::SHA256 qw(sha256_b64);
use MIME::Base64;
my $salt = Crypt::Salt::salt(16);
my $iterations = 4096;
my $password = <STDIN>;
chomp $password;
my $digest_key = pbkdf2($password, $salt, $iterations, 'SHA256', 32);
my $client_key = hmac('SHA256', $digest_key ,'Client Key');
my $b64_client_key = sha256_b64($client_key);
my $b64_server_key = hmac_b64('SHA256', $digest_key, 'Server Key');
my $b64_salt = encode_base64($salt, '');
print "SCRAM-SHA-256\$$iterations:$b64_salt\$$b64_client_key:$b64_server_key\n";

---

Perl code using Crypt::PostgreSQL module:

#!/usr/bin/perl
use Crypt::PostgreSQL;
print Crypt::PostgreSQL::encrypt_scram('my password')."\n";

Useful links:

• https://www.postgresql.org/docs/current/auth-password.html
• https://stackoverflow.com/questions/68400120/how-to-generate-scram-sha-256-to-create-postgres-13-user - See answer 10 with Python and Go code examples
• https://metacpan.org/pod/Crypt::PostgreSQL - Module for generating encrypted password for PostgreSQL

Comments (edit)