Part 8: The Final Frontier – Tackling Vendor Defaults and First-Time Login Woes

Or, ‘Speak, Friend, and Enter! (But the “Friend” Must Be a New, Strong Password, Not a Known Foe)’

Greetings, fellow digital guardians and purveyors of fine PostgreSQL instances! Evandro here, back from another perilous journey through the digital wastelands of insecure configurations. If you’ve been following our epic saga, “The Epic Quest for Secure Passwords: A PostgreSQL & PCI DSS Saga,” you know we’ve battled the monstrous password complexity requirements, outsmarted the ghostly password reusability, and even installed a bouncer at the gate for account lockouts.

(Just need the quick code snippets and deployment steps for tackling vendor defaults and first-time logins? Your Quick-Reference: Part 8 – Vendor Defaults & First-Time Logins awaits!)

For the full, executable code, including all SQL scripts discussed in this article, please visit our GitHub repository: hey-dba-pgtle-pcidss. The specific update script for this part can be found here: pci_password_check_rules_0.6_up_0.5-0.6.sql.

Review of Our Epic Quest So Far:

Our journey began in Part 1: The Guardians of the Gate – Unmasking PCI DSS Password Rules, where we first deciphered the ancient, often confusing, scrolls of PCI DSS v4.0.1 password mandates. We then meticulously prepared our battle station in Part 2: The Environment Setup – Preparing for pgtle Glory, getting our PostgreSQL environment ready to wield the power of custom extensions.

The real battles commenced in Part 3 & 4: The Enigma of Complexity, where we armed our database with formidable password complexity rules, ensuring no weak password could ever sneak in unnoticed. We then faced the spectral menace of password reusability in Part 5: The Ghost of Passwords Past, banishing old, recycled credentials to the digital void.

Our temporal defenses were honed in Part 6: The Sands of Time, where we mastered password expiration and change frequency, ensuring credentials didn’t overstay their welcome like an unexpected relative. Most recently, in Part 7: The Bouncer at the Gate, we implemented robust account lockout mechanisms and proactive inactive account management, turning our database into a fortress worthy of Helm’s Deep, capable of repelling even the most relentless brute-force attacks.

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 final 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 Gandalf’s counsel or a blueprint for your Barad-dûr’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-forged blade, using the PCI DSS password requirements as our Orc skirmisher. This code has not been tested in any corner of Middle-earth (i.e., a production environment), so wield it wisely and test rigorously in your own simulated battles!

But alas, even after all that, two insidious threats still lurk in the shadows, waiting to exploit the unwary: the dreaded vendor default password and the perilous first-time login. Fear not, for in this, our final installment, we journey to “The Final Frontier” to tackle these last bastions of vulnerability. Prepare your mind-melds and set phasers to “secure,” because it’s time to make these issues vanish faster than a Gungan in a swamp.

The Vendor Default Menace: Why “admin/admin” is a Nazgûl’s dream

Let’s consult the sacred scrolls of PCI DSS v4.0.1. A critical good practice requirement guides our battle against easily compromised credentials:

Requirement 8.3.8 (Good Practice): Implement processes to confirm passwords meet policy.

• The Problem: Imagine the Death Star’s main reactor, protected by a single, easily guessable code like “000000” or “Vader.” It’s an open invitation for any Rebel pilot to waltz in and blow the place up. Similarly, using vendor-supplied defaults or common, easily guessed passwords (like “password,” “123456,” or even “dragon”) is leaving the keys to your digital kingdom under the doormat. Attackers don’t need the Force to guess these; they just need a dictionary. This highlights the critical need for bad password prevention.
• The Mandate: While 8.3.8 is a “good practice,” in the realm of security, “good practice” often means “do it or face the consequences.” It implies having a system in place to prevent the use of weak, easily compromised, or default passwords. This means rejecting passwords that appear on lists of known insecure credentials or are too simple to guess. We’re not just hoping users pick strong passwords; we’re actively preventing them from picking weak ones.

Ah, vendor defaults. The bane of every security professional’s existence. It’s like leaving the keys to your Millennium Falcon in the ignition, with a sign saying “Please Steal Me!” PCI DSS, in its infinite wisdom (and often, its infinite bureaucracy), has a very clear directive: Thou shalt not use vendor-supplied defaults for system passwords and other security parameters. (Requirement 2.2.2, for those keeping score).

New Features and Logic Explained (Version 0.6 Changes)

This 0.6 update significantly enhances our pg_tle extension’s capabilities to meet PCI DSS requirements by introducing a “bad passwords” list and a robust mechanism for enforcing mandatory first-time password changes.

1. The password_check.bad_passwords Table: The Wall of Shame

This new table is where we store the cryptographic hashes of every password that’s ever made a security professional weep. Known weak, compromised, or vendor-default passwords – they all get a spot here.

• Purpose: To serve as a comprehensive blacklist of unacceptable passwords, directly supporting PCI DSS 8.3.8 by preventing their use. This is essential for bad password prevention.
• Key Columns:
  • password_hash (TEXT PRIMARY KEY): Stores the hashed version of the bad password.
  • source (TEXT): Indicates where the bad password entry originated (e.g., ‘common_default’, ‘breached_list’).
• Snippet (CREATE TABLE):

  CREATE TABLE IF NOT EXISTS password_check.bad_passwords (
    password_hash TEXT PRIMARY KEY,
    source TEXT DEFAULT 'manual'
  );

• Initial Population: The extension update includes a large INSERT statement to pre-populate this table with a curated list of common and default password hashes.
  • Logic: Each plaintext password from our curated list is hashed using a consistent Blowfish algorithm (crypt() with a fixed salt) and inserted. This ensures consistency for comparison.
  • Snippet (INSERT – illustrative, showing structure):

    INSERT INTO password_check.bad_passwords
    SELECT * FROM (VALUES
     ('$2a$06$uDrK/2blP99mE1qXATTJcefCUe3vYLWF.pLoDBDLuXUCUJkBOg3/i', 'common_default'), -- Example: 'password' hashed with Blowfish
     ('$2a$06$uDrK/2blP99mE1qXATTJcecOQVkt5AN8zYvpg7xFvKcrbrSXW7f.e', 'common_default')  -- Example: '123456' hashed with Blowfish
    -- ... many more entries ...
    ) ON CONFLICT (password_hash) DO NOTHING;

  Note on Hashing: This table’s hashes and the is_bad_password function use crypt() with a fixed Blowfish salt ($2a$06$uDrK/2blP99mE1qXATTJce) for comparison. It’s critical that the INSERT statements populating bad_passwords also use this Blowfish hashing for the plaintext values, ensuring accurate matches.

• Helper Functions:
  • password_check.add_bad_password(TEXT): Allows administrators to add new plaintext passwords (which are then hashed internally) to the bad_passwords list.
  • password_check.remove_bad_password(TEXT): Allows administrators to remove passwords from the list.
  • password_check.is_bad_password(TEXT): Checks if a given plaintext password matches any hash in the bad_passwords table.
  • Snippet (is_bad_password):

    CREATE OR REPLACE FUNCTION password_check.is_bad_password(
        password TEXT
    ) RETURNS BOOLEAN AS $$
    DECLARE
      l_salt TEXT := '$2a$06$uDrK/2blP99mE1qXATTJce'; -- The fixed Blowfish salt
      l_password_hash TEXT;
    BEGIN
      l_password_hash := crypt(lower(password), l_salt); -- Hashing with Blowfish for comparison
      RETURN EXISTS (SELECT 1 FROM password_check.bad_passwords WHERE password_hash = l_password_hash);
    END;
    $$ LANGUAGE plpgsql SECURITY DEFINER;

2. passcheck_hook Integration: The Sniff Test

Our main passcheck_hook (the ultimate password gatekeeper) is updated to consult the bad_passwords list.

• Logic: When a user attempts to set a new password, if it’s a PLAINTEXT type, the passcheck_hook calls password_check.is_bad_password(). If the password is found on the “Wall of Shame,” the change is rejected.
• Snippet (Relevant lines from passcheck_hook):

  -- Inside password_check.passcheck_hook
  IF password_type IN ('PASSWORD_TYPE_PLAINTEXT') THEN
    IF password_check.is_bad_password(password) THEN
      invalid_pw_reason := invalid_pw_reason || 'Password is too common, known to be insecure, or a default value. Please choose a different password.';
    END IF;
  ELSE
    RAISE WARNING 'Password type % common/dictionary password checks (PCI DSS 8.3.8) within this hook. Consider enforcing TEXT for these checks.', password_type;
  END IF;

  If your chosen password is on the list, you’ll be politely (or perhaps not-so-polutely, depending on your database’s mood) informed that you need to pick something else. No “You shall not pass!” this time, but definitely a “You shall not use that!”

The First-Time Login Gauntlet: No More “New User, Same Old Password”

Our journey continues with another crucial PCI DSS v4.0.1 requirement:

Requirement 8.3.5: Require all users to change their password upon first login after account creation or password reset.

• The Problem: Imagine a freshly minted Gondorian recruit, given a standard-issue uniform and a basic sword, but no training. They look the part, but they’re vulnerable. Similarly, when an administrator creates a new user account and assigns a temporary password (e.g., “Welcome123!”), if that password isn’t immediately changed by the user, it becomes a gaping vulnerability. It’s a known, static credential, easily exploited by anyone who might have seen it or guessed it. It’s like leaving the gates of Minas Tirith open after the first watch.
• The Mandate: Just as a new recruit must undergo training and prove their readiness, new users (or users whose passwords have been reset by an admin) must immediately change their temporary password to a unique, strong one of their own choosing. This ensures that the initial, potentially weak or shared, credential is never used for ongoing access, closing a critical security loophole. This is a vital aspect of PostgreSQL user authentication and password lifecycle management.

3. password_check.user_login_activity Table: Initiation Flags

We’ve added two crucial flags to our password_check.user_login_activity table to manage this “rite of passage.”

• New Columns:
  • password_reset_required (BOOLEAN): This flag is the digital equivalent of a stern warning from Gandalf. If set to TRUE, the user must change their password before they can do anything else. This is automatically set for new users or when an administrator explicitly flags an account for a reset.
  • password_first_login (BOOLEAN): This flag helps us differentiate between a brand-new user’s very first login and subsequent logins where password_reset_required might be set for other reasons (e.g., an admin forcing a reset). It’s a subtle but important distinction for managing the user experience.
• Snippet (ALTER TABLE):

  ALTER TABLE password_check.user_login_activity
    ADD COLUMN IF NOT EXISTS password_reset_required BOOLEAN NOT NULL DEFAULT FALSE,
    ADD COLUMN IF NOT EXISTS password_first_login BOOLEAN NOT NULL DEFAULT FALSE;

  Note: The update script also includes UPDATE statements to backfill these columns for existing users, setting them to FALSE by default, as they wouldn’t require a first-time reset.

4. passcheck_hook Logic: The Sorting Hat for Newbies

When a fresh face joins your PostgreSQL realm and gets assigned to the pci_new_users role (our special “newbie” role), the passcheck_hook automatically stamps them with the “must-change-password” mark.

• Logic: For new users (or existing users who are pci_new_users), this logic ensures password_reset_required and password_first_login are set to TRUE in user_login_activity, initiating the forced change flow.
• Snippet (Relevant lines from passcheck_hook):

  -- Inside password_check.passcheck_hook
  ELSIF NOT user_exists and user_priority_role = 'pci_new_users' THEN
    RAISE NOTICE 'ACTION REQUIRED: Assign a PCI ROLE to the user IMMEDIATELY after creation.';
    --...<omitted code>
  
    -- Enforce a reset upon the next user login. They're basically in digital purgatory until they change it.
    INSERT INTO password_check.user_login_activity(username, password_reset_required, password_first_login)
    VALUES (passcheck_hook.username, true, true)
    ON CONFLICT (username) DO UPDATE
    SET
      password_reset_required = true,
      password_first_login = true
    WHERE password_check.user_login_activity.username = EXCLUDED.username;
  
    --...<omitted code>
  END IF;

5. clientauth_hook Logic: The “No Soup For You!” Login Policy

Our clientauth_hook is the stern but fair maître d’ at the login restaurant. It now has a special policy for those who need to change their password.

• Logic:
  • On a user’s first successful login when password_reset_required is TRUE and password_first_login is TRUE, the clientauth_hook issues a WARNING message, prompting the user to change their password. Crucially, password_first_login is then immediately set to FALSE.
  • If password_reset_required is still TRUE but password_first_login is now FALSE (meaning they’ve had their warning and didn’t comply), the clientauth_hook raises a hard EXCEPTION, preventing login until the password is updated.
  • Superusers are explicitly exempted from this first-time password change enforcement.
• Snippet (Relevant lines from clientauth_hook):

  -- Inside password_check.clientauth_hook
  -- Initialize or retrieve user login activity (includes fetching flags)
  IF user_exists THEN
  
    SELECT
      lockout_threshold,
      lockout_duration_minutes,
      password_reset_required, --Fetch the flag from user_login_activity
      password_first_login
    INTO
      current_lockout_threshold,
      current_lockout_duration_minutes,
      l_password_reset_required, --Assign to variable
      l_password_first_login
    FROM
      password_check.profiles pr
    JOIN password_check.user_login_activity ula ON ula.username = l_username --Join to get the flag
    WHERE
      pr.role=password_check.get_member_priority_role(l_username);
  
    IF NOT FOUND THEN
      ...--<omitted code>
      -- If user_exists but no profile found, assume password_reset_required is false unless explicitly set
      --Ensure flag is fetched even if profile not found
      SELECT 
        password_reset_required,
        password_first_login
      INTO
        l_password_reset_required,
        l_password_first_login
      FROM 
        password_check.user_login_activity 
      WHERE 
        username = l_username;
  
      IF l_password_first_login IS NULL THEN l_password_reset_required := FALSE; END IF; -- Default to FALSE if no activity record yet
      IF l_password_reset_required IS NULL THEN l_password_first_login := FALSE; END IF; -- Default to FALSE if no activity record yet
  
    END IF;
  ELSE
    -- If user doesn't exist, use default lockout parameters for consistency
    current_lockout_threshold := 10;
    current_lockout_duration_minutes := 30;
    l_password_reset_required := FALSE; --Default to FALSE for non-existent users
    l_password_first_login := FALSE;
  
  END IF;
  
  ...--<omitted code>
  
  -- Handle password_reset_required flag (PCI DSS 8.3.6)
  IF l_password_reset_required THEN
    IF l_password_first_login THEN
      RAISE WARNING 'Your password must be changed. Please execute "ALTER USER % WITH PASSWORD <YOUR_NEW_PASSWORD>" to set a new password.', l_username;
      RAISE INFO 'INFO: Your password must be changed. Please execute "ALTER USER % WITH PASSWORD <YOUR_NEW_PASSWORD>" to set a new password.', l_username;
    ELSE
      RAISE EXCEPTION 'Your password must be changed before you can log in. Please contact the Database Administrator to set a new password.';
    END IF;
  END IF;
  
  ...--<omitted code>
  
  -- On successful login, reset failed attempts and update last_activity/last_successful_login.
  -- Crucially, set password_first_login to FALSE.
  UPDATE password_check.user_login_activity la
  SET failed_attempts = 0, last_activity = NOW(), last_successful_login = NOW(), password_first_login = FALSE
  WHERE la.username = l_username;

6. passcheck_hook Logic: Graduation Day!

Once a user finally sees the light and successfully changes their password, our passcheck_hook acts like a proud parent, clearing those “initiation” flags. They’ve earned their stripes!

• Logic: After a new password passes all validation checks, this logic ensures both password_reset_required and password_first_login are set to FALSE.
• Snippet (Relevant lines from passcheck_hook):

  -- Inside password_check.passcheck_hook (within the ELSE block where validation passed)
  IF user_exists and user_priority_role != 'pci_new_users' THEN
    UPDATE password_check.user_login_activity la
    SET
      password_reset_required = FALSE,
      password_first_login = FALSE
    WHERE la.username = _username_param;
  END IF;

Installation/Update: The Final Protocol!

To upgrade your pci_password_check_rules extension to Version 0.6 and enable these new security features, follow these steps. This is the ultimate ritual to imbue your database with its final security enchantments.

1. Connect to Your Maintenance Database: Open your psql client and connect to the maintenance database (heydbamaint) as a superuser (postgres). Only a true Lord of the Database can wield this power.

   psql -d heydbamaint -U postgres

2. Execute the Update Path Code: Download the pci_password_check_rules_0.6_up_0.5-0.6.sql file from our GitHub repository to a local directory and execute itfrom your psql prompt. This script handles all schema changes, function updates, and new function creations.

   \i pci_password_check_rules_0.6_up_0.5-0.6.sql

3. Apply the Extension Update: Now that the update path is defined, tell your extension to upgrade! This is the moment where the new version truly takes hold.

   ALTER EXTENSION pci_password_check_rules UPDATE TO '0.6';

4. Enable clientauth Hook (Crucial!): These steps ensure the clientauth hook is globally enabled. Remember to exclude superusers like postgres to prevent accidental lockout. You don’t want to lock yourself out of your own fortress, do you? That’s a rookie mistake, even for a wizard. This is a critical pgtle configuration step for PostgreSQL login security.

   ALTER SYSTEM SET pgtle.clientauth_users_to_skip TO 'postgres'; --If not done already
   SELECT pg_catalog.pg_reload_conf();
   
   ALTER SYSTEM SET pgtle.enable_clientauth TO 'on'; --If not done already
   -- IMPORTANT: This setting requires a database restart to take full effect.

Verification: Did the New Defenses Come Online?

After applying the update and restarting your PostgreSQL cluster, let’s confirm everything is in good order. It’s time to check if our new guardians are actually standing at their posts, or if they’ve just gone off for elevenses. Verifying these steps is essential for security best practices.

• Check Extension Version:

  SELECT * FROM pgtle.available_extensions();
  -- Should show 'pci_password_check_rules' with version '0.6'
  ```sql
  SELECT * FROM pgtle.available_extension_versions();
  -- Should list '0.6' as an available version for pci_password_check_rules
  ```sql
  SELECT * FROM pgtle.extension_update_paths('pci_password_check_rules');
  -- Should show the update path from '0.5' to '0.6'

• Verify New Tables and Columns:

  \d password_check.user_login_activity
  -- Confirm 'password_reset_required' and 'password_first_login' columns
  ```sql
  \d password_check.bad_passwords
  -- Confirm 'password_hash' and 'source' columns
  ```sql
  SELECT name, setting, short_desc, context FROM pg_settings WHERE name LIKE 'pgtle%';
  -- Confirm pgtle.enable_clientauth is 'on' and pgtle.clientauth_users_to_skip is set

Testing Your New Defenses!

Time to put your new bad password prevention and first-time login enforcement policies to the test! These scenarios will demonstrate your enhanced PostgreSQL security and adherence to PCI DSS requirements. Let’s see if our bouncer can truly say “You shall not pass!” without just politely asking.

Scenario 1: Testing Bad Password Rejection (PCI DSS 8.3.8)

This scenario simulates an attempt to use a known weak or default password.

1. Add a known bad password to your list (if not already there): Let’s add “daewuu” (a common weak password) to our bad_passwords list.

   SELECT password_check.add_bad_password('daewuu');

2. Attempt to create a user with a bad password (should fail):

   CREATE USER myuser4 WITH PASSWORD 'daewuu';

   • Expected Error: ERROR: Password validation failed for user myuser4: Password is too common, known to be insecure, or a default value. Please choose a different password.
3. Attempt to change an existing user’s password to a bad password (should fail):

   ALTER USER evandro WITH PASSWORD 'daewuu';

   • Expected Error: ERROR: Password validation failed for user evandro: Password is too common, known to be insecure, or a default value. Please choose a different password.
4. Verify superuser cannot bypass this check: Even a superuser cannot set a bad password for another user via ALTER USER.

   -- As superuser
   ALTER USER some_other_user WITH PASSWORD 'daewuu';

   • Expected Error: Same “Password is too common…” error.

Scenario 2: Testing First-Time Login Enforcement (PCI DSS 8.3.5)

This scenario verifies that new users are forced to change their password on first login.

1. Create a new user (this will set password_reset_required = TRUE and password_first_login = TRUE):

   CREATE ROLE new_recruit WITH PASSWORD 'TemporaryP@ssw0rd!';
   GRANT pci_standard_users TO new_recruit; -- Assign to a PCI role

   • Verify the flags (as superuser): SELECT username, password_reset_required, password_first_login FROM password_check.user_login_activity WHERE username = 'new_recruit'; (Both should be t).
2. Attempt to log in for the first time with the temporary password:

   psql -U new_recruit -d heydbamaint
   # Enter 'TemporaryP@ssw0rd!'

   • Expected Behavior: The connection should succeed, but you should see a WARNING and INFO message in your psql client and PostgreSQL logs: WARNING: Your password must be changed. Please execute "ALTER USER new_recruit WITH PASSWORD <YOUR_NEW_PASSWORD>" to set a new password.
   • Verify password_first_login is now FALSE (as superuser): SELECT username, password_reset_required, password_first_login FROM password_check.user_login_activity WHERE username = 'new_recruit'; (password_reset_required should still be t, password_first_login should be f).
3. Attempt to log in a second time with the same temporary password (should fail):

   psql -U new_recruit -d heydbamaint
   # Enter 'TemporaryP@ssw0rd!' again

   • Expected Error: FATAL: Your password must be changed before you can log in. Please contact the Database Administrator to set a new password.

You’ve successfully implemented robust bad password prevention and first-time login enforcement policies directly within your PostgreSQL database using pgtle! This significantly strengthens your PostgreSQL security posture and helps you achieve PCI DSS compliance. Your database is now safer than a vault on Coruscant.

The Adventure Concludes!

We’ve come a long way, haven’t we? From the humble beginnings of understanding PCI DSS to implementing a robust, pg_tle-powered security solution right within your PostgreSQL database. With vendor defaults banished to the Outer Rim and first-time logins becoming a secure rite of passage, your database is now more resilient than ever.

While no system is truly impenetrable (the Borg always find a way, eventually), we’ve certainly built a formidable shield. This concludes our epic quest for secure passwords. Until our next adventure, keep your passwords strong, your databases patched, and may the Force (of good security practices) be with you!