Part 6: The Sands of Time – Enforcing Change Frequency

Or, “Your Password Has Expired. This Is Not The Password You Are Looking For.”

Read the full article here: https://hey-dba.com/articles/part-6-the-sands-of-time-enforcing-change-frequency/

Welcome back, time-traveling guardians of PostgreSQL security! We’ve journeyed through the intricate mazes of Part 1: The Guardians of the Gate – Unmasking PCI DSS Password Rules, prepared our battle station in Part 2: The Environment Setup – Preparing for pgtle Glory, conquered the beast of password complexity in Part 3 and Part 4, and even banished the spectral menace of password reusability in Part 5. Your PostgreSQL database is becoming a fortress worthy of the Galactic Empire (but, you know, for good).

(Just need the quick code snippets and deployment steps for password expiration? Your Quick-Reference: Part 6 – Change Frequency 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 embark on this temporal mission, 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 confront the relentless march of time. Passwords, like old droids, can grow stale and vulnerable if left unchanged. Fear not, for PCI DSS v4.0.1 has rules for these temporal threats, and our trusty pgtle is ready to enforce them, ensuring your PostgreSQL password management is as timely as a hyperdrive jump! This is paramount for robust PostgreSQL security and overall PCI DSS compliance.

The PCI DSS Mandate: Time Waits for No Password (PCI DSS 8.3.9 & 8.6.3)

Let’s consult the sacred scrolls of PCI DSS v4.0.1. Two requirements stand out in our temporal battle for password security:

• Requirement 8.3.9: Change user passwords/passphrases at least every 90 days.Why? Even the strongest password can eventually be compromised through various means (e.g., brute-force attacks, credential stuffing, phishing). Regular changes reduce the window of opportunity for an attacker using a compromised password. It’s like changing the access codes to your secret base before the Imperials figure out the old ones. This is a core aspect of password change frequency and password expiration policies.
• Requirement 8.6.3: Application and System Accounts Password Changes.Why? Non-human accounts (like those used by applications or services) often have broad privileges and are less frequently monitored. While they don’t typically log in interactively, their credentials must still be managed securely to prevent long-term compromise.Mandate: Passwords for these accounts must be protected against misuse and changed periodically (at least once a year is a best practice), and upon suspicion or confirmation of compromise. This ensures application account security and proper password lifecycle management.

Standard PostgreSQL roles have a rolvaliduntil property, but this only defines an expiration date for the role itself, not a forced password change interval. This is where our pgtle extension will once again prove its worth, helping us implement these critical PostgreSQL security measures by enforcing the VALID UNTIL clause for strict PCI DSS compliance.

The pgtle Strategy: Our Temporal Guardian for Password Expiration

To enforce these time-based password policies, our pgtle strategy will involve:

1. Defining Maximum Validity: We’ll introduce a max_validity_interval in our password_check.profiles table to specify the maximum allowed lifespan for passwords per role (e.g., 90 days for human users, 1 year for application accounts). This sets the ‘shelf life’ for your digital credentials, ensuring they don’t become stale like forgotten rations on a long space journey. This is key for password expiration management.
2. Leveraging VALID UNTIL (in passcheck_hook): We will enforce that the VALID UNTIL clause, used when creating or altering a user’s password, adheres to the max_validity_interval defined for their role. This directly utilizes PostgreSQL’s native password expiration mechanism and ensures all passwords have a defined expiration date. This ensures that every new password set is like a freshly issued security clearance, with a clear expiration date, preventing any attempts to bypass the system with an ‘eternal’ password.
3. Enforcing Password Expiration on Login (via clientauth_hook): This is the crucial piece! We will introduce a new pgtle hook (clientauth_hook) that fires every time a user attempts to log in. This hook will check the password’s validity period (which was set by the passcheck_hook) and prevent login if the password has expired, forcing the user to change it. If NOW() has surpassed this critical date, it’s like the password’s ‘self-destruct sequence’ has activated, and access is denied until a new, compliant password is provided. This provides robust login enforcement for password change frequency.

It’s important to note a key distinction here: PostgreSQL’s native VALID UNTIL clause (or rolvaliduntil) sets an expiration date for the role’s ability to log in. While useful, it can be extended by an ALTER ROLE ... VALID UNTIL command without a new password being set. This means the password itself might not have been updated, which is what PCI DSS 8.3.9 and 8.6.3 truly require. Our pgtle strategy overcomes this by:

• Ensuring the VALID UNTIL date is always set compliantly when a password is created or changed (via passcheck_hook). This is our proactive password policy enforcement.
• Critically, the clientauth_hook then checks the actual password’s valid_until date stored in our password_check.password_history table at every login attempt. This ensures that even if rolvaliduntil was manually manipulated, the user will still be forced to provide a new password once the valid_until date associated with their last password change has passed. This guarantees the password itself is updated, directly meeting the PCI DSS mandate for password change frequency.

Database Design Changes (Version 0.4)

To track these temporal elements and support our password expiration logic, we’ll enhance our existing tables. This is vital for maintaining database security and PCI DSS compliance.

Add max_validity_interval to password_check.profiles:

• This column will define the maximum allowed duration for a password’s validity for each role (e.g., ’90 days’ for standard users, ‘1 year’ for application users). For the pci_new_users role, this is specifically set to ’15 minutes’ to ensure new users are assigned to a proper profile role within that short timeframe, otherwise, their account will not allow a login. Think of it as setting the ‘shelf life’ for your digital credentials, ensuring they don’t become stale like forgotten rations on a long space journey. This is key for password expiration management.
• Snippet:

-- In the pgtle.install_extension_version_sql block:
ALTER TABLE password_check.profiles
  ADD COLUMN IF NOT EXISTS max_validity_interval INTERVAL DEFAULT '90 days';

-- Example updates for different roles:
UPDATE password_check.profiles SET max_validity_interval = '1 year' WHERE role = 'pci_app_users';
UPDATE password_check.profiles SET max_validity_interval = '30 days' WHERE role = 'pci_admin_users';
UPDATE password_check.profiles SET max_validity_interval = '15 minutes' WHERE role = 'pci_new_users'; -- For testing        

Add valid_until to password_check.password_history:

• This column will store the calculated expiration date for each password entry in the history, which is essential for the clientauth_hook to check password age on login. This valid_until date becomes our digital timestamp in the chronicles of password changes, crucial for our login gatekeeper. This ensures accurate password lifecycle tracking.
• Snippet:

ALTER TABLE password_check.password_history
  ADD COLUMN IF NOT EXISTS valid_until TIMESTAMPTZ;

-- Update existing history records with a calculated valid_until based on current profiles
UPDATE password_check.password_history ph
SET
  valid_until = ph.change_timestamp + (
  WITH profiles AS (
    SELECT
	  CASE p.role
	    WHEN 'pci_new_users' then 1
	    WHEN 'pci_admin_users' then 2
		WHEN 'pci_app_users' then 3
		WHEN 'pci_standard_users' then 4
	  END as priority,
	  p.role,
	  p.max_validity_interval
	FROM
	  password_check.profiles p
	ORDER BY 1
  ),
  members as (
    SELECT
      r_member.rolname as member,
      r_role.rolname as role,
	  pr.max_validity_interval,
      ROW_NUMBER() OVER (PARTITION BY r_member.rolname ORDER BY pr.priority) as rn
    FROM pg_catalog.pg_roles AS r_member
    JOIN pg_catalog.pg_auth_members AS am ON r_member.oid = am.member
    JOIN pg_catalog.pg_roles AS r_role ON am.roleid = r_role.oid
    JOIN profiles pr ON pr.role = r_role.rolname
  )
  SELECT
	m.max_validity_interval
  FROM
    members m
  WHERE
    rn=1 AND
	m.member = ph.username
);

-- Ensure any remaining NULLs (e.g., for users not in a PCI role yet) get a default validity
UPDATE password_check.password_history ph
SET
  valid_until = ph.change_timestamp + '15 minutes'::INTERVAL -- A small default for safety
WHERE
  valid_until IS NULL;

-- Make the column NOT NULL after populating
ALTER TABLE password_check.password_history
  ALTER COLUMN valid_until SET NOT NULL;        

Introducing the clientauth_hook: Your Login Gatekeeper for Account Security

Before we dive into the Version 0.4 code, let’s take a moment to understand the clientauth_hook. This is a powerful feature provided by pgtle that allows you to intercept and control client connection attempts before PostgreSQL’s standard authentication methods are even evaluated. This is where the clientauth_hook truly becomes the bouncer at the digital cantina, checking IDs and ensuring only those with valid, unexpired credentials get past the velvet rope. This is fundamental for robust account security and login enforcement.

• What it is: The clientauth_hook is a pgtle extension point that fires for every incoming connection attempt to your PostgreSQL server. It’s like a sentry at the gate of your database security.
• What it does: It provides your custom pgtle function with details about the connection (like username, database, remote host) and allows you to either permit the connection to proceed or reject it with a custom error message. This gives you fine-grained control over user authentication.
• Its purpose in our quest: For Part 6, we’re leveraging the clientauth_hook to enforce password expiration. By checking the valid_until date of a user’s password at the moment of login, we can prevent access if the password has expired, thereby forcing the user to change their password to regain entry. This is crucial for meeting PCI DSS 8.3.9 and 8.6.3, as it ensures passwords are actively refreshed and PostgreSQL password management remains compliant.

Updating Our pgtle Extension: Version 0.4 for Enhanced Password Policy

Now, let’s create an update path for our pci_password_check_rules extension, moving from 0.3 to 0.4. This script will:

1. Add the max_validity_interval column to the password_check.profiles table.
2. Add the valid_until column to password_check.password_history and populate it.
3. Modify the passcheck_hook function to:Retrieve the max_validity_interval for the user’s role.Enforce that the VALID UNTIL clause (passed to the hook) is not NULL and does not exceed the max_validity_interval.Include the calculated valid_until in the password_check.password_history INSERT statement.
4. Create the clientauth_hook to prevent login if the password’s validity has expired.

The full SQL code for this update path is provided below:

Full SQL code Below.        

Full code Here:

New Features and Logic Explained (Version 0.4 Changes):

This 0.4 update significantly enhances password validity enforcement by leveraging both passcheck_hook and the new clientauth_hook, solidifying our PostgreSQL security posture.

New max_validity_interval column in password_check.profiles:

• Purpose: Defines the maximum lifespan of a password for users belonging to a specific role. This allows administrators to set different ‘mission parameters’ for password lifespans (e.g., 90 days for human users, 1 year for application users), directly addressing PCI DSS 8.3.9 and the periodic change aspect of PCI DSS 8.6.3. This is critical for effective password policy management.
• Code Snippet: (Refer to the “Database Design Changes” section above for the ALTER TABLE and UPDATE snippets.) And within the passcheck_hook (example for pci_admin_users):

SELECT
  -- ... existing fields ...
  history_limit,
  max_validity_interval -- This line is new
INTO
  -- ... existing variables ...
  current_history_limit,
  current_max_validity_interval -- This line is new
FROM
  password_check.profiles
WHERE
  role='pci_admin_users';        

Enforcing Password Validity (PCI DSS 8.3.9 & 8.6.3) via VALID UNTIL in passcheck_hook:

• Purpose: The passcheck_hook now checks the valid_until parameter passed to it during password creation/change. It ensures that:

1. valid_until is not set to NULL (all passwords must expire).
2. The specified valid_until date is not beyond the current_max_validity_interval for the user’s role. This prevents setting excessively long password lifetimes. This ensures that every new password set is like a freshly issued security clearance, with a clear expiration date, preventing any attempts to bypass the system with an ‘eternal’ password. This is our proactive password policy enforcement. This mechanism directly enforces password expiration at the point of creation or modification.
3. Code Snippet:

DECLARE
  -- ...
  current_max_validity_interval INTERVAL;
  -- ...
BEGIN
  -- ... (after fetching profile parameters) ...

  -- Ensure the account is not created/updated with "VALID UNTIL NULL"
  IF valid_null THEN
    invalid_pw_reason := invalid_pw_reason || 'New user password must have a "VALID UNTIL" date. "VALID UNTIL NULL" is not allowed.';
  -- Ensure the "VALID UNTIL" clause is not specified above the maximum value for the role.
  ELSE
    IF valid_until > (NOW() + current_max_validity_interval) THEN
      invalid_pw_reason := invalid_pw_reason || 'Account validity date cannot be more than ' || current_max_validity_interval || ' in the future for this role. ';
    END IF;
  END IF;
  -- ... (rest of the hook logic) ...

  -- Include the valid_until in the password history insert
  INSERT INTO password_check.password_history (username, password_hash, valid_until)
  VALUES (_username_param, new_password_hashed, NOW() + current_max_validity_interval);        

• New clientauth_hook for Login Enforcement (PCI DSS 8.3.9 & 8.6.3):Purpose: This is the critical addition that enforces password expiration on login. When a user attempts to authenticate, this hook checks the valid_until date of their most recent password (from password_check.password_history). If NOW() is past this valid_until date, the login attempt is rejected with an exception, forcing the user to change their password to regain access. If NOW() has surpassed this critical date, it’s like the password’s ‘self-destruct sequence’ has activated, and access is denied until a new, compliant password is provided. This ensures continuous account security and PCI DSS compliance.Code Snippet:CREATE OR REPLACE FUNCTION password_check.clientauth_hook( port pgtle.clientauth_port_subset, status INTEGER ) RETURNS VOID AS $$ DECLARE l_username TEXT := port.user_name; current_valid_until TIMESTAMPTZ; BEGIN -- Check the valid_until date from password_check.password_history for the current account. SELECT ph.valid_until INTO current_valid_until FROM password_check.password_history ph WHERE ph.username = l_username ORDER BY change_timestamp DESC LIMIT 1; IF FOUND THEN IF NOW() > current_valid_until THEN RAISE EXCEPTION 'The password has expired, please contact the admin.'; END IF; END IF; END; $$ LANGUAGE plpgsql SECURITY DEFINER; -- Register the clientauth hook. REVOKE ALL ON FUNCTION password_check.clientauth_hook(pgtle.clientauth_port_subset, INTEGER) FROM PUBLIC; GRANT EXECUTE ON FUNCTION password_check.clientauth_hook(pgtle.clientauth_port_subset, INTEGER) TO PUBLIC; SELECT pgtle.register_feature_if_not_exists('password_check.clientauth_hook', 'clientauth');

Installation/Update: The Time-Warp Protocol for PostgreSQL Security!

To bring your pgtle extension to Version 0.4 and enable these temporal defenses, you’ll need to execute the update path and then enable the clientauth hook globally. This is a crucial step for PostgreSQL security and PCI DSS compliance.

1. Connect to Your Maintenance Database: Open your psql client and connect to the maintenance database (heydbamaint) as a superuser (postgres).psql -d heydbamaint -U postgres
2. Execute the Update Path Code: Copy the entire SQL code from the pci_password_check_rules_0.4_up_0.3-0.4.sql document and paste it into your psql prompt, then press Enter. This will define the update path.If you prefer to run it from a file: Save the code to a .sql file (e.g., update_pci_password_rules_v0.4.sql) and then run:psql -d heydbamaint -U postgres -f update_pci_password_rules_v0.4.sql
3. 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.4';
4. Enable clientauth Hook (Crucial!): These steps enable the clientauth hook globally. Remember to exclude superusers like postgres to prevent accidental lockout. This is a critical pgtle configuration step for login enforcement.-- Skip 'postgres' user from clientauth hook checks ALTER SYSTEM SET pgtle.clientauth_users_to_skip TO 'postgres'; SELECT pg_catalog.pg_reload_conf(); -- Apply this setting without a full restart -- Enable the clientauth hook globally ALTER SYSTEM SET pgtle.enable_clientauth TO 'on'; -- IMPORTANT: This setting requires a database restart to take full effect. -- Context: SIGHUP. Note: A database restart is needed to enable the clientauth feature, i.e. to switch from off to on or require -- You will need to restart your PostgreSQL cluster for this to be active.

Verification: Did the Time-Warp Succeed for your PostgreSQL Password Management?

After applying the update and restarting your PostgreSQL cluster, let’s confirm everything is shipshape:

-- Check all available pg_tle extensions (should show 0.4 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 max_validity_interval column was added to profiles
SELECT * FROM password_check.profiles;

-- Verify the valid_until column was added to password_history and is NOT NULL
SELECT username, password_hash, change_timestamp, valid_until FROM password_check.password_history LIMIT 5;

-- Verify clientauth is enabled and postgres is skipped
SELECT name, setting, short_desc, context FROM pg_settings WHERE name LIKE 'pgtle%';        

You should see pci_password_check_rules listed with 0.4 as its default_version, and pgtle.enable_clientauth as on, with pgtle.clientauth_users_to_skip set to postgres. This confirms your PostgreSQL security setup is working as intended.

Testing Your Temporal Defenses!

Time to put your new password validity policies to the test! These scenarios will demonstrate your enhanced PostgreSQL password management and adherence to PCI DSS password rules.

Scenario 1: Enforcing VALID UNTIL (PCI DSS 8.3.9 & 8.6.3) via passcheck_hook

1. Set max_validity_interval for pci_standard_users: Let’s use a very short interval for quick testing, e.g., ‘1 day’.UPDATE password_check.profiles SET max_validity_interval = '1 day' WHERE role = 'pci_standard_users'; -- For pci_app_users, you might set it to '1 year' UPDATE password_check.profiles SET max_validity_interval = '1 year' WHERE role = 'pci_app_users';
2. Create a test user with a password that expires too far in the future (should fail):CREATE ROLE test_valid_user WITH PASSWORD 'ValidP@ssw0rd1!' VALID UNTIL (NOW() + INTERVAL '30 days'); GRANT pci_standard_users TO test_valid_user;Expected Error: Password validation failed for user test_valid_user: Account validity date cannot be more than 1 day in the future for this role.
3. Create a test user with VALID UNTIL NULL (should fail):CREATE ROLE test_null_user WITH PASSWORD 'NullP@ssw0rd!'; GRANT pci_standard_users TO test_null_user;Expected Error: Password validation failed for user test_null_user: New user password must have a "VALID UNTIL" date. "VALID UNTIL NULL" is not allowed.
4. Create a test user with a compliant VALID UNTIL (should succeed):CREATE ROLE compliant_user WITH PASSWORD 'CompliantP@ssw0rd!' VALID UNTIL (NOW() + INTERVAL '12 hours'); GRANT pci_standard_users TO compliant_user;This should succeed, as 12 hours is within the ‘1 day’ max_validity_interval.

Scenario 2: Enforcing Password Expiration on Login (PCI DSS 8.3.9 & 8.6.3) via clientauth_hook

1. Create a user with a password set to expire very soon:CREATE ROLE expiring_user WITH PASSWORD 'ExpiringP@ssw0rd!' VALID UNTIL (NOW() + INTERVAL '1 minute'); GRANT pci_standard_users TO expiring_user;
2. Attempt to log in immediately (should succeed):psql -U expiring_user -d heydbamaintThis should allow you to log in.
3. Wait for the password to expire (more than 1 minute).
4. Attempt to log in again (should fail):psql -U expiring_user -d heydbamaintExpected Error: FATAL: The password has expired, please contact the admin.
5. Change the password (as an admin or via a process that allows password changes for expired users):ALTER ROLE expiring_user WITH PASSWORD 'NewValidP@ssw0rd!';This should succeed, and the valid_until for expiring_user in password_check.password_history will be updated to NOW() + current_max_validity_interval.
6. Attempt to log in with the new password (should succeed):psql -U expiring_user -d heydbamaintThis should now allow you to log in.

You’ve now added powerful temporal defenses to your PostgreSQL fortress, ensuring passwords don’t overstay their welcome and forcing changes when they do! This is a prime example of proactive PostgreSQL security and PCI DSS compliance in action.

The Adventure Continues…

Our quest for PostgreSQL security is nearing its epic conclusion! In the next thrilling installment, we’ll dive into implementing account lockout and inactive account management to fend off those pesky brute-force attacks and secure dormant user accounts. Stay tuned for Part 7: The Bouncer at the Gate – Implementing Account Lockout and Inactive Account Management!