Log access restricted to audit/compliance roles only

Audit logs that any authenticated user can query are a surveillance and privacy failure — they expose the financial activity of every customer to every employee who has a database login. PCI-DSS v4.0 Req-10.3.1 limits read access to audit log files to those with a job-related need — precisely the scope of this control. NIST 800-53 AU-9 requires that audit information is protected from unauthorized access, modification, and deletion. OWASP A01:2021 (Broken Access Control) covers this class directly — granting SELECT on transaction_logs to app_role without a compliance restriction means any developer querying the database has full visibility into the financial movements of all customers. CWE-285 (Improper Authorization) is the vulnerability. A disgruntled employee or compromised app credential can exfiltrate the complete transaction history of any user with a single query.

Revoke SELECT from general roles and grant it only to a designated compliance role in db/migrations/:

REVOKE SELECT ON transaction_logs FROM app_role;
CREATE ROLE compliance_role;
GRANT SELECT ON transaction_logs TO compliance_role;

-- Row-level: users can only see their own rows
ALTER TABLE transaction_logs ENABLE ROW LEVEL SECURITY;
CREATE POLICY user_self_read ON transaction_logs
  FOR SELECT TO app_role
  USING (user_id = current_setting('app.current_user_id')::uuid);

In src/middleware/ or your route handlers, enforce the compliance role check before any audit log query:

app.get('/api/audit-logs', authenticate, requireRole('compliance'), async (req, res) => {
  const logs = await db('transaction_logs').where({ user_id: req.query.userId });
  res.json(logs);
});