UitbreidenOS When to activate
- Reviewing database user privileges and role assignments
- Auditing access logs for suspicious queries or unauthorized access
- Verifying encryption at rest and in transit
- Preparing for compliance audits (SOC 2, HIPAA, GDPR)
- Identifying overly permissive grants or orphaned accounts
When NOT to use
- For network-level security (use firewall/security group tools)
- For application-layer auth (use auth-flow-designer)
- For OS-level hardening
Instructions
- Enumerate all database users and roles. Export current grants and map to principle of least privilege.
- Identify orphaned accounts. Users with no recent login or belonging to departed employees.
- Audit sensitive data access. Log queries touching PII, PCI, or PHI columns in the last 30 days.
- Verify encryption status. Confirm TLS for connections, AES/TDE for data at rest, and key rotation schedule.
- Check password policies. Enforce minimum complexity, rotation intervals, and failed-login lockout thresholds.
- Review DDL audit logs. Flag unauthorized schema changes, privilege escalations, or bulk exports.
- Generate compliance report. Map findings to SOC 2 / HIPAA / GDPR controls with remediation priorities.
Example
# Audit all users with SUPER or ADMIN privileges
SELECT user, host, grantee, privilege_type FROM information_schema.user_privileges WHERE privilege_type IN ('SUPER','ALL PRIVILEGES');
# Find accounts with no login in 90 days
SELECT usename, valuntil FROM pg_user WHERE usename NOT IN (SELECT usename FROM pg_stat_activity WHERE backend_start > now() - interval '90 days');