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. It's not about 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.