@osiris-ai/postgres-sdk

PostgreSQL database adapter and secret sharing authenticator for Osiris MCP data storage and query execution.

Overview

The PostgreSQL SDK provides production-ready database storage and secure query execution for the Osiris ecosystem. Store MCP authentication data with proper indexing and constraints, plus execute user-provided SQL queries with built-in safety validation.

Key Features:

• 📊 Schema Management - Automatic table creation and migrations
• 📝 Full TypeScript - Complete type safety and IDE support

Installation

npm install @osiris-ai/postgres-sdk pg
npm install --save-dev @types/pg

Quick Start

Database Adapter Setup

import { createMcpServer } from '@osiris-ai/sdk';
import { PostgresDatabaseAdapter } from '@osiris-ai/postgres-sdk';

const postgresAdapter = new PostgresDatabaseAdapter({
  connectionString: process.env.DATABASE_URL!,
  schema: 'osiris' // Optional: defaults to 'public'
});

await createMcpServer({
  name: 'my-mcp',
  version: '1.0.0',
  auth: {
    useHub: true,
    hubConfig: {
      baseUrl: process.env.HUB_BASE_URL!,
      clientId: process.env.OAUTH_CLIENT_ID!,
      clientSecret: process.env.OAUTH_CLIENT_SECRET!,
    },
    database: postgresAdapter
  },
  configure: (server) => {
    // Your MCP tools here
  }
});

Connection Configuration Examples

// Connection string (recommended)
const adapter = new PostgresDatabaseAdapter({
  connectionString: 'postgresql://user:pass@localhost:5432/database'
});

// Individual parameters
const adapter = new PostgresDatabaseAdapter({
  host: 'localhost',
  port: 5432,
  database: 'my_mcp_db',
  user: 'postgres',
  password: 'secret',
  ssl: true,
  schema: 'osiris'
});

// Heroku/Railway style
const adapter = new PostgresDatabaseAdapter({
  connectionString: process.env.DATABASE_URL,
  ssl: process.env.NODE_ENV === 'production'
});

Database Schema

The adapter automatically creates these tables:

-- Core tables (new architecture)
connections      -- MCP connection records
authentications  -- OAuth tokens and user data  
sessions         -- Authentication session management
secrets          -- Encrypted credential storage

-- Legacy table (backward compatibility)
tokens           -- Legacy token storage

API Reference

PostgresDatabaseAdapter

Production-ready database adapter implementing the Osiris DatabaseAdapter interface.

class PostgresDatabaseAdapter implements DatabaseAdapter

Constructor

new PostgresDatabaseAdapter(config: PostgresConfig)

Configuration Options

• connectionString: PostgreSQL connection URL
• host, port, database, user, password: Individual connection parameters
• ssl: Enable SSL connection
• schema: Database schema (defaults to 'public')

Core Methods

// Authentication management
await adapter.storeAuthentication(auth);
await adapter.getAuthentication(connectionId, service);
await adapter.updateAuthentication(id, updates);

// Session management  
await adapter.storeSession(session);
await adapter.cleanupExpiredSessions();

// Connection management
await adapter.storeConnection(connection);
await adapter.getConnectionsForPackage(packageId);

PostgresSecretSharingAuthenticator

Secure SQL query execution with built-in safety validation.

class PostgresSecretSharingAuthenticator extends SecretSharingAuthenticator

Methods

// Set database credentials
authenticator.set({ db_url: 'postgresql://...' });

// Execute validated SQL query
await authenticator.action(params, secrets);

Usage Examples

Environment Configuration

# .env file
DATABASE_URL=postgresql://user:pass@localhost:5432/mydb
POSTGRES_SCHEMA=osiris

# For production
DATABASE_URL=postgresql://user:pass@prod-host:5432/prod_db?sslmode=require

Security Features

SQL Injection Protection

The authenticator includes built-in validation:

// Blocked dangerous operations
- DROP statements
- DELETE without proper WHERE clauses  
- TRUNCATE statements
- ALTER statements
- Multiple statement execution (SQL injection)

Getting Started

1. Install PostgreSQL:

   # macOS
   brew install postgresql
   brew services start postgresql
      
   # Ubuntu/Debian
   sudo apt-get install postgresql postgresql-contrib
      
   # Or use managed service (AWS RDS, Railway, Supabase)

2. Create database:

   CREATE DATABASE my_mcp_db;
   CREATE USER mcp_user WITH PASSWORD 'secure_password';
   GRANT ALL PRIVILEGES ON DATABASE my_mcp_db TO mcp_user;

3. Install the adapter:

   npm install @osiris-ai/postgres-sdk pg

4. Configure your MCP:

   import { PostgresDatabaseAdapter } from '@osiris-ai/postgres-sdk';
      
   const adapter = new PostgresDatabaseAdapter({
     connectionString: 'postgresql://mcp_user:secure_password@localhost:5432/my_mcp_db'
   });

Contributing

We welcome contributions! Please see our Contributing Guide for details.

Support

• Documentation: https://docs.osirislabs.xyz
• GitHub Issues: https://github.com/fetcchx/osiris-ai/issues
• Discord Community: Join our Discord

License

MIT License - see LICENSE file for details.

Built with ❤️ by the Osiris Labs team.