Decision Tree

Need access control?       → Define ROLE hierarchy
Need row isolation?        → Enable RLS with policies
Need sensitive data?       → Encrypt with pgcrypto
Need audit compliance?     → Create AUDIT_LOG table
Need secure connections?   → Enforce SSL/TLS

Database Security & RBAC Standards

Security must be implemented at the database layer (Defense in Depth), ensuring that even if the application layer is compromised, the data remains protected by strict access controls.

1. Role-Based Access Control (RBAC)

1.1 Standard Roles

Implement a hierarchy of roles to categorize users. Avoid assigning permissions to individual users; assign them to Roles.

• ROLE_ADMIN: Full DDL/DML access. Capable of altering schema.
• ROLE_APP_BACKEND: The role used by the API. Can SELECT, INSERT, UPDATE, DELETE on operational tables but cannot alter schema.
• ROLE_READ_ONLY: For reporting/analytics tools. SELECT only.
• ROLE_GUEST / ROLE_ANON: For unauthenticated public access (if applicable).

1.2 Granting Permissions

• Least Privilege: Start with no permissions. Grant only what is absolutely necessary.
• Grant Statements:

  GRANT SELECT, INSERT, UPDATE ON TABLE users TO ROLE_APP_BACKEND;
  GRANT USAGE, SELECT ON SEQUENCE users_id_seq TO ROLE_APP_BACKEND;
  

• Revoke: Explicitly REVOKE ALL from PUBLIC on sensitive tables to prevent accidental default access.

2. Row Level Security (RLS)

Use RLS to strictly enforce data isolation at the row level based on the current user context.

2.1 Implementing RLS

1. Enable RLS:

   ALTER TABLE sensitive_documents ENABLE ROW LEVEL SECURITY;
   

2. Create Policy:

   CREATE POLICY user_access_policy ON sensitive_documents
   FOR ALL
   USING (owner_id = current_setting('app.current_user_id')::INT);
   

2.2 Application Context

Ensure the application sets the context variable (e.g., app.current_user_id) at the start of every transaction to allow RLS to function correctly.

3. Data Protection

3.1 Sensitive Data

• Passwords: NEVER store plain-text passwords. Use bcrypt or argon2 hashes.
• PII: Identify Personally Identifiable Information (Emails, Phones, IDs).
  • Consider separate schemas or tables for PII with stricter access controls.
  • Encrypt highly sensitive columns (e.g., Credit Card tokens) at rest if the database supports it (e.g., pgcrypto).

3.2 SQL Injection Prevention

• Prepared Statements: ALL application code must use Parameterized Queries/Prepared Statements.
• Dynamic SQL: In PL/pgSQL, use EXECUTE ... USING ... to safely bind parameters in dynamic strings. Avoid simple string concatenation.

4. Audit & Compliance

4.1 Audit Logging Tables

Track sensitive operations for compliance (GDPR, SOC2, etc.).

• Log: WHO changed WHAT, WHEN, and the OLD/NEW values.
• Store in a separate audit schema with restricted access.

4.2 Immutable Logs

• Make audit tables append-only: revoke UPDATE and DELETE from all application roles.
• Consider log shipping or write-ahead log archiving for tamper-proofing.

5. Connection Security

5.1 Connection Pooling

• Application connections should use a pool (e.g., PgBouncer).
• Each pool should connect using the ROLE_APP_BACKEND role, not a superuser.

5.2 SSL/TLS

• Enforce encrypted connections: sslmode=require or verify-full.
• Ensure certificates are properly managed and rotated.