{"id":11548,"library":"pg-server","title":"pg-server","description":"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.","status":"maintenance","version":"0.1.8","language":"javascript","source_language":"en","source_url":null,"tags":["javascript","postgres","postgresql","database","db","emulator","server","honeypot","proxy","typescript"],"install":[{"cmd":"npm install pg-server","lang":"bash","label":"npm"},{"cmd":"yarn add pg-server","lang":"bash","label":"yarn"},{"cmd":"pnpm add pg-server","lang":"bash","label":"pnpm"}],"dependencies":[{"reason":"Often used for in-memory database emulation alongside pg-server for testing and development.","package":"pg-mem","optional":true},{"reason":"Recommended for parsing and analyzing SQL queries when intercepting requests to implement custom filtering or modification logic.","package":"pgsql-ast-parser","optional":true}],"imports":[{"note":"This is the primary factory function to create a simple Postgres proxy server instance. The library is primarily designed for TypeScript/ESM usage.","wrong":"const { createSimpleProxy } = require('pg-server');","symbol":"createSimpleProxy","correct":"import { createSimpleProxy } from 'pg-server';"},{"note":"This TypeScript interface defines the contract for session handlers that determine how `pg-server` processes new connections and SQL queries.","symbol":"ISimpleProxySession","correct":"import { ISimpleProxySession } from 'pg-server';"},{"note":"The `Socket` type used within the `onConnect` handler (e.g., `onConnect(socket: Socket)`) refers to Node.js's built-in `net.Socket` and is not exported directly by `pg-server`.","symbol":"Socket","correct":"import { Socket } from 'net';"}],"quickstart":{"code":"import { createSimpleProxy, ISimpleProxySession } from 'pg-server';\nimport { parse, astVisitor } from 'pgsql-ast-parser';\nimport { Client } from 'pg'; // For testing the connection\nimport { Socket } from 'net'; // For the onConnect handler's socket type\n\n// Define a session class to handle connections and queries\nclass QueryFilteringProxySession implements ISimpleProxySession {\n    onConnect(socket: Socket) {\n        console.log(`👤 Client connected from ${socket.remoteAddress}:${socket.remotePort}`);\n    }\n\n    onQuery(query: string) {\n        try {\n            // Parse the query using pgsql-ast-parser\n            const parsedQueries = parse(query);\n            if (parsedQueries.length !== 1) {\n                return { error: 'Error: Only single queries accepted by this proxy.' };\n            }\n\n            const [firstQuery] = parsedQueries;\n            if (firstQuery.type !== 'select') {\n                return { error: 'Error: Only SELECT queries allowed by this proxy.' };\n            }\n\n            let authorized = true;\n            astVisitor(m => ({\n                tableRef: r => authorized = authorized\n                    && !r.schema // Ensure no explicit schema references for simplicity\n                    && ['public_data', 'reports'].includes(r.name) // Allow specific tables\n            })).statement(firstQuery);\n\n            if (!authorized) {\n                return { error: `Error: Access denied to table or query type. Only 'public_data' and 'reports' are allowed.` };\n            }\n\n            console.log(`✅ Allowed query: ${query.substring(0, Math.min(query.length, 100))}...`);\n            return query; // Proceed with the original query to the real database\n\n        } catch (e: any) {\n            console.error(`❌ Query parsing error for '${query.substring(0, Math.min(query.length, 50))}...': ${e.message}`);\n            return { error: `Invalid query syntax or internal parsing error: ${e.message}` };\n        }\n    }\n}\n\n// Create the proxy server, forwarding to a real Postgres instance on default port 5432\nconst REAL_DB_HOST = 'localhost';\nconst REAL_DB_PORT = 5432;\nconst PROXY_PORT = 1234;\nconst PROXY_HOST = '127.0.0.1';\n\nconst proxyServer = createSimpleProxy(\n    { port: REAL_DB_PORT, host: REAL_DB_HOST }, // The real DB to proxy\n    QueryFilteringProxySession\n);\n\nproxyServer.listen(PROXY_PORT, PROXY_HOST, () => {\n    console.log(`🚀 pg-server proxy listening on ${PROXY_HOST}:${PROXY_PORT}`);\n    console.log(`Forwarding requests to real database at ${REAL_DB_HOST}:${REAL_DB_PORT}`);\n\n    // Example: Connecting a pg client to the proxy to test\n    const client = new Client(`postgresql://user:password@${PROXY_HOST}:${PROXY_PORT}/mydatabase`);\n    client.connect()\n        .then(() => {\n            console.log('Client connected to proxy successfully.');\n            // This query should be allowed\n            return client.query('SELECT id, name FROM public_data WHERE active = true;');\n        })\n        .then(res => console.log('Client query to public_data successful, rows:', res.rows.length))\n        .then(() => {\n            // This query should be blocked by the proxy\n            return client.query('SELECT * FROM secret_table;').catch(err => {\n                console.error('Client query to secret_table blocked as expected:', err.message);\n                return Promise.resolve(); // Prevent crashing the test\n            });\n        })\n        .catch(err => console.error('An unexpected client error occurred:', err.message))\n        .finally(() => client.end());\n});\n\n// Handle server-level errors\nproxyServer.on('error', (err: NodeJS.ErrnoException) => {\n    if (err.code === 'EADDRINUSE') {\n        console.error(`Error: Port ${PROXY_PORT} is already in use. Please choose another port or stop the conflicting process.`);\n    } else {\n        console.error('Proxy server encountered an error:', err.message);\n    }\n    process.exit(1);\n});\n","lang":"typescript","description":"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."},"warnings":[{"fix":"Pin to a specific patch version (e.g., `\"pg-server\": \"~0.1.8\"`) or thoroughly test your application when upgrading any version.","message":"As a pre-1.0 package (current version 0.1.8), the API surface is subject to breaking changes without adhering strictly to semantic versioning. Any update, even a minor or patch version, could introduce breaking changes.","severity":"breaking","affected_versions":">=0.1.0"},{"fix":"Be aware that community support might be limited. For critical applications, consider contributing to the project or forking it if significant changes or features are required.","message":"The package appears to be in maintenance mode, with its last npm update over two years ago. This suggests that active development for new features, performance improvements, or critical bug fixes may not occur.","severity":"gotcha","affected_versions":"<1.0.0"},{"fix":"Thoroughly audit all custom logic within `onQuery` and `onConnect` handlers. Ensure robust error handling, input validation, and secure parsing of SQL queries, especially when interacting with untrusted client input.","message":"When used as a proxy or honeypot, `pg-server` handles sensitive database traffic. Improper configuration or vulnerabilities in custom query handlers (e.g., in `onQuery`) could expose data, bypass security controls, or lead to other security breaches.","severity":"gotcha","affected_versions":">=0.1.0"},{"fix":"Benchmark performance in your specific use case. Consider optimizing query parsing logic or primarily using `pg-server` for development, testing, or security auditing rather than as a primary production proxy for all traffic.","message":"Intercepting and parsing every SQL query can introduce significant performance overhead, especially for high-volume database traffic. This might make `pg-server` unsuitable for certain production environments requiring extremely low latency.","severity":"gotcha","affected_versions":">=0.1.0"}],"env_vars":null,"last_verified":"2026-04-19T00:00:00.000Z","next_check":"2026-07-18T00:00:00.000Z","problems":[{"fix":"Choose an available port for `proxyServer.listen()` or identify and stop the process currently using the conflicting port. On Linux, `sudo lsof -i :1234` can help identify the process.","cause":"The specified port (e.g., 1234) that `pg-server` attempts to listen on is already in use by another process on your system.","error":"Error: listen EADDRINUSE: address already in use :::1234"},{"fix":"Ensure your actual Postgres database server is running and accessible on the specified host and port (e.g., `localhost:5432`). Check network connectivity and firewall rules if applicable.","cause":"When `pg-server` is configured as a proxy, this error indicates that the target Postgres server (the `host:port` provided in `createSimpleProxy`'s first argument) is not running or is inaccessible from where `pg-server` is executed.","error":"Client query failed: connect ECONNREFUSED 127.0.0.1:5432"},{"fix":"Install the `pg` client library: `npm install pg` (and `npm install --save-dev @types/pg` if using TypeScript).","cause":"This error typically occurs in the test client code (e.g., `new Client()`) if the `pg` client library is not installed or incorrectly imported.","error":"TypeError: Cannot read properties of undefined (reading 'Client')"},{"fix":"Ensure the SQL query string passed to `parse()` is valid PostgreSQL syntax. Implement robust `try/catch` blocks around `parse()` calls in `onQuery` to gracefully handle malformed queries and provide informative error messages to the client.","cause":"Your `onQuery` handler attempts to parse an invalid or unexpectedly formatted SQL query string using `pgsql-ast-parser`, leading to a parsing error.","error":"Query parsing error: expecting EOF at 'FROM' but found 'blah'"}],"ecosystem":"npm"}