PostgreSQL elephant mascot in Rebel pilot gear, holding a blaster and defending a futuristic hourglass filled with glowing binary code, symbolizing password expiration and PostgreSQL security for PCI DSS compliance.

Part 6: The Sands of Time – Enforcing Change Frequency

By evandroPostgresql, Security

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

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.

  1. 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
  2. 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:

SELECT
	pgtle.install_update_path (
		'pci_password_check_rules', -- Name of your custom pg_tle extension
		'0.3',                      -- The version this update path is from
		'0.4',                      -- The version this update path goes to (new version)
		$_pgtle_$

  -- Add a column to the profiles table to control Time Waits for No Password (PCI DSS 8.3.9 & 8.6.3)
  ALTER TABLE password_check.profiles
    ADD COLUMN IF NOT EXISTS max_validity_interval INTERVAL DEFAULT '90 days';
  
  UPDATE password_check.profiles set max_validity_interval = '1 year' where role = 'pci_app_users';
  UPDATE password_check.profiles set max_validity_interval = '15 minutes' where role = 'pci_new_users';
  UPDATE password_check.profiles set max_validity_interval = '30 days' where role = 'pci_admin_users';
  
  -- Add a column to control the password validity. This differ from the "VALID UNTIL" clause of the CREATE USER statement and will control when the password must be changed rather than when the account will be unable to login.
  ALTER TABLE password_check.password_history
    ADD COLUMN IF NOT EXISTS valid_until TIMESTAMPTZ;

  --Update password_check.password_history's valid_until based on the max_validity_interval of the User's assigned roles (using a priority rule)
  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
			--member, 
			--role,
			M.MAX_VALIDITY_INTERVAL
		FROM
			MEMBERS M
		WHERE
			RN = 1
			AND M.MEMBER = PH.USERNAME
	);
  
  update password_check.password_history ph
  SET
    valid_until = ph.change_timestamp + '15 minutes'::INTERVAL
  WHERE 
    valid_until is null;
  
  ALTER TABLE password_check.password_history
    ALTER COLUMN valid_until SET NOT NULL;

  -- clientauth hook function
  -- This function is called by pg_tle after any authentication attempt.
  -- It checks if the account is locked and prevents login.
  -- It tracks FAILED authentication attempts and locks the account if a threshold is reached.
  CREATE OR REPLACE FUNCTION PASSWORD_CHECK.CLIENTAUTH_HOOK (
    PORT PGTLE.CLIENTAUTH_PORT_SUBSET, -- Now receives the port object
    STATUS INTEGER -- Now receives the authentication status
) RETURNS VOID AS $$
  DECLARE
    l_username TEXT := port.user_name; -- Extract username from the port object
    current_valid_until TIMESTAMPTZ;
  BEGIN
  
    -- 1. Determine if password is still valid: (PCI DSS 8.3.9 & 8.6.3)
    --	. Check the valid_until date from password_check.password_history.
    --		. This query looks for the last value of "current_valid_until" 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;
  
  
  -- 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;
    current_max_validity_interval INTERVAL;
    
    -- 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;
    
    -- 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,
        max_validity_interval
      INTO
        current_min_length,
        current_require_special_char,
        current_require_uppercase,
        current_require_lowercase,
        current_require_digit,
        current_history_limit,
        current_max_validity_interval
      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,
        max_validity_interval
      INTO
        current_min_length,
        current_require_special_char,
        current_require_uppercase,
        current_require_lowercase,
        current_require_digit,
        current_history_limit,
        current_max_validity_interval
      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,
        max_validity_interval
      INTO
        current_min_length,
        current_require_special_char,
        current_require_uppercase,
        current_require_lowercase,
        current_require_digit,
        current_history_limit,
        current_max_validity_interval
      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,
        max_validity_interval
      INTO
        current_min_length,
        current_require_special_char,
        current_require_uppercase,
        current_require_lowercase,
        current_require_digit,
        current_history_limit,
        current_max_validity_interval
      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;

  
    -- Ensure the account is not created 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;
    
    -- 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.
      -- Add the valid_until date to ensure the password will be changed within the maximum interval.
      INSERT INTO password_check.password_history (username, password_hash, valid_until)
      VALUES (_username_param, new_password_hashed, NOW() + current_max_validity_interval);
      
      -- 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');
  
  -- 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');
  
  $_pgtle_$
);

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.

  1. 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';
  2. 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:
      • valid_until is not set to NULL (all passwords must expire).
      • 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.
    • 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);

      Note on RAISE WARNING vs. RAISE EXCEPTION for password_type checks: The script continues to use RAISE WARNING for PASSWORD_TYPE_SCRAM_SHA_256 and complexity checks when password_type != 'PASSWORD_TYPE_PLAINTEXT'. While this provides flexibility, for strict PCI DSS compliance where a rule must be enforced, RAISE EXCEPTION is generally preferred. The warning for SCRAM-SHA-256 is a known technical limitation when relying on crypt() for reusability checks. Organizations should consider their overall security posture and potentially enforce plaintext/MD5 for password changes if the hook is the sole mechanism for these checks, or use external tools for SCRAM-hashed password validation.

  3. 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):
    DO 
$do$
BEGIN
  EXECUTE format($$CREATE USER test_valid_user WITH PASSWORD 'CompliantP@ssw0rd!' VALID UNTIL %L$$, NOW() + INTERVAL '30 days');
END;
$do$;
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):
    DO 
$do$
BEGIN
  EXECUTE format($$CREATE USER compliant_user WITH PASSWORD 'CompliantP@ssw0rd!' VALID UNTIL %L$$, NOW() + INTERVAL '15 minutes');
END;
$do$;
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:
    DO
$do$
BEGIN
  EXECUTE format($$CREATE ROLE expiring_user WITH PASSWORD 'ExpiringP@ssw0rd!' VALID UNTIL %L$$, NOW() + INTERVAL '1 minute');
END;
$do$;
GRANT pci_standard_users TO expiring_user;
  2. Attempt to log in immediately (should succeed):
    psql -U expiring_user -d heydbamaint

    This 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 heydbamaint

    Expected 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 heydbamaint

    This 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!

By evandro

You might also like:

A PostgreSQL elephant dressed as a wizard looks into a Palantír of logs, shielding its eyes from a dangerous log entry showing a cleartext password, symbolizing the importance of pgAudit password protection.

pgAudit Log Interpretation & Password Protection in PostgreSQL: Part 4 – The Palantír of Logs

A cartoon PostgreSQL elephant holds up a glowing orange ring with the word 'GRANT' inscribed on it, symbolizing the power of object-level auditing in pgAudit.

pgAudit Object-Level Auditing in PostgreSQL: Part 3 – One GRANT to Rule Them All

A PostgreSQL elephant dressed as Legolas uses a magnifying glass to audit a holographic screen showing hobbits being taken to an INSERT command, symbolizing pgAudit session auditing.

pgAudit Session Auditing in PostgreSQL: Part 2 – My God, It’s Full of Logs!

Leave a Reply

Your email address will not be published. Required fields are marked *