Users, roles and permissions

📘What it really is

• Server databases like PostgreSQL have their own users and roles, with permissions on each table:

CREATE ROLE crm_app LOGIN PASSWORD '...';
GRANT SELECT, INSERT, UPDATE ON customers, orders, order_items, payments TO crm_app;

CREATE ROLE reporting LOGIN PASSWORD '...';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO reporting;

• Least privilege (Module 48), for databases:
• the app connects as a role that can only do what the app needs (perhaps no DELETE on payments, and never DROP);
• migrations run as a separate role that can change structure;
• reports and analysts get read-only access, ideally to a copy (a replica), not the live database;

• nobody uses the all-powerful superuser (postgres) for everyday work.

• These are separate from your app's users and roles (Module 44): the app checks what a staff member may do; the database checks what the app may do. Two layers of defence.

• SQLite has no users; whoever can open the file can do anything. Protect the file with operating-system permissions and keep it out of web-accessible folders.

• Passwords for database roles are secrets: environment variables, never in code or Git (Module 41).