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 DEFINERThe 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)