Should organisation access be enforced in application code, database policies or both?

Introduction

When building SaaS applications, deciding where to enforce organisation access, application code, database policies, or both, is a critical architectural choice. This article compares these approaches with a focus on layered enforcement, administrator access paths, and background job operations, highlighting how PostgreSQL's row-level security (RLS) and Supabase's implementation shape practical decisions.

Understanding Access Enforcement Layers

Access enforcement can occur at multiple layers:

• Application Code: Server-side logic that authorizes identity, organisation and action before querying or modifying data. Frontend controls can improve the workflow but are not a trusted access boundary.
• Database Policies: Built-in mechanisms like PostgreSQL's RLS that restrict which rows a user can access directly at the data source.

Each layer has strengths and limitations. Application code can implement complex, dynamic rules but risks inconsistencies if not applied uniformly. Database policies provide a consistent, central enforcement but may lack flexibility for nuanced business logic.

Database Row-Level Security (RLS) Explained

PostgreSQL's RLS allows defining policies per table that restrict rows visible or modifiable based on the querying user's role and context. For example, enabling RLS on an accounts table can limit users to only see their own accounts. Policies combine permissively (OR) or restrictively (AND) to refine access.

Supabase extends this by mapping requests to roles like anon, authenticated, and service_role, each with distinct privileges. The service_role bypasses RLS and is intended for trusted server-side operations.

Application Code Enforcement: Flexibility vs Risk

Application-level checks allow embedding organisation access logic alongside business rules, enabling complex scenarios like conditional access based on user session data or external APIs. However, this requires rigorous discipline to avoid missing checks, especially in multi-path access scenarios like APIs, admin interfaces, or background jobs.

Layered Enforcement: Combining Application and Database Controls

A best practice is to combine both layers:

• Use RLS to enforce baseline data isolation and prevent accidental or malicious cross-organisation data leaks.
• Use application code to implement nuanced business rules, validate inputs, and handle exceptions.

This proposed architecture supplies an additional boundary only for requests that actually run under its policies. Privileged roles, views or functions may bypass them; layered enforcement is not an isolation guarantee.

Administrator Paths and Background Jobs

Administrators and background jobs often require elevated access:

• Administrators: Start with least privilege for customer administrators and support operators. Separate exceptional platform-wide operations; do not grant bypass by default.
• Background Jobs: Prefer scoped roles where feasible. A privileged job needs independent scope validation and auditing.

Managing these paths requires clear policies and monitoring.

Practical Access-Enforcement Architecture Brief

How to Use This Brief

• Define roles and privileges clearly in your database.
• Implement RLS policies per table for organisation data.
• Enforce business rules in application code consistently.
• Separate support and job roles with the least privileges their operations require.
• Audit and monitor all elevated access.

Worked Example: Organisation-Specific User Profiles

Suppose you have a profiles table where users should only access their own profile.

1. Enable RLS:

ALTER TABLE profiles ENABLE ROW LEVEL SECURITY;

2. Create RLS policy:

CREATE POLICY user_own_profile ON profiles
FOR ALL
TO authenticated
USING (user_id = auth.uid())
WITH CHECK (user_id = auth.uid());

3. Application code:

• Authenticate user and include JWT with sub claim.
• Queries use the authenticated role.
• Business logic enforces additional constraints (e.g., profile completeness).

4. Admin role:

• Define a scoped support role; any exceptional bypass operation needs separately checked scope and audit evidence.
• Admin UI enforces audit logs and restricts actions.

This illustrative SQL assumes user_id is the authenticated user's UUID. It demonstrates owner-only profiles, not organisation membership. Verify the actual schema, grants and role context; the code has not been run here.

Define privileged access without treating bypass as protection

PostgreSQL documents that superusers and roles with BYPASSRLS always bypass row policies. Table owners normally bypass them too; FORCE ROW LEVEL SECURITY can subject an owner to policies. It does not constrain superusers or BYPASSRLS roles, and it does not create audit logs. See the PostgreSQL row-security guide.

For ordinary support work, define a diagnostic role exposing only required fields and organisations. Record the operator, target, purpose, allowed action and access window. An organisation's customer administrator must not inherit platform-wide privileges.

For exceptional platform repair, define a separate server-side operation. Validate the initiating operator and authorised target before using elevated access. Do not pass a customer-supplied organisation identifier directly into an unrestricted query.

Supabase's RLS documentation warns about privileged bypass and views. Verify the effective role on every deployed path. A successful administrator query does not test customer isolation.

Scope background jobs explicitly

A customer-specific job should carry a server-issued job ID, approved organisation, permitted operation and selection boundary. Validate these when the job executes, not just when a browser queues it.

Prefer a role limited by grants and policies. If a service role bypasses RLS, the job's independent scope checks are its protection; RLS cannot rescue a missing filter. Record selected IDs and affected-row counts. Make unexpected scope visible before a destructive action.

A filter covering every active organisation is not the same as a customer-specific scope. Shared maintenance needs its own operation boundary and recovery procedure.

Filled hypothetical architecture decision

A fictional CRM needs ordinary user access, customer administration, support diagnostics and per-organisation aggregation. The proposed design below is a specification, not an implemented or tested system.

Identify every route to the same records: API, direct database access where exposed, exports, jobs, support views and file delivery. A policy tested on one route does not automatically cover another.

Acceptance tests and recovery

Create fictional Alpha and Beta data with distinct markers. Establish a working Alpha positive control, then request Beta's known IDs with Alpha's session. Repeat per operation and inspect response bodies and exports.

A denied SELECT may return no rows rather than a permission error. Other operations may fail differently. Agree the contract per route and inspect content, not only status.

If a test exposes another organisation's records, stop the affected path, retain evidence and repair the missing boundary. Do not routinely disable RLS to restore access. Use controlled maintenance access, retest the failed case and positive control, and reconcile affected data.

OWASP's authorisation guidance supports deny-by-default permissions and checks on each request. Logging helps investigate enforcement; it does not replace it.

Documentation boundaries for this decision

PostgreSQL's row-level security (RLS) enforces access control directly in the database by allowing policies that restrict which rows a user can query or modify. These policies are table-specific and can be combined permissively or restrictively. Importantly, superusers and roles with BYPASSRLS privileges can bypass these policies, and table owners typically bypass RLS unless forced. This means that while RLS provides a strong baseline for data isolation, it does not automatically cover all access paths, especially for administrators or special roles. Source: PostgreSQL row security

Supabase enhances PostgreSQL RLS by mapping requests to distinct roles such as anon, authenticated, and service_role. The service_role bypasses RLS and is intended for trusted server-side operations like background jobs. Supabase emphasizes that policies must be defined per role and operation, and that grants must be set correctly to avoid unintended access. Additionally, JWT claims used for authorization may be stale until refreshed, so application logic must not rely solely on user-editable metadata. This highlights the need for combined enforcement in application code and database policies. Source: Supabase row-level security

OWASP recommends a deny-by-default access control approach with explicit permission validation on every request. It warns that relying solely on application-level authorization without enforcing least privilege principles can lead to broken access control, a common and severe vulnerability. Authorization logic should be consistent and cover all access paths, including APIs, admin interfaces, and background jobs. This aligns with layered enforcement where database policies provide baseline isolation and application code handles complex business rules and exceptions. Source: OWASP authorization guidance

Frequently asked questions

Should I rely solely on database policies for access control?

No. While RLS provides strong data isolation, complex business rules and user context often require application-level checks for full enforcement.

How do I handle access for background jobs?

Prefer scoped roles. If bypass is necessary, independently validate trusted job scope, audit the operation and verify affected records.

Can administrators bypass row-level security?

Some platform roles can: superusers and BYPASSRLS roles bypass policies, and table owners normally do too unless forced. A customer's administrator should remain scoped to its organisation. Audit and separately authorize exceptional platform operations.

What about public data access?

Public data can be exposed via database grants or public buckets but ensure sensitive data is never exposed and consider RLS policies even for public data if needed.

Conclusion

Enforcing organisation access requires a layered approach combining database row-level security and application code checks. Verify the chosen boundaries across user, administrator and job contexts; the design alone does not establish isolation. Clear role definitions, policy management, and auditing are essential to maintain robust security.

If your business needs expert guidance on SaaS development or access control architecture, consider consulting Symaxx's SaaS development services. For practical implementation, our platform comparison guide and website maintenance cost overview can help you plan effectively. Understanding the user journey is also vital for designing secure access flows.

If you need help implementing secure access control in your SaaS product, get in touch with Symaxx via our SaaS development route.

Sources

• PostgreSQL row security
• Supabase row-level security
• OWASP authorization guidance
• Supabase storage access control
• Supabase authenticated and signed downloads