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

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!