PostgreSQL是否支持多次未授权登录后锁定用户账户及实现方法

Great question! PostgreSQL doesn’t include a native account-locking feature for failed login attempts out of the box, but there are several reliable ways to implement this functionality—both using built-in tools and external scripts. Let’s walk through the most practical approaches.

Short answer: No, not natively. But you can build this capability using PostgreSQL's system views, custom functions, or external tools tailored to your version and needs.

Method 1: Using pg_auth_history (PostgreSQL 14+) #

PostgreSQL 14 introduced the pg_auth_history system view, which tracks all authentication attempts (successes and failures). We can pair this with a custom PL/pgSQL function and a scheduler to automatically lock users who exceed a failure threshold.

Step 1: Verify pg_auth_history is enabled #

Ensure your PostgreSQL instance is version 14+, and add these settings to postgresql.conf:

log_authentication = on
track_activities = on

Restart PostgreSQL if you modify these settings to apply changes.

Step 2: Create a function to lock users with too many failed attempts #

This function checks the last hour's failed logins and locks users who hit a threshold (we’ll use 5 attempts as an example):

CREATE OR REPLACE FUNCTION lock_failed_users()
RETURNS void AS $$
DECLARE
    rec record;
    failure_threshold integer := 5; -- Adjust this to your security needs
    lock_window interval := '1 hour'; -- Only count recent failures
BEGIN
    FOR rec IN
        SELECT rolname, count(*) as failed_count
        FROM pg_auth_history
        WHERE failed = true
          AND now() - auth_time < lock_window
        GROUP BY rolname
        HAVING count(*) >= failure_threshold
    LOOP
        UPDATE pg_authid
        SET rolcanlogin = false
        WHERE rolname = rec.rolname;
        
        RAISE NOTICE 'Locked user "%" due to % consecutive failed login attempts', rec.rolname, rec.failed_count;
    END LOOP;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Grant execute permission to the scheduler user (e.g., postgres)
GRANT EXECUTE ON FUNCTION lock_failed_users() TO postgres;

Step 3: Automate the function with pg_cron #

The pg_cron extension lets you schedule database jobs. Install it first (via your package manager or source), then schedule the function to run every minute:

-- Enable pg_cron if not already installed
CREATE EXTENSION IF NOT EXISTS pg_cron;

-- Schedule the lock check to run every minute
SELECT cron.schedule('*/1 * * * *', 'SELECT lock_failed_users();');

Step 4: Add automatic unlocking #

To unlock users after a set lock period, create a similar function and schedule it:

CREATE OR REPLACE FUNCTION unlock_expired_locks()
RETURNS void AS $$
DECLARE
    rec record;
    lock_duration interval := '1 hour'; -- How long users stay locked
BEGIN
    FOR rec IN
        SELECT a.rolname, max(h.auth_time) as last_failed_time
        FROM pg_authid a
        JOIN pg_auth_history h ON a.rolname = h.rolname
        WHERE a.rolcanlogin = false
          AND h.failed = true
        GROUP BY a.rolname
        HAVING now() - max(h.auth_time) >= lock_duration
    LOOP
        UPDATE pg_authid
        SET rolcanlogin = true
        WHERE rolname = rec.rolname;
        
        RAISE NOTICE 'Unlocked user "%" after lock period expired', rec.rolname;
    END LOOP;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

GRANT EXECUTE ON FUNCTION unlock_expired_locks() TO postgres;

-- Schedule the unlock check to run every minute
SELECT cron.schedule('*/1 * * * *', 'SELECT unlock_expired_locks();');

Method 2: External Scripting (For PostgreSQL <14) #

If you’re on an older PostgreSQL version without pg_auth_history, you can parse the PostgreSQL log file to track failed attempts and lock users via a script.

Here’s a bash script example (adjust paths and thresholds to your setup):

#!/bin/bash
PG_LOG="/var/log/postgresql/postgresql-13-main.log"
FAILURE_THRESHOLD=5
LOCK_DURATION=3600 # 1 hour in seconds
PG_USER="postgres"

# Get users with too many failed logins in the last hour
failed_users=$(grep -E "FATAL:  password authentication failed for user" "$PG_LOG" \
    | grep -E "$(date -d "-1 hour" +"%Y-%m-%d %H")" \
    | awk '{print $10}' | sed 's/"//g' \
    | sort | uniq -c \
    | awk -v thresh="$FAILURE_THRESHOLD" '$1 >= thresh {print $2}')

# Lock those users
for user in $failed_users; do
    psql -U "$PG_USER" -c "ALTER USER \"$user\" NOLOGIN;"
    echo "[$(date)] Locked user: $user" >> /var/log/pg_login_lock.log
done

# Unlock users who've been locked longer than the duration
locked_users=$(psql -U "$PG_USER" -t -c "SELECT rolname FROM pg_authid WHERE rolcanlogin = false;" | grep -v "^$")
for user in $locked_users; do
    last_failed=$(grep -E "FATAL:  password authentication failed for user \"$user\"" "$PG_LOG" | tail -1 | awk '{print $1, $2}')
    if [ -n "$last_failed" ]; then
        last_epoch=$(date -d "$last_failed" +%s)
        current_epoch=$(date +%s)
        if [ $((current_epoch - last_epoch)) -ge $LOCK_DURATION ]; then
            psql -U "$PG_USER" -c "ALTER USER \"$user\" LOGIN;"
            echo "[$(date)] Unlocked user: $user" >> /var/log/pg_login_lock.log
        fi
    fi
done

Save this as pg_login_lock.sh, make it executable (chmod +x pg_login_lock.sh), then add a cron job to run it every minute:

*/1 * * * * /path/to/pg_login_lock.sh

Method 3: Using a Connection Pool (pgBouncer) #

If you use pgBouncer as a connection pool, you can leverage its max_login_attempts configuration to block repeated failed login attempts at the pool level. This doesn’t lock the database user itself, but it prevents further connection attempts from the offending IP or user.

Add this to your pgbouncer.ini:

max_login_attempts = 5

• Always test these methods in a staging environment first to avoid locking out legitimate users.
• The SECURITY DEFINER function runs with the privileges of its creator, so ensure only trusted users can modify these functions.
• For manual unlocks, use ALTER USER <username> LOGIN;

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