Or, “No, You Can’t Use ‘Password2023’ Again, Even If It Was a Good Year!”
Welcome back, intrepid explorers of PostgreSQL security! In our previous adventures, we bravely tackled Part 1: The Guardians of the Gate – Unmasking PCI DSS Password Rules, set up our battle station in Part 2: The Environment Setup – Preparing for pgtle Glory, and then, in Part 3: The Enigma of Complexity – Taming Passwords with pgtle (Part 1) and Part 4: The Enigma of Complexity – Taming Passwords with pgtle (Part 2), we armed our database with formidable PCI DSS password complexity rules using pgtle. Your PostgreSQL passwords are now tougher than a two-dollar steak!
(Just need the quick code snippets and deployment steps for password reusability? Your Quick-Reference: Part 5 – Password Reusability awaits!)
If you’re here to conquer the entire saga, a true hero’s journey, then make sure to check out our Series Overview Page for all the other exciting chapters and quick-reference guides on PostgreSQL PCI DSS compliance.
Before we delve into the spectral realm of password history, a crucial transmission from command: While we’re charting a course through the perilous asteroid fields of PCI DSS password rules, remember this article is a technical showcase, not a Jedi Master’s consultancy advice or a blueprint for your Death Star’s production environment. We’re here to demonstrate the incredible power of pgtle and PostgreSQL’s hooking system – think of it as showing you how to build your own custom lightsaber, using the PCI DSS password requirements as our training dummy. This code has not been tested in any galaxy far, far away (i.e., a production environment), so wield it wisely and test rigorously in your own simulated battles!
Today, we face a new, insidious threat: the Ghost of Passwords Past! This spectral menace whispers tempting old passwords into users’ ears, encouraging them to recycle their digital keys. But fear not, for PCI DSS v4.0.1 has a rule for this, and our trusty pgtle is here to banish these specters forever, ensuring your PostgreSQL password history is clean and unique, bolstering your database security!
The PCI DSS Mandate: No Recycling! (PCI DSS 8.3.7 for Password Reusability)
Let’s revisit the sacred texts of PCI DSS v4.0.1. Requirement 8.3.7 is crystal clear on password reusability:
“Your new password must be different from the previous four passwords you’ve used.”
This isn’t just a suggestion; it’s a direct order from the Council of Card Brands. Why? Because if a villain (or a forgetful user) gets their hands on one of your old passwords, and you’re allowed to reuse it, they’ve got a golden ticket back into your system. This PCI DSS password rule forces users to constantly innovate their passwords, making it harder for attackers to gain persistent access using leaked credentials. It’s like ensuring your secret hideout always has a new, unique passphrase, not just “Swordfish” again. This is a critical aspect of PCI DSS password management.
The challenge, as always, is how to enforce this directly within PostgreSQL. Standard PostgreSQL doesn’t keep a password history for comparison. This is precisely where pgtle steps in, ready to become our digital historian and bouncer for PostgreSQL password security!
The pgtle Strategy: Our Digital Historian for Password History
Our strategy for battling password reusability hinges on extending our existing pgtle extension. We’ll use the same passcheck_hook that’s already doing a fantastic job with password complexity. The core idea for robust password history enforcement is simple:
- Store Password Hashes: For each user, we’ll maintain a history of their hashed passwords (never plaintext!). This password history will live in a new, dedicated table within our
password_checkschema. - Intercept and Compare: When a user attempts to set a new password, our
passcheck_hookwill:- Hash the new proposed password.
- Compare this new hash against the hashes stored in the user’s password history.
- If a match is found within the configured history limit (e.g., the last four entries as per PCI DSS 8.3.7), the password change is rejected, ensuring password reusability enforcement.
- Update History: If the new password is unique and passes all other checks (like password complexity), its hash will be added to the password history table.
- Prune Old Entries: To keep the password history tidy and compliant with the “previous four” rule, we’ll automatically remove the oldest entries beyond the required count. This is key for efficient PostgreSQL password management.
Database Design: The Password History Ledger for pgtle
We need a new table to meticulously record our password history. Let’s call it password_check.password_history.
username(TEXT): The name of the user.password_hash(TEXT): The hashed version of the password. Crucially, we will usecrypt()from thepgcryptoextension for strong password hashing.change_timestamp(TIMESTAMPTZ): When the password was changed, useful for ordering and pruning.
We’ll also add an index to username and change_timestamp for efficient lookups and ordering, enhancing PostgreSQL database security.
Additionally, to make our password reusability policy flexible, we’re adding a new column to our existing password_check.profiles table:
history_limit(INTEGER): This column will allow us to define, per role profile, how many previous passwords should be checked for reusability (e.g., 4 for standard users, or perhaps more for highly privileged accounts). This provides granular control over PCI DSS password rules.
Prerequisite: The pgcrypto Extension for Secure Hashing
To hash our passwords securely before storing them (and to compare new hashes with old ones), we’ll leverage PostgreSQL’s built-in pgcrypto extension, specifically its crypt() function. This is vital for robust PostgreSQL password security.
If you haven’t already, enable pgcrypto in your heydbamaint database:
-- Connect to your heydbamaint database
-- psql -d heydbamaint -U postgres
CREATE EXTENSION IF NOT EXISTS pgcrypto;
Updating Our pgtle Extension: Version 0.3 for Enhanced Security
Now, let’s create an update path for our pci_password_check_rules extension, moving from 0.2 to 0.3. This script will:
- Create the
password_check.password_historytable. - Add the
history_limitcolumn to thepassword_check.profilestable. - Modify the
passcheck_hookfunction to implement the password history check and management, along with enhanced password type handling.
The full SQL code for this update path:
SELECT
pgtle.install_update_path (
'pci_password_check_rules', -- Name of your custom pg_tle extension
'0.2', -- The version this update path is from
'0.3', -- The version this update path goes to (new version)
$_pgtle_$
CREATE TABLE IF NOT EXISTS password_check.password_history (
username TEXT NOT NULL,
password_hash TEXT NOT NULL,
change_timestamp TIMESTAMPTZ DEFAULT NOW() NOT NULL,
PRIMARY KEY (username, change_timestamp) -- Composite primary key for uniqueness and ordering
);
-- Create an index on username for efficient lookups when checking history.
CREATE INDEX IF NOT EXISTS idx_password_history_username ON password_check.password_history (username);
-- Add a column to the profiles table to control the password reusability (8.3.7)
ALTER TABLE password_check.profiles
ADD COLUMN IF NOT EXISTS history_limit INTEGER DEFAULT 4 NOT NULL;
-- The main passcheck hook function that enforces password policies.
CREATE
OR REPLACE FUNCTION password_check.passcheck_hook (
USERNAME TEXT,
PASSWORD TEXT,
PASSWORD_TYPE PGTLE.PASSWORD_TYPES,
VALID_UNTIL TIMESTAMPTZ,
VALID_NULL BOOLEAN
) RETURNS VOID AS $_FUNCTION_$ -- ADDED THIS BLOCK LABEL
DECLARE
invalid_pw_reason TEXT := '';
current_min_length INTEGER := 12;
current_require_special_char BOOLEAN := TRUE;
current_require_uppercase BOOLEAN := TRUE;
current_require_lowercase BOOLEAN := TRUE;
current_require_digit BOOLEAN := TRUE;
current_history_limit INTEGER := 4;
-- Add on 0.2: Flag to check if the user already exists in pg_roles
-- This helps differentiate between CREATE ROLE and ALTER ROLE.
user_exists BOOLEAN;
-- Variable to store the hashed version of the new password.
new_password_hashed TEXT;
-- Cursor to iterate through previous password hashes.
history_cursor CURSOR (cur_history_limit INTEGER) FOR
SELECT h.password_hash
FROM password_check.password_history h
WHERE h.username = passcheck_hook.username
ORDER BY h.change_timestamp DESC
LIMIT cur_history_limit; -- PCI DSS 8.3.7: must be different from previous four.
-- Variable to hold a hash from the history cursor.
old_password_hash TEXT;
-- Local variable for ambiguity resolution in INSERT/DELETE statements
_username_param TEXT;
BEGIN
-- Check if the user already exists in pg_roles.
-- This helps differentiate between CREATE ROLE and ALTER ROLE.
SELECT EXISTS (SELECT 1 FROM pg_catalog.pg_roles WHERE rolname = passcheck_hook.username)
INTO user_exists;
--RAISE NOTICE ' user_exists: %', user_exists;
-- 1. Determine Role-Based Policies (from password_check.profiles table)
-- For CREATE ROLE, we allow a default policy. For ALTER ROLE, we enforce role membership.
IF user_exists AND password_check.is_member_of_role(username, 'pci_admin_users') THEN
SELECT
min_length,
require_special_char,
require_uppercase,
require_lowercase,
require_digit,
history_limit
INTO
current_min_length,
current_require_special_char,
current_require_uppercase,
current_require_lowercase,
current_require_digit,
current_history_limit
FROM
password_check.profiles
WHERE
role='pci_admin_users';
ELSIF user_exists AND password_check.is_member_of_role(username, 'pci_app_users') THEN -- NOW FOR NON-HUMAN APP ACCOUNTS
SELECT
min_length,
require_special_char,
require_uppercase,
require_lowercase,
require_digit,
history_limit
INTO
current_min_length,
current_require_special_char,
current_require_uppercase,
current_require_lowercase,
current_require_digit,
current_history_limit
FROM
password_check.profiles
WHERE
role='pci_app_users';
ELSIF user_exists AND password_check.is_member_of_role(username, 'pci_standard_users') THEN
SELECT
min_length,
require_special_char,
require_uppercase,
require_lowercase,
require_digit,
history_limit
INTO
current_min_length,
current_require_special_char,
current_require_uppercase,
current_require_lowercase,
current_require_digit,
current_history_limit
FROM
password_check.profiles
WHERE
role='pci_standard_users';
--If the user does not exists (It's a CREATE ROLE), allow it to be created and set the default password rules.
--Later, the user will not be allowed to CHANGE THE PASSWORD if not set to any of the PCI roles.
ELSIF NOT user_exists THEN
SELECT
min_length,
require_special_char,
require_uppercase,
require_lowercase,
require_digit,
history_limit
INTO
current_min_length,
current_require_special_char,
current_require_uppercase,
current_require_lowercase,
current_require_digit,
current_history_limit
FROM
password_check.profiles
WHERE
role='pci_new_users';
RAISE NOTICE 'Policy: Default for NEW user (CREATE ROLE)';
RAISE NOTICE 'Assign a PCI ROLE to the user IMMEDIATELY';
ELSE
-- If the user exists but does not belong to any defined PCI role, prevent password change.
RAISE EXCEPTION 'Password change not allowed for user %: User must be assigned to one of the defined roles (pci_admin_users, pci_app_users, pci_standard_users).', username;
END IF;
-- 2. Apply Password Complexity Checks (PCI DSS 8.3.6 and 8.6.3)
-- These checks use the policy parameters determined by the user's role.
-- Check if the password type is PASSWORD_TYPE_PLAINTEXT. The Complexity of the password can only be checked if its not encrypted.
IF password_type != 'PASSWORD_TYPE_PLAINTEXT' THEN
-- If we want to prevent it from continuing, replace RAISE WARNING by RAISE EXCEPTION.
RAISE WARNING 'Password type % will not allow Complexity Checks (PCI DSS 8.3.6 and 8.6.3)', password_type;
END IF;
IF length(password) < current_min_length THEN
invalid_pw_reason := invalid_pw_reason || 'Password must be at least ' || current_min_length || ' characters long. ';
END IF;
IF current_require_uppercase AND password !~ '[A-Z]' THEN
invalid_pw_reason := invalid_pw_reason || 'Password must contain at least one uppercase letter. ';
END IF;
IF current_require_lowercase AND password !~ '[a-z]' THEN
invalid_pw_reason := invalid_pw_reason || 'Password must contain at least one lowercase letter. ';
END IF;
IF current_require_digit AND password !~ '[0-9]' THEN
invalid_pw_reason := invalid_pw_reason || 'Password must contain at least one number. ';
END IF;
IF current_require_special_char AND password !~ '[^a-zA-Z0-9\s]' THEN
invalid_pw_reason := invalid_pw_reason || 'Password must contain at least one special character. ';
END IF;
-- PASSWORD_TYPE_SCRAM_SHA_256 WILL NOT PREVENT from password reusability.
-- It generates a new salt every time a new password is set, what makes it impossible to compare with old passwords.
IF password_type in ('PASSWORD_TYPE_PLAINTEXT','PASSWORD_TYPE_MD5') THEN
-- Hash the new password using crypt() for secure comparison.
-- The gen_salt() function generates a new salt for each hash.
new_password_hashed := crypt(password, gen_salt('bf')); -- 'bf' for Blowfish, a strong algorithm
ELSE
-- This doesn't make any difference, but to allow the function to proceed.
new_password_hashed := password;
-- If we wish to prevent from PASSWORD_TYPE_SCRAM_SHA_256 to be used by the HOOK, just replace RAISE WARNING by RAISE EXCEPTION.
-- This doesn't affect the way postgresql stores the password, as it is controled by the postgresql.conf parameter password_encryption.
-- The password_type in the hook function only inform if the password was already encrypted before being stored. If the password is being updated via "ALTER USER" it will most certainly be PASSWORD_TYPE_PLAINTEXT or PASSWORD_TYPE_MD5. Tools like psql's \password will encrypt the password before passing it on, thus using PASSWORD_TYPE_SCRAM_SHA_256.
RAISE WARNING 'Password type % may not prevent password reusability (PCI DSS 8.3.7) or common/dictionary passwords (PCI DSS 8.3.5). Please enforce TEXT or MD5', password_type;
--RAISE EXCEPTION 'Password type % may not prevent password reusability (PCI DSS 8.3.7) or common/dictionary passwords (PCI DSS 8.3.5). Please enforce TEXT or MD5', password_type;
END IF;
-- 3. Apply Password Reusability Check (PCI DSS 8.3.7)
-- Only perform this check if the user already exists (i.e., it's an ALTER USER operation).
-- For CREATE USER, there's no history to check against yet.
IF user_exists THEN
OPEN history_cursor(current_history_limit);
LOOP
FETCH history_cursor INTO old_password_hash;
EXIT WHEN NOT FOUND;
-- Compare the new password hash with the old password hash.
-- crypt(password, old_password_hash) re-hashes 'password' using the salt from 'old_password_hash'
-- and compares it to 'old_password_hash'. This is the standard way to verify passwords with crypt().
IF crypt(password, old_password_hash) = old_password_hash THEN
invalid_pw_reason := invalid_pw_reason || format('Password cannot be one of the previous %1$s passwords. ', current_history_limit);
EXIT; -- No need to check further if a match is found
END IF;
END LOOP;
CLOSE history_cursor;
END IF;
-- 4. Final Check and Raise Exception
-- If any validation failed, raise an exception to prevent the password change.
IF invalid_pw_reason != '' THEN
RAISE EXCEPTION 'Password validation failed for user %: %', username, invalid_pw_reason;
ELSE
-- Assign the parameter to the local variable for safe insertion/deletion
_username_param := username;
-- 5. Update Password History (only if validation passed)
-- If the password change is allowed, record the new password's hash in the history.
-- This ensures we maintain the history for future reusability checks.
INSERT INTO password_check.password_history (username, password_hash)
VALUES (_username_param, new_password_hashed);
-- 6. Prune Old Password History (keep only the last 4 + 1 for the current new one, so 5 total)
-- This keeps the password_history table clean and adheres to the "previous four" requirement.
-- Delete older entries for this user, keeping only the most recent 'history_limit'
DELETE FROM password_check.password_history ph_old
WHERE ph_old.username = _username_param
AND ph_old.change_timestamp < (
SELECT ph_latest.change_timestamp
FROM password_check.password_history ph_latest
WHERE ph_latest.username = _username_param
ORDER BY ph_latest.change_timestamp DESC
OFFSET current_history_limit
LIMIT 1
);
END IF;
END;
$_FUNCTION_$ LANGUAGE PLPGSQL;
-- Revoke and grant execute privileges to ensure the function can be called by pg_tle.
REVOKE ALL ON FUNCTION password_check.passcheck_hook(TEXT, TEXT, pgtle.password_types, TIMESTAMPTZ, BOOLEAN) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION password_check.passcheck_hook(TEXT, TEXT, pgtle.password_types, TIMESTAMPTZ, BOOLEAN) TO PUBLIC;
-- Register the updated passcheck_hook function with pg_tle.
SELECT pgtle.register_feature_if_not_exists('password_check.passcheck_hook', 'passcheck');
$_pgtle_$
);
New Features and Logic Explained (Version 0.3 Changes for pgtle):
This 0.3 update significantly enhances the passcheck_hook to fully implement PCI DSS 8.3.7 and improve robustness in PostgreSQL password security.
- New
password_check.password_historytable:- Purpose: This table is the core of our password reusability check. It stores the Blowfish-hashed versions of each user’s previous passwords along with a timestamp. This allows the
passcheck_hookto look back and compare new passwords against historical ones, ensuring PCI DSS password history requirements are met. - Code Snippet:
CREATE TABLE IF NOT EXISTS password_check.password_history ( username TEXT NOT NULL, password_hash TEXT NOT NULL, change_timestamp TIMESTAMPTZ DEFAULT NOW() NOT NULL, PRIMARY KEY (username, change_timestamp) ); CREATE INDEX IF NOT EXISTS idx_password_history_username ON password_check.password_history (username);
- Purpose: This table is the core of our password reusability check. It stores the Blowfish-hashed versions of each user’s previous passwords along with a timestamp. This allows the
- New
history_limitcolumn inpassword_check.profiles:- Purpose: This column, added to the
profilestable, allows you to configure the number of past passwords to check for reusability per role. For example,pci_admin_usersmight have ahistory_limitof 10, whilepci_standard_usersmight have the PCI-mandated 4. This adds crucial flexibility to your PostgreSQL password policies and PCI DSS compliance. - Code Snippet:
ALTER TABLE password_check.profiles ADD COLUMN IF NOT EXISTS history_limit INTEGER DEFAULT 4 NOT NULL;And within the
passcheck_hook(example forpci_admin_users):SELECT min_length, require_special_char, require_uppercase, require_lowercase, require_digit, history_limit -- This line is new INTO current_min_length, current_require_special_char, current_require_uppercase, current_require_lowercase, current_require_digit, current_history_limit -- This line is new FROM password_check.profiles WHERE role='pci_admin_users';
- Purpose: This column, added to the
new_password_hashedvariable:- Purpose: This variable is introduced to store the Blowfish hash of the new password being set (
crypt(password, gen_salt('bf'))). This calculated hash is then used efficiently for both the reusability comparison and for inserting the new password into thepassword_check.password_historytable, optimizing PostgreSQL password management. - Code Snippet:
DECLARE -- ... other declarations ... new_password_hashed TEXT; -- Declaration -- ... BEGIN -- ... IF password_type in ('PASSWORD_TYPE_PLAINTEXT','PASSWORD_TYPE_MD5') THEN new_password_hashed := crypt(password, gen_salt('bf')); -- Assignment ELSE new_password_hashed := password; -- Fallback for non-comparable types END IF; -- ... INSERT INTO password_check.password_history (username, password_hash) VALUES (_username_param, new_password_hashed); -- Usage
- Purpose: This variable is introduced to store the Blowfish hash of the new password being set (
history_cursorandold_password_hashvariables:- Purpose: These variables facilitate the efficient traversal of the
password_check.password_historytable. Thehistory_cursoris a parameterized cursor that fetches the most recentcurrent_history_limitpasswords for the user, andold_password_hashholds each historical hash as it’s iterated. This is key for the password reusability check. - Code Snippet:
DECLARE -- ... other declarations ... history_cursor CURSOR (cur_history_limit INTEGER) FOR SELECT h.password_hash FROM password_check.password_history h WHERE h.username = passcheck_hook.username ORDER BY h.change_timestamp DESC LIMIT cur_history_limit; -- Cursor declaration old_password_hash TEXT; -- Variable to hold fetched hash -- ... BEGIN -- ... OPEN history_cursor(current_history_limit); LOOP FETCH history_cursor INTO old_password_hash; -- Fetching loop EXIT WHEN NOT FOUND; -- ... comparison logic ... END LOOP; CLOSE history_cursor; -- ...
- Purpose: These variables facilitate the efficient traversal of the
password_typechecks for Reusability and Complexity:- Complexity Check: A new check is added before the complexity checks. This warns the administrator if the password type is not plaintext, as password complexity can only be reliably checked against the raw password string.
- Reusability Check: The password reusability check now explicitly verifies the
password_type. This is critical becausecrypt()(which we use for comparison) expects a plaintext password or an MD5 hash that can be re-hashed. If thepassword_typeisPASSWORD_TYPE_SCRAM_SHA_256, the hook will issue a warning (or could be configured to raise an exception) because SCRAM-SHA-256 hashes generate a new salt every time, making directcrypt()comparisons impossible for reusability. This ensures that only reliably comparable password types are subjected to the history check, maintaining the integrity of the PCI DSS password reusability policy. - Code Snippet (Complexity Check):
IF password_type != 'PASSWORD_TYPE_PLAINTEXT' THEN RAISE WARNING 'Password type % will not allow Complexity Checks (PCI DSS 8.3.6 and 8.6.3)', password_type; END IF; - Code Snippet (Reusability Check):
IF password_type in ('PASSWORD_TYPE_PLAINTEXT','PASSWORD_TYPE_MD5') THEN new_password_hashed := crypt(password, gen_salt('bf')); -- 'bf' for Blowfish, a strong algorithm ELSE new_password_hashed := password; -- This doesn't make any difference, but to allow the function to proceed. RAISE WARNING 'Password type % may not prevent password reusability (PCI DSS 8.3.7) or common/dictionary passwords (PCI DSS 8.3.5). Please enforce TEXT or MD5', password_type; END IF;
current_history_limitvariable:- Purpose: This local variable dynamically stores the
history_limitvalue fetched from thepassword_check.profilestable based on the user’s role. This ensures the password reusability check respects the per-role configuration, aligning with PCI DSS password rules. - Code Snippet:
DECLARE -- ... other declarations ... current_history_limit INTEGER := 4; -- Declaration with default -- ... BEGIN -- ... (within profile fetching queries, e.g., for pci_admin_users) SELECT -- ... other fields ... history_limit INTO -- ... other fields ... current_history_limit -- Assignment from profile FROM password_check.profiles WHERE role='pci_admin_users'; -- ... OPEN history_cursor(current_history_limit); -- Usage in cursor -- ... invalid_pw_reason := invalid_pw_reason || format('Password cannot be one of the previous %1$s passwords. ', current_history_limit); -- Usage in error message -- ... OFFSET current_history_limit -- Usage in pruning DELETE statement LIMIT 1 -- ...
- Purpose: This local variable dynamically stores the
- Modified profile fetching queries:
- The
SELECT ... INTOstatements that retrieve policy parameters frompassword_check.profilesnow also includehistory_limit, populating thecurrent_history_limitvariable. (Refer to the code snippet provided forhistory_limitin point 2).
- The
_username_paramlocal variable:- Purpose: Introduced to explicitly pass the
usernameparameter toINSERTandDELETEstatements, resolving potential ambiguity within the PL/pgSQL function’s scope and ensuring correct data manipulation for password history records. - Code Snippet:
DECLARE -- ... other declarations ... _username_param TEXT; -- Declaration BEGIN -- ... IF invalid_pw_reason != '' THEN -- ... ELSE _username_param := username; -- Assignment INSERT INTO password_check.password_history (username, password_hash) VALUES (_username_param, new_password_hashed); -- Usage in INSERT DELETE FROM password_check.password_history ph_old WHERE ph_old.username = _username_param -- Usage in DELETE AND ph_old.change_timestamp < ( SELECT ph_latest.change_timestamp FROM password_check.password_history ph_latest WHERE ph_latest.username = _username_param -- Usage in subquery -- ... ); END IF; END;
- Purpose: Introduced to explicitly pass the
- Pruning Old Password History:
- The
DELETEstatement for pruning history is refined to keep exactlycurrent_history_limit(plus the newly added password, making itcurrent_history_limit + 1total in the table for a brief moment before pruning) entries for each user, ensuring strict adherence to the PCI DSS 8.3.7 requirement for password history. - Code Snippet:
DELETE FROM password_check.password_history ph_old WHERE ph_old.username = _username_param AND ph_old.change_timestamp < ( SELECT ph_latest.change_timestamp FROM password_check.password_history ph_latest WHERE ph_latest.username = _username_param ORDER BY ph_latest.change_timestamp DESC OFFSET current_history_limit LIMIT 1 );
- The
Installation/Update: Unleashing the History Keeper!
Ready to make your PostgreSQL database remember those pesky old passwords? Follow these steps to apply the Version 0.3 update:
- Ensure
pgcryptois Installed: Connect to yourheydbamaintdatabase as a superuser and run:CREATE EXTENSION IF NOT EXISTS pgcrypto; - Connect to Your Maintenance Database: Open your
psqlclient and connect to the maintenance database (heydbamaint) as a superuser (postgres).psql -d heydbamaint -U postgres - Execute the Update Path Code: Copy the entire SQL code from the pci_password_check_rules_0.3.sql – Password Reusability Enforcement Code document and paste it into your
psqlprompt, then press Enter. This will define the update path.If you prefer to run it from a file: Save the code to a.sqlfile (e.g.,update_pci_password_rules_v0.3.sql) and then run:psql -d heydbamaint -U postgres -f update_pci_password_rules_v0.3.sql - Apply the Extension Update: Now that the update path is defined, tell your extension to upgrade!
ALTER EXTENSION pci_password_check_rules UPDATE TO '0.3';
Verification: Did It Work, Captain?
After applying the update, let’s confirm everything is shipshape:
-- Check all available pg_tle extensions (should show 0.3 as default_version)
SELECT * FROM pgtle.available_extensions();
-- Check all available versions for your extension
SELECT * FROM pgtle.available_extension_versions();
-- Check the update paths defined for your specific extension
SELECT * FROM pgtle.extension_update_paths('pci_password_check_rules');
-- Verify the new history table exists and is empty (or has initial entries if you had some)
SELECT * FROM password_check.password_history;
-- Verify the history_limit column was added to profiles
SELECT * FROM password_check.profiles;You should see pci_password_check_rules listed with 0.3 as its default_version.
Testing Your New History Keeper!
Time to put our new password reusability check to the test!
Scenario: Standard User test_user
- Create a new user (if you don’t have one):
CREATE ROLE test_user WITH PASSWORD 'StrongP@ssw0rd1!'; GRANT pci_standard_users TO test_user;This should succeed, and its hash will be in
password_check.password_history. - Change password (unique, should succeed):
ALTER ROLE test_user WITH PASSWORD 'V3ryStr0ngP@ssw0rd2!';This should succeed. The history for
test_userwill now have two entries. - Change password again (unique, should succeed):
ALTER ROLE test_user WITH PASSWORD 'Sup3rDuperP@ssw0rd3!';This should succeed. History will have three entries.
- Change password yet again (unique, should succeed):
ALTER ROLE test_user WITH PASSWORD 'Ult1mateP@ssw0rd4!';This should succeed. History will have four entries.
- Attempt to reuse the first password (should fail – it’s one of the previous four):
ALTER ROLE test_user WITH PASSWORD 'StrongP@ssw0rd1!';Expected Error:
Password validation failed for user test_user: Password cannot be one of the previous 4 passwords. - Attempt to reuse the second password (should fail):
ALTER ROLE test_user WITH PASSWORD 'V3ryStr0ngP@ssw0rd2!';Expected Error:
Password validation failed for user test_user: Password cannot be one of the previous 4 passwords. - Set a new, unique password to clear the history for the next cycle:
ALTER ROLE test_user WITH PASSWORD 'TotallyN3wP@ssw0rd5!';This should succeed. The history will now contain the latest five entries, effectively rotating out the oldest one beyond the
history_limit.
You’ve successfully taught your PostgreSQL database to remember and reject old, recycled passwords! The Ghost of Passwords Past has been vanquished (at least for the last four iterations, or whatever your history_limit is set to).
The Adventure Continues…
With password complexity and reusability under control, your PostgreSQL fortress is becoming truly impenetrable! But our quest isn’t over yet. In the next thrilling installment, we’ll dive into managing password change frequency and dealing with those pesky inactive accounts. Stay tuned for Part 6: The Sands of Time – Enforcing Change Frequency and Dealing with Inactive Accounts!
