#!perl
use strict;
use warnings;
use DBI;
use DBD::Pg;
use DBIx::Pg::CallFunction;
use DBIx::Connector;
use Time::HiRes;
use JSON;
use Plack::Request;
# DBIx::Connector allows us to safely reuse connections by making sure that we
# don't reuse DBI connections we inherited from our parent process after a
# fork().
my $dbconn = DBIx::Connector->new("dbi:Pg:service=pg_proc_jsonrpc", '', '', {pg_enable_utf8 => 1});
# We can simply pass undef as the database handle since we set it for every
# request anyway. Additionally, enable the lookup cache to avoid unnecessary
# database roundtrips.
my $pg = DBIx::Pg::CallFunction->new(undef, {RaiseError => 0, EnableFunctionLookupCache => 1});
my $app = sub {
my $env = shift;
my $invalid_request = [
'400',
[ 'Content-Type' => 'application/json; charset=utf-8' ],
[ to_json({
jsonrpc => '2.0',
error => {
code => -32600,
message => 'Invalid Request.'
},
id => undef
}, {pretty => 1}) ]
];
my ($method, $params, $id, $version);
if ($env->{REQUEST_METHOD} eq 'GET') {
my $req = Plack::Request->new($env);
$method = $req->path_info;
$method =~ s{^.*/}{};
$params = $req->query_parameters->mixed;
} elsif ($env->{REQUEST_METHOD} eq 'POST' &&
$env->{HTTP_ACCEPT} =~ m!application/json! &&
$env->{CONTENT_TYPE} =~ m!^application/json!
) {
my $json_input;
my $jsonrpc;
$env->{'psgi.input'}->read($json_input, $env->{CONTENT_LENGTH});
my $json_rpc_request = from_json($json_input);
$method = $json_rpc_request->{method};
$params = $json_rpc_request->{params};
$id = $json_rpc_request->{id};
$version = $json_rpc_request->{version};
$jsonrpc = $json_rpc_request->{jsonrpc};
# must be version 2.0 if "jsonrpc" is defined
if (defined $jsonrpc)
{
return $invalid_request if ($jsonrpc ne '2.0');
return $invalid_request if (defined $version);
$version = '2.0';
}
# assume 1.0 if "version" is not defined
if (!defined $version)
{
$version = 1.0;
}
if (!($version eq '1.0' || $version eq '1.1' || $version eq '2.0'))
{
# unsupported version
return $invalid_request;
}
} else {
return $invalid_request;
}
unless ($method =~ m/
^
(?:
([a-zA-Z_][a-zA-Z0-9_]*) # namespace
\.)?
([a-zA-Z_][a-zA-Z0-9_]*) # function name
$
/x && (!defined $params || ref($params) eq 'HASH')) {
return $invalid_request;
}
my ($namespace, $function_name) = ($1, $2);
my $dbh;
my $result;
my $error = undef;
my $success;
# A list of SQLSTATEs which, after failure, have a reasonably good chance
# of succeeding if retried. See
# http://www.postgresql.org/docs/current/static/errcodes-appendix.html for
# a list of error codes.
my @retryable_sqlstates = (
"40001", # serialization failure
"40P01" # deadlock
);
$success = 0;
eval
{
# Ask for a connection from DBIx::Connector. It is important to do this
# inside the eval in case the connection attempt fails so we can catch
# the error message and send that to the client. Note: it is important
# to use _dbh instead of dbh here because of reasons explained below.
$dbh = $dbconn->dbh;
# if there's no connection, we're done
die $DBI::errstr if (!defined($dbh));
$pg->set_dbh($dbh);
# $dbconn->dbh calls $dbh->ping() every time, and there's no reason to do
# that, so we do the following instead: we keep getting the connection
# from _dbh so long as queries work correctly on that connection. If,
# for some reason, a query does not work on that connection and we get
# back an SQLSTATE suggesting that the connection might be broken, we
# call dbh to go through the entire ping/reconnect procedure and retry
# the loop immediately. Because we skip the delay, we only allow that
# to happen once per loop.
my $retried_connection = 0;
# loop until we hit an error we can't recover from
my $delay = 0.1;
while ($delay <= 3.0)
{
$result = $pg->$function_name($params, $namespace);
if (!$retried_connection &&
($pg->{SQLState} eq "08000" ||
$pg->{SQLState} eq "57P01" ||
$pg->{SQLState} eq "57P02"))
{
$retried_connection = 1;
print STDERR "ERROR SQLSTATE $pg->{SQLState}; re-establishing connection\n";
$dbh = $dbconn->dbh;
$pg->set_dbh($dbh);
next;
}
# If the function succeeded but didn't return any results, $result
# will still be undef. We need to check the actual SQLSTATE.
if ($pg->{SQLState} eq "00000")
{
$success = 1;
last;
}
# if there's no reason to assume that retrying would help, exit the loop
last if (scalar grep { $_ eq $pg->{SQLState} } @retryable_sqlstates) == 0;
# sleep for a while and then retry
print STDERR "ERROR SQLSTATE $pg->{SQLState}; retrying in $delay seconds\n";
Time::HiRes::sleep($delay);
$delay = $delay * 3;
}
};
# extract the appropriate error message if the function call didn't succeed
if ($@) {
$error = $@;
}
elsif (!$success) {
$error = $pg->{SQLErrorMessage};
}
my $response = {
result => $result,
error => $error
};
if (defined $id) {
$response->{id} = $id;
}
if ($version eq '1.1') {
$response->{version} = $version;
}
if ($version eq '2.0') {
$response->{jsonrpc} = $version;
}
if ($version eq '1.1' || $version eq '2.0') {
# JSON-RPC 1.1 and 2.0 requires us to delete the "error" field on success and
# the "result" field on error. Additionally, the "error" field is an object.
delete $response->{error} if ($success);
if (!$success)
{
delete $response->{result};
my $errobj = { code => -32000, message => $response->{error} };
$response->{error} = $errobj;
}
}
return [
'200',
[ 'Content-Type' => 'application/json; charset=utf-8' ],
[ to_json($response, {pretty => 1}) ]
];
};
__END__
=head1 NAME
pg_proc_jsonrpc.psgi - PostgreSQL Stored Procedures JSON-RPC Daemon
=head1 SYNOPSIS
How to setup using C<Apache2>, C<mod_perl> and L<Plack::Handler::Apache2>.
Instructions for a clean installation of Ubuntu 12.04 LTS.
Install necessary packages
sudo apt-get install cpanminus build-essential postgresql-9.1 libplack-perl libdbd-pg-perl libjson-perl libmodule-install-perl libtest-exception-perl libapache2-mod-perl2 apache2-mpm-prefork
Create a database and database user for our shell user
sudo -u postgres createuser --no-superuser --no-createrole --createdb $USER
sudo -u postgres createdb --owner=$USER $USER
Try to connect
psql -c "SELECT 'Hello world'"
?column?
-------------
Hello world
(1 row)
Create database user for apache
sudo -u postgres createuser --no-superuser --no-createrole --no-createdb www-data
Download and build DBIx::Pg::CallFunction
cpanm --sudo DBIx::Pg::CallFunction
Download and build DBIx::Connector
cpanm --sudo DBIx::Pg::Connector
Grant access to connect to our database
psql -c "GRANT CONNECT ON DATABASE $USER TO \"www-data\""
Configure pg_service.conf
# copy sample config
sudo cp -n /usr/share/postgresql/9.1/pg_service.conf.sample /etc/postgresql-common/pg_service.conf
echo "
[pg_proc_jsonrpc]
application_name=pg_proc_jsonrpc
dbname=$USER
" | sudo sh -c 'cat - >> /etc/postgresql-common/pg_service.conf'
Configure Apache
# Add the lines below between <VirtualHost *:80> and </VirtualHost>
# to your sites-enabled file, or to the default file if this
# is a new installation.
# /etc/apache2/sites-enabled/000-default
<Location /postgres>
SetHandler perl-script
PerlResponseHandler Plack::Handler::Apache2
PerlSetVar psgi_app /usr/local/bin/pg_proc_jsonrpc.psgi
</Location>
<Perl>
use Plack::Handler::Apache2;
Plack::Handler::Apache2->preload("/usr/local/bin/pg_proc_jsonrpc.psgi");
</Perl>
Restart Apache
sudo service apache2 restart
Done!
You can now access PostgreSQL Stored Procedures, e.g.
L<http://127.0.0.1/postgres/now> using any JSON-RPC client,
such as a web browser, some Perl program, or
any application capable of talking HTTP and JSON-RPC.
Let's try it with an example!
Connect to our database using psql and copy/paste the SQL commands
to create a simple schema with some Stored Procedures.
Note the C<SECURITY DEFINER> below. It means the functions will
be executed by the same rights as our C<$USER>, with full access
to our database C<$USER>. The C<www-data> user is only granted
C<EXECUTE> access to the functions, and cannot touch the tables
using C<SELECT>, C<UPDATE>, C<INSERT> or C<DELETE> SQL commands.
You can think of C<SECURITY DEFINER> as a sudo for SQL.
psql
-- Some tables:
CREATE TABLE users (
userid serial not null,
username text not null,
datestamp timestamptz not null default now(),
PRIMARY KEY (userid),
UNIQUE(username)
);
CREATE TABLE usercomments (
usercommentid serial not null,
userid integer not null,
comment text not null,
datestamp timestamptz not null default now(),
PRIMARY KEY (usercommentid),
FOREIGN KEY (userid) REFERENCES Users(userid)
);
-- By default, all users including www-data, will be able to execute any functions.
-- Revoke all access on functions from public, which allows us to explicitly grant
-- access only to those functions we wish to expose publicly.
ALTER DEFAULT PRIVILEGES REVOKE ALL ON FUNCTIONS FROM PUBLIC;
-- Function to make a new comment
CREATE OR REPLACE FUNCTION new_user_comment(_username text, _comment text) RETURNS BIGINT AS $$
DECLARE
_userid integer;
_usercommentid integer;
BEGIN
SELECT userid INTO _userid FROM users WHERE username = _username;
IF NOT FOUND THEN
INSERT INTO users (username) VALUES (_username) RETURNING userid INTO STRICT _userid;
END IF;
INSERT INTO usercomments (userid, comment) VALUES (_userid, _comment) RETURNING usercommentid INTO STRICT _usercommentid;
RETURN _usercommentid;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
-- Function to get all comments by a user
CREATE OR REPLACE FUNCTION get_user_comments(OUT usercommentid integer, OUT comment text, OUT datestamp timestamptz, _username text) RETURNS SETOF RECORD AS $$
SELECT
usercomments.usercommentid,
usercomments.comment,
usercomments.datestamp
FROM usercomments JOIN users USING (userid) WHERE users.username = $1
ORDER BY 1
$$ LANGUAGE sql SECURITY DEFINER;
-- Function to get all comments by all users
CREATE OR REPLACE FUNCTION get_all_comments(OUT usercommentid integer, OUT username text, OUT comment text, OUT datestamp timestamptz) RETURNS SETOF RECORD AS $$
SELECT
usercomments.usercommentid,
users.username,
usercomments.comment,
usercomments.datestamp
FROM usercomments JOIN users USING (userid)
ORDER BY 1
$$ LANGUAGE sql SECURITY DEFINER;
-- Grant EXECUTE on the functions to www-data
GRANT EXECUTE ON FUNCTION new_user_comment(_username text, _comment text) TO "www-data";
GRANT EXECUTE ON FUNCTION get_user_comments(OUT usercommentid integer, OUT comment text, OUT datestamp timestamptz, _username text) TO "www-data";
GRANT EXECUTE ON FUNCTION get_all_comments(OUT usercommentid integer, OUT username text, OUT comment text, OUT datestamp timestamptz) TO "www-data";
The JSON-RPC service supports both GET and POST,
let's try GET as it is easiest to test using a web browser.
However, when developing for real ALWAYS use POST and
set Content-Type to application/json.
L<http://127.0.0.1/postgres/new_user_comment?_username=joel&_comment=Accessing+PostgreSQL+from+a+browser+is+easy!>
{
"error" : null,
"result" : "1"
}
L<http://127.0.0.1/postgres/new_user_comment?_username=lukas&_comment=I+must+agree!+Also+easy+from+JQuery!>
{
"error" : null,
"result" : "2"
}
L<http://127.0.0.1/postgres/new_user_comment?_username=claes&_comment=Or+using+JSON::RPC::Simple>
{
"error" : null,
"result" : "3"
}
L<http://127.0.0.1/postgres/get_all_comments>
{
"error" : null,
"result" : [
{
"usercommentid" : 1,
"comment" : "Accessing PostgreSQL from a browser is easy!",
"datestamp" : "2012-06-03 01:20:25.653989+07",
"username" : "joel"
},
{
"usercommentid" : 2,
"comment" : "I must agree! Also easy from JQuery!",
"datestamp" : "2012-06-03 01:21:30.19081+07",
"username" : "lukas"
},
{
"usercommentid" : 3,
"comment" : "Or using JSON::RPC::Simple",
"datestamp" : "2012-06-03 01:22:09.149454+07",
"username" : "claes"
}
]
}
=head1 DESCRIPTION
C<pg_proc_jsonrpc> is a JSON-RPC daemon to access PostgreSQL stored procedures.
The script implements the L<PSGI> standard and can be started using
the L<plackup> script, or by any webserver capable of handling PSGI files,
such as Apache using L<Plack::Handler::Apache2>.
As L<DBI> is not thread safe, you must not use threaded webservers,
such as C<apache2-mpm-worker>, use instead e.g. C<apache2-mpm-prefork>.
It only supports named parameters, JSON-RPC version 1.1 or 2.0.
L<DBIx::Pg::CallFunction> is used to map
method and params in the JSON-RPC call to the corresponding
PostgreSQL stored procedure. DBIx::Connector is used to safely
maintain database connections across requests.
=head1 SEE ALSO
L<plackup> L<Plack::Runner> L<PSGI|PSGI> L<DBIx::Pg::CallFunction> L<DBIx::Connector>
=cut