Skip to content

Instantly share code, notes, and snippets.

@alasarr
Last active June 8, 2017 18:50
Show Gist options
  • Save alasarr/113c2bfd3a08ddabea539e9a737829ce to your computer and use it in GitHub Desktop.
Save alasarr/113c2bfd3a08ddabea539e9a737829ce to your computer and use it in GitHub Desktop.
HOW TO access to a private table in CARTO and get rid of NAMED MAPS.
---
-- HOW TO access to a private tabled in CARTO and get rid of NAMED MAPS.
-- It uses SECURITY DEFINER
-- tesla_supercharger is a privated table .
---
CREATE table user_tokens (token text, user_id integer, expiration timestamp);
INSERT INTO user_tokens VALUES ('eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJzdWIiOiIxMjM0NTY3ODkwIiwibmFtZSI6IkpvaG4gRG9lIiwiYWRtaW4iOnRydWV9.TJVA95OrM7E2cBab30RMHrHDcEfxjoYZgeFONFh7HgQ',1,now()+'10 years'::interval);
----------
--- OPTION 1: THIS IS TOO DANGEROUS. Please, try to avoid this method.
-----------
--DROP FUNCTION IF EXISTS sudo(access_token text, sql text);
CREATE FUNCTION sudo(access_token text, sql text) RETURNS setof record AS $$
DECLARE
BEGIN
-- check if token exists
IF EXISTS (SELECT 1 FROM user_tokens where access_token=token and expiration>now()) THEN
RETURN QUERY EXECUTE sql;
END IF;
END;
$$ LANGUAGE plpgsql
SECURITY DEFINER;
GRANT EXECUTE ON FUNCTION sudo(text,text) TO publicuser;
select * from sudo('eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJzdWIiOiIxMjM0NTY3ODkwIiwibmFtZSI6IkpvaG4gRG9lIiwiYWRtaW4iOnRydWV9.TJVA95OrM7E2cBab30RMHrHDcEfxjoYZgeFONFh7HgQ',
'select the_geom_webmercator from telsa_supercharger') as t(the_geom_webmercator geometry);
---
-- OPTION 2: recommended. A function per table, A function instead of a namedmaps.
---
DROP FUNCTION telsa_supercharger(access_token text, param1 integer);
CREATE FUNCTION telsa_supercharger(access_token text, param1 integer) RETURNS TABLE (the_geom_webmercator geometry) AS $$
DECLARE
BEGIN
-- check if token exists
IF EXISTS (SELECT 1 FROM user_tokens where access_token=token and expiration>now()) THEN
RETURN QUERY EXECUTE 'select the_geom_webmercator from telsa_supercharger';
END IF;
END;
$$ LANGUAGE plpgsql
SECURITY DEFINER;
GRANT EXECUTE ON FUNCTION telsa_supercharger(text,integer) TO publicuser;
select the_geom_webmercator from telsa_supercharger('eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJzdWIiOiIxMjM0NTY3ODkwIiwibmFtZSI6IkpvaG4gRG9lIiwiYWRtaW4iOnRydWV9.TJVA95OrM7E2cBab30RMHrHDcEfxjoYZgeFONFh7HgQ',
1);
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<title></title>
<link rel="stylesheet" href="http://libs.cartocdn.com/cartodb.js/v3/3.15/themes/css/cartodb.css" />
<script src="http://libs.cartocdn.com/cartodb.js/v3/3.15/cartodb.js"></script>
<style>
body { margin:0;position:fixed;top:0;right:0;bottom:0;left:0; }
#map {
height: 100%;
}
</style>
</head>
<body>
<div id="map"></div>
<script>
var map = new L.Map('map', {
center: [35.04798673426734,-100.283203125],
zoom: 4,
layers: [
L.tileLayer('https://cartodb-basemaps-{s}.global.ssl.fastly.net/{style}/{z}/{x}/{y}.png',
{ style: 'light_all', zIndex: 0 } )
]
});
var tmp = new Date().getTime();
// var sql_option1 =`select the_geom_webmercator from
// sudo('eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJzdWIiOiIxMjM0NTY3ODkwIiwibmFtZSI6IkpvaG4gRG9lIiwiYWRtaW4iOnRydWV9.TJVA95OrM7E2cBab30RMHrHDcEfxjoYZgeFONFh7HgQ','select the_geom_webmercator,''${tmp}''::text as nocache from telsa_supercharger') as t(the_geom_webmercator geometry, nocache text);`
var sql_option2 = `select the_geom_webmercator,'${tmp}'::text as nocache from telsa_supercharger('eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJzdWIiOiIxMjM0NTY3ODkwIiwibmFtZSI6IkpvaG4gRG9lIiwiYWRtaW4iOnRydWV9.TJVA95OrM7E2cBab30RMHrHDcEfxjoYZgeFONFh7HgQ',
1)`
cartodb.createLayer(map, {
user_name: 'alasarr',
type: 'cartodb',
sublayers: [{
sql: sql_option2,
cartocss: `#layer {marker-width: 7;
marker-fill: #27b0ff;
marker-fill-opacity: 0.9;
marker-allow-overlap: true;
marker-line-width: 1;
marker-line-color: #FFF;
marker-line-opacity: 1;
}`
}]
})
.addTo(map);
</script>
</body>
</html>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment