如何在EDB PostgreSQL中创建命名配置文件并配置账户安全规则

Alright, let's tackle how to set up named configuration profiles in EDB PostgreSQL and enforce those critical account security rules. I'll break this down into actionable steps so you can implement each part smoothly.

EDB PostgreSQL supports Oracle-style profiles to bundle security and resource limits for roles. Start by creating a profile (pick a descriptive name like strict_security_profile):

CREATE PROFILE strict_security_profile LIMIT
  -- We'll add specific security rules in the next sections
  FAILED_LOGIN_ATTEMPTS 5
  PASSWORD_LOCK_TIME 1;

Once the profile exists, assign it to any login role with:

ALTER ROLE your_login_role PROFILE strict_security_profile;

Let's add each required security rule to the profile, one by one.

2.1 Track Failed Login Attempts #

To count failed login attempts, you need two key pieces:

1. Enable login failure logging in your postgresql.conf file:

   log_connections = on
   log_failed_connections = on
   log_line_prefix = '%t [%p]: [%c-%l] user=%u,db=%d,app=%a,client=%h '
   

   Reload the configuration to apply changes:

   SELECT pg_reload_conf();
   

2. Query system views for attempt data: EDB provides the pg_auth_failure system view to view recent failed logins. Run this query for a breakdown:

   SELECT usename, client_addr, count(*) AS failed_attempts, max(auth_time) AS last_failure
   FROM pg_auth_failure
   GROUP BY usename, client_addr
   ORDER BY failed_attempts DESC;
   

2.2 Lock Accounts After Too Many Failed Attempts #

We set the basics in the initial profile, but let's clarify and adjust as needed:

• FAILED_LOGIN_ATTEMPTS 5: Locks the account after 5 failed login tries
• PASSWORD_LOCK_TIME 1: Keeps the account locked for 1 hour (use UNLIMITED for permanent lock until manual unlock)

Update the profile to tweak these values:

ALTER PROFILE strict_security_profile LIMIT
  FAILED_LOGIN_ATTEMPTS 3  -- Adjust to your preferred threshold
  PASSWORD_LOCK_TIME 24;   -- Lock for 24 hours instead

To manually unlock a locked account:

ALTER ROLE your_login_role ACCOUNT UNLOCK;

2.3 Enforce Password Complexity Rules #

EDB lets you use a custom password verification function to enforce complexity. Here's a sample function requiring 8+ characters, uppercase, lowercase, numbers, and special characters:

CREATE OR REPLACE FUNCTION enforce_password_complexity(username text, password text, old_password text)
RETURNS boolean AS $$
BEGIN
  -- Check minimum length
  IF length(password) < 8 THEN
    RAISE EXCEPTION 'Password must be at least 8 characters long';
  END IF;
  -- Require uppercase letter
  IF password !~ '[A-Z]' THEN
    RAISE EXCEPTION 'Password must include at least one uppercase letter';
  END IF;
  -- Require lowercase letter
  IF password !~ '[a-z]' THEN
    RAISE EXCEPTION 'Password must include at least one lowercase letter';
  END IF;
  -- Require number
  IF password !~ '[0-9]' THEN
    RAISE EXCEPTION 'Password must include at least one number';
  END IF;
  -- Require special character
  IF password !~ '[!@#$%^&*()]' THEN
    RAISE EXCEPTION 'Password must include at least one special character (!@#$%^&*())';
  END IF;
  RETURN true;
END;
$$ LANGUAGE plpgsql STRICT;

Attach this function to your profile:

ALTER PROFILE strict_security_profile LIMIT
  PASSWORD_VERIFY_FUNCTION enforce_password_complexity;

Note: EDB also provides a built-in edb_password_verify function if you don't want to write your own—just use that function name instead.

2.4 Restrict Password Reuse #

You can prevent users from reusing old passwords with either of these two profile parameters (you can't use both at the same time):

1. Limit by number of previous passwords: Block reuse of the last 5 passwords

   ALTER PROFILE strict_security_profile LIMIT
     PASSWORD_REUSE_MAX 5;
   

2. Limit by time: Block reuse of passwords used in the last 90 days

   ALTER PROFILE strict_security_profile LIMIT
     PASSWORD_REUSE_TIME 90;
   

• Make sure you're using a superuser account to create profiles and alter roles
• Any changes to postgresql.conf require a reload or restart to take effect
• Test each rule thoroughly (e.g., intentionally fail logins to verify locking, try weak passwords to check complexity enforcement)

内容的提问来源于stack exchange，提问作者Dheeraj Nambiar