Quick-Reference: Part 7 – Account Lockout & Inactive Accounts

Your Cheat Sheet for PostgreSQL Account Lockout and Inactive Account Management with pgtle

Welcome, vigilant guardians! This quick-reference guide distills the essence of “Part 7: The Bouncer at the Gate – Implementing Account Lockout and Inactive Account Management” into actionable steps. If you’re looking to rapidly implement account lockout and inactive account management using pgtle in PostgreSQL for PCI DSS compliance, you’ve come to the right place! This guide is your essential resource for enhancing PostgreSQL security and ensuring database security best practices.

For the full narrative and deeper explanations, you can always refer back to the main blog post: Part 7: The Bouncer at the Gate – Implementing Account Lockout and Inactive Account Management. And for the entire saga, check out our Series Overview Page.

This guide focuses on Version 0.5, which introduces robust account lockout and inactive account management features.

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.

The PCI DSS Mandate: Keeping the Gates Secure

• Requirement 8.3.4: Account Lockout.
  • 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. This is crucial for brute-force attack prevention.
• Requirement 8.2.6: Lock or disable inactive user accounts after a maximum of 90 days.
  • Mandate: User accounts that have not been active for a maximum of 90 days must be either locked or disabled. This is vital for inactive account management.

The pgtle Strategy: Our Bouncer and Sentry Droid

Our strategy leverages pgtle to enforce these critical account security policies:

1. Tracking Login Attempts and Activity: New tables (password_check.user_login_activity) will meticulously record every login attempt (successful or failed) and the last successful activity for each user.
2. Modifying clientauth_hook for Account Lockout: Our existing clientauth_hook (from Part 6) is enhanced to:
   • Check for active lockouts in password_check.locked_accounts and immediately reject connections if an account is locked.
   • Increment failed login attempts on authentication failure.
   • Lock the account in password_check.locked_accounts if failed attempts exceed the configured lockout_threshold.
3. Implementing Inactive Account Management: A new pgtle function (password_check.manage_inactive_accounts) will periodically identify and disable accounts based on their last_successful_login against a configurable inactive_threshold.

Database Design Changes (Version 0.5)

These schema changes are crucial for supporting both account lockout and inactive account management:

1. New password_check.user_login_activity Table:
   • Purpose: Tracks username, last_successful_login, failed_attempts, and last_activity.
   • Snippet:

     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
     );

2. New password_check.locked_accounts Table:
   • Purpose: Tracks accounts currently locked due to failed login attempts, including username, locked_until, and locked_by.
   • Snippet:

     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
     );

3. Updated password_check.profiles Table (New Columns):
   • Purpose: Adds configurable parameters for lockout and inactivity per role.
   • New Columns: lockout_threshold (INTEGER, default 10), lockout_duration_minutes (INTEGER, default 30), inactive_threshold (INTERVAL, default ’90 days’).
   • Snippet:

     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';

4. New password_check.v_role_members_parameters View:
   • Purpose: Consolidates user, role, and policy parameters (max validity, lockout, inactivity thresholds) for easy querying.
   • Snippet: (Refer to the main blog post for the full CREATE OR REPLACE VIEW statement.)
5. New password_check.get_member_priority_role Function:
   • Purpose: Helper function to determine a user’s most prioritized PCI role for dynamic policy application.
   • Snippet: (Refer to the main blog post for the full CREATE OR REPLACE FUNCTION statement.)

Key clientauth_hook Logic (Version 0.5 for PCI DSS 8.3.4)

The clientauth_hook is central to account lockout enforcement:

• Initial Lockout Check: Checks password_check.locked_accounts. If NOW() < locked_until, RAISE EXCEPTION to reject login. If expired, deletes entry and resets failed_attempts.
• Update user_login_activity: Inserts/updates user record.
• Failed Login (status = -1): Increments failed_attempts. If failed_attempts >= lockout_threshold, inserts/updates password_check.locked_accounts with locked_until = NOW() + lockout_duration_minutes. Resets failed_attempts to 0.
• Successful Login (status = 0): Resets failed_attempts to 0, updates last_activity and last_successful_login. Includes existing password expiration check from Part 6.

New password_check.manage_inactive_accounts Function (PCI DSS 8.2.6)

This function handles inactive account management:

• Purpose: Identifies and disables accounts inactive for longer than their inactive_threshold.
• Logic: Iterates through user_login_activity and v_role_members_parameters. If last_successful_login is older than inactive_threshold, executes ALTER ROLE <username> NOLOGIN;.
• Execution: Typically run via an external scheduler (e.g., cron job).

Installation/Update: Version 0.5

To bring your pgtle extension to Version 0.5:

1. Connect to Your Maintenance Database: 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 file (provided in the main blog post) and paste it into your psql prompt.
3. Apply the Extension Update: ALTER EXTENSION pci_password_check_rules UPDATE TO '0.5';
4. Ensure clientauth Hook is Enabled (Crucial!):

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

Verification: Did the Bouncer and Sentry Droid Come Online?

After applying the update and restarting PostgreSQL:

• SELECT * FROM pgtle.available_extensions(); (should show 0.5)
• SELECT * FROM pgtle.available_extension_versions();
• SELECT * FROM pgtle.extension_update_paths('pci_password_check_rules');
• \d password_check.user_login_activity (verify table exists)
• \d password_check.locked_accounts (verify table exists)
• SELECT role, lockout_threshold, lockout_duration_minutes, inactive_threshold FROM password_check.profiles; (verify new columns)
• SELECT * FROM password_check.v_role_members_parameters LIMIT 5; (verify view)
• SELECT name, setting, short_desc, context FROM pg_settings WHERE name LIKE 'pgtle%'; (verify clientauth settings)

Testing Your Account Security Defenses!

Scenario 1: Testing Account Lockout (PCI DSS 8.3.4)

1. Create test user:

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

2. Attempt failed logins (10+ times):

   psql -U locked_test_user -d heydbamaint

   • Expected: Subsequent login attempts fail with FATAL: Account locked_test_user has been locked...
3. Verify lockout (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';

4. Wait for lockout to expire, then log in (should succeed).

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

1. Create new user:

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

2. Simulate old login:

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

3. Run function:

   SELECT password_check.manage_inactive_accounts();

   • Expected: NOTICE: Account inactive_test_user disabled due to inactivity.
4. Verify status:

   \du inactive_test_user

   (should show Cannot login)

5. Attempt login (should fail).

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.