What is the principle of least privilege, and how does it apply to database user/role design?

The problem with over-privileged accounts

-- Anti-pattern: application connects using the database's superuser/owner account
-- for every single query, including simple SELECTs

If this credential leaks (committed to a public repo, exposed in a misconfigured environment variable dump, extracted via a different vulnerability like SQL injection), the attacker gets everything: read all data, modify or delete any table, alter permissions, drop the whole database. A single over-privileged credential turns any one vulnerability into total compromise.

Designing role-based access

-- Read-only role, for reporting/analytics tools
CREATE ROLE reporting_role;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO reporting_role;

-- Application role: read/write on specific tables it actually needs, nothing else
CREATE ROLE app_role;
GRANT SELECT, INSERT, UPDATE, DELETE ON orders, order_items, customers TO app_role;
-- Deliberately NOT granted: DROP, ALTER, access to an unrelated 'admin_audit_log' table

-- Migration/admin role: schema changes, used only by deployment tooling, not the running app
CREATE ROLE migration_role;
GRANT ALL PRIVILEGES ON SCHEMA public TO migration_role;

Each role reflects a distinct responsibility, and the actual credential used by the running application in production should be the narrowly-scoped app_role, not an admin/superuser account — even though that's more convenient during initial development.

Practical guidelines

• No shared superuser credentials for routine application traffic. Reserve superuser/owner accounts for genuinely administrative tasks (schema migrations, one-off maintenance), performed by a human or a separate, tightly-controlled deployment pipeline — never the day-to-day running application.
• Separate read-only from read-write access wherever the use case allows it — a reporting dashboard or BI tool almost never needs write access, so it shouldn't have it, regardless of how trusted the tool is assumed to be.
• Table/column-level grants, not blanket schema access, when the application genuinely only touches a subset of the schema — this limits what a SQL injection vulnerability (or a bug that constructs an unintended query) can actually reach, even if it manages to execute arbitrary SQL as that role.
• Separate credentials per environment and per service — a microservice that only needs the orders table shouldn't share a credential with one that manages customers, so a compromise of one service's credential doesn't expose unrelated data.
• Regularly audit granted privileges against what's actually used — permissions tend to accumulate over time (a role granted broad access "temporarily" to unblock a one-off task, never revoked) and rarely get pruned without a deliberate review process.

Why this matters beyond "best practice" box-checking

Least privilege is specifically about limiting the blast radius of an inevitable future incident — not preventing every possible attack (defense in depth assumes some layer will eventually fail), but ensuring that when something does go wrong (a leaked credential, an injection bug, a compromised service), the damage is contained to what that specific credential could actually do, rather than exposing the entire database.