CREST CPSA: The Read-Only Database Account That Wasn't

The Read-Only Database Account That Wasn't

You are assessing a database-backed application. The app connects to PostgreSQL as user app_ro, which the DBA describes as 'read-only, least privilege.' You have application-level access and can reach the DB directly from the test host (authorised). You find you can connect as app_ro using credentials recovered from the app's config, and that app_ro can SELECT from all schemas including audit and customers, and can call a stored procedure rotate_keys() that the app uses. app_ro cannot write tables directly. The DBA says 'see, read-only - it's fine.'

1. Is 'read-only' an adequate control here? Justify your answer with what you observed.

Read the worked answer

No. 'Read-only' is not the same as 'least privilege,' and the observations show both excessive read scope and an unintended write path:

Excessive read: app_ro can SELECT every schema, including audit and customers. The app almost certainly does not need audit data or full customer records for its function. Read access to PII and audit logs is a confidentiality impact in its own right - an SQL injection or credential leak now yields the entire dataset.
Hidden write path: rotate_keys() is a stored procedure that, by definition, performs privileged actions (key rotation). Being able to execute it means app_ro can cause a security-relevant state change - rotating keys could disrupt the app or invalidate other systems' keys. SECURITY DEFINER procedures frequently run with the definer's privileges, so 'cannot write tables' is misleading.

So the effective privilege set is broader than 'read-only' suggests. Report scope of SELECT and the procedure execution separately.

2. What specific remediation would you recommend, and how would you verify it?

Read the worked answer

Remediation:

Scope reads per schema/table: grant SELECT only on the tables the app actually queries; revoke from audit, customers (or expose only the needed columns via a view).
Remove EXECUTE on rotate_keys() from app_ro; that belongs to a separate privileged role invoked by an admin/ops process, not the web app's runtime account. If the app must trigger rotation, expose a narrowly scoped wrapper that does not require broad definer privileges.
Review SECURITY DEFINER procedures for search_path and privilege escalation.
Consider a separate lower-privilege account for reporting vs runtime.

Verification:

Re-connect as app_ro and attempt SELECT on audit/customers (expect permission denied), EXECUTE rotate_keys() (expect denied), and confirm the app still functions for its real queries.
Confirm the app's normal operations succeed after the change (functional regression), so the fix doesn't get reverted.

Report the effective privilege set, not the label the DBA applied to it.