Quick-Reference: Part 8 – Vendor Defaults & First-Time Logins

Your Cheat Sheet for Tackling Default Passwords and Mandatory First Logins with pgtle

Welcome, vigilant guardians! This quick-reference guide distills the essence of “Part 8: The Final Frontier – Tackling Vendor Defaults and First-Time Login Woes” into actionable steps. If you’re looking to rapidly implement bad password prevention and first-time login enforcement 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 8: The Final Frontier – Tackling Vendor Defaults and First-Time Login Woes. And for the entire saga, check out our Series Overview Page.

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.

This guide focuses on Version 0.6, which introduces robust mechanisms for preventing common/default passwords and enforcing mandatory password changes on first login.

The PCI DSS Mandate: Securing the Gates

• Requirement 8.3.8 (Good Practice): Implement processes to confirm passwords meet policy.
  • Mandate: Prevent the use of weak, easily compromised, or vendor-supplied default passwords. This is crucial for bad password prevention.
• Requirement 8.3.5: Require all users to change their password upon first login after account creation or password reset.
  • Mandate: New users (or those with reset passwords) must immediately change their temporary password to a unique, strong one. This is vital for first-time login enforcement.

The pgtle Strategy: Our Final Defenses

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

1. password_check.bad_passwords Table: A new table to store cryptographic hashes of known weak, compromised, or vendor-default passwords. This is central to bad password prevention.
2. passcheck_hook Integration: Our main passcheck_hook is updated to consult the bad_passwords list and reject non-compliant passwords. This ensures password policy enforcement.
3. password_check.user_login_activity Table (New Columns): Adds password_reset_required and password_first_login flags to manage the first-time login flow.
4. passcheck_hook Logic for New Users: Sets password_reset_required and password_first_login to TRUE for new users (pci_new_users role), initiating the mandatory password change process.
5. clientauth_hook Logic for First Login:
   • On a user’s first successful login when password_reset_required is TRUE and password_first_login is TRUE, it issues a WARNING and immediately sets password_first_login to FALSE.
   • On subsequent logins (if password_reset_required is TRUE but password_first_login is FALSE), it raises a hard EXCEPTION, preventing login until the password is changed. This ensures login enforcement.
   • Superusers are explicitly exempted from this first-time login rule.
6. passcheck_hook Logic for Flag Clearance: Clears password_reset_required and password_first_login flags upon a successful password change, signifying compliance.

Database Design Changes (Version 0.6)

These schema changes are crucial for supporting bad password prevention and first-time login enforcement:

1. New password_check.bad_passwords Table:
   • Purpose: Stores hashes of unacceptable passwords.
   • Snippet (CREATE TABLE):

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

   • Initial Population: Populated with Blowfish-hashed common/default passwords. (Refer to the main blog post for INSERT statement details).
2. Updated password_check.user_login_activity Table (New Columns):
   • Purpose: Manages the first-time login “rite of passage.”
   • New Columns: password_reset_required (BOOLEAN), password_first_login (BOOLEAN).
   • 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;

3. New Helper Functions for Bad Passwords Management:
   • password_check.add_bad_password(TEXT): Adds a plaintext password (hashed internally) to the list.
   • password_check.remove_bad_password(TEXT): Removes a password from the list.
   • password_check.is_bad_password(TEXT): Checks if a password is in the list.
   • 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;

Installation/Update: Version 0.6

To bring your pgtle extension to Version 0.6:

1. Connect to Your Maintenance Database: psql -d heydbamaint -U postgres
2. Execute the Update Path Code: Download the entire SQL code from pci_password_check_rules_0.6_up_0.5-0.6.sql and execute it in your psql prompt.

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

3. Apply the Extension Update:

   ALTER EXTENSION pci_password_check_rules UPDATE TO '0.6';

4. Enable clientauth Hook (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'; --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 PostgreSQL:

• SELECT * FROM pgtle.available_extensions();

  (should show 0.6)

• SELECT * FROM pgtle.available_extension_versions();

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

• \d password_check.user_login_activity

  (confirm new columns)

• \d password_check.bad_passwords

  (confirm table exists)

• SELECT name, setting, short_desc, context FROM pg_settings WHERE name LIKE 'pgtle%';

  (confirm clientauth settings)

Testing Your New Defenses!

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. Create user with bad password:

   CREATE USER myuser4 WITH PASSWORD 'daewuu';

   • Expected Error: ERROR: Password validation failed for user myuser4: Password is too common...
3. Change existing password to bad password:

   ALTER USER evandro WITH PASSWORD 'daewuu';

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

   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 flags (superuser):

     SELECT username, password_reset_required, password_first_login FROM password_check.user_login_activity WHERE username = 'new_recruit';

     (Both should be t).

2. First login attempt with temporary password:

   psql -U new_recruit -d heydbamaint

   • Expected Behavior: Connection succeeds, but WARNING and INFO messages appear: Your password must be changed...
   • Verify password_first_login is f (superuser):

     SELECT username, password_reset_required, password_first_login FROM password_check.user_login_activity WHERE username = 'new_recruit';

     (password_reset_required t, password_first_login f).

3. Second login attempt with same temporary password:

   psql -U new_recruit -d heydbamaint

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

This quick-reference provides the essential commands and concepts for implementing PCI DSS bad password prevention and first-time login enforcement with pgtle Version 0.6. You’re now well on your way to a more secure and compliant PostgreSQL database!