Security and Permissions

Protect data with proper roles/privileges and safe query practices.

Roles and privileges

-- Create roles/users (engine-specific commands vary)
CREATE ROLE app_user LOGIN PASSWORD '...';
CREATE ROLE readonly;
GRANT readonly TO app_user;

GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
REVOKE INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public FROM readonly;

Use schema-wide grants and default privileges for new tables.

Least privilege

• Separate app roles: read-only, read-write, admin
• Restrict DDL to migrations role
• Avoid using superuser in applications

Row-level security (RLS)

-- Postgres example
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON orders
  USING (tenant_id = current_setting('app.tenant_id')::bigint);

Apply per-tenant/user policies.

Injection defense

• Always use parameterized queries / prepared statements
• Avoid concatenating untrusted input into SQL strings

Auditing and logs

• Enable statement or row-change auditing where supported
• Log slow queries for investigation

Secrets management

Store credentials in a secure vault and rotate regularly.

Summary

• Grant least privilege via roles; avoid superusers in apps
• Use parameterized queries and consider RLS for multi-tenant isolation