bottleneckcrm/backend/common/middleware/rls_context.py

"""
Database middleware for Row-Level Security (RLS) context.

This middleware sets the PostgreSQL session variable `app.current_org`
which is used by RLS policies to filter data at the database level.

Enable RLS policies after this middleware is in place:

    ALTER TABLE leads ENABLE ROW LEVEL SECURITY;
    CREATE POLICY org_isolation ON leads
      USING (org_id = current_setting('app.current_org', true)::uuid);

Usage in settings.py:
    MIDDLEWARE = [
        ...
        'common.middleware.get_company.GetProfileAndOrg',
        'common.middleware.rls_context.RequireOrgContext',  # After GetProfileAndOrg
        ...
    ]
"""

import logging

from django.db import connection
from django.http import JsonResponse

from common.modules import route_refusal

logger = logging.getLogger(__name__)


class RequireOrgContext:
    """
    Sets `app.current_org` for the request and refuses a request with no org
    context, except on the exempt paths below.

    Usage in settings.py:
        MIDDLEWARE = [
            ...
            'common.middleware.get_company.GetProfileAndOrg',
            'common.middleware.rls_context.RequireOrgContext',
            ...
        ]
    """

    # Paths that don't require org context
    EXEMPT_PATHS = [
        "/api/auth/login/",
        "/api/auth/refresh-token/",
        "/api/auth/me/",
        "/api/auth/switch-org/",
        "/api/auth/google/",
        "/api/auth/magic-link/request/",
        "/api/auth/magic-link/verify/",
        "/api/auth/magic-link/verify-code/",
        "/api/org/",
        "/api/sellers/",
        "/api/customers/",
        "/admin/",
        "/swagger-ui/",
        "/api/schema/",
        # Public CSAT survey link (Tier 2 csat). Anonymous, sets RLS
        # context manually inside the view from the survey's own org_id.
        "/api/public/csat/",
        # Client portal: the invoice and estimate links emailed to customers.
        # Anonymous by design: the view authorises on `public_token` alone
        # (see invoices/public_views.py) and never reads request.user or
        # request.org, so requiring org context here rejected every customer
        # who clicked a link. Listed as two specific prefixes rather than a
        # blanket "/api/public/" so that anything mounted there in future has
        # to opt in deliberately.
        #
        # NOTE: this restores reachability, not readability. `_set_org_context`
        # returns early when request.org is None, so these requests run with
        # `app.current_org` empty, and the isolation policy from
        # `get_enable_policy_sql` is `org_id::text = NULLIF(current_setting(
        # 'app.current_org', true), '')`, which matches nothing when empty.
        # Verified as `crm_user` (non-superuser, no BYPASSRLS): invoice,
        # estimate and csat_survey all return 0 rows with empty context. So on
        # a correctly-configured production database these endpoints now
        # answer 404 rather than 403 until the view can resolve the org from
        # the token before querying. See docs/PORTAL_RLS.md.
        "/api/public/invoice/",
        "/api/public/estimate/",
        # Public web forms (issue #634). Anonymous by design: a form embedded
        # on a customer's website has no JWT and no org claim.
        #
        # Unlike the invoice and estimate entries above, these views DO resolve
        # the org before they query. It is the first path segment after this
        # prefix, and `webforms/public_views.py:load_form` calls
        # `set_rls_context(org_id)` before the form lookup, so these read
        # correctly under a non-superuser role rather than answering 404 the
        # way the NOTE above describes.
        #
        # Prefix-matched, so this covers the three routes under
        # `/api/public/forms/` and nothing else. The org id sits AFTER the
        # fixed prefix for that reason; see webforms/public_urls.py.
        "/api/public/forms/",
        # Public help center (G6). Anonymous by design: search engines and
        # visitors read it with no credential. The views take the org from the
        # slug after this prefix and set the RLS context before any org-scoped
        # read, like the web form entry above. Prefix-matched, so this covers
        # `/api/public/help/<slug>/...` and nothing else under /api/public/.
        "/api/public/help/",
        # Task calendar feed (G14). Anonymous by design: a calendar app polls
        # it with no credential but the token in the path. The view resolves
        # the profile and org from the token (`calendar_feed_token` has no RLS
        # policy, like `personal_access_token`) and sets the context before it
        # reads `task`. See common/views/calendar_feed_views.py.
        "/api/public/calendar/",
        # Customer portal sign-in. Anonymous by design: the caller has no org
        # claim yet, so the view takes the org from the URL and sets the RLS
        # context itself before it reads `contacts`.
        #
        # This is the login prefix ONLY, and it must stay that way. Adding the
        # bare "/api/portal/" here would strip tenant-context enforcement from
        # every authenticated portal endpoint in one line, because this list is
        # prefix-matched. That is the same trap already recorded for
        # /api/packs/ in EXEMPT_EXACT_PATHS below. The authenticated portal
        # endpoints resolve their org in GetProfileAndOrg and are meant to be
        # checked here like everything else.
        "/api/portal/login/",
    ]

    # Paths exempt on an EXACT match only, never prefix-matched like
    # EXEMPT_PATHS above. Mirrors the exact-match pattern already used in
    # common.middleware.get_company.GetProfileAndOrg.process_request
    # (`if request.path in auth_skip_paths`).
    #
    # GET /api/packs/ is static content read from JSON files in the repo,
    # byte-identical for every tenant, no tenant data whatsoever. It backs
    # the pack chooser on the org-creation page, where a user creating their
    # *first* org has no org_id claim yet, so RequireOrgContext would 403 the
    # exact audience the page serves.
    #
    # This MUST stay an exact match, not a prefix: POST
    # /api/packs/<pack_id>/apply/ (writes org-wide config, ADMIN only) and
    # DELETE /api/packs/sample-data/ (deletes records, ADMIN only) both start
    # with "/api/packs/" too. Adding "/api/packs/" to the prefix-matched
    # EXEMPT_PATHS list instead of here would silently strip tenant-context
    # enforcement from those write/destroy endpoints. Do not move it there.
    # POST /api/auth/logout/ revokes the refresh token it is handed. It is
    # reached most often when the access token has already expired, so there is
    # no org claim to require, and demanding one would fail sign-out in exactly
    # the case sign-out matters. Exact match: the view reads nothing but the
    # token in the body, and no sub-path should inherit this.
    #
    # GET /healthz/ is an unauthenticated liveness probe (load balancers, uptime
    # monitors, container orchestrators). It renders a static template and reads
    # no tenant data, so there is no org context to require. Gating it makes every
    # health check return 403 and the service read as down. Exact match: nothing
    # should hang off /healthz/.
    EXEMPT_EXACT_PATHS = [
        "/healthz/",
        "/api/packs/",
        "/api/auth/logout/",
    ]

    # Routes exempt by their resolved, namespaced URL name, for an anonymous
    # route whose path carries an id and so cannot be an exact path, and which
    # sits beside authenticated routes that a prefix would also catch.
    #
    # The inbound email webhook (`/api/cases/inbound/<mailbox_id>/`) is POSTed
    # by AWS SNS with no credential, so it has no org claim to require, and
    # without this entry every delivery answered 403. The view authenticates
    # the delivery itself (SNS signature plus the mailbox's TopicArn pin) and
    # resolves the org from the unscoped `PortalAccessToken` lookup before it
    # sets the RLS context and reads `inbound_mailbox`. See
    # cases/inbound_views.py.
    #
    # By name, not by prefix: "/api/cases/" or "/api/cases/inbound/" in
    # EXEMPT_PATHS would reach further than this one route as soon as anything
    # else is mounted there, and the admin mailbox endpoints under /api/cases/
    # must keep requiring org context like everything else.
    EXEMPT_VIEW_NAMES = frozenset({"common_urls:api_cases:inbound_webhook"})

    def __init__(self, get_response):
        self.get_response = get_response

    def __call__(self, request):
        # Check if path requires org context
        if not self._is_exempt(request.path):
            # Skip check for URLs that don't resolve (let Django return 404)
            from django.urls import resolve
            from django.urls.exceptions import Resolver404

            try:
                match = resolve(request.path)
            except Resolver404:
                return self.get_response(request)

            if match.view_name not in self.EXEMPT_VIEW_NAMES and (
                not hasattr(request, "org") or request.org is None
            ):
                return JsonResponse(
                    {"detail": "Organization context is required. Please login again."},
                    status=403,
                )

            refusal = route_refusal(request, match)
            if refusal is not None:
                return refusal

        # Set org context
        self._set_org_context(request)

        # The reset is in a finally because
        # `app.current_org` is SESSION-scoped and survives an exception. On a
        # pooled or otherwise reused connection, skipping it leaks this
        # tenant's context to whoever borrows the connection next.
        try:
            return self.get_response(request)
        finally:
            self._reset_org_context()

    def _is_exempt(self, path):
        """Check if path is exempt from org context requirement."""
        if path in self.EXEMPT_EXACT_PATHS:
            return True
        return any(path.startswith(exempt) for exempt in self.EXEMPT_PATHS)

    def _set_org_context(self, request):
        """Set PostgreSQL session variable (session scope for autocommit mode)."""
        if not hasattr(request, "org") or request.org is None:
            return

        org_id = str(request.org.id)

        try:
            with connection.cursor() as cursor:
                cursor.execute(
                    "SELECT set_config('app.current_org', %s, false)", [org_id]
                )
        except Exception as e:
            logger.warning("Failed to set RLS context: %s", e)

    def _reset_org_context(self):
        """Reset PostgreSQL session variable after request."""
        try:
            with connection.cursor() as cursor:
                cursor.execute("SELECT set_config('app.current_org', '', false)")
        except Exception:
            pass


# SQL to enable RLS on all org-scoped tables
RLS_SETUP_SQL = """
-- Enable RLS on main tables
-- Run this after all org-scoped tables are identified

-- Example for leads table:
-- ALTER TABLE lead ENABLE ROW LEVEL SECURITY;
-- ALTER TABLE lead FORCE ROW LEVEL SECURITY;  -- Apply to table owner too
-- CREATE POLICY org_isolation ON lead
--   USING (org_id = NULLIF(current_setting('app.current_org', true), '')::uuid);

-- Tables that need RLS policies:
-- lead, accounts, contacts, opportunity, cases, tasks, invoices,
-- comment, attachments, document, teams, activity, tags, address,
-- api_settings, board, board_column, board_task, board_member

-- Note: Use NULLIF to handle empty string when context is not set
-- This makes the policy return no rows when context is not set (fail-safe)
"""