Session Management & RLS Setup

Overview

Every authenticated request establishes a session context by:

1. Verifying the Clerk session token
2. Loading the internal user from the database (AdminPool — bypasses RLS)
3. Routing to the correct connection pool: AdminPool for superadmins (owner, bypasses RLS) or AppPool for everyone else (restricted, RLS enforced)
4. Setting PostgreSQL session variables on that connection
5. Using that connection for all queries in the request

This ensures tenant isolation via Row-Level Security (RLS) without application-level filtering.

Session Variables

Three PostgreSQL session variables control RLS:

Variable	Type	Set By	Used By
app.current_user_id	BIGINT	OrganizationContext middleware	User-scoped RLS policies
app.current_org_id	BIGINT	OrganizationContext middleware	Org-scoped RLS policies (most tables)
app.current_role	TEXT	OrganizationContext middleware	Role-based RLS policies

These are transaction-scoped (set_config(..., true)) — they reset automatically when the request ends.

Middleware Stack

1. ClerkAuth Middleware

Purpose: Verify token, load user context

// internal/middleware/auth.go

type UserContext struct {
    ID                    int64
    ClerkUserID           string
    Email                 string
    Role                  string
    CurrentOrganizationID int64
    SpecialistID          *int64      // If user is a specialist
    PatientID             *int64      // If user is a patient
    OrganizationIDs       []int64     // All orgs user belongs to
}

func ClerkAuth(clerkClient *clerk.Client) func(http.Handler) http.Handler {
    return func(next http.Handler) http.Handler {
        return http.HandlerFunc(func(w http.ResponseWriter, r *http.Request) {
            // 1. Extract Bearer token
            token := extractBearerToken(r)
            if token == "" {
                httputil.Unauthorized(w, "missing authorization token")
                return
            }

            // 2. Verify with Clerk
            claims, err := clerkClient.VerifyToken(token)
            if err != nil {
                httputil.Unauthorized(w, "invalid or expired token")
                return
            }

            // 3. Load internal user (single optimized query with joins)
            user, err := userRepo.FindByClerkID(r.Context(), claims.Subject)
            if err != nil {
                httputil.Unauthorized(w, "user not found")
                return
            }

            if user.Blocked {
                httputil.Forbidden(w, "account is blocked")
                return
            }

            // 4. Set user in request context
            ctx := context.WithValue(r.Context(), userContextKey, &UserContext{
                ID:                    user.ID,
                ClerkUserID:           user.ClerkUserID,
                Email:                 user.Email,
                Role:                  user.Role,
                CurrentOrganizationID: user.CurrentOrganizationID,
                SpecialistID:          user.SpecialistID,
                PatientID:             user.PatientID,
                OrganizationIDs:       user.OrganizationIDs,
            })

            next.ServeHTTP(w, r.WithContext(ctx))
        })
    }
}

Query optimization: FindByClerkID uses a single query with LEFT JOINs:

SELECT
    u.id, u.clerk_user_id, u.email, u.username, u.role,
    u.confirmed, u.blocked, u.current_organization_id,
    s.id AS specialist_id,
    p.id AS patient_id,
    ARRAY_AGG(uo.organization_id) AS organization_ids
FROM users u
LEFT JOIN specialists s ON s.user_id = u.id
LEFT JOIN patients p ON p.user_id = u.id
LEFT JOIN user_organizations uo ON uo.user_id = u.id
WHERE u.clerk_user_id = $1
GROUP BY u.id, s.id, p.id;

This loads everything needed for UserContext in one round-trip.

2. OrganizationContext Middleware

Purpose: Route to correct pool by role, acquire connection, set RLS session variables

The middleware accepts two connection pools and routes requests based on role:

• Superadmins → AdminPool (restartix owner role — bypasses RLS)
• All other users → AppPool (restartix_app restricted role — RLS enforced)

// internal/middleware/organization.go

func OrganizationContext(adminPool, appPool *pgxpool.Pool) func(http.Handler) http.Handler {
    return func(next http.Handler) http.Handler {
        return http.HandlerFunc(func(w http.ResponseWriter, r *http.Request) {
            user := UserFromContext(r.Context())
            if user == nil {
                httputil.Unauthorized(w, "no user context")
                return
            }

            orgID := user.CurrentOrganizationID

            // Prefer X-Organization-ID header if present (set by frontend
            // proxy based on domain resolution — runs server-side).
            if headerOrgID := r.Header.Get("X-Organization-ID"); headerOrgID != "" {
                if parsed, err := strconv.ParseInt(headerOrgID, 10, 64); err == nil && parsed > 0 {
                    orgID = parsed
                }
            }

            if orgID == 0 {
                // Auto-select first organization if none set
                if len(user.OrganizationIDs) > 0 {
                    orgID = user.OrganizationIDs[0]
                } else if user.IsSuperadmin() {
                    // Superadmin can operate without org context
                    orgID = 0
                } else {
                    httputil.Forbidden(w, "user has no organization")
                    return
                }
            }

            // Validate org membership (except superadmin)
            if !user.IsSuperadmin() && orgID != 0 {
                if !slices.Contains(user.OrganizationIDs, orgID) {
                    httputil.Forbidden(w, "not a member of this organization")
                    return
                }
            }

            // Route superadmins to owner pool (RLS bypassed),
            // everyone else to app pool (RLS enforced)
            pool := appPool
            if user.IsSuperadmin() {
                pool = adminPool
            }

            // Acquire connection from chosen pool
            conn, err := pool.Acquire(r.Context())
            if err != nil {
                slog.Error("failed to acquire connection", "error", err)
                httputil.InternalError(w)
                return
            }
            defer conn.Release()

            // Set RLS session variables (transaction-scoped)
            // Set even for superadmins — useful for audit logging.
            _, err = conn.Exec(r.Context(), `
                SELECT set_config('app.current_user_id', $1::text, true),
                       set_config('app.current_org_id', $2::text, true),
                       set_config('app.current_role', $3, true)
            `, user.ID, orgID, user.Role)
            if err != nil {
                slog.Error("failed to set RLS context", "error", err)
                httputil.InternalError(w)
                return
            }

            // Store connection in request context
            ctx := database.ContextWithConn(r.Context(), conn)
            next.ServeHTTP(w, r.WithContext(ctx))
        })
    }
}

Connection lifecycle:

• Acquired: Start of OrganizationContext middleware
• Stored: In request context (all repositories use this connection)
• Released: End of request (via defer conn.Release())

3. Repository Pattern

All repositories extract the connection from context:

// internal/domain/appointment/repository.go

type Repository struct {
    adminPool *pgxpool.Pool  // System queries (bypasses RLS)
    appPool   *pgxpool.Pool  // Fallback for background jobs (RLS enforced)
}

func (r *Repository) querier(ctx context.Context) database.Querier {
    // Extract RLS-configured connection from request context
    if conn := database.ConnFromContext(ctx); conn != nil {
        return conn
    }
    // Fallback to appPool (RLS enforced)
    return r.appPool
}

func (r *Repository) FindByID(ctx context.Context, id int64) (*Appointment, error) {
    conn := r.connFromContext(ctx)  // Uses request connection
    query := `
        SELECT id, title, uid, status, started_at, ended_at,
               specialist_id, organization_id, user_id
        FROM appointments
        WHERE id = $1
    `
    // RLS automatically filters by organization_id
    var a Appointment
    err := conn.QueryRow(ctx, query, id).Scan(...)
    return &a, err
}

Why this works:

• Request connection has app.current_org_id = 1 (example)
• RLS policy: organization_id = current_app_org_id()
• PostgreSQL enforces this at the row level — impossible to see other orgs' data

RLS Helper Functions

Defined in schema.sql:

CREATE OR REPLACE FUNCTION current_app_user_id() RETURNS BIGINT AS $$
    SELECT NULLIF(current_setting('app.current_user_id', true), '')::BIGINT;
$$ LANGUAGE SQL STABLE;

CREATE OR REPLACE FUNCTION current_app_org_id() RETURNS BIGINT AS $$
    SELECT NULLIF(current_setting('app.current_org_id', true), '')::BIGINT;
$$ LANGUAGE SQL STABLE;

CREATE OR REPLACE FUNCTION current_app_role() RETURNS TEXT AS $$
    SELECT NULLIF(current_setting('app.current_role', true), '');
$$ LANGUAGE SQL STABLE;

-- Note: No is_superadmin() function. Superadmins bypass RLS via AdminPool (owner role).

STABLE keyword: Tells PostgreSQL the function won't change during the transaction — safe for index scans.

Connection Pool Management

Pool Sizing

Because each request holds a connection for its full duration, pool size must match concurrency:

DB_POOL_MIN=5          # Baseline connections
DB_POOL_MAX=25         # Must be >= peak concurrent requests

Critical: If DB_POOL_MAX is too low, requests will block waiting for a connection.

Monitoring

Critical metric: Connection pool wait count (should be 0)

// Health check exposes pool stats
{
  "database": {
    "total_connections": 12,
    "idle_connections": 7,
    "active_connections": 5,
    "max_connections": 25,
    "wait_count": 0,           // ← Should always be 0
    "wait_duration": "0s"
  }
}

Alert threshold: wait_count > 0 for 1+ minute → increase DB_POOL_MAX

Connection Lifetime

DB_POOL_MAX_CONN_LIFETIME=1h       # Recycle connections hourly
DB_POOL_MAX_CONN_IDLE_TIME=30m     # Close idle connections after 30min

These settings prevent long-lived connections from holding stale state.

Security Guarantees

Tenant Isolation

Guaranteed by PostgreSQL, not application code:

1. Middleware sets app.current_org_id = 1
2. RLS policy: WHERE organization_id = current_app_org_id()
3. Query: SELECT * FROM appointments
4. PostgreSQL rewrites to: SELECT * FROM appointments WHERE organization_id = 1
5. Even if query has WHERE id = 999, RLS adds the org filter

Impossible scenarios:

• ❌ Org 1 sees Org 2's data (RLS blocks at DB level)
• ❌ Request without org context sees data (no current_app_org_id() → NULL → no matches)
• ❌ Superadmin org context pollutes patient request (session vars are transaction-scoped)

Transaction Scope

Session variables are set with set_config(..., true):

• Third parameter = true: Transaction-scoped
• Automatic cleanup: Variables reset when transaction ends
• No cross-request pollution: Each request gets a clean connection

Superadmin Access

Superadmins bypass all RLS via the AdminPool (table owner role restartix). PostgreSQL does not enforce RLS policies against the table owner.

No RLS policy uses is_superadmin() — the bypass is at the connection level, not the policy level:

Superadmin request → AdminPool (restartix, owner) → RLS bypassed entirely
Staff/patient request → AppPool (restartix_app, restricted) → RLS enforced
Public request → AppPool (no session vars) → public-access policies only

This is a defense-in-depth guarantee: even if application code has a bug, a non-superadmin request on the AppPool cannot access data outside its org scope.

Fail Modes

Failure	Behavior	Recovery
Missing Authorization header	401 Unauthorized	Client must retry with token
Invalid/expired token	401 Unauthorized	Client refreshes session, retries
User blocked	403 Forbidden	User must contact support
No org membership	403 Forbidden	Admin must add user to org
Pool exhausted	503 Service Unavailable	Alert fires, scale up pool
RLS variables fail to set	500 Internal Server Error	Connection released, retried

Performance Considerations

Single Connection Per Request

Why: RLS session variables are connection-specific. Using a new connection mid-request would lose the RLS context.

Impact: Connection pool must be sized for peak concurrency.

Optimization: Keep request handlers fast (< 500ms) to maximize connection reuse.

Query Plan Caching

PostgreSQL caches query plans. RLS policies are baked into plans, so there's no per-query overhead for RLS filtering.

Benchmark: RLS adds < 1ms to query time (PostgreSQL uses indexes on organization_id).

Index Requirements

Every table with organization_id must have an index:

CREATE INDEX idx_appointments_org ON appointments(organization_id);
CREATE INDEX idx_patients_org ON patients(organization_id);
CREATE INDEX idx_forms_org ON forms(organization_id);
-- etc.

Without these, RLS becomes a sequential scan (slow).

Testing RLS

Integration Tests

Set session variables manually to test RLS:

func TestRLS(t *testing.T) {
    // Set up: Create users in org 1 and org 2
    org1User := createUser(t, org1.ID)
    org2User := createUser(t, org2.ID)

    // Test: User from org 1 queries appointments
    conn := testDB.Acquire(ctx)
    defer conn.Release()

    conn.Exec(ctx, `
        SELECT set_config('app.current_user_id', $1::text, true),
               set_config('app.current_org_id', $2::text, true),
               set_config('app.current_role', $3, true)
    `, org1User.ID, org1.ID, "patient")

    var count int
    conn.QueryRow(ctx, "SELECT COUNT(*) FROM appointments").Scan(&count)

    // Verify: Only org 1's appointments visible
    assert.Equal(t, org1AppointmentCount, count)
}

Manual Verification (psql)

-- Set session vars
SELECT set_config('app.current_org_id', '1', true);
SELECT set_config('app.current_role', 'patient', true);

-- Query (RLS applies)
SELECT * FROM appointments;
-- Should only return org 1's appointments

-- Switch org
SELECT set_config('app.current_org_id', '2', true);
SELECT * FROM appointments;
-- Should only return org 2's appointments

Related Documentation

• Schema - RLS policies, helper functions
• API - /v1/me, /v1/me/switch-organization
• Clerk Integration - Token verification
• RLS Policies - System-wide RLS reference