Last active
June 8, 2017 18:50
-
-
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.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
--- | |
-- 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); |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<!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