Authentication Guide

This guide covers how to implement secure authentication for Tabula Lens HTTP API endpoints, protecting your database queries from unauthorized access.

Overview

Tabula Lens uses token-based authentication at the HTTP API level. This approach:

Keeps database credentials secure on the backend
Provides flexible integration with existing auth systems
Supports standard authentication patterns (JWT, OAuth, session-based)
Enables fine-grained access control

Authentication Patterns

JWT Authentication

The most common pattern for Tabula Lens authentication is JSON Web Tokens (JWT).

Backend Implementation

import { TabulaLens, expressAdapter } from '@tabula-lens/node';
import jwt from 'jsonwebtoken';

const tabulaLens = new TabulaLens({
  url: process.env.DATABASE_URL,
  // type is auto-detected from the connection string
});
const JWT_SECRET = process.env.JWT_SECRET;

// Verify JWT middleware
function authenticateToken(req, res, next) {
  const authHeader = req.headers['authorization'];
  const token = authHeader && authHeader.split(' ')[1]; // Bearer TOKEN

  if (!token) {
    return res.status(401).json({ error: 'Access token required' });
  }

  jwt.verify(token, JWT_SECRET, (err, user) => {
    if (err) {
      return res.status(403).json({ error: 'Invalid or expired token' });
    }
    req.user = user;
    next();
  });
}

// Apply authentication middleware before the Tabula Lens adapter
app.use('/api/tabula-lens', authenticateToken, expressAdapter(tabulaLens));

Frontend Implementation

import { DatabaseViewer } from '@tabula-lens/react';

function App() {
  const [token, setToken] = useState(localStorage.getItem('token'));

  const getAuthHeaders = async () => ({
    'Authorization': `Bearer ${token}`
  });

  return (
    <DatabaseViewer
      path="/api/tabula-lens"
      getAuthHeaders={getAuthHeaders}
      onError={(error) => {
        if (error.status === 401) {
          // Redirect to login
          window.location.href = '/login';
        }
      }}
    />
  );
}

Session-Based Authentication

For traditional web applications, you can use session-based authentication with cookies.

Backend Implementation

import express from 'express';
import session from 'express-session';
import { TabulaLens, expressAdapter } from '@tabula-lens/node';

const app = express();
const tabulaLens = new TabulaLens({
  url: process.env.DATABASE_URL,
  // type is auto-detected from the connection string
});

// Session middleware
app.use(session({
  secret: process.env.SESSION_SECRET,
  resave: false,
  saveUninitialized: false,
  cookie: {
    secure: process.env.NODE_ENV === 'production',
    httpOnly: true,
    sameSite: 'strict'
  }
}));

// Authentication middleware
function requireAuth(req, res, next) {
  if (!req.session.userId) {
    return res.status(401).json({ error: 'Authentication required' });
  }
  next();
}

// Apply authentication middleware before the Tabula Lens adapter
app.use('/api/tabula-lens', requireAuth, expressAdapter(tabulaLens));

Frontend Implementation

import { DatabaseViewer } from '@tabula-lens/react';

function App() {
  const getAuthHeaders = async () => ({
    // Cookies are automatically sent by the browser
    'Content-Type': 'application/json'
  });

  return (
    <DatabaseViewer
      path="/api/tabula-lens"
      getAuthHeaders={getAuthHeaders}
      // With cookies, credentials must be included
      // This is handled by the browser automatically
    />
  );
}

OAuth 2.0 Integration

For applications using OAuth 2.0 providers (Google, GitHub, Auth0, etc.).

Backend Implementation

import { TabulaLens } from '@tabula-lens/node';
import { OAuth2Client } from 'google-auth-library';

const tabulaLens = new TabulaLens({
  url: process.env.DATABASE_URL,
  // type is auto-detected from the connection string
});
const oauth2Client = new OAuth2Client(process.env.GOOGLE_CLIENT_ID);

async function verifyGoogleToken(token) {
  try {
    const ticket = await oauth2Client.verifyIdToken({
      idToken: token,
      audience: process.env.GOOGLE_CLIENT_ID,
    });
    return ticket.getPayload();
  } catch (error) {
    throw new Error('Invalid Google token');
  }
}

// Protected endpoint
app.get('/api/tabula-lens', async (req, res) => {
  try {
    const authHeader = req.headers['authorization'];
    const token = authHeader?.replace('Bearer ', '');

    if (!token) {
      return res.status(401).json({ error: 'Authentication required' });
    }

    const user = await verifyGoogleToken(token);

    const result = await tabulaLens.query(req.query);
    res.json(result);
  } catch (error) {
    res.status(401).json({ error: 'Authentication failed' });
  }
});

Advanced Authentication Patterns

Role-Based Access Control (RBAC)

Implement role-based access to control which tables users can query.

interface User {
  id: string;
  role: 'admin' | 'user' | 'readonly';
  permissions: string[];
}

function checkTableAccess(user: User, table: string): boolean {
  if (user.role === 'admin') return true;
  if (user.role === 'readonly') return user.permissions.includes(`read:${table}`);
  return user.permissions.includes(`access:${table}`);
}

app.get('/api/tabula-lens', authenticateToken, async (req, res) => {
  const { table } = req.query;

  if (!checkTableAccess(req.user, table)) {
    return res.status(403).json({
      error: 'Access denied to this table'
    });
  }

  try {
    const result = await tabulaLens.query(req.query);
    res.json(result);
  } catch (error) {
    res.status(500).json({ error: 'Failed to execute query' });
  }
});

Row-Level Security

Combine authentication with database row-level security for fine-grained access control.

app.get('/api/tabula-lens', authenticateToken, async (req, res) => {
  const { table } = req.query;

  // Add user context to query for row-level security
  const enhancedQuery = {
    ...req.query,
    userId: req.user.id, // For database RLS policies
    userRole: req.user.role
  };

  try {
    const result = await tabulaLens.query(enhancedQuery);
    res.json(result);
  } catch (error) {
    res.status(500).json({ error: 'Failed to execute query' });
  }
});

Multi-Tenant Authentication

For multi-tenant applications, add tenant isolation to your authentication.

interface TenantUser {
  userId: string;
  tenantId: string;
  role: string;
}

function authenticateTenant(req, res, next) {
  const token = req.headers['authorization']?.replace('Bearer ', '');

  try {
    const decoded = jwt.verify(token, JWT_SECRET) as TenantUser;
    req.user = decoded;

    // Add tenant context to all queries
    req.query.tenantId = decoded.tenantId;

    next();
  } catch (error) {
    res.status(401).json({ error: 'Invalid token' });
  }
}

app.get('/api/tabula-lens', authenticateTenant, async (req, res) => {
  try {
    const result = await tabulaLens.query(req.query);
    res.json(result);
  } catch (error) {
    res.status(500).json({ error: 'Failed to execute query' });
  }
});

Token Management

Token Refresh

Implement token refresh to maintain secure sessions without forcing frequent logins.

// Refresh token endpoint
app.post('/api/auth/refresh', async (req, res) => {
  const { refreshToken } = req.body;

  try {
    const decoded = jwt.verify(refreshToken, REFRESH_SECRET);
    const newAccessToken = jwt.sign(
      { userId: decoded.userId },
      JWT_SECRET,
      { expiresIn: '15m' }
    );

    res.json({ accessToken: newAccessToken });
  } catch (error) {
    res.status(401).json({ error: 'Invalid refresh token' });
  }
});

Token Storage Best Practices

Frontend Token Storage:

// Best: httpOnly cookies (set by backend)
// Backend: res.cookie('token', token, { httpOnly: true, secure: true })

// Good: Memory storage for SPA
const [token, setToken] = useState(null);

// Acceptable: sessionStorage (cleared on tab close)
sessionStorage.setItem('token', token);

// Avoid: localStorage (vulnerable to XSS)
// localStorage.setItem('token', token); // Not recommended

Error Handling

Handle authentication errors gracefully in the frontend.

function DatabaseViewerWithAuth() {
  const [token, setToken] = useState(null);

  const handleAuthError = (error) => {
    if (error.status === 401) {
      // Token expired or invalid
      setToken(null);
      // Redirect to login or show login modal
    } else if (error.status === 403) {
      // User doesn't have permission
      alert('You do not have permission to access this data');
    }
  };

  if (!token) {
    return <LoginForm onLogin={setToken} />;
  }

  return (
    <DatabaseViewer
      path="/api/tabula-lens"
      getAuthHeaders={async () => ({
        'Authorization': `Bearer ${token}`
      })}
      onError={handleAuthError}
    />
  );
}

Security Best Practices

1. Always Use HTTPS

// Never use HTTP for authenticated requests in production
const endpoint = 'https://api.example.com/api/tabula-lens';

2. Validate Tokens on Every Request

// Don't cache authentication state without validation
function authenticateToken(req, res, next) {
  const token = req.headers['authorization']?.replace('Bearer ', '');

  if (!token) {
    return res.status(401).json({ error: 'Token required' });
  }

  // Always verify the token
  jwt.verify(token, JWT_SECRET, (err, user) => {
    if (err) {
      return res.status(403).json({ error: 'Invalid token' });
    }
    req.user = user;
    next();
  });
}

3. Set Appropriate Token Expiration

// Short-lived access tokens
const accessToken = jwt.sign(
  { userId: user.id },
  JWT_SECRET,
  { expiresIn: '15m' } // Short expiration
);

// Longer-lived refresh tokens
const refreshToken = jwt.sign(
  { userId: user.id },
  REFRESH_SECRET,
  { expiresIn: '7d' } // Longer expiration
);

4. Implement Token Revocation

const revokedTokens = new Set();

function revokeToken(token) {
  revokedTokens.add(token);
}

function isTokenRevoked(token) {
  return revokedTokens.has(token);
}

// In your auth middleware
if (isTokenRevoked(token)) {
  return res.status(401).json({ error: 'Token revoked' });
}

Testing Authentication

Test your authentication implementation with different scenarios.

// Test: Valid token
test('authenticated request succeeds', async () => {
  const token = generateValidToken();
  const response = await fetch('/api/tabula-lens', {
    headers: { 'Authorization': `Bearer ${token}` }
  });

  expect(response.status).toBe(200);
});

// Test: Invalid token
test('invalid token is rejected', async () => {
  const response = await fetch('/api/tabula-lens', {
    headers: { 'Authorization': 'Bearer invalid-token' }
  });

  expect(response.status).toBe(403);
});

// Test: Missing token
test('missing token is rejected', async () => {
  const response = await fetch('/api/tabula-lens');

  expect(response.status).toBe(401);
});

Framework-Specific Examples

Next.js Middleware

import { NextResponse } from 'next/server';
import type { NextRequest } from 'next/server';

export function middleware(request: NextRequest) {
  const token = request.headers.get('authorization')?.replace('Bearer ', '');

  if (!token || !isValidToken(token)) {
    return NextResponse.json({ error: 'Unauthorized' }, { status: 401 });
  }

  return NextResponse.next();
}

export const config = {
  matcher: '/api/tabula-lens/:path*'
};

Express Middleware

export function createAuthMiddleware(jwtSecret: string) {
  return (req, res, next) => {
    const token = req.headers['authorization']?.replace('Bearer ', '');

    if (!token) {
      return res.status(401).json({ error: 'Token required' });
    }

    jwt.verify(token, jwtSecret, (err, user) => {
      if (err) {
        return res.status(403).json({ error: 'Invalid token' });
      }
      req.user = user;
      next();
    });
  };
}

// Usage
app.use('/api/tabula-lens', createAuthMiddleware(process.env.JWT_SECRET));

Troubleshooting

Common Issues

Issue: CORS errors with authentication

// Ensure your backend sends proper CORS headers
app.use(cors({
  origin: 'https://your-frontend.com',
  credentials: true, // Required for cookies
  allowedHeaders: ['Content-Type', 'Authorization']
}));

Issue: Token expires too quickly

// Increase token expiration or implement refresh tokens
const accessToken = jwt.sign(
  { userId: user.id },
  JWT_SECRET,
  { expiresIn: '1h' } // Increase from 15m to 1h
);

Issue: Frontend can’t read authentication error

// Ensure your error handler properly reads the error response
<DatabaseViewer
  onError={async (error) => {
    const errorData = await error.response.json();
    console.error('Authentication error:', errorData);
  }}
/>

Next Steps

Implement role-based access control for your application
Set up token refresh mechanisms
Add audit logging for authentication events
Configure rate limiting to prevent brute force attacks
Review the Security Model for comprehensive security guidance