SQL / SECURITY, ROUTINES, AND DIALECTS
Least privilege and the accounts your app uses
Design the database accounts an application connects with so a bug, injection, or leaked password can only do the handful of things the code needs.
What you will learn
- Give the app's runtime role only the verbs its queries use, on only the tables it needs
- Run migrations as a separate owner role so the app never owns its own tables
- Know that REVOKE cannot contain an owner: it can re-grant and DROP freely
- Grant privileges for new tables in migrations or via ALTER DEFAULT PRIVILEGES
Understanding Least privilege and the accounts your app uses
The role named in your connection string is the ceiling on what any request can do to the database. Least privilege means deriving that role's rights from the code itself: go through the queries the application issues, build a verb-per-table list (invoices needs SELECT, INSERT, UPDATE; audit_log needs INSERT only; countries needs SELECT), and grant exactly that. This does not stop a query from being subverted; it decides what a subverted query reaches. A reporting endpoint whose account only holds SELECT turns an injected DROP TABLE into a line in the error log instead of an outage.
That leads to several accounts rather than one. An owner role creates the schema and tables and runs migrations, and it is used only by the deploy process; a login role is what the app connects as and holds DML only, with no CREATE on the schema; a read-only role serves reports and analytics; human superuser access stays out of application config entirely. The reason the app must not be the owner is that ownership is checked separately from the grant list: an owner can DROP or ALTER its tables no matter what you revoke, and it can GRANT anything back to itself. Withholding CREATE on the schema matters for the same reason — an app that never issues DDL has no need to be able to install a table or function that later code might resolve by accident.
The operational half of this is keeping the grants true over time. A table created by the owner in the next migration carries no privileges for anyone else, so it silently works for whoever ran the migration and fails with "permission denied for table" for the app; the fix is to grant in the migration or to set ALTER DEFAULT PRIVILEGES for the owning role in that schema. Remember also that connection pools share one database role across every end user, so that role must be sized for the least trusted request path, and distinguishing customers from each other needs row-level security or SET ROLE rather than a narrower GRANT. Watch privileges held by PUBLIC too, since every role inherits them: in PostgreSQL new functions are executable by PUBLIC by default, so revoking from your app role alone changes nothing.
-- PostgreSQL, run as a superuser, one statement at a time (autocommit).
CREATE ROLE app_owner NOLOGIN;
CREATE ROLE app_runtime LOGIN PASSWORD 'replace_me';
CREATE SCHEMA app AUTHORIZATION app_owner;
-- Migrations run as the owner: it makes the objects and hands out access.
SET ROLE app_owner;
CREATE TABLE app.invoices (id int, amount numeric, paid boolean DEFAULT false);
INSERT INTO app.invoices (id, amount) VALUES (1, 40.00), (2, 12.50);
GRANT USAGE ON SCHEMA app TO app_runtime;
GRANT SELECT, INSERT, UPDATE ON app.invoices TO app_runtime;
RESET ROLE;
-- The application connects as app_runtime: read, insert, update, nothing else.
SET ROLE app_runtime;
SELECT count(*) AS visible FROM app.invoices;
UPDATE app.invoices SET paid = true WHERE id = 1;
DELETE FROM app.invoices WHERE id = 2;The privileges of the role your application logs in as define the maximum damage a bug or injection can do, which is why it should hold only the verbs the code runs and must not own the objects it touches.
Worked examples
An owner cannot be fenced in
Shows that revoking privileges from a role that owns the table still leaves it able to drop the table.
-- PostgreSQL, run as a superuser, one statement at a time.
CREATE ROLE risky LOGIN PASSWORD 'replace_me';
CREATE TABLE sessions (token text);
ALTER TABLE sessions OWNER TO risky;
REVOKE ALL ON sessions FROM risky;
SET ROLE risky;
SELECT count(*) FROM sessions;
DROP TABLE sessions;
RESET ROLE;
DROP ROLE risky;Example explained
Line 1ALTER TABLE ... OWNER TO risky makes risky the owner; the grant list on the table is a separate thing from ownership.
Line 2REVOKE ALL leaves risky with no listed privileges, so its plain SELECT is refused: owners get no free pass on DML checks.
Line 3DROP TABLE still succeeds, because DROP, ALTER and the right to GRANT are owner rights that no REVOKE can remove.
Line 4So an app account that owns its tables can undo every restriction placed on it, which is why the owner and the login role must be different roles.
New tables start with no grants
Shows a later migration producing a table the app cannot read, and how default privileges close that gap for future tables.
-- PostgreSQL, continuing from the app schema above, as a superuser.
SET ROLE app_owner;
CREATE TABLE app.payments (id int);
RESET ROLE;
SET ROLE app_runtime;
SELECT count(*) FROM app.payments;
RESET ROLE;
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
GRANT SELECT, INSERT, UPDATE ON TABLES TO app_runtime;
SET ROLE app_owner;
CREATE TABLE app.refunds (id int);
RESET ROLE;
SET ROLE app_runtime;
SELECT count(*) FROM app.refunds;
RESET ROLE;Example explained
Line 1app.payments is created with privileges for its owner only, so app_runtime is refused even though it can read app.invoices in the same schema.
Line 2USAGE on the schema is already granted, so the failure is about the table, not about reaching into app at all.
Line 3ALTER DEFAULT PRIVILEGES applies only to tables created after it runs, and only to tables created by app_owner.
Line 4app.refunds therefore picks up SELECT, INSERT, UPDATE at creation and the count returns 0 rather than an error.
Important notes
Run these scripts as separate autocommit statements. Inside a single transaction the first permission error aborts it and every later statement fails with "current transaction is aborted".
SQLite has no accounts at all, so least privilege there is file permissions and whether the process opened the database read-only; MySQL attaches grants to 'user'@'host' rather than to an object owner, so the owner loophole above does not apply in the same form.
Common mistakes
Pointing the connection string at the superuser or the database owner "just until it works": nothing ever errors, so the shortcut ships, and any injected statement then has unlimited reach.
Making the app account the owner of its tables and then revoking DELETE and TRUNCATE from it: the owner can grant those straight back to itself and can drop the table anyway, so the revoke buys nothing.
Granting privileges by hand in a psql session instead of in a migration: dev works because you were superuser there, and production breaks on the next release with "permission denied for table".
Try it yourself
Change, predict, then run
In a PostgreSQL sandbox, create a role report_ro, grant it USAGE on your schema and SELECT on one table, then SET ROLE report_ro and run both a SELECT and an INSERT on that table. Confirm the SELECT works and that the INSERT fails with an error naming the table.
Open the SQL workspaceCheck your understanding
An application connects as the role that owns its tables. You REVOKE DELETE and TRUNCATE from that role. Why does this barely reduce the damage an injected statement can do?
- Because REVOKE only applies to connections opened after it runs, and the pool keeps reusing old ones
- Because DELETE and TRUNCATE are not grantable privileges, so the REVOKE is silently ignored
- Because ownership is checked separately from the grant list: the owner can still DROP or ALTER the table and can GRANT the revoked privileges back to itself
- Because injected SQL always executes as the role that created the database, whatever role the app used
Show answer
Ownership rights such as DROP, ALTER and handing out privileges are not entries in the table's grant list, so REVOKE cannot take them away, and an owner can re-grant itself DELETE in one statement. The pooling option is tempting because pools do hold connections open for a long time, but each statement is checked against the privileges in effect at that moment, so stale connections are not the problem here.