pg-server

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); });