Why shouldn't a running application connect to its relational database as a superuser or as the owner of the tables it queries, and what kind of account would you give it instead?
answer
- owner implicitly holds all privileges + can DROP
- table owner usually bypasses RLS
- runtime = DML only, no DDL
- blast radius, not prevention
- definer's-rights procedure for the rare privileged action
basics
~20 sA superuser or table owner can drop and alter objects, read everything, and often bypass row-level rules. The application only needs SELECT/INSERT/UPDATE/DELETE on its own tables, so it should connect as a dedicated non-owner account holding exactly those grants. That caps the damage when credentials leak or a query is abused.
solid answer
~50 sSuperuser and owner accounts carry privileges the application never exercises: DDL such as DROP and ALTER, access to other schemas, and in many engines the ability to bypass row-level security or disable auditing. If the app runs as one of those, any credential leak or abused query inherits all of it - the difference between 'some rows were modified' and 'the schema is gone and every table was readable'. The standard shape: a separate owner role owns the objects; the runtime role the app connects as holds only SELECT/INSERT/UPDATE/DELETE on the specific tables it uses, plus the sequence and schema USAGE grants it needs. No CREATE, no DROP, no rights on unrelated schemas, nothing inherited from PUBLIC. This is containment, not prevention. It does not stop a bug; it caps blast radius and makes incident response tractable, because you can prove the account was structurally incapable of the worst actions.
code
sql · 5 linesCREATE ROLE app_rw LOGIN PASSWORD '...';
GRANT USAGE ON SCHEMA app TO app_rw;
GRANT SELECT, INSERT, UPDATE, DELETE ON app.orders, app.order_items TO app_rw;
GRANT USAGE ON SEQUENCE app.orders_id_seq TO app_rw;
-- deliberately absent: CREATE, DROP, ALTER, ownership, rights on other schemasgo deeper
Say what superuser and owner can do that the app never needs (DROP, ALTER, read everything) and name the alternative: a dedicated account with only SELECT/INSERT/UPDATE/DELETE on its tables.
Add the three-account split (owner/migration, runtime, read-only) and mention that owners typically bypass row-level security, so 'not superuser' is not enough.
Frame it as blast-radius control that survives application bugs, and describe how grants are kept from drifting - ownership and grants live in migration scripts, reviewed like code.
Discuss where the boundary should sit across many services, what you give up in operational convenience, and when a definer's-rights procedure is the right escape hatch versus widening an account.
## The principle Least privilege means a process holds exactly the permissions its job requires and nothing more. A database login is a process identity, and a typical web application's job is narrow: read and write rows in a known set of tables. It does not create tables, does not read other applications' schemas, and does not manage users. Every privilege beyond that set is pure downside - it can never help correct behaviour, and it is available to anything that gets hold of the connection. ## What superuser and owner actually carry A superuser (Postgres `postgres`, MySQL `root`, Oracle `SYSDBA`) bypasses privilege checks entirely. It can read and modify every schema, alter the server configuration, install extensions, read or write files on the host in some engines, and switch identity to any other account. Auditing configured by that same account can usually be turned off by it. The *owner* of an object is weaker but still far beyond runtime needs. Owners implicitly hold all privileges on their objects, can `DROP` or `ALTER` them, can `GRANT` them to anyone else, and - critically - in engines with row-level security the table owner is normally exempt from the policies unless forced otherwise. So an app running as owner both defeats RLS and can destroy the schema, without anyone ever running a `GRANT` that says so. ## The account layout that follows A conventional split is three identities: - **Owner/migration role** - owns the schema, used only by the deploy pipeline. Has DDL rights. - **Runtime role** - what the application pool authenticates as. Holds only DML on the specific tables plus `USAGE` on the schema and sequences. No DDL, no ownership. - **Read-only role** - for reporting, dashboards, on-call inspection. `SELECT` only. Roles are granted per object rather than 'all tables in this database', so a new table added by a different team is not automatically reachable. ## Why this matters even with perfect application code Credentials escape in mundane ways: an environment variable dumped in a stack trace, a config file in an image layer, a heap dump, an SSRF that reaches an internal admin page, a compromised CI runner. The privilege set attached to those credentials is what decides the incident's severity. Two systems with identical code and identical bugs have completely different worst cases depending on whether the leaked string is `app_rw` or `postgres`. The same logic applies to statements the application is tricked into running: scoping the account does not stop the injection, but a runtime account with no DDL cannot drop a table, and one with no rights on the `billing` schema cannot read it. Containment is the contribution here; input handling is a separate control. ## Practical constraints ORMs and frameworks sometimes want DDL at startup - schema auto-update, temp-table creation, extension installation. Those wants are the reason people give the app owner rights, and the correct response is to move schema changes into the migration step rather than widening the runtime account. Where the app genuinely needs a privileged action (say, a maintenance task), the usual pattern is a definer's-rights stored procedure: the procedure runs with the owner's privileges, the runtime role is granted only `EXECUTE` on it, and the surface stays one named operation instead of full DDL. A second constraint is drift. Grants made once decay as new tables appear, so ownership and grant assignment belong in the migration scripts themselves, reviewed like code, rather than typed by hand into production once.
- The ORM wants to create tables at startup. How do you keep the runtime account free of DDL?Turn schema auto-generation off and move all DDL into an explicit migration step that runs as the owner/migration account before the app starts. The application then only validates that the schema matches what it expects. If a feature genuinely needs to create objects at runtime - temp tables, for instance - grant just that narrow right rather than general CREATE on the schema.
- If the application still has UPDATE and DELETE on its tables, hasn't an attacker already won?No - severity is graded, not binary. Row changes are recoverable from backups and visible in audit logs; a dropped schema, a read of an unrelated tenant's data, or an extension that executes host commands are not equivalent. Scoping also shrinks the investigation: you can rule out whole classes of action because the account structurally could not perform them.
A delivery driver gets a key to the loading dock, not the master key to every office and the safe. The job is the same either way; only the worst case changes.
saying these in an interview costs you the question
- Claiming the owner account is fine 'because it isn't a superuser' - owners can still DROP their tables and typically bypass row-level security.
- Treating the runtime account as a substitute for parameterised queries rather than a second, independent layer.
- Granting the app account rights on all tables in the database instead of the specific objects it uses.
- Assuming a leaked read-write credential and a leaked superuser credential are 'both a breach, so equally bad'.