Does PostgreSQL have built-in password policies?

No. PostgreSQL validates password format (it must be a string) but does not enforce complexity, length, rotation, or history. The pg_hba.conf controls authentication methods, but actual password strength is unregulated. Extensions like pgcomply or passwordcheck can add policy enforcement.

What is SCRAM-SHA-256 and why does it matter?

SCRAM-SHA-256 is a secure challenge-response authentication protocol. Unlike MD5 (which sends a weak hash), SCRAM never transmits the password or its hash over the network. It has been the recommended method since PostgreSQL 10 and the default since PostgreSQL 14. Set password_encryption = scram-sha-256 in postgresql.conf.

PostgreSQL Password Policy Enforcement: Beyond Default Settings

The Default Problem

Create a PostgreSQL role with a one-character password:

CREATE ROLE analyst WITH LOGIN PASSWORD 'x';
-- No error. PostgreSQL accepts this.

This is why password policies must be enforced at the application or extension level.

Setting a Password Policy

SELECT pgcomply.set_password_policy(
  min_length := 12,
  require_upper := true,
  require_lower := true,
  require_digit := true,
  require_special := true,
  max_age_days := 90,
  history_count := 5
);

Checking Current Policy

SELECT * FROM pgcomply.password_policy();

Checking Role Compliance

SELECT * FROM pgcomply.password_status();

 role_name | password_age | expires_in | meets_policy | last_changed
-----------+--------------+------------+--------------+--------------------
 app_user  | 45 days      | 45 days    | true         | 2026-01-08 10:00
 analyst   | 120 days     | EXPIRED    | false        | 2025-10-25 09:00
 dev_lead  | 23 days      | 67 days    | true         | 2026-01-30 14:00

SCRAM-SHA-256 Setup

-- 1. Set encryption method
ALTER SYSTEM SET password_encryption = 'scram-sha-256';
SELECT pg_reload_conf();

-- 2. Update pg_hba.conf to require SCRAM
-- hostssl all all 0.0.0.0/0 scram-sha-256

-- 3. Re-set existing passwords (they need to be re-hashed)
ALTER ROLE analyst PASSWORD 'NewStr0ng!Pass#';

Monitoring Authentication

Track suspicious activity:

-- Active sessions with connection details
SELECT * FROM pgcomply.session_tracking();

-- Find idle connections (potential stale credentials)
SELECT * FROM pgcomply.idle_users('1 hour');

Detecting Weak Passwords in Existing Roles

After setting a policy, check which existing roles violate it:

SELECT * FROM pgcomply.password_status();

 role_name  | password_set | password_age | expires_in  | meets_policy | last_changed
------------+--------------+--------------+-------------+--------------+-------------------
 app_user   | true         | 45 days      | 45 days     | true         | 2026-01-08 10:00
 analyst    | true         | 120 days     | EXPIRED     | false        | 2025-10-25 09:00
 dev_lead   | true         | 23 days      | 67 days     | true         | 2026-01-30 14:00
 backup_svc | true         | 340 days     | EXPIRED     | false        | 2025-03-18 08:00
 staging    | false        | —            | NO_PASSWORD | false        | —

Three immediate actions from this output: force analyst and backup_svc to reset passwords, and either set a password or remove the staging role.

Common PostgreSQL Authentication Mistakes

Mistake 1: Using trust authentication in pg_hba.conf

# NEVER do this in production
host all all 0.0.0.0/0 trust

This allows anyone to connect as any user without a password. It exists for initial setup and should be replaced with scram-sha-256 immediately.

Mistake 2: Using md5 instead of scram-sha-256

MD5 password hashing has been cryptographically broken since 2004. Existing md5 entries in pg_hba.conf should be migrated:

-- Check current authentication methods
SELECT * FROM pg_hba_file_rules;

-- Update all roles to use SCRAM
ALTER SYSTEM SET password_encryption = 'scram-sha-256';
SELECT pg_reload_conf();

-- Re-set every role's password to re-hash with SCRAM
ALTER ROLE app_user PASSWORD 'existing_or_new_password';

Mistake 3: No distinction between human and service accounts

Service accounts (backup, monitoring, replication) should have separate password policies: longer passwords, no rotation (use certificates instead), and restricted connection sources in pg_hba.conf.

Integrating with Health Checks

pgcomply.health_check() includes password-related checks:

SELECT rule, status, detail FROM pgcomply.health_check()
WHERE category = 'auth';

 rule            | status | detail
-----------------+--------+---------------------------
 PW_ENCRYPTION   | PASS   | scram-sha-256
 SUPERUSER_COUNT | WARN   | 2 superusers (recommend ≤ 1)

Summary

Enterprise password security in PostgreSQL requires explicit configuration. pgcomply.set_password_policy() enforces complexity, rotation, and history. Combined with SCRAM-SHA-256 and SSL, this brings PostgreSQL authentication to CIS Benchmark standards.