Least-Privilege DB Users

-- 1) Postgres — role per service, minimum grants
CREATE ROLE app_login WITH LOGIN PASSWORD 's3cret';

GRANT CONNECT ON DATABASE myapp TO app_login;
GRANT USAGE   ON SCHEMA public TO app_login;

-- Read-only example (a reporting service)
GRANT SELECT  ON ALL TABLES    IN SCHEMA public TO app_login;
GRANT SELECT  ON ALL SEQUENCES IN SCHEMA public TO app_login;

-- Read-write example (the web app)
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES    IN SCHEMA public TO app_login;
GRANT USAGE,  SELECT, UPDATE         ON ALL SEQUENCES IN SCHEMA public TO app_login;

-- Future tables — without this, new tables don't get permissions
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_login;

-- 2) DENY by default — revoke DDL + sensitive tables
REVOKE CREATE ON SCHEMA public FROM app_login;
REVOKE ALL    ON TABLE audit_log FROM app_login;       -- audit log is admin-only
REVOKE ALL    ON TABLE billing.cards FROM app_login;   -- PCI table

-- 3) Migrations should run as a SEPARATE higher-privileged account
-- Web user can't run ALTER / CREATE; only the migration account can.
CREATE ROLE app_migrate WITH LOGIN PASSWORD 'm1grate';
GRANT  CREATE ON SCHEMA public TO app_migrate;
GRANT  CONNECT ON DATABASE myapp TO app_migrate;

-- 4) Row-Level Security — per-tenant filtering, even on bug
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON posts
    USING (tenant_id = current_setting('app.tenant_id')::uuid);

-- App sets per-request: SET LOCAL app.tenant_id = '…';

-- 5) MySQL equivalent
CREATE USER 'app'@'%' IDENTIFIED BY 's3cret';
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp.* TO 'app'@'%';
REVOKE ALL ON myapp.audit_log FROM 'app'@'%';
FLUSH PRIVILEGES;

-- 6) MongoDB equivalent
db.createUser({
    user:  'app',
    pwd:   's3cret',
    roles: [{ role: 'readWrite', db: 'myapp' }],   // not dbAdmin
});

-- 7) Verify what your app user can DO
SELECT *
FROM   information_schema.role_table_grants
WHERE  grantee = 'app_login';

-- The web app's DB user should not be able to DROP TABLE.
CREATE USER 'web'@'%' IDENTIFIED BY '…';
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp.* TO 'web'@'%';
-- Use a separate higher-priv user for migrations only.