Skip to content

Instantly share code, notes, and snippets.

@ernesmb
Last active February 3, 2017 18:12
Show Gist options
  • Star 2 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save ernesmb/beb25f539f8ff38bbd891e6d114ea7f4 to your computer and use it in GitHub Desktop.
Save ernesmb/beb25f539f8ff38bbd891e6d114ea7f4 to your computer and use it in GitHub Desktop.
Setup a point collector app

SETUP A SECURITY DEFINER FUNCTION

Insert values to a table from the publicuser

This guide is intended to provide the steps for letting people (aka publicuser role in PostgreSQL) insert point features directly from a simple CARTO map application.

We basically need to write a stored procedure that takes the data and insert it into the table. The only trick is writing the procedure with a SECURITY DEFINER that will allow to grant execution permission to the function without messing with table permission directly.

The function will be executed with the same privileges as the user who creates it, so we need to be very careful about what our function does.

Writing the function

CREATE OR REPLACE FUNCTION insertpoint(
  lon numeric, 
  lat numeric, 
  name text, 
  description text, 
  category text, 
  tablename text)
RETURNS TABLE(cartodb_id INT)
LANGUAGE 'plpgsql' SECURITY DEFINER
RETURNS NULL ON NULL INPUT 
AS $$
DECLARE
  sql text;
BEGIN
  sql:=
  'WITH do_insert AS (
    INSERT INTO '||quote_ident(tablename)||'(the_geom, name, description, category) 
    VALUES '
      ||'('
      ||'ST_SetSRID(ST_MakePoint('||lon||','||lat||'), 4326),'
      ||quote_literal(name)||','
      ||quote_literal(description)||','
      ||quote_literal(category)
      ||')'
    ||'RETURNING cartodb_id)' 
  ||'SELECT cartodb_id FROM do_insert';

RETURN QUERY EXECUTE sql;

END;
$$; 

So, first thing is giving a name to the function, as well as defining the arguments that it will take as input. In this case, we are only taking lon and lat coordinates, name for the new feature, description and category as the typical input we can expect for a crowdsourced data collection app. We should also specify the table name as the last parameter (tablename).

As we will later see, our function is going to return a table that only contains the cartodb_id for the inserted row:

RETURNS TABLE(cartodb_id INT)

This part specifies the language we are using and allows to grant specific permission to the function:

LANGUAGE 'plpgsql' SECURITY DEFINER

The body of the function should be between AS $$ and $$ lines.

We need to declare a text variable that will store the function itself so we can execute it afterwards. We need to follow this logic in order to being able to use the table name and different column names as arguments/variables within the function.

DECLARE
  sql text;

This is the body of the function and where most of the logic relies:

'WITH do_insert AS (
    INSERT INTO '||quote_ident(tablename)||'(the_geom, name, description, category) 
    VALUES '
      ||'('
      ||'ST_SetSRID(ST_MakePoint('||lon||','||lat||'), 4326),'
      ||quote_literal(name)||','
      ||quote_literal(description)||','
      ||quote_literal(category)
      ||')'
    ||'RETURNING cartodb_id)' 
||'SELECT cartodb_id FROM do_insert';

Apart from the string-variable concatenation ('string'||variable||'string') that may produce some confusing syntax, the query is quite straight forward.

We are going to insert a geometry with its alphanumerical data, using the function arguments as values.

The main difficulty here is correctly parsing the arguments so we don't duplicate, miss or change the expected quoting.

The name of the table needs to be used inside the quote_ident() function. That means that it is going to be interpreted as an identifier for the table (double quoted), instead of as a plain string.

The same logic applies for the string values we need to insert, they need to be single-quoted. Otherwise, they will be interpreted as column names (double quoted) or plain text (no quotes) with spaces that would break the SQL syntax. quote_literal() will serve for our purposes.

We are wrapping the INSERT INTO query inside a WITH do_insert AS statement that returns cartodb_id for the newly inserted row. After that, we do a SELECT cartodb_id FROM do_insert, according with what our function returns:

RETURNS TABLE(cartodb_id INT)

The last step is executing the query we have dinamically crafted into the sql variable and returning its output:

RETURN QUERY EXECUTE sql;

Granting permission to the function

In order to make the function executable for the publicuser with the same privileges as the function owner, we need to grant execution permissions to it:

GRANT EXECUTE ON FUNCTION insertpoint(lon numeric, lat numeric, name text, description text, category text, tablename text) TO publicuser;

Remember that the function is defined by its name and input arguments.

Calling the function

We could execute the function just by including it in a SELECT statement, providing the necessary parameters:

SELECT insertpoint(-4.565,33.294,'pointName','pointDescription','pointCategory','tableName');

that will produce this result:

{
  rows: [
    {
       insertpoint: 8
    }
  ],
  time: 0.173,
  fields: {
    insertpoint: {
      type: "number"
    }
  },
  total_rows: 1
}

A more orthodox way to call it, since the function returns a table would be:

SELECT * FROM insertpoint(-4.565,33.294,'pointName','pointDescription','pointCategory','tableName');

which produces:

{
  rows: [
    {
      cartodb_id: 9
    }
  ],
  time: 12.489,
  fields: {
    cartodb_id: {
      type: "number"
    }
  },
  total_rows: 1
}

Revoking permission / removing the function

If at some point we need to remove the function's privileges, we could run:

REVOKE EXECUTE ON FUNCTION insertpoint(lon numeric, lat numeric, name text, description text, category text, tablename text) TO publicuser;

For removing the function, we would run:

DROP FUNCTION insertpoint(lon numeric, lat numeric, name text, description text, category text, tablename text)
<!--
based on @iriberri's http://bl.ocks.org/iriberri/7d84ed35ef0b5e80555d
-->
<!DOCTYPE html>
<html>
<head>
<title>Point Collector | CARTO</title>
<meta name="viewport" content="initial-scale=1.0, user-scalable=no" />
<meta http-equiv="content-type" content="text/html; charset=UTF-8"/>
<link rel="shortcut icon" href="http://cartodb.com/assets/favicon.ico" />
<style>
html, body {
height: 100%;
padding: 0;
margin: 0;
}
#map {
height: 80%;
padding: 0;
margin: 0;
}
#input {
height:20%;
padding: 0;
margin: 0;
}
</style>
<link rel="stylesheet" href="http://libs.cartocdn.com/cartodb.js/v3/3.15/themes/css/cartodb.css" />
</head>
<body>
<div id="map"></div>
<div id="input">
<p>Point's data</p>
<p>
Latitude: <input id="lat" type="text" name="lat">
Longitude: <input id="lon" type="text" name="lon">
</p>
<p>
Name: <input id="name" type="text" name="name">
Description: <input id="desc" type="text" name="descripton">
Category: <input id="cat" type="text" name="category">
<input type="button" value="Send data" id="send" onclick="send();"/>
</p>
<!-- include cartodb.js library -->
<script src="http://libs.cartocdn.com/cartodb.js/v3/3.15/cartodb.js"></script>
<script>
var my_layer;
var tableName='point_collection';
function main() {
var lat;
var lon;
var name;
var desc;
var cat;
// create leaflet map
var map = L.map('map', {
zoomControl: false,
center: [43, 0],
zoom: 3
})
// add a base layer
L.tileLayer('http://{s}.basemaps.cartocdn.com/light_all/{z}/{x}/{y}.png',
{ attribution: '&copy; <a href="http://www.openstreetmap.org/copyright">OpenStreetMap</a> contributors, &copy; <a href="https://carto.com/attributions">CARTO</a>' }).addTo(map);
cartodb.createLayer(map, {
user_name: 'ernestomb',
type: 'cartodb',
sublayers: [{
sql: "SELECT * FROM point_collection",
cartocss: '#layer{ marker-fill-opacity: 0.9; marker-line-color: #FFF; marker-line-width: 1; marker-line-opacity: 1; marker-placement: point; marker-type: ellipse; marker-width: 10; marker-fill: #FF6600; marker-allow-overlap: true;}'
}]
})
.addTo(map)
.done(function(layer) {
my_layer = layer;
});
map.on('click', function(e) {
$("#lat").val(e.latlng.lat);
$("#lon").val(e.latlng.lng);
});
}
function send(){
lat = document.getElementById("lat").value;
lon = document.getElementById("lon").value;
name = document.getElementById("name").value;
desc = document.getElementById("desc").value;
cat = document.getElementById("cat").value;
var sql = new cartodb.SQL({ user: 'ernestomb' });
sql.execute("SELECT * FROM insertpoint("+lon+","+lat+",'"+name+"','"+desc+"','"+cat+"','"+tableName+"')");
my_layer.redraw();
}
// you could use $(window).load(main);
window.onload = main;
</script>
</body>
</html>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment