Your Cheat Sheet for Role-Based Password Complexity with pgtle
Welcome, swift strategists! This quick-reference guide distills the essence of “Part 4: The Enigma of Complexity – Taming Passwords with pgtle (Part 2)” into actionable steps. If you’re looking to rapidly implement role-based password complexity using pgtle in PostgreSQL for PCI DSS compliance, you’ve come to the right place! This guide is your essential resource for enhancing PostgreSQL password 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 4 (SEO): The Enigma of Complexity – Taming Passwords with pgtle (Part 2). And for the entire saga, check out our Series Overview Page.
This guide focuses solely on Version 0.2, which introduces robust role-based password policies, making your PostgreSQL password security truly dynamic and compliant with PCI DSS password rules.
The pgtle Setup Ritual (Quick Recap for pgtle Installation)
Before deploying your custom extension, ensure your PostgreSQL environment is primed for pgtle‘s powerful password validation. This pgtle setup is fundamental for robust database security.
- Create Your Maintenance Database (e.g.,
heydbamaint):CREATE DATABASE heydbamaint; - Grant
pgtle_adminto Your Superuser (e.g.,postgres):GRANT pgtle_admin TO postgres; - Point
pgtleto Your Maintenance DB: This crucialpgtleconfiguration step ensures your password policies are centrally managed.ALTER SYSTEM SET pgtle.passcheck_db_name TO 'heydbamaint'; SELECT pg_catalog.pg_reload_conf(); -- Apply the change - Enable
pgtlePassword Checking (chooseonorrequire): Activating thispgtlehook is key for enforcing password complexity.ALTER SYSTEM SET pgtle.enable_password_check TO 'on'; -- Or 'require' for strict enforcement SELECT pg_catalog.pg_reload_conf(); -- Apply the change
The pci_password_check_rules Extension – Version 0.2
This version introduces advanced role-based password policies, allowing different password rules for distinct user groups (e.g., pci_admin_users, pci_app_users, pci_standard_users, and a default for new users). This is a significant enhancement for PostgreSQL password security and PCI DSS compliance.
What it does:
- Creates necessary group roles if they don’t exist, crucial for defining role-based password policies.
- Introduces a
password_check.profilestable to store distinct password rules per role, centralizing your PostgreSQL password policy definitions. - Modifies the
passcheck_hookto dynamically apply rules based on a user’s role membership, ensuring tailored password validation. - Enforces that newly created users must be assigned to a PCI role before their password can be changed (a crucial step for PCI DSS 8.3.5 later), bolstering PostgreSQL security best practices.
The SQL Code (Version 0.2):
The full SQL code for pci_password_check_rules_0.2_up_0.1-0.2.sql is available in its dedicated file. You will need to copy the contents from that file.
SELECT
PGTLE.INSTALL_UPDATE_PATH (
'pci_password_check_rules', -- Name of your custom pg_tle extension
'0.1',
'0.2', -- Incremented version to add role separation for non-human account handling
$_pgtle_$
--Check if the ROLES exists. It's a requirement that the roles exists in order to proceed.
--pci_admin_users,pci_app_users,pci_standard_users
do
$$
declare
v_arr_rolnames text ARRAY;
v_rolname text;
begin
v_arr_rolnames := '{"pci_admin_users","pci_app_users","pci_standard_users"}';
FOREACH v_rolname IN ARRAY v_arr_rolnames
LOOP
if not exists (SELECT 1 FROM pg_catalog.pg_roles r WHERE r.rolname = v_rolname) then
RAISE NOTICE 'Role % does not exist, creating it.', v_rolname;
EXECUTE format($e$
CREATE ROLE %1$s
$e$, v_rolname);
end if;
END LOOP;
end
$$
;
--Create the table to hold the profiles.
--The table must check if the group roles already exists (create a trigger).
--Or maybe the table must not allow any update to its values, and make it fixed.
CREATE TABLE IF NOT EXISTS password_check.profiles (
role TEXT PRIMARY KEY,
min_length INTEGER DEFAULT 15 NOT NULL ,
require_special_char BOOLEAN DEFAULT true NOT NULL,
require_uppercase BOOLEAN DEFAULT true NOT NULL,
require_lowercase BOOLEAN DEFAULT true NOT NULL,
require_digit BOOLEAN DEFAULT true NOT NULL
);
INSERT INTO password_check.profiles (role,min_length) values ('pci_admin_users',15) ON CONFLICT (role) DO NOTHING;
INSERT INTO password_check.profiles (role,min_length) values ('pci_app_users',15) ON CONFLICT (role) DO NOTHING;
INSERT INTO password_check.profiles (role,min_length) values ('pci_standard_users',12) ON CONFLICT (role) DO NOTHING;
INSERT INTO password_check.profiles (role,min_length) values ('pci_new_users',12) ON CONFLICT (role) DO NOTHING;
-- Helper function to check if a given username is a member of a specified PostgreSQL role (group).
CREATE OR REPLACE FUNCTION password_check.is_member_of_role(
target_username TEXT,
group_role_name TEXT
) RETURNS BOOLEAN AS $$
DECLARE
is_member BOOLEAN := FALSE;
BEGIN
SELECT EXISTS (
SELECT 1
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
WHERE r_member.rolname = target_username
AND r_role.rolname = group_role_name
) INTO is_member;
RETURN is_member;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER; --Executed with the privileges of the user that owns the function.
-- The main passcheck hook function that enforces password policies.
CREATE OR REPLACE FUNCTION password_check.passcheck_hook(
username TEXT,
password TEXT,
password_type pgtle.password_types,
valid_until TIMESTAMPTZ,
valid_null BOOLEAN
) RETURNS VOID AS $_FUNCTION_$ -- ADDED THIS BLOCK LABEL
DECLARE
invalid_pw_reason TEXT := '';
current_min_length INTEGER := 12;
current_require_special_char BOOLEAN := TRUE;
current_require_uppercase BOOLEAN := TRUE;
current_require_lowercase BOOLEAN := TRUE;
current_require_digit BOOLEAN := TRUE;
-- Add on 0.2: Flag to check if the user already exists in pg_roles
-- This helps differentiate between CREATE ROLE and ALTER ROLE.
user_exists BOOLEAN;
BEGIN
-- Check if the user already exists in pg_roles.
-- This helps differentiate between CREATE ROLE and ALTER ROLE.
SELECT EXISTS (SELECT 1 FROM pg_catalog.pg_roles WHERE rolname = passcheck_hook.username)
INTO user_exists;
--- 1. Determine Role-Based Policies ---
--- For CREATE ROLE, we allow a default policy. For ALTER ROLE, we enforce role membership.
IF user_exists AND password_check.is_member_of_role(username, 'pci_admin_users') THEN
SELECT
min_length,
require_special_char,
require_uppercase,
require_lowercase,
require_digit
INTO
current_min_length,
current_require_special_char,
current_require_uppercase,
current_require_lowercase,
current_require_digit
FROM
password_check.profiles
WHERE
role='pci_admin_users';
ELSIF user_exists AND password_check.is_member_of_role(username, 'pci_app_users') THEN -- NOW FOR NON-HUMAN APP ACCOUNTS
SELECT
min_length,
require_special_char,
require_uppercase,
require_lowercase,
require_digit
INTO
current_min_length,
current_require_special_char,
current_require_uppercase,
current_require_lowercase,
current_require_digit
FROM
password_check.profiles
WHERE
role='pci_app_users';
ELSIF user_exists AND password_check.is_member_of_role(username, 'pci_standard_users') THEN
SELECT
min_length,
require_special_char,
require_uppercase,
require_lowercase,
require_digit
INTO
current_min_length,
current_require_special_char,
current_require_uppercase,
current_require_lowercase,
current_require_digit
FROM
password_check.profiles
WHERE
role='pci_standard_users';
--If the user does not exists (It's a CREATE ROLE), allow it to be created and set the default password rules.
--Later, the user will not be allowed to CHANGE THE PASSWORD if not set to any of the PCI roles.
ELSIF NOT user_exists THEN
SELECT
min_length,
require_special_char,
require_uppercase,
require_lowercase,
require_digit
INTO
current_min_length,
current_require_special_char,
current_require_uppercase,
current_require_lowercase,
current_require_digit
FROM
password_check.profiles
WHERE
role='pci_new_users';
RAISE NOTICE 'Policy: Default for NEW user (CREATE ROLE)';
RAISE NOTICE 'Assign a PCI ROLE to the user IMMEDIATELY';
ELSE
-- If the user exists but does not belong to any defined PCI role, prevent password change.
RAISE EXCEPTION 'Password change not allowed for user %: User must be assigned to one of the defined roles (pci_admin_users, pci_app_users, pci_standard_users).', username;
END IF;
--- 2. Apply Password Complexity Checks (PCI DSS 8.3.6) ---
IF length(password) < current_min_length THEN
invalid_pw_reason := invalid_pw_reason || 'Password must be at least ' || current_min_length || ' characters long. ';
END IF;
IF current_require_uppercase AND password !~ '[A-Z]' THEN
invalid_pw_reason := invalid_pw_reason || 'Password must contain at least one uppercase letter. ';
END IF;
IF current_require_lowercase AND password !~ '[a-z]' THEN
invalid_pw_reason := invalid_pw_reason || 'Password must contain at least one lowercase letter. ';
END IF;
IF current_require_digit AND password !~ '[0-9]' THEN
invalid_pw_reason := invalid_pw_reason || 'Password must contain at least one number. ';
END IF;
IF current_require_special_char AND password !~ '[^a-zA-Z0-9\s]' THEN
invalid_pw_reason := invalid_pw_reason || 'Password must contain at least one special character. ';
END IF;
--- 3. Final Check and Raise Exception / Update History ---
IF invalid_pw_reason != '' THEN
RAISE EXCEPTION 'Password validation failed for user %: %', username, invalid_pw_reason;
END IF;
END;
$_FUNCTION_$ LANGUAGE plpgsql;
REVOKE ALL ON FUNCTION password_check.passcheck_hook(TEXT, TEXT, pgtle.password_types, TIMESTAMPTZ, BOOLEAN) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION password_check.passcheck_hook(TEXT, TEXT, pgtle.password_types, TIMESTAMPTZ, BOOLEAN) TO PUBLIC;
SELECT pgtle.register_feature_if_not_exists('password_check.passcheck_hook', 'passcheck');
$_pgtle_$
);Brief Logic Explanation of pgtle Password Validation: The core of the passcheck_hook function performs essential password validation checks. It verifies that the password meets minimum length requirements and includes a mix of character types: at least one uppercase letter, one lowercase letter, one digit, and one special character. If any of these complexity rules are not met, an error message is generated and the password change is prevented, ensuring robust PostgreSQL password security.
The 0.2 update builds upon this by introducing a password_check.profiles table. This table stores varying complexity rules (like different minimum lengths or character type requirements) for different user roles. The passcheck_hook now dynamically queries this table based on the user’s role membership (pci_admin_users, pci_app_users, pci_standard_users). This means the general password validation logic applies, but with parameters tailored to the specific role. For new users, it applies a default policy and then prevents subsequent password changes until they are assigned to a specific PCI role, ensuring proper access control from the start and enhancing overall database security. This comprehensive approach reinforces PostgreSQL PCI DSS compliance.
Installation: Unleashing Version 0.2!
Assuming you have already installed Version 0.1 of the pgtle extension, follow these steps to update to Version 0.2:
- Connect to your
heydbamaintdatabase as a superuser.psql -d heydbamaint -U postgres - Execute the entire content of
pci_password_check_rules_0.2_up_0.1-0.2.sql.- You will need to manually copy the content of the SQL file into your
psqlprompt or run it from a saved file.
# Example if running from a file: psql -d heydbamaint -U postgres -f pci_password_check_rules_0.2_up_0.1-0.2.sql - You will need to manually copy the content of the SQL file into your
- Run the
ALTER EXTENSIONcommand: This finalizes the update of yourpgtleextension.ALTER EXTENSION pci_password_check_rules UPDATE TO '0.2';
Verification: Did It Work, Captain?
Confirm your pgtle extension is correctly installed and updated, validating your PostgreSQL password security implementation:
- Check available extensions:
SELECT * FROM pgtle.available_extensions(); - Check all available versions for your extension:
SELECT * FROM pgtle.available_extension_versions();(You should see
pci_password_check_ruleslisted with0.2as itsdefault_version.) - Check update paths:
SELECT * FROM pgtle.extension_update_paths('pci_password_check_rules');
Testing Your New Fortress!
Put your new role-based password policies to the test! These examples demonstrate the enhanced PostgreSQL password security and adherence to PCI DSS password rules.
- Weak password for
pci_standard_users(should fail):CREATE ROLE test_standard_user WITH PASSWORD 'short'; -- Expected error: Password must be at least 12 characters long. - Strong password for
pci_admin_users(should succeed): This showcases effective PostgreSQL password policy for administrators.CREATE ROLE test_admin_user WITH PASSWORD 'P@ssw0rdF0rAdm1n!'; GRANT pci_admin_users TO test_admin_user; - Change password for unassigned user (should fail after initial creation): This demonstrates the enforcement of PCI DSS requirements for role assignment.
CREATE ROLE unassigned_user WITH PASSWORD 'InitialP@ssw0rd1!'; -- Succeeds initially ALTER ROLE unassigned_user WITH PASSWORD 'NewP@ssw0rd2!'; -- Expected error: User must be assigned to one of the defined PCI roles.Then, assign and retry (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 quick-reference provides the essential commands and concepts for implementing PCI DSS password complexity with pgtle Version 0.2. You’re now well on your way to a more secure and compliant PostgreSQL database! This is a prime example of PostgreSQL security best practices in action.
