Security Fundamentals in Design: Database Access Control

Previously in this course, we discussed Database Documentation: Generating ERD and Schema Metadata. While documenting your schema helps with clarity, securing it prevents unauthorized access. This lesson adds a critical layer of defense: implementing security directly within your database engine.

Why Database-Level Security Matters

Developers often rely solely on the application layer for security. They assume that if the API says "no," the user cannot see the data. However, if your database user is an admin or superuser, a bug in your code or a SQL injection attack could expose your entire dataset.

By implementing Role-Based Access Control (RBAC) and schema-level security, we enforce the Principle of Least Privilege: users and applications should only have the permissions necessary to perform their specific tasks.

Implementing Schema-Level Security

In a production SaaS environment, your application should never connect as the database owner. Instead, we create dedicated database users (roles) with restricted access.

1. Defining Roles

Rather than assigning permissions to individual user accounts, we assign permissions to Roles, then grant those roles to the users.

SQL
-- Create a role for the application's backend service
CREATE ROLE app_service;

-- Create a read-only role for reporting/analytics
CREATE ROLE report_reader;

2. Managing Permissions (Granting Access)

Once roles are defined, you must explicitly grant access to specific schemas or tables. In PostgreSQL, for example, you should move application tables into a specific schema (e.g., app_data) rather than the default public schema to make management easier.

SQL
-- Grant connection access
GRANT CONNECT ON DATABASE saas_db TO app_service;

-- Grant usage on the schema
GRANT USAGE ON SCHEMA app_data TO app_service;

-- Grant specific CRUD permissions
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app_data TO app_service;

-- Analytics role gets ONLY read access
GRANT SELECT ON ALL TABLES IN SCHEMA app_data TO report_reader;

Advanced Access Control: Row-Level Security (RLS)

In multi-tenant SaaS applications, you often need to ensure that Tenant A cannot see the data of Tenant B, even if both use the same table. While designing for multi-tenancy is often handled at the application level, RLS provides a database-native safety net.

SQL
-- Enable RLS on the users table
ALTER TABLE users ENABLE ROW LEVEL SECURITY;

-- Create a policy that restricts rows to the current tenant
CREATE POLICY tenant_isolation_policy ON users
USING (tenant_id = current_setting('app.current_tenant')::uuid);

Note: This ensures that even if a query is written incorrectly in your code, the database engine will filter the result set based on the session variable.

Hands-on Exercise

Your task is to refine your SaaS project’s security model.

1. Create a readonly_user role in your local database.
2. Ensure your current application user does not have DROP or TRUNCATE permissions on your tables.
3. Verify this by attempting to run a TRUNCATE command while logged in as the restricted user; it should fail with a "permission denied" error.

Common Pitfalls

• Connecting as Superuser: Never use the postgres (or root) user in your application connection string. If your app is compromised, the attacker has full control over the database files.
• Ignoring Schema Separation: Mixing system tables with application data makes it difficult to define broad GRANT rules. Keep your data in a clean, dedicated schema.
• Over-granting: It is tempting to use GRANT ALL PRIVILEGES. Avoid this. Explicitly list the operations (SELECT, INSERT, etc.) needed.
• Forgetting to revoke: If you test with a broad role, remember to REVOKE those rights before moving to production.

FAQ

Q: Is database-level security a replacement for API-level authorization? No. They are complementary. API security (as discussed in API Security: Decoupling Field-Level Authorization from Controllers) handles business logic, while database security provides a last line of defense against infrastructure-level threats.

Q: Does RLS impact performance? Yes, it can. Every query must be evaluated against the policy. However, for most SaaS applications, this overhead is negligible compared to the massive benefit of guaranteed data isolation.

Recap

Security in database design is not an afterthought; it is a structural requirement. By using RBAC to define roles, schema-level grants to limit scope, and Row-Level Security for tenant isolation, you create a robust perimeter that protects your data even when application-level code fails. Always follow the principle of least privilege.

Up next: Handling Soft Deletes — we’ll learn how to keep data recoverable by marking records as deleted rather than purging them permanently.