Securityintermediate

Database Authorization

Designing roles that grant only what is used, and keeping privileges correct as the schema evolves.

2 min readIntermediateUpdated 31 Jul 2026Edit this page

Authorization answers what a connection may do. The goal is that a compromised application account cannot destroy or exfiltrate more than that application already handles.

Role-Based Access Control

Grant privileges to roles, and roles to users. Users change; roles are stable, reviewable and describable.

-- PostgreSQL: roles by function, not by person.
CREATE ROLE app_read;
CREATE ROLE app_write;
CREATE ROLE app_migrate;
 
GRANT CONNECT ON DATABASE shop TO app_read;
GRANT USAGE ON SCHEMA public TO app_read;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_read;
 
GRANT app_read TO app_write;
GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_write;
 
-- The migration role owns the objects; the application role does not.
GRANT CREATE ON SCHEMA public TO app_migrate;
 
-- Users receive roles.
CREATE ROLE app_service LOGIN PASSWORD :'password';
GRANT app_write TO app_service;

Grants do not cover tables created later

GRANT SELECT ON ALL TABLES applies to the tables that exist at that moment. The next migration creates a table the application cannot read — a failure that appears at deploy time, in production.

ALTER DEFAULT PRIVILEGES FOR ROLE app_migrate IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_write;

ALTER DEFAULT PRIVILEGES must name the role that will create the objects.

Principle of Least Privilege

Grant what the application executes, and nothing else. Concretely:

The application cannot create or drop objects. Schema changes run as a separate role, during migrations only.
The application does not own its tables. An owner can drop them regardless of grants.
Read-only workloads get a read-only role. Analysts, dashboards and reporting jobs have no reason to write.
No application uses a superuser. A superuser bypasses every permission check, including row-level security.
DELETE is granted only where the application deletes. Many services only ever insert and update.

-- What can this role actually do?
SELECT table_name, privilege_type
FROM information_schema.table_privileges
WHERE grantee = 'app_service' ORDER BY table_name;

Row-level security

When one table serves several tenants, the database can enforce isolation rather than relying on every query carrying the right predicate:

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
 
CREATE POLICY tenant_isolation ON orders
  USING (tenant_id = current_setting('app.tenant_id')::bigint);
 
-- The application sets the tenant per transaction.
SET LOCAL app.tenant_id = '42';

Other engines

-- MySQL: scope to the exact schema and host.
GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'app_service'@'10.20.0.%';
SHOW GRANTS FOR 'app_service'@'10.20.0.%';

// MongoDB: built-in roles per database, or a custom role for narrower access.
db.grantRolesToUser("app_service", [{ role: "readWrite", db: "shop" }]);

# Redis ACL: command categories plus key patterns.
ACL SETUSER app on >secret ~shop:* +@read +@write -@admin -@dangerous

Reviewing

Privileges drift as much as accounts do. Quarterly, export the grant list per role, diff it against the previous review, and confirm every privilege is still used — the engine's own statistics views will tell you which tables a role actually touches.