Give the app a least-privilege database user

How to do it

Run migrations with a separate owner role. Grant the app role only SELECT, INSERT, UPDATE and DELETE on its tables, including tables that later migrations create. In PostgreSQL, add USAGE on sequences that serial columns use. Reporting tools get a read-only role on a replica. Connect over TLS, as the encrypt data rule says.

Examples

SQL example

CREATE ROLE shop_app LOGIN;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO shop_app;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO shop_app;
-- Run as the owner role, so tables from later migrations get the same grants
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO shop_app;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE ON SEQUENCES TO shop_app;

SQL example 2

-- MySQL
CREATE USER 'shop_app'@'10.0.%' IDENTIFIED BY '...' REQUIRE SSL;
GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'shop_app'@'10.0.%';

How to check

Through the app's connection, try DROP TABLE on a test table. The database refuses it.

Sources

• OWASP Database Security Cheat Sheet

Related rules

• APS-54 Encrypt data on the wire and on disk in Application security
• DAT-66 Log in to the database with SCRAM instead of MD5 in Data and databases
• DAT-59 Turn off database features that reach the server in Data and databases
• DEN-30 Give each pipeline and BI tool its own narrow account in Data engineering