Securing User Accounts in PostgreSQL

Securing User Accounts in PostgreSQL: A Comprehensive Technical Guide

PostgreSQL’s robust security framework provides multiple layers of protection for user accounts, but proper configuration is essential to prevent unauthorized access and data breaches. This guide covers advanced techniques for hardening PostgreSQL user account security.

Authentication Methods and Configuration

PostgreSQL supports multiple authentication methods through the pg_hba.conf file. The most secure approaches include:

-- Create users with strong password policies
CREATE USER app_user WITH 
    PASSWORD 'complex_password_123!' 
    VALID UNTIL '2026-12-31'
    CONNECTION LIMIT 10;

-- Enable password encryption
ALTER SYSTEM SET password_encryption = 'scram-sha-256';
SELECT pg_reload_conf();

Configure pg_hba.conf for secure authentication:

# Use SCRAM-SHA-256 for local connections
local   all             all                     scram-sha-256
host    all             all     127.0.0.1/32    scram-sha-256
host    all             all     ::1/128         scram-sha-256

Role-Based Access Control (RBAC)

Implement granular permissions using PostgreSQL’s role system:

-- Create application-specific roles
CREATE ROLE read_only_role;
CREATE ROLE app_writer_role;
CREATE ROLE admin_role;

-- Grant specific permissions
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only_role;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO app_writer_role;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO admin_role;

-- Create users and assign roles
CREATE USER readonly_user WITH PASSWORD 'secure_pass_456!';
GRANT read_only_role TO readonly_user;

-- Prevent role inheritance for sensitive operations
CREATE USER sensitive_admin WITH PASSWORD 'admin_pass_789!' NOINHERIT;
GRANT admin_role TO sensitive_admin;

Password Security and Policies

Implement comprehensive password policies:

-- Install passwordcheck extension for password validation
CREATE EXTENSION IF NOT EXISTS passwordcheck;

-- Set password complexity requirements in postgresql.conf
-- passwordcheck.minimum_length = 12
-- passwordcheck.maximum_length = 128
-- passwordcheck.special_chars = true

-- Create function to enforce password rotation
CREATE OR REPLACE FUNCTION enforce_password_rotation()
RETURNS event_trigger AS $$
DECLARE
    r RECORD;
BEGIN
    FOR r IN SELECT * FROM pg_user WHERE valuntil < CURRENT_DATE + INTERVAL '30 days'
    LOOP
        RAISE WARNING 'Password for user % expires soon: %', r.usename, r.valuntil;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

CREATE EVENT TRIGGER password_rotation_check
    ON ddl_command_end
    EXECUTE FUNCTION enforce_password_rotation();

Connection Security and Monitoring

Secure connections and implement monitoring:

-- Enable SSL/TLS connections
ALTER SYSTEM SET ssl = 'on';
ALTER SYSTEM SET ssl_cert_file = 'server.crt';
ALTER SYSTEM SET ssl_key_file = 'server.key';

-- Configure connection limits and timeouts
ALTER SYSTEM SET max_connections = 200;
ALTER SYSTEM SET idle_in_transaction_session_timeout = '10min';
ALTER SYSTEM SET statement_timeout = '30min';

-- Enable comprehensive logging
ALTER SYSTEM SET log_connections = 'on';
ALTER SYSTEM SET log_disconnections = 'on';
ALTER SYSTEM SET log_failed_login_attempts = 'on';
ALTER SYSTEM SET log_statement = 'all';

Account Lockout and Brute Force Protection

Implement account lockout mechanisms:

-- Create table to track failed login attempts
CREATE TABLE failed_login_attempts (
    username TEXT,
    attempt_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    ip_address INET
);

-- Create function to check and lock accounts
CREATE OR REPLACE FUNCTION check_failed_logins()
RETURNS TRIGGER AS $$
DECLARE
    attempt_count INTEGER;
    lockout_duration INTERVAL := '30 minutes';
BEGIN
    -- Count recent failed attempts
    SELECT COUNT(*) INTO attempt_count
    FROM failed_login_attempts
    WHERE username = NEW.username
    AND attempt_time > CURRENT_TIMESTAMP - INTERVAL '15 minutes';

    -- Lock account after 5 failed attempts
    IF attempt_count >= 5 THEN
        UPDATE pg_authid 
        SET rolvaliduntil = CURRENT_TIMESTAMP + lockout_duration
        WHERE rolname = NEW.username;

        RAISE EXCEPTION 'Account locked due to multiple failed login attempts';
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Create trigger for failed login tracking
CREATE TRIGGER failed_login_trigger
    BEFORE INSERT ON failed_login_attempts
    FOR EACH ROW
    EXECUTE FUNCTION check_failed_logins();

Database-Level Security Hardening

Configure additional security parameters:

-- Disable superuser access from network
ALTER SYSTEM SET local_preload_libraries = 'auth_delay';
ALTER SYSTEM SET auth_delay.milliseconds = 1000;

-- Enable row-level security
ALTER TABLE sensitive_data ENABLE ROW LEVEL SECURITY;

-- Create security policies
CREATE POLICY user_data_policy ON user_accounts
    FOR ALL TO app_user
    USING (user_id = current_setting('app.current_user_id')::INTEGER);

-- Audit trail for sensitive operations
CREATE TABLE security_audit (
    id SERIAL PRIMARY KEY,
    username TEXT,
    operation TEXT,
    table_name TEXT,
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    ip_address INET
);

-- Create audit trigger function
CREATE OR REPLACE FUNCTION audit_security_operations()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO security_audit (username, operation, table_name, ip_address)
    VALUES (
        current_user,
        TG_OP,
        TG_TABLE_NAME,
        inet_client_addr()
    );

    IF TG_OP = 'DELETE' THEN
        RETURN OLD;
    ELSE
        RETURN NEW;
    END IF;
END;
$$ LANGUAGE plpgsql;

Monitoring and Alerting in PostgreSQL

Implement comprehensive monitoring:

-- Create monitoring view for security events
CREATE VIEW security_dashboard AS
SELECT 
    username,
    COUNT(*) as failed_attempts,
    MAX(attempt_time) as last_attempt,
    array_agg(DISTINCT ip_address) as source_ips
FROM failed_login_attempts
WHERE attempt_time > CURRENT_TIMESTAMP - INTERVAL '24 hours'
GROUP BY username
HAVING COUNT(*) > 3;

-- Function to generate security alerts
CREATE OR REPLACE FUNCTION generate_security_alerts()
RETURNS TABLE(alert_type TEXT, message TEXT, severity TEXT) AS $$
BEGIN
    -- Check for suspicious login patterns
    RETURN QUERY
    SELECT 
        'SUSPICIOUS_LOGIN'::TEXT,
        'Multiple failed logins for user: ' || username,
        'HIGH'::TEXT
    FROM security_dashboard
    WHERE failed_attempts > 10;

    -- Check for expired passwords
    RETURN QUERY
    SELECT 
        'PASSWORD_EXPIRY'::TEXT,
        'Password expires soon for user: ' || rolname,
        'MEDIUM'::TEXT
    FROM pg_roles
    WHERE rolvaliduntil < CURRENT_TIMESTAMP + INTERVAL '7 days'
    AND rolvaliduntil IS NOT NULL;
END;
$$ LANGUAGE plpgsql;

Best Practices Summary

1. Use SCRAM-SHA-256 authentication for all connections
2. Implement role-based access control with principle of least privilege
3. Enable comprehensive logging and monitoring
4. Configure SSL/TLS for all network connections
5. Set connection limits and timeouts appropriately
6. Regular password rotation and complexity enforcement
7. Monitor failed login attempts and implement lockout mechanisms
8. Use row-level security for sensitive data access
9. Maintain audit trails for all security-related operations
10. Regular security assessments and penetration testing

This comprehensive approach to PostgreSQL user account security provides multiple layers of protection against common attack vectors while maintaining operational efficiency. Regular review and updates of these security measures ensure continued protection against evolving threats.

Further Reading:

• Troubleshooting InnoDB Cluster Write Throughput and Latency
• Apache Kafka for DBAs
• Kafka Performance Tuning – Producer Configuration and Cluster Optimization
• SQL Performance Nightmares – 5 Query Anti-Patterns
• Sizing Milvus Vector Database for Maximum Performance
• Database roles