pg-server

0.1.8 · maintenance · verified Sun Apr 19

pg-server is a JavaScript/TypeScript library designed to act as a Postgres database server emulator, proxy, or honeypot. It allows developers to intercept, modify, and filter SQL queries in real-time. The current stable version is 0.1.8, indicating it is pre-1.0 and its API may evolve. While the package has not seen recent updates (last updated over two years ago), it provides unique capabilities for testing, security analysis, and development workflows by integrating with tools like pg-mem for in-memory database emulation and pgsql-ast-parser for detailed SQL query parsing and manipulation. Its primary differentiator is the granular control it offers over the Postgres wire protocol, enabling use cases from security honeypots to robust query filtering for specific applications. It is particularly useful for developers needing to control or observe database interactions at a low level without altering client code.

Common errors

Warnings

Install

Imports

Quickstart

Demonstrates setting up a Postgres proxy that filters SELECT queries based on allowed tables, integrating with `pgsql-ast-parser`, and includes a test client to show both allowed and blocked queries.

import { createSimpleProxy, ISimpleProxySession } from 'pg-server';
import { parse, astVisitor } from 'pgsql-ast-parser';
import { Client } from 'pg'; // For testing the connection
import { Socket } from 'net'; // For the onConnect handler's socket type

// Define a session class to handle connections and queries
class QueryFilteringProxySession implements ISimpleProxySession {
    onConnect(socket: Socket) {
        console.log(`👤 Client connected from ${socket.remoteAddress}:${socket.remotePort}`);
    }

    onQuery(query: string) {
        try {
            // Parse the query using pgsql-ast-parser
            const parsedQueries = parse(query);
            if (parsedQueries.length !== 1) {
                return { error: 'Error: Only single queries accepted by this proxy.' };
            }

            const [firstQuery] = parsedQueries;
            if (firstQuery.type !== 'select') {
                return { error: 'Error: Only SELECT queries allowed by this proxy.' };
            }

            let authorized = true;
            astVisitor(m => ({
                tableRef: r => authorized = authorized
                    && !r.schema // Ensure no explicit schema references for simplicity
                    && ['public_data', 'reports'].includes(r.name) // Allow specific tables
            })).statement(firstQuery);

            if (!authorized) {
                return { error: `Error: Access denied to table or query type. Only 'public_data' and 'reports' are allowed.` };
            }

            console.log(`✅ Allowed query: ${query.substring(0, Math.min(query.length, 100))}...`);
            return query; // Proceed with the original query to the real database

        } catch (e: any) {
            console.error(`❌ Query parsing error for '${query.substring(0, Math.min(query.length, 50))}...': ${e.message}`);
            return { error: `Invalid query syntax or internal parsing error: ${e.message}` };
        }
    }
}

// Create the proxy server, forwarding to a real Postgres instance on default port 5432
const REAL_DB_HOST = 'localhost';
const REAL_DB_PORT = 5432;
const PROXY_PORT = 1234;
const PROXY_HOST = '127.0.0.1';

const proxyServer = createSimpleProxy(
    { port: REAL_DB_PORT, host: REAL_DB_HOST }, // The real DB to proxy
    QueryFilteringProxySession
);

proxyServer.listen(PROXY_PORT, PROXY_HOST, () => {
    console.log(`🚀 pg-server proxy listening on ${PROXY_HOST}:${PROXY_PORT}`);
    console.log(`Forwarding requests to real database at ${REAL_DB_HOST}:${REAL_DB_PORT}`);

    // Example: Connecting a pg client to the proxy to test
    const client = new Client(`postgresql://user:password@${PROXY_HOST}:${PROXY_PORT}/mydatabase`);
    client.connect()
        .then(() => {
            console.log('Client connected to proxy successfully.');
            // This query should be allowed
            return client.query('SELECT id, name FROM public_data WHERE active = true;');
        })
        .then(res => console.log('Client query to public_data successful, rows:', res.rows.length))
        .then(() => {
            // This query should be blocked by the proxy
            return client.query('SELECT * FROM secret_table;').catch(err => {
                console.error('Client query to secret_table blocked as expected:', err.message);
                return Promise.resolve(); // Prevent crashing the test
            });
        })
        .catch(err => console.error('An unexpected client error occurred:', err.message))
        .finally(() => client.end());
});

// Handle server-level errors
proxyServer.on('error', (err: NodeJS.ErrnoException) => {
    if (err.code === 'EADDRINUSE') {
        console.error(`Error: Port ${PROXY_PORT} is already in use. Please choose another port or stop the conflicting process.`);
    } else {
        console.error('Proxy server encountered an error:', err.message);
    }
    process.exit(1);
});

view raw JSON →