jpigd - The FastCGI JSON Postgresql Gateway

A tool to aid the elimination of CGI scripts on web servers and moving all the business functionality into the database server along with the rest the business storage logic.

What ?

The original LAMP definition is a Linux machine, Apache web server, MySQL database & PHP application scripting.

graph LR
    c1["Browser"]-.->b1
    subgraph "Linux Machine"
    b1["Apache Web Server"]-->a1
    subgraph "Business Logic"
    a1["PHP cgi"]-->a2["MySQL Database"]
    end
    end

Apart from the obvious variations where the web server is nginx, or database is PostgreSQL or the scripting language is Perl or Python the structure was the backbone of almost every web development. Consider it a template for web applications as we follow the deployment progression.

Whilst this makes for a simple deployment there are drawbacks that have been addressed in various manners.

• The scripts were instantiated on demand, but interpreted scripts are slow to start
  • fix this by allowing the running the scripts as part of the apache process

graph LR
    c1["Browser"]-.->b1["Apache Web Server"]
    subgraph "Linux Machine"
    subgraph "Business Logic"
    subgraph "Web Server"
    b1["Apache Web Server"]-->a1["mod_php PHP handler"]
    end
    a1-->a2["MySQL Database"]
    end
    end

• mod_perl and mod_python for perl and python resident code
• But, these persistent scripts more often than not would leak memory like a sieve and bloat the apache processes until it got killed. Also this usually means updating the PHP scripts requires a restart of the web server with attendant visible outage.
  • fix this by using FastCGI protocol and PHP-FPM (PHP Fast Page Manager) to make the PHP persistent in a separate process

graph LR
    c1["Browser"]-.->b1
    subgraph "Linux Machine"
    b1["Apache Web Server"]-->a1["PHP-FPM daemon."]
    subgraph "Business Logic"
    a1["PHP-FPM daemon."]-->a2["MySQL Database"]
    end
    end

• Use of java as the resident application language using tomcat as a proxy target from the web server achieves a similar browser/application split.

All of these solutions have their uses but one problem that was never tackled was the split of the business logic between the structures and enforcement of constraints in the database and the validation logic in the middleware.

graph LR
    c1["Browser"]-.->b1["Web Server"]
    subgraph "Linux Machine"
    b1["Web Server"]-->a1["Middleware (perl/python/PHP/java)"]
    subgraph "Business Logic"
    a1["Middleware (perl/python/PHP/java)"]-->a2["Database"]
    end
    end

So What ?

jpigd is a component to aid in the resolution of this conceptual split. Its development was triggered by the following observations

• All modern browsers handle JSON as a native data type
• A lot of modern web middleware code is simply interpreting the JSON, applying business logic and submitting it to the db and then converting the result back to JSON to return to the browser.
• PostgreSQL 9.4 and greater can handle JSON data as a native data type
• PostgreSQL has a variety of embeddable languages which can be used to write almost any validation or application that only relies on submitted data and database contents.

So jpigd does a few simple things.

• runs as a fastCGI handler to receive non-static requests from the webserver
• converts the supplied CGI environment to JSON
• validates that the POST data is JSON (or blank)
• calls a PostgreSQL function with the POST data, and the encoded environment
• returns the result of the function as the fastCGI output.

graph LR
    c1["Browser"]-.->b1
    subgraph "Linux Machine"
    b1["Apache Web Server"]-->a1
    a1["jpigd"]-->a2["PostgresSQL Entry function"]
    subgraph "Business Logic / PostgreSQL Database"
    a2["PostgresSQL Entry function"]-->a3["Database"]
    end
    end

What does this mean for me as a Web Developer ?

Fundamentally it entails no change whatsoever for a web developer. As long as the browsers expects to send submitted data to the backend as JSON and receive the results as JSON there is nothing to do.

What does this mean for me as a Middleware Developer ?

Time for training up on PostgreSQL embedded procedural languages PostgreSQL Procedural Languages

What does this mean for me as the Application Developer ?

Essentially it is the same as always.

• Validate input
• check for observance of business rules
• persist data
• generate results

It is only the runtime arena that has changed. The most important lesson that has been observed in use so far would be that no caching of session data is needed, the database is right next to you, just do the checks live.

What are the security considerations ?

From the outset the overiding design consideration has been security.

• The fastCGI protocol defines the details of the POST data in a control channel
• buffers are only allocated according to this metadata.
• failure to read sufficient data is treated as an error condition and a simple JSON error is returned.
• the incoming data is verified as valid JSON or a simple JSON error is returned.
• pass the JSON and environment data to the database in the parameter block This makes Little Bobby Tables type attacks impossible at this level.
• returned data is simply written back as based on returned length
• returned headers, whilst we obviously trust the database, have to be returned as valid JSON or they are simply ignored.
• the daemon can run as any user/group in a suitably isolated environment as long as
  • it can open the socket for listening
  • it can open the log file
  • it can open the connection to the database
• the only database permission required is to be able to call the entry function,
  • the database function can be written with the SECURITY DEFINER attribute to further isolate the caller from any other database access.

And in the database ?

Although the security aspects of the handling function are outside of the control of jpigd use of the preferred embedded procedural languages like plpgsql will normally provide suitable protection. plpgsql makes it easy to safely manipulate data without exposing yourself to quoting attacks, and if you eliminate any use of the PERFORM command you'll almost certainly be safe. The logic problems above and beyond the preceeding are all yours :-)

---

How To Use jpigd

Starting The Daemon

The daemon is a normal self-forking service that is configured via command line options.

jpigd [options ...]

where the options are as follows

Option	Parameter	Default	Meaning
-d	none	none	Enable debugging (may be used more than once)
-D	none	none	Enable child debugging at different level to Main (may be used more than once)
-l	file name	/var/log/jpig.log	Log file name
-a	IP address	127.0.0.1	Address to listen for requests on
-p	Port number	9001	Port to listen on
-i	integer	1	Number of POST handler processes
-m	integer	1024 (is 1M POST)	Maximum POST size in kilobytes (minimum 64)
-c	connection string	host=localhost	postgresql connection string
-f	function name	jsongateway	name of postgresql function

Connection String details

The connection string should be in the postgresql documented format

https://www.postgresql.org/docs/10/static/libpq-connect.html#LIBPQ-CONNSTRING

for command line simplicity it is suggested you use a service=servicename parameter and then have the actual details in a ~/.pg_service.conf file (https://www.postgresql.org/docs/9.6/static/libpq-pgservice.html).

# comment
[servicename]
host=somehost
port=5432
user=jcaller

Function Name and Function Call Behaviour

The function name provided will be used to complete the SQL statement

    select * from %s($1,$2);

where the function would be defined in the following manner

CREATE TYPE jpig_rv_t AS (
    content jsonb,
    headers jsonb
    );

CREATE OR REPLACE FUNCTION jpig2( i_postdata jsonb, i_envdata jsonb ) RETURNS jpig_rv_t
    LANGUAGE plpgsql STABLE SECURITY DEFINER
AS $_$
DECLARE
    p_rv jpig_rv_t;
BEGIN
    SELECT jsontest1( i_postdata, i_envdata )
	INTO p_rv.content;
    SELECT
	jsonb_build_object( 'content-shape', 'triangle', 'incoherence', 'gibberish' )
	INTO p_rv.headers;
    RETURN p_rv;
END
$_$;

Each time the daemon receives an FCGI post it takes the POST data, validates it is JSON and sets it as the first parameter, encodes the CGI environment as a JSON dictionary and sets it as the second parameter.

The function can return one or two parameters. The first is used as the return data. The second is an optional set of HTTP response headers (returned as a JSON dictionary of header/value pairs). The Content-Type header will be set to application/json unless it is provided as part of the data in the second parameter returned.

---

Additional notes on running dameon

The daemon consists of a master, monitoring process and a number of POST handling processes.

The daemon master process handles the following signals

Signal	Action
HUP	Re-open log file
USR1	Increase the number of POST handler processes
USR2	Decrease the number of POST handler processes
INT	Cleanly terminate children and exit
TERM	Cleanly terminate children and exit
QUIT	Cleanly terminate children and exit

The daemon will always keep at least one POST handler.

Configuring Your Webserver,

nginx

location ~ \.cji$ {
    gzip off;
    fastcgi_pass    127.0.0.1:9001;
}

Apache using mod_proxy_fcgi

    ProxyPassMatch "/(.*\.cji)$" "fcgi://localhost:9001/"