Part 4: The Enigma of Complexity – Taming Passwords with pgtle (Part 2)

Or, “SQL-Powered Password Fortresses: Building Beyond ‘Password123’!”

Welcome back, brave coders and database defenders! In Part 1: The Guardians of the Gate – Unmasking PCI DSS Password Rules, we deciphered the ancient scrolls of PCI DSS v4.0.1 password rules. Then, in Part 2: The Environment Setup – Preparing for pgtle Glory, we meticulously prepared our PostgreSQL environment and successfully completed our pgtle installation. Finally, Part 3: The Enigma of Complexity – Taming Passwords with pgtle (Part 1) laid out our grand strategy for tackling password complexity.

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. This series is your ultimate resource for achieving PostgreSQL PCI DSS compliance.

Now, with our pgtle extension standing ready in our PostgreSQL fortress and our battle plan for password complexity understood, it’s time for the main event: deploying the code! This installment of The Epic Quest for Secure Passwords is all about getting your hands dirty with PL/pgSQL and transforming those theoretical PCI DSS password complexity requirements into active, in-database enforcement. Get ready to witness your PostgreSQL database actively rejecting weak passwords with the sternness of a grumpy wizard, enhancing your overall PostgreSQL password security!

Before We Code: The Essential pgtle Setup Ritual (Again!)

Before you unleash the code, ensure your PostgreSQL environment is properly configured for pgtle‘s powerful password validation capabilities. Think of these as the pre-flight checks before launching your security rocket! This pgtle setup is crucial for robust database security.

1. Create Your Maintenance Database: This is where your pg_tle extension and its related code will live, keeping your core application databases clean. We’ll call it heydbamaint – because even database security needs a home base!Create your maintenance database using:CREATE DATABASE heydbamaint;
2. Grant pgtle_admin to Your Superuser: This role is essential for managing pgtle extensions and their password policies. In most cases, you’ll grant it to your postgres superuser. It’s like giving your main character the “Admin Privileges” perk for pgtle configuration.Grant pgtle_admin to your superuser using:GRANT pgtle_admin TO postgres;
3. Point pgtle to Your Maintenance DB: This tells pgtle where to find its password-checking functions. Without this, pgtle will be looking in all the wrong places, like a Stormtrooper looking for droids. This is a key pgtle configuration step for effective PostgreSQL password security.Point pgtle to your maintenance DB and reload the configuration using:ALTER SYSTEM SET pgtle.passcheck_db_name TO 'heydbamaint'; SELECT pg_catalog.pg_reload_conf(); -- Apply the change without a full restart
4. Enable pgtle Password Checking: This activates the passcheck hook, which is the heart of our password validation system.'on' will enable the hook but only execute your function if it’s registered. It’s polite, but optional.'require' will force the hook to be present and registered; if not, password changes will fail. This is the “no excuses” mode, ideal for strict PCI DSS compliance. Choose wisely, young Padawan!Enable pgtle Password Checking and reload the configuration using:ALTER SYSTEM SET pgtle.enable_password_check TO 'on'; -- OR -- ALTER SYSTEM SET pgtle.enable_password_check TO 'require'; SELECT pg_catalog.pg_reload_conf(); -- Apply the change

Once these pgtle setup steps are complete, you’re ready to deploy your custom pgtle extension and its powerful PostgreSQL password policy enforcement!

The Grand Unveiling: Our pci_password_check_rules Extension Code – Version 0.1

Our journey begins with pci_password_check_rules_0.1.sql. This script represents our initial foray into pgtle-powered password validation. It’s a “proof of concept” that focuses solely on enforcing the core PCI DSS 8.3.6 complexity rules for standard users. It does not yet include the sophisticated profiles logic for role separation. Think of it as building a basic, but functional, laser blaster before you upgrade to a lightsaber. It ensures your passwords meet the minimum length and character type requirements.

The full SQL code for this version is available in a separate document: pci_password_check_rules_0.1.sql – Initial PCI DSS Password Complexity Code.

SELECT
	PGTLE.INSTALL_EXTENSION (
		'pci_password_check_rules', -- Name of your custom pg_tle extension
		'0.1', -- Incremented version for non-human account handling
		'Enforces PCI DSS 4.0.1 password complexity', -- Description of the extension
		$_pgtle_$
  
    CREATE SCHEMA IF NOT EXISTS password_check;
    REVOKE ALL ON SCHEMA password_check FROM PUBLIC;
    GRANT USAGE ON SCHEMA password_check TO PUBLIC;
    
    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;
    
    BEGIN
    
      --- 1. Apply Password Complexity Checks (PCI DSS 8.3.6) ---
      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;
      
      --- 2. Final Check and Raise Exception / Update History ---
      IF invalid_pw_reason != '' THEN
        RAISE EXCEPTION 'Password validation failed for user %: %', username, invalid_pw_reason;
      END IF;
    
    END;
    $_FUNCTION_$ LANGUAGE plpgsql;
    
    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;
    SELECT pgtle.register_feature_if_not_exists('password_check.passcheck_hook', 'passcheck');
  $_pgtle_$
	);
        

Logic Explained: pci_password_check_rules_0.1.sql

This script uses pgtle.install_extension to package our custom logic into a pg_tle extension. Here’s a breakdown of its key components and their purpose:

• CREATE SCHEMA IF NOT EXISTS password_check;This line creates a dedicated schema named password_check. This is a best practice for organizing database objects, keeping our custom functions and tables separate from the default public schema and other system objects. It enhances database security by providing a clear namespace for our password policy components.
• CREATE OR REPLACE FUNCTION password_check.passcheck_hook(...)This defines our main PL/pgSQL function, passcheck_hook. This is the function that pgtle will call every time a password is created or changed.It declares variables to store invalid_pw_reason (to accumulate error messages) and hardcoded current_min_length, current_require_special_char, etc., which represent the PCI DSS 8.3.6 requirements (12 characters, requiring special, upper, lower, and digit).The BEGIN...END block contains a series of IF statements. Each IF statement checks a specific password complexity rule (e.g., length(password) < current_min_length). If a rule is violated, a descriptive message is appended to the invalid_pw_reason variable.Finally, IF invalid_pw_reason != '' THEN RAISE EXCEPTION ... END IF; is the critical part. If any rule was violated (meaning invalid_pw_reason is not empty), it raises an SQL exception with all the accumulated reasons. This exception prevents the password change from completing, ensuring only compliant passwords are set.
• REVOKE ALL ON FUNCTION ... FROM PUBLIC;andGRANT EXECUTE ON FUNCTION ... TO PUBLIC;These lines manage the function’s permissions. By revoking all and then granting only EXECUTE to PUBLIC, we ensure that pg_tle (which runs as a superuser) can call our hook, but regular users cannot directly execute it, maintaining a secure execution context.
• SELECT pgtle.register_feature_if_not_exists('password_check.passcheck_hook', 'passcheck');This is the command that registers our passcheck_hook function with pgtle‘s passcheck feature. This tells pgtle to invoke this specific function whenever a password validation event occurs.

Installation: Unleashing Version 0.1!

Ready to make your PostgreSQL database a bastion of password strength? Follow these steps to install our shiny new pci_password_check_rules extension (Version 0.1):

1. Connect to Your Maintenance Database: Open your psql client and connect to the maintenance database you created (e.g., heydbamaint) as a superuser (postgres).psql -d heydbamaint -U postgres
2. Execute the Extension Code: Copy the entire SQL code from the pci_password_check_rules_0.1.sql – Initial PCI DSS Password Complexity Code document and paste it into your psql prompt, then press Enter. This will install the pci_password_check_rules extension at version 0.1, immediately applying robust PostgreSQL password policy enforcement for enhanced PostgreSQL password security.If you prefer to run it from a file: Save the code to a .sql file (e.g., install_pci_password_rules_v0.1.sql) and then run:psql -d heydbamaint -U postgres -f install_pci_password_rules_v0.1.sql

Version 0.2: The Evolution – Role-Based Policies

Now, let’s level up! PCI DSS v4.0.1 (especially 8.6.3) distinguishes between human and non-human accounts, often requiring even stronger policies for the latter. This version 0.2 update introduces role-based password policies, allowing you to define different rules for different user groups. It’s like giving your Jedi Masters a different, more powerful lightsaber than your standard troopers.

This update path handles:

• Role Verification/Creation: Ensures the necessary group roles (pci_admin_users, pci_app_users, pci_standard_users) exist, creating them if they don’t.
• Profile Table: Introduces password_check.profiles to store distinct password rules for each role.
• Intelligent Hook: Modifies the passcheck_hook to dynamically fetch and apply rules based on the user’s role membership.
• New User Handling: Allows new users to be created with a default policy, but then requires them to be assigned to a PCI role before their password can be changed. This is an initial and crucial logic that will be used later to enforce “Mandate a Password Change After First Login” (PCI DSS 8.3.5) on Part 8.

The full SQL code for this update path is available in a separate document: pci_password_check_rules_0.2_up_0.1-0.2.sql – Role-Based Password Policies Update Code.

SELECT
	PGTLE.INSTALL_UPDATE_PATH (
		'pci_password_check_rules', -- Name of your custom pg_tle extension
		'0.1',
		'0.2', -- Incremented version to add role separation for non-human account handling
		$_pgtle_$

  --Check if the ROLES exists. It's a requirement that the roles exists in order to proceed.
  --pci_admin_users,pci_app_users,pci_standard_users
  do
  $$
  declare
    v_arr_rolnames text ARRAY;
    v_rolname text;
    
  begin
    v_arr_rolnames := '{"pci_admin_users","pci_app_users","pci_standard_users"}';
    
    FOREACH v_rolname IN ARRAY v_arr_rolnames
    LOOP
    
      if not exists (SELECT 1 FROM pg_catalog.pg_roles r WHERE r.rolname = v_rolname) then
        RAISE NOTICE 'Role % does not exist, creating it.', v_rolname;
  	  
        EXECUTE format($e$
          CREATE ROLE %1$s
        $e$, v_rolname);
  	  
      end if;
      
    END LOOP;
    
  end
  $$
  ;

  --Create the table to hold the profiles.
  --The table must check if the group roles already exists (create a trigger).
  --Or maybe the table must not allow any update to its values, and make it fixed.
  CREATE TABLE IF NOT EXISTS password_check.profiles (
    role TEXT PRIMARY KEY,
  	min_length INTEGER DEFAULT 15 NOT NULL ,
  	require_special_char BOOLEAN DEFAULT true NOT NULL,
  	require_uppercase BOOLEAN DEFAULT true NOT NULL,
  	require_lowercase BOOLEAN DEFAULT true NOT NULL,
  	require_digit BOOLEAN DEFAULT true NOT NULL
  );
  
  INSERT INTO password_check.profiles (role,min_length) values ('pci_admin_users',15) ON CONFLICT (role) DO NOTHING;
  INSERT INTO password_check.profiles (role,min_length) values ('pci_app_users',15) ON CONFLICT (role) DO NOTHING;
  INSERT INTO password_check.profiles (role,min_length) values ('pci_standard_users',12) ON CONFLICT (role) DO NOTHING;
  INSERT INTO password_check.profiles (role,min_length) values ('pci_new_users',12) ON CONFLICT (role) DO NOTHING;

  -- Helper function to check if a given username is a member of a specified PostgreSQL role (group).
  CREATE OR REPLACE FUNCTION password_check.is_member_of_role(
      target_username TEXT,
      group_role_name TEXT
  ) RETURNS BOOLEAN AS $$
  DECLARE
    is_member BOOLEAN := FALSE;
  BEGIN
    SELECT EXISTS (
      SELECT 1
      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
      WHERE r_member.rolname = target_username
        AND r_role.rolname = group_role_name
    ) INTO is_member;
    RETURN is_member;
  END;
  $$ LANGUAGE plpgsql SECURITY DEFINER; --Executed with the privileges of the user that owns the function.
  
  -- 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;
  	
    -- 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;
	
  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 ---
    --- 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
  	  INTO
  	    current_min_length,
          current_require_special_char,
          current_require_uppercase,
          current_require_lowercase,
          current_require_digit
  	  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
  	  INTO
  	    current_min_length,
        current_require_special_char,
        current_require_uppercase,
        current_require_lowercase,
        current_require_digit
  	  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
  	  INTO
  	    current_min_length,
        current_require_special_char,
        current_require_uppercase,
        current_require_lowercase,
        current_require_digit
  	  FROM 
  	    password_check.profiles
  	  WHERE
  	    role='pci_app_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
  	  INTO
  	    current_min_length,
        current_require_special_char,
        current_require_uppercase,
        current_require_lowercase,
        current_require_digit
  	  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) ---
    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;

    --- 3. Final Check and Raise Exception / Update History ---
    IF invalid_pw_reason != '' THEN
      RAISE EXCEPTION 'Password validation failed for user %: %', username, invalid_pw_reason;

    END IF;
  
  END;
  $_FUNCTION_$ LANGUAGE plpgsql;
  
  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;
  SELECT pgtle.register_feature_if_not_exists('password_check.passcheck_hook', 'passcheck');
$_pgtle_$
	);
        

Logic Explained: pci_password_check_rules_0.2_up_0.1-0.2.sql

This update script builds upon Version 0.1 by introducing a more granular, role-based approach to password policies.

• do $$ declare ... end $$ ;This anonymous PL/pgSQL block is executed once during the update.It defines an array v_arr_rolnames containing the names of the required PCI roles: pci_admin_users, pci_app_users, and pci_standard_users.It then iterates through this array. For each role, it checks if the role already exists in pg_catalog.pg_roles. If a role does not exist, it uses EXECUTE format('CREATE ROLE %1$I', v_rolname) to dynamically create that role. This ensures that the necessary group roles are present in your PostgreSQL instance before they are referenced.
• CREATE TABLE IF NOT EXISTS password_check.profiles (...)This creates a new table, password_check.profiles, within our password_check schema. This table is the heart of our role-based policy system.It has a role column (TEXT PRIMARY KEY) to store the PostgreSQL role name.It includes columns like min_length, require_special_char, require_uppercase, require_lowercase, and require_digit (all BOOLEAN or INTEGER with NOT NULL constraints). These columns will store the specific password requirements for each defined role.
• INSERT INTO password_check.profiles (...) ON CONFLICT (role) DO NOTHING;These INSERT statements populate the password_check.profiles table with default password policies for each of our defined roles (pci_admin_users, pci_app_users, pci_standard_users) and a special pci_new_users profile.ON CONFLICT (role) DO NOTHING is important: it prevents errors if you run the script multiple times and the rows already exist, ensuring idempotency.Notice the different min_length values (e.g., 15 for admin/app users, 12 for standard/new users), reflecting PCI DSS best practices for different account types.
• CREATE OR REPLACE FUNCTION password_check.is_member_of_role(...)This is a new helper function.It takes a target_username and a group_role_name as input.It queries pg_catalog.pg_roles and pg_catalog.pg_auth_members (which stores role memberships) to determine if the target_username is a member of the group_role_name.It is defined as SECURITY DEFINER, meaning it runs with the privileges of the user who created it (typically a superuser). This is necessary because querying pg_catalog.pg_roles requires elevated privileges.
• CREATE OR REPLACE FUNCTION password_check.passcheck_hook(...)(Updated)The core passcheck_hook function is significantly modified.It first determines user_exists by checking pg_catalog.pg_roles. This helps differentiate between CREATE ROLE (where user_exists is false) and ALTER ROLE (where user_exists is true).It then uses a series of IF...ELSIF statements, along with the new password_check.is_member_of_role function, to determine which password profile (from password_check.profiles) applies to the current username.If the user is a member of pci_admin_users, pci_app_users, or pci_standard_users, it fetches the specific policy parameters (min_length, require_special_char, etc.) from the password_check.profiles table for that role.If NOT user_exists, it applies the pci_new_users policy, and a RAISE NOTICE reminds the administrator to assign a proper PCI role.Crucially, if user_exists is true but the user does not belong to any of the predefined PCI roles, it RAISE EXCEPTIONs, preventing the password change until the user is correctly assigned. This enforces proper role management.The subsequent password complexity checks (length, character types) then use the dynamically fetched current_min_length and current_require_... variables, making the policy adaptable per role.
• SELECT pgtle.register_feature_if_not_exists(...)This re-registers the updated passcheck_hook function with pgtle, ensuring the new logic is active.

To update your extension to Version 0.2:

1. Ensure you have already installed Version 0.1.
2. Connect to your heydbamaint database as a superuser.
3. Execute the entire content of pci_password_check_rules_0.2_up_0.1-0.2.sql – Role-Based Password Policies Update Code.
4. Then, run the ALTER EXTENSION command:ALTER EXTENSION pci_password_check_rules UPDATE TO '0.2';

Verification: Did It Work, Captain?

After installing your pgtle extension, you can verify its presence and version information using these handy commands. It’s like checking the diagnostics on your starship’s new warp core!

Check available extensions using:

SELECT * FROM pgtle.available_extensions();        

Check all available versions for your extension using:

SELECT * FROM pgtle.available_extension_versions();        

You should see pci_password_check_rules listed with 0.2 as its default_version. If you encounter any errors during installation, double-check your pgtle setup ritual (especially the pgtle.passcheck_db_name setting and the pg_reload_conf()!).

Check the update paths defined for your specific extension:

SELECT * FROM pgtle.extension_update_paths('pci_password_check_rules');        

Testing Your New Fortress!

Now for the fun part: testing! Try creating users and changing passwords to see your new password validation rules in action. This will demonstrate your enhanced PostgreSQL password security and adherence to PCI DSS compliance.

Example 1: Creating a pci_standard_users member with a weak password (should fail):

Try creating a user with a weak password, like:

CREATE ROLE test_standard_user WITH PASSWORD 'short';        

Expected error: Password validation failed for user test_standard_user: Password must be at least 12 characters long.

Or with insufficient complexity:

CREATE ROLE another_standard_user WITH PASSWORD 'nopassword';        

Expected error: Password validation failed for user another_standard_user: Password must contain at least one uppercase letter. Password must contain at least one number. Password must contain at least one special character.

Example 2: Creating a pci_admin_users member with a strong password (should succeed):

CREATE ROLE test_admin_user WITH PASSWORD 'P@ssw0rdF0rAdm1n!'; -- Should succeed (17 chars, complex)
GRANT pci_admin_users TO test_admin_user; -- Assign to the role        

This should succeed (17 chars, complex), demonstrating effective PostgreSQL password policy for administrators.

Example 3: Creating a pci_app_users member with a strong password (should succeed, demonstrating application accounts security):

CREATE ROLE test_app_service WITH PASSWORD 'AppS3rv!ce_P@ssw0rd_2025!'; -- Should succeed (26 chars, complex)
GRANT pci_app_users TO test_app_service; -- Assign to the role        

This should succeed (26 chars, complex), showcasing robust service accounts security.

Example 4: Trying to change password for a user not assigned to a PCI role (should fail after initial creation):

Create an unassigned user:

CREATE ROLE unassigned_user WITH PASSWORD 'InitialP@ssw0rd1!';        

This succeeds initially (uses pci_new_users profile).

Now try to change its password without assigning it to a PCI role:

ALTER ROLE unassigned_user WITH PASSWORD 'NewP@ssw0rd2!';        

Expected error: Password change not allowed for user unassigned_user: User must be assigned to one of the defined PCI roles (pci_admin_users, pci_app_users, pci_standard_users).

Assign it to a role, then try again (should succeed if new password is valid for that role):

GRANT pci_standard_users TO unassigned_user;
ALTER ROLE unassigned_user WITH PASSWORD 'NewP@ssw0rd2!';        

This should now succeed if it meets the standard user policy, confirming your dynamic PostgreSQL password policy.

This robust pgtle extension ensures that your PostgreSQL password security is now directly integrated and enforced at the database level, making your system significantly more compliant with PCI DSS v4.0.1. This is a prime example of PostgreSQL security best practices in action!

The Adventure Continues…

Congratulations, brave DBA! You’ve just deployed a powerful pgtle extension that enforces PCI DSS password complexity, even differentiating rules based on user roles. This is a huge leap in securing your PostgreSQL fortress and a major win for PostgreSQL password security!

In the next parts of our saga, we’ll continue to build upon this foundation, tackling other crucial PCI DSS password requirements like reusability, change frequency, account lockout, and more. Stay tuned, because the quest for ultimate PostgreSQL security best practices is far from over!