Your application connects to its database as a user that owns every table, can alter schema, can drop anything, and can read the audit log. It has done since the first migration ran, because that is what the framework's quickstart produces.
That single decision sets the ceiling on how bad several other problems can get. A SQL injection is limited by what the connected user may do. A compromised application process is limited by the same thing. In most deployments the answer is "everything".
This post is how to split it, and what each split actually buys. For whoever owns the schema.
Separate by what the connection needs
Four roles cover most applications, and the boundaries are natural rather than arbitrary.
The owner. Owns the objects, can alter schema. Used only by migrations, from your deployment pipeline, and by nothing else. This credential is the most privileged thing in your system and it should be used for minutes a week.
The application. SELECT, INSERT, UPDATE, DELETE on the specific tables it needs. No DROP, no ALTER, no TRUNCATE, and no access to tables holding audit records beyond appending to them.
Reporting and analytics. SELECT only, ideally against a replica, and only on the tables or views that reporting legitimately needs rather than everything.
Support and debugging. Read-only, time-bounded, issued to a person rather than shared, and logged. This is what removes the temptation to hand out the application credential when someone needs to investigate.
-- application user: bounded, explicit
GRANT SELECT, INSERT, UPDATE, DELETE ON orders, order_items TO app_user;
GRANT INSERT ON audit_log TO app_user; -- append only, cannot read or delete
REVOKE ALL ON schema_migrations FROM app_user;
What each boundary actually prevents
Worth being concrete, because "least privilege" as a principle persuades nobody with a deadline.
No DROP or TRUNCATE on the application user means a SQL injection in a reporting query cannot destroy the table, and a bug in an ORM cannot either. It converts a catastrophic outcome into a data disclosure, which is bad and recoverable.
No ALTER means an injection cannot add a trigger, which is one of the routes from injection to persistent code execution on several engines.
Append-only on the audit log means an attacker with application-level access cannot erase their trail. This is the single most valuable grant on the list and it costs one line.
Read-only for reporting means the analytics job that somebody wrote under time pressure cannot write. Reporting connections are frequently used from notebooks and ad hoc tools, by people who are not thinking about writes.
No superuser anywhere in the application path. On several engines a superuser can read and write files on the host, which turns a database injection into host access.
Row-level security for tenancy
If you are multi-tenant, the strongest place to enforce scoping is the database, because it applies to every query regardless of which code path wrote it.
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON orders
USING (tenant_id = current_setting('app.tenant_id')::uuid);
The application sets app.tenant_id per connection or transaction, and every query is filtered whether or not the developer remembered a WHERE clause. That is a meaningfully different guarantee from enforcing it in a repository layer somebody can bypass with a raw query.
Two cautions. The setting must be applied reliably on connections taken from a pool, or a request inherits the previous request's tenant, which is the worst possible failure. And the owner and any role with BYPASSRLS are exempt, so the application must not connect as either.
Connection strings are credentials
Obvious and routinely violated, because a connection string looks like configuration.
They end up in committed config files, in container images as a build argument that persists in a layer, in CI logs when a command echoes its arguments, and in error messages when a connection fails and the library helpfully includes the target.
Use a secret manager, prefer short-lived credentials issued per workload where your platform supports it, and check what your database driver logs on a failed connection. Several include the full connection string.
Getting there from one user
The reason most systems have one user is that changing it later looks risky. It is manageable in a specific order:
- Create the new roles with grants, without using them. No behaviour change.
- Point reporting and analytics at the read-only role first. Lowest risk, immediate benefit, and it is where ad hoc access lives.
- Split migrations onto the owner, used only by the pipeline. This is a one-line change in your deployment configuration.
- Move the application to the restricted role in staging, and run your full test suite. The failures tell you exactly which grants you missed, which is the information you wanted.
- Switch production, with the old credential still valid so a rollback is a configuration change rather than a schema change.
Step four is the whole project. Everything else is minutes.
The concession
Multiple roles add operational complexity: more credentials to rotate, more ways for a deploy to fail with a permission error, and a category of incident where the fix is a GRANT that nobody can issue at 03:00 because the owner credential is deliberately hard to reach.
That last one is real and it argues for a documented break-glass path to the owner credential rather than for keeping one user. For a small internal service with no tenancy and no sensitive data, one user is a defensible choice. For anything multi-tenant, holding personal data, or exposed to user-supplied queries, the append-only audit grant and the absence of DROP are worth the added friction on their own.
The implication
Every other database control you build sits inside the boundary set by the connected user's grants. If that user can do anything, your controls are conventions.
Start with two lines: append-only on the audit log, and no DROP for the application. They take an afternoon, they break nothing, and they change what the worst day looks like.
