Part 7: The Bouncer at the Gate – Implementing Account Lockout and Inactive Account Management

Or, “You Shall Not Pass! (Unless You Change Your Password or Wake Up)”

Welcome, vigilant guardians, to another thrilling chapter in our PostgreSQL security saga! 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, banished the spectral menace of password reusability in Part 5, and even mastered the flow of time for password expiration in Part 6. Your PostgreSQL database is becoming a fortress worthy of Minas Tirith (but, you know, for good).(Just need the quick code snippets and deployment steps for account lockout and inactive accounts? Your Quick-Reference: Part 7 – Account Lockout & Inactive Accounts 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.5_up_0.4-0.5.sql.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!Today, we face two formidable threats to your PostgreSQL security: the relentless brute-force attack, where digital villains try endless combinations to crack your defenses, and the silent danger of inactive accounts, like abandoned outposts waiting for a scavenger (or worse, a Nazgûl) to claim them. Fear not, for PCI DSS v4.0.1 has rules for these threats, and our trusty pgtle is ready to enforce them, ensuring your PostgreSQL user authentication is as impenetrable as the walls of Helm’s Deep! This is paramount for robust PostgreSQL security and overall PCI DSS compliance. Implementing these measures is a key security best practice for any database security professional.

The PCI DSS Mandate: Keeping the Gates Secure (PCI DSS 8.3.4 & 8.2.6)

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

• Requirement 8.3.4: Account Lockout.
  • The Problem: Imagine a horde of Uruk-hai, endlessly banging on the gates of your database, trying every password combination under the sun. Without a bouncer, they’ll eventually get in. It’s like trying to get into the Prancing Pony after hours – eventually, someone gets lucky if there’s no bouncer. This is why brute-force attack prevention is so vital.
  • The Mandate: After a maximum of 10 unsuccessful authentication attempts, the account must be locked out for at least 30 minutes, or until an administrator manually unlocks it. Basically, after ten “You shall not pass!” moments, the gate slams shut and stays shut for a while. We’re not just saying “No,” we’re saying “No, and now you’re in timeout.” This PCI DSS 8.3.4 requirement is a cornerstone of PostgreSQL login security.
• Requirement 8.2.6: Lock or disable inactive user accounts after a maximum of 90 days.
  • The Problem: Picture an old, forgotten watchtower on the borders of Gondor, left unguarded. It’s just asking for an opportunistic Orc to set up shop. Similarly, inactive accounts are low-hanging fruit for attackers – easy entry points that no one’s watching. Think of them as those dusty old accounts from that forum you joined in 2005, still technically active, just waiting for a phishing email to turn them into a botnet zombie.
  • The Mandate: User accounts that have not been active for a maximum of 90 days must be either locked or disabled. If a user hasn’t shown their face at the Black Gate in three months, they’re probably up to no good, or just really, really bad at remembering their login. Either way, access revoked. Because if you haven’t logged in for 90 days, you’re either on vacation in Valinor or you’ve been eaten by a Grue. Either way, we’re cutting off your access. This PCI DSS 8.2.6 mandate is crucial for effective PostgreSQL user account management.

Standard PostgreSQL, bless its heart, doesn’t natively track failed login attempts or user activity with the kind of meticulousness required by PCI DSS. It’s more of a “trust everyone until they break something” kind of system. This is where our pgtle extension, our very own customized Palantír, comes into play, helping us implement these critical PostgreSQL security measures for strict PCI DSS compliance.

The pgtle Strategy: Our Bouncer and Sentry Droid for Database Access Control

To enforce these crucial account security policies, our pgtle strategy will involve:

1. Tracking Login Attempts and Activity: We’re building a grand ledger, like the records kept in the Archives of Minas Tirith, detailing every attempt to enter. Except ours is digital, less dusty, and hopefully won’t be eaten by a Balrog. This is foundational for robust PostgreSQL password management.
2. Modifying clientauth_hook for Account Lockout: Our existing clientauth_hook is getting an upgrade. It’s becoming the vigilant bouncer at the gates, ready to yell “You shall not pass!” and then actually enforce it. No more Mr. Nice Guy. This pgtle hook is vital for brute-force attack prevention.
3. Implementing Inactive Account Management: We’ll deploy a diligent Sentry Droid (or perhaps a very bored Elf) to periodically sweep through the user list, identifying and disabling those inactive accounts that have gone full hermit mode. Because even Legolas needs to check in sometimes. This ensures comprehensive user account security.

New Features, Logic, and Database Design Changes (Version 0.5)

This 0.5 update significantly enhances PostgreSQL security by introducing new database structures and implementing automated account lockout and providing a framework for inactive account management, directly addressing PCI DSS 8.3.4 and 8.2.6. Think of these as new fortifications and watchtowers for our digital fortress, solidifying your database security.

1. New Tables: password_check.user_login_activity and password_check.locked_accounts
   • Purpose (password_check.user_login_activity): This table is the backbone of our lockout and inactivity tracking. It’s where we jot down every single login attempt, like a meticulous Scribe of Gondor. Hopefully, it’s more exciting than accounting for sheep. It will store username, last_successful_login, failed_attempts, and last_activity.
   • Purpose (password_check.locked_accounts): This table is the “Prison of Angband” for accounts that have misbehaved. If you’re in here, you’re doing time. Unless an admin, like a benevolent wizard, manually unlocks you. Don’t hold your breath. This table is central to account lockout enforcement.

   CREATE TABLE IF NOT EXISTS password_check.user_login_activity (
     username TEXT PRIMARY KEY,
     last_successful_login TIMESTAMPTZ DEFAULT NULL,
     failed_attempts INTEGER DEFAULT 0 NOT NULL,
     last_activity TIMESTAMPTZ DEFAULT NULL
   );
   
   CREATE TABLE IF NOT EXISTS password_check.locked_accounts (
     username TEXT PRIMARY KEY REFERENCES password_check.user_login_activity(username),
     locked_until TIMESTAMPTZ NOT NULL,
     locked_by TEXT DEFAULT 'SYSTEM' NOT NULL
   );

2. Updated password_check.profiles Table (New Columns)
   • Purpose: We’re adding new rules to our “Scrolls of Ancient Lore” to define how long an Orc can bang on the door before it gets locked, and how long a forgotten Ranger can wander before their account is disabled. Because one size does not fit all in Middle-earth, or in your database. These columns specify password policy parameters for lockout and inactivity.
   • New Columns:
     • lockout_threshold: Maximum unsuccessful authentication attempts before an account is locked out (directly addresses PCI DSS 8.3.4.a).
     • lockout_duration_minutes: The minimum number of minutes an account will remain locked out (directly addresses PCI DSS 8.3.4.b).
     • inactive_threshold: The maximum period of inactivity (e.g., ’90 days’) after which a user account must be disabled (directly addresses PCI DSS 8.2.6).

   ALTER TABLE password_check.profiles
     ADD COLUMN IF NOT EXISTS lockout_threshold INTEGER DEFAULT 10,
     ADD COLUMN IF NOT EXISTS lockout_duration_minutes INTEGER DEFAULT 30,
     ADD COLUMN IF NOT EXISTS inactive_threshold INTERVAL DEFAULT '90 days';

3. New View: password_check.v_role_members_parameters
   • Purpose: A convenient “Scrying Pool” to see all users, their primary roles, and the specific security policies applied to them. Much faster than consulting the stars, and less prone to cryptic prophecies. This view aids in PostgreSQL user account management by consolidating information from pg_roles, pg_auth_members, and password_check.profiles.

   CREATE OR REPLACE VIEW password_check.v_role_members_parameters AS
   WITH profiles AS (
     SELECT
         CASE p.role
           WHEN 'pci_admin_users' then 1
           WHEN 'pci_app_users' then 2
           WHEN 'pci_standard_users' then 3
           WHEN 'pci_new_users' then 4
         END as priority,
         p.role,
         p.max_validity_interval,
         p.lockout_threshold,
         p.lockout_duration_minutes,
         p.inactive_threshold
       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,
           pr.lockout_threshold,
           pr.lockout_duration_minutes,
           pr.inactive_threshold,
           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.member as username,
         m.role,
         m.max_validity_interval,
         m.lockout_threshold,
         m.lockout_duration_minutes,
         m.inactive_threshold
       FROM
         members m
       WHERE
         rn=1;

4. New Function: password_check.get_member_priority_role
   • Purpose: A quick “Who’s Who” function to figure out which PCI role a user’s most important (and thus, which rules apply). Because even in a hierarchy, someone’s more important than someone else. It’s just how the world works, even digital ones. This helper function is used internally by the clientauth_hook and manage_inactive_accounts function to efficiently determine the most prioritized PCI role a user belongs to, allowing for dynamic application of policies based on role hierarchy.

   CREATE OR REPLACE FUNCTION password_check.get_member_priority_role(
       target_username TEXT
   ) RETURNS TEXT AS $$
   DECLARE
     is_member BOOLEAN := FALSE;
     group_role_name TEXT := 'pci_new_users';
   BEGIN
     WITH profiles AS (
       SELECT
       CASE p.role
         WHEN 'pci_admin_users' then 1
         WHEN 'pci_app_users' then 2
         WHEN 'pci_standard_users' then 3
         WHEN 'pci_new_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.role
     INTO
       group_role_name
     FROM
       members m
     WHERE
       rn=1 AND
       m.member = target_username;
   
     IF NOT FOUND THEN
       group_role_name := 'pci_new_users';
     END IF;
   
     RETURN group_role_name;
   END;
   $$ LANGUAGE plpgsql SECURITY DEFINER;

5. Enhanced clientauth_hook for Account Lockout (PCI DSS 8.3.4)
   • Purpose: This is where the real-time lockout magic happens. The hook will now manage login attempt counters and apply temporary lockouts by updating the password_check.user_login_activity and password_check.locked_accounts tables. This ensures that only authorized individuals access sensitive systems. It’s the bouncer at the digital cantina, checking IDs and ensuring only those with valid, unexpired credentials get past the velvet rope.
   • Logic:
     • On every login attempt, it first checks password_check.locked_accounts. If an account is found here and its locked_until timestamp is in the future, the connection is immediately rejected with an exception. This is the primary enforcement point for active lockouts.
     • If the lockout period has expired, the entry is removed from password_check.locked_accounts, and failed_attempts are reset in password_check.user_login_activity.
     • On successful authentication (status = 0): Update last_successful_login and reset failed_attempts in password_check.user_login_activity. This ensures a clean slate after a successful login, like a hero resetting their watch.
     • On failed authentication (status = -1): Increment failed_attempts in password_check.user_login_activity. If the count exceeds the lockout_threshold (fetched dynamically from password_check.profiles), an entry is inserted/updated in password_check.locked_accounts with the locked_until timestamp. The updates to locked_accounts and user_login_activity are designed to commit successfully, and the actual connection termination for a newly locked account will occur on the next login attempt, when the “Handle already locked accounts” section at the beginning of this hook detects the entry in locked_accounts.

   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;
   	user_exists BOOLEAN;
   	current_locked_until_time TIMESTAMPTZ;
   	current_failed_attempts INTEGER;
   	current_lockout_threshold INTEGER; -- Configurable threshold
   	current_lockout_duration_minutes INTEGER; -- Configurable duration
   	l_role_name TEXT;
   BEGIN
   
     -- Determine Role-Based Policies (from password_check.profiles table)
     l_role_name := password_check.get_member_priority_role(l_username);
     SELECT
       lockout_threshold,
       lockout_duration_minutes
     INTO
       current_lockout_threshold,
       current_lockout_duration_minutes
     FROM
       password_check.profiles
     WHERE
       role = l_role_name;
   
     -- 1. Handle already locked accounts: THIS IS THE PRIMARY LOCKOUT ENFORCEMENT POINT
     SELECT locked_until INTO current_locked_until_time
     FROM password_check.locked_accounts la
     WHERE la.username = l_username;
   
     IF FOUND THEN
         IF NOW() < current_locked_until_time THEN
             -- Account is locked and lockout period is still active
             RAISE EXCEPTION 'Account % has been locked due to too many failed authentication attempts. Try again after %.', l_username, current_locked_until_time;
         ELSE
             -- Account is locked but the lockout period has expired
             DELETE FROM password_check.locked_accounts la WHERE la.username = l_username;
             -- Reset failed attempts for this user as lockout period has passed
             UPDATE password_check.user_login_activity la
             SET failed_attempts = 0, last_activity = NOW()
             WHERE la.username = l_username;
             RAISE NOTICE 'Account % was automatically unlocked as the lockout period expired.', l_username;
         END IF;
     END IF;
   
     -- Ensure user_login_activity record exists, create if not
     INSERT INTO password_check.user_login_activity (username) VALUES (l_username)
     ON CONFLICT (username) DO NOTHING;
   
     -- 2. Track authentication attempts based on status
     IF status = -1 THEN -- Authentication FAILED
       -- Increment failed attempts and update last_activity
       UPDATE password_check.user_login_activity
       SET failed_attempts = failed_attempts + 1, last_activity = NOW()
       WHERE username = l_username
       RETURNING failed_attempts INTO current_failed_attempts;
   
       -- If the threshold is reached, lock the account by inserting into locked_accounts
       IF current_failed_attempts >= current_lockout_threshold THEN
         INSERT INTO password_check.locked_accounts (username, locked_until)
         VALUES (l_username, NOW() + (current_lockout_duration_minutes || ' minutes')::INTERVAL)
         ON CONFLICT (username) DO UPDATE
         SET locked_until = NOW() + (current_lockout_duration_minutes || ' minutes')::INTERVAL,
             locked_by = 'SYSTEM'
         WHERE password_check.locked_accounts.username = EXCLUDED.username;
   
         -- Reset failed attempts after locking to prevent immediate re-lock after unlock
         UPDATE password_check.user_login_activity la
         SET failed_attempts = 0, last_activity = NOW()
         WHERE la.username = l_username;
   
         -- The actual connection termination for a newly locked account
         -- will happen on the *next* login attempt, when the "1. Handle already locked accounts"
         -- section at the beginning of this hook detects the entry in locked_accounts.
         -- This ensures the updates to locked_accounts and user_login_activity are committed.
       END IF;
     ELSIF status = 0 THEN -- Authentication SUCCESSFUL
       -- 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;
   
       UPDATE password_check.user_login_activity la
       SET failed_attempts = 0, last_activity = NOW(), last_successful_login = NOW()
       WHERE la.username = l_username;
     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');

6. New Function: password_check.manage_inactive_accounts (PCI DSS 8.2.6)
   • Purpose: This function will be responsible for identifying and disabling accounts that have been inactive for too long. It’s designed to be run periodically, not on every login. It’s our diligent Sentry Droid, constantly scanning for dormant accounts. Once identified, it casts a powerful ALTER ROLE ... NOLOGIN; spell on them, effectively disabling their ability to log in. Think of it as putting a “No Entry” sign on their forgotten hobbit-hole. Because if you’re not using it, you’re losing it. Simple as that.
   • Logic:
     • Query password_check.user_login_activity (joining with v_role_members_parameters to get inactive_threshold) to find users whose last_successful_login is older than their configured inactivity threshold.
     • For identified inactive accounts, execute ALTER ROLE <username> NOLOGIN; to disable their login. This is like putting a dormant fortress into permanent lockdown.

   CREATE OR REPLACE FUNCTION password_check.manage_inactive_accounts() RETURNS VOID AS $$
   DECLARE
     r RECORD;
   BEGIN
     FOR r IN
     SELECT
       l.username
     FROM
       password_check.user_login_activity AS l
       JOIN password_check.v_role_members_parameters AS v ON l.username = v.username
     WHERE
       l.last_successful_login < NOW() - v.inactive_threshold
       AND l.username NOT IN (
         SELECT
           rolname
         FROM
           pg_roles
         WHERE
           rolcanlogin = false
       ) -- Only process active logins
     LOOP
       EXECUTE 'ALTER ROLE ' || quote_ident(r.username) || ' NOLOGIN;';
       RAISE NOTICE 'Account % disabled due to inactivity.', r.username;
     END LOOP;
   END;
   $$ LANGUAGE plpgsql SECURITY DEFINER;

   Note: The manage_inactive_accounts function would typically be scheduled via an external cron job or a PostgreSQL background worker, as it’s not part of the real-time authentication flow. For example, a cron job entry might look like: 0 3 * * * psql -d heydbamaint -U postgres -c "SELECT password_check.manage_inactive_accounts();" (This example runs the function daily at 3 AM UTC in the heydbamaint database as the postgres user.)

Installation/Update: The Account Security Protocol!

To bring your pgtle extension to Version 0.5 and enable these robust account security features, you’ll need to execute the update path and ensure the clientauth hook is properly configured. This is less like a simple software update and more like forging a new ring of power – it requires precision and a bit of magic.

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.5_up_0.4-0.5.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.5.sql) and then run:

   psql -d heydbamaint -U postgres -f update_pci_password_rules_v0.5.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.5';

4. Ensure clientauth Hook is Enabled (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';
   SELECT pg_catalog.pg_reload_conf(); -- Apply this setting without a full restart
   
   ALTER SYSTEM SET pgtle.enable_clientauth TO 'on';
   -- IMPORTANT: This setting requires a database restart to take full effect.
   -- You will need to restart your PostgreSQL cluster for this to be active.

Verification: Did the Bouncer and Sentry Droid Come Online?

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

-- Check all available pg_tle extensions (should show 0.5 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 password_check.user_login_activity table exists
\d password_check.user_login_activity

-- Verify the new password_check.locked_accounts table exists
\d password_check.locked_accounts

-- Verify the new columns in password_check.profiles
SELECT role, lockout_threshold, lockout_duration_minutes, inactive_threshold FROM password_check.profiles;

-- Verify the new password_check.v_role_members_parameters view
SELECT * FROM password_check.v_role_members_parameters 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.5 as its default_version, and pgtle.enable_clientauth as on, with pgtle.clientauth_users_to_skip set to postgres. If not, someone’s been slacking off. Go poke them with a stick.

Testing Your Account Security Defenses!

Time to put your new account lockout and inactive account management 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 Account Lockout (PCI DSS 8.3.4)

1. Set up a test user:

   CREATE ROLE locked_test_user WITH PASSWORD 'SecureP@ssw0rd!';

2. Attempt multiple failed logins (more than 10): From a separate psql session or application, try to log in as locked_test_user with an incorrect password repeatedly. Be relentless, like a Ringwraith pursuing a hobbit.

   psql -U locked_test_user -d heydbamaint
   # Enter incorrect password 10+ times

   Expected Result: After the 10th (or configured threshold) failed attempt, the password_check.user_login_activity and password_check.locked_accounts tables should be updated. On the next attempt to log in, you should receive an error message like: FATAL: Account locked_test_user has been locked due to too many failed authentication attempts. Try again after [timestamp]. The bouncer has spoken! And he means it this time. This confirms effective brute-force attack prevention.
3. Verify lockout in user_login_activity and locked_accounts tables (as superuser):

   SELECT username, failed_attempts, last_activity FROM password_check.user_login_activity WHERE username = 'locked_test_user';
   SELECT username, locked_until, locked_by FROM password_check.locked_accounts WHERE username = 'locked_test_user';

   Expected Result: In user_login_activity, failed_attempts should be 0 (reset after lockout), and last_activity should be updated. In locked_accounts, an entry for locked_test_user should exist with locked_until set to a timestamp in the future. Proof that the account is indeed in digital solitary confinement. No visitors, no mail, just pure, unadulterated lockout.
4. Attempt to log in during lockout period (should fail):

   psql -U locked_test_user -d heydbamaint
   # Enter correct password

   Expected Result: Still receive the FATAL: Account locked_test_user locked out... message. Persistence is futile, for now. Go home, you’re digitally drunk.
5. Wait for lockout period to expire, then log in (should succeed): After the lockout_duration_minutes has passed (e.g., 30 minutes), try logging in with the correct password. The digital prison gates have opened. Expected Result: Login successful. The entry for locked_test_user should be removed from password_check.locked_accounts, and failed_attempts in user_login_activity should be 0. Freedom! (Until the next 10 failed attempts, of course.) Don’t mess it up this time.

Scenario 2: Testing Inactive Account Management (PCI DSS 8.2.6)

1. Create a new user for inactivity testing:

   CREATE ROLE inactive_test_user WITH PASSWORD 'InactiveP@ssw0rd!';
   GRANT pci_standard_users TO inactive_test_user;

2. Simulate an old last_successful_login (as superuser): We need to make this user appear inactive for more than 90 days. Think of it as setting their last known location to a very, very remote part of the Misty Mountains, 91 days ago. Probably chasing butterflies or something equally unproductive.

   UPDATE password_check.user_login_activity
   SET last_successful_login = NOW() - INTERVAL '91 days'
   WHERE username = 'inactive_test_user';

3. Run the manage_inactive_accounts function (as superuser):

   SELECT password_check.manage_inactive_accounts();

   Expected Result: You should see a NOTICE: Account inactive_test_user disabled due to inactivity. The Sentry Droid has found its target. No more digital squatters. This demonstrates automated inactive account management.
4. Verify the user’s login status:

   \du inactive_test_user

   • The Attributes for inactive_test_user should now include Cannot login. Their hobbit-hole is officially boarded up. And we didn’t even send them a polite eviction notice. Oops.
5. Attempt to log in with the disabled user (should fail):

   psql -U inactive_test_user -d heydbamaint

   • You should receive a FATAL: password authentication failed for user "inactive_test_user" (due to NOLOGIN attribute). They are now truly locked out, not just temporarily. Their quest for login has ended prematurely.

You’ve successfully implemented robust account lockout and inactive account management 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 the Shire after the War of the Ring. You’re welcome. These security best practices are crucial for any database security strategy.

The Adventure Continues…

With account lockout and inactive account management now in place, your PostgreSQL fortress is nearly impenetrable! In the final installment of our saga, we’ll tackle the last crucial PCI DSS requirements: managing vendor defaults and mandating password changes after first login. Stay tuned for Part 8: The Final Frontier – Tackling Vendor Defaults and First-Time Login Woes! Because even after all this, there’s always one more boss fight.