Simplifying User Management in PostgreSQL with SCRAM-SHA-256 Authentication

As a Database Administrator (DBA), I receive countless requests daily to create new user requests. Managing these requests efficiently while adhering to best security practices can be challenging, especially when it comes to keeping secrets secure.

Inspired by Jonathan Kraft's video on "Protect your PostgreSQL Passwords: How SCRAM works and why you need it" (https://www.youtube.com/watch?v=20p4zP_pvQU&list=WL&t=1355s), I decided to share a simple Python script that can help streamline this process while ensuring passwords remain secret.

The context is to avoid writing down the clear text password on the command line, the psql client, and any other GUI you may use. You can use the \password meta-command, which demands creating a new user and collecting the generated password from the pg_catalog.pg_authid table, and drop the user to create a new one.

What do you think about having a side script to generate a calculated hash for you?

In PostgreSQL, using SCRAM-SHA-256 for password encryption is a robust method to secure user credentials. To use this approach effectively, you must ensure the PostgreSQL parameter password_encryption is set to scram-sha-256. With this configuration, user passwords are stored in a hashed format, making them significantly more secure against breaches.

Below is a Python script that generates the SCRAM-SHA-256 hash for a given password. This hash can be used directly when creating new users, eliminating the need to handle plaintext passwords.

import hashlib
import hmac
import base64
import os
import getpass

class SCRAMSHA256Hasher:

    def init(self, password, salt=None, iterations=4096):
        self.password = password
        self.salt = salt if salt else os.urandom(16)  # Generate random salt if not provided
        self.iterations = iterations

    def generatesalted_password(self):
        return hashlib.pbkdf2_hmac('sha256', self.password.encode(), self.salt, self.iterations)

    def generateclient_key(self, salted_password):
        return hmac.new(salted_password, b"Client Key", hashlib.sha256).digest()

    def generateserver_key(self, salted_password):
        return hmac.new(salted_password, b"Server Key", hashlib.sha256).digest()

    def compute_scram_hash(self):
        salted_password = self._generate_salted_password()
        client_key = self._generate_client_key(salted_password)
        stored_key = hashlib.sha256(client_key).digest()
        server_key = self._generate_server_key(salted_password)
        salt_b64 = base64.b64encode(self.salt).decode()
        stored_key_b64 = base64.b64encode(stored_key).decode()
        server_key_b64 = base64.b64encode(server_key).decode()
        return f"SCRAM-SHA-256${self.iterations}:{salt_b64}${stored_key_b64}:{server_key_b64}"

# Create the object and generate the hash
if name == "__main__":
    password = getpass.getpass("Enter your password: ")
    hasher = SCRAMSHA256Hasher(password)
    scram_hash = hasher.compute_scram_hash()
    print(f"\nSCRAM-SHA-256 Hash:\n{scram_hash}")        

How It Works

1. Password Hashing: The script uses the PBKDF2-HMAC-SHA256 algorithm to generate a salted password hash.

2. Client and Server Keys: These keys are derived using HMAC-SHA256, ensuring secure authentication mechanisms.

3. Base64 Encoding: Outputs are formatted according to PostgreSQL's expected SCRAM-SHA-256 format.

Why Use This Script?

This script eliminates the need to handle plaintext passwords directly when creating PostgreSQL users. By generating the SCRAM-SHA-256 hash beforehand, you can securely pass the hash to PostgreSQL with a CREATE USER or ALTER USER command, as shown below:

$ main.py 
Enter your password: 
SCRAM-SHA-256 Hash:
SCRAM-SHA-256$4096:isQe28N9yA/eQr4QWjyzoA==$PmPFm4bJNcYQExZo1TRkvtD/51yz5QunicKaWr1k97k=:ofaxIJJJvGEbgtoFvzr6nOnrKnEthVm+q9E9ACUlUyQ=

CREATE USER jim 
WITH PASSWORD 'SCRAM-SHA-256$4096:isQe28N9yA/eQr4QWjyzoA==$PmPFm4bJNcYQExZo1TRkvtD/51yz5QunicKaWr1k97k=:ofaxIJJJvGEbgtoFvzr6nOnrKnEthVm+q9E9ACUlUyQ=';

[BIANCHI.LABS] postgres@n1: ~ $ psql -U jim -W -h `hostname -i` -d template1
Password:
psql (16.4)
Type "help" for help.

template1=> select current_user;
 current_user
--------------
 jim
(1 row)        

???? Of course, the main idea is the security of the process, but you need to work out the ROLE and its permissions better and use the statement IN ROLE so you can add the user in a group to INHERIT Set privileges.

?? Another aspect of daily work is that most DBAs add the user password or secret to a Hashicorp Vault and share it with the development groups to avoid password exposure.

?? Here, we start thinking: Why create users manually when we can request Dynamic Credentials to the Hashicorp Vault? This is food for thought, folks.

Final Thoughts

With this script, you can simplify user creation requests while maintaining robust security practices. Thank you, Jonathan Kraft, for the insightful video that inspired this article! Please let me know your thoughts and if this script helps you in your daily DBA tasks.

Leave your comments, thanks. ??♂?