Table of contents

• 🔌 Usage: As a proxy
• 💻 Usage: As a Postgres server emulator
• 📚 Some literature

🔌 Usage: As a proxy

Let's say that you want to proxy a real postgres server instance, and let filter only requests that access a given table.

pg-server contains a small utility that abstracts away most of the heavy lifting and which lets you listen/intercept requests.

import {createSimpleProxy, ISimpleProxySession} from 'pg-server';


const server = createSimpleProxy(
    // The DB that must be proxied
    { port: 5432, host: 'localhost' }

    // A new session context
    //  (one will be constructed for each connection)
    , class implements ISimpleProxySession {
        // An optional handler which will be called
        //  on each new connection
        onConnect(socket: Socket) {
            console.log('👤 Client connected, IP: ', socket.remoteAddress);
        }
        // A handler which will be called for each sql query
        onQuery(query: string) {

            // Ok, proceed to this query, unmodified.
            // You could also choose to modify the query.
            return query;
            // ... or return an error
            return { error: 'Forbidden !' };
        }
});


// listen on localhost:1234
// ... which will now appear as a postgres db server !
sever.listen(1234, 'localhost');

Example: Analyze & Intercept some queries

You can use pgsql-ast-parser, another library of mine, to parse the inbound requests in order to decide if you'd like to forward them to the actual sql server.

For instance, to only allow simple select requests without joins on a given set of tables, you could do something like that:

import {createSimpleProxy, ISimpleProxySession} from 'pg-server';
import {parse, astVisitor} from 'pgsql-ast-parser';


const server = createSimpleProxy(
    // The DB that must be proxied
    { port: 5432, host: 'localhost' }
    , class implements ISimpleProxySession {
        onQuery(query: string) {

            // parse the query & check it has only one query
            const parsed = parse(query);
            if (parsed.length !== 1) {
                return { error: 'Only single queries accepted' };
            }

            // check that it is a select
            const [first] = parsed
            if (first.type !== 'select') {
                return { error: 'Only SELECT queries accepted' };
            }

            // check that it selects data from "some_public_table" only
            let authorized = true;
            astVisitor(m => ({
                tableRef: r => authorized = authorized
                    && !r.schema
                    && r.name === 'some_public_table',
            })).statement(first);
            if (!authorized) {
                return { error: `You're note supposed to be here :/` };
            }

            // ok, proceed to this query, unmodified.
            return query;
        }
});

server.listen(1234, '127.0.0.1');

Test it:

const client = new require('pg')
      .Client('postgresql://user:pwd@localhost:1234/mydb')

// this works:
await client.query('select * from some_public_table')
// this fails:
await client.query('select * from other_table')

Advanced proxy

The createSimpleProxy abstracts away lots of things. If you wish to have a more fine grained control over which data is exchanged, you can use createAdvancedProxy() (refer to the types, and to the "Some literature" section below to understand how it works).

💻 Usage: As a Postgres server emulator

You could expose a brand new fake postgres server to the world, without an actual postgres datbase server. As a Honeypot, for instance.

You could also simulate a postgres db, for which you could use pg-mem, another lib of mine which simulates a db in memory.

Simplified interface

TODO

Advanced interface

createAdvancedServer() gives you full control over commands received/responses sent. Only use this if you know the pg protocol a bit.

Example:

const server = createAdvancedServer(class implements IAdvanceServerSession {
    // An optional handler which will be called
    //  on each new connection
    onConnect(socket: Socket) {
        console.log('👤 Client connected, IP: ', socket.remoteAddress);
    }

    // A handler which will be called on each received instuction.
    onCommand({ command }: DbRawCommand, response: IResponseWriter) {
        // use the "response" writer
        // to react to the "command"  argument
    }
})

server.listen(1234, '127.0.0.1');

If you would like your postgres server on a custom already open socket, you can also use the bindSocket(), of which createAdvancedServer() is just a wrapper.

With pg-mem

TODO

📚 Some literature

• awesome-honeypots A list of honeypots
• simple & extended queries A very short explanation about query modes
• postgres on the wire 55 slides about the postgres protocol
• Protocol flow The official procol explanation

Todo

• SSL support (protocol version 1234.5679 on init)
• Simplified interface