I am trying to find an easy way to have private tables, yet exposing them to certain clients if they are authenticated.
This authentication can be achieved by a token system that allows the client to perform queries only if the token or the credentials are valid.
The approach here was to have a function with the authentication logic and whatever queries or operations you need to expose, and call that function from the client along with his credentials.
Problem with this is that you end up with a pg function for every operation, need to precisely define the input and output of the function, no polimorphy, so every time anything changes (like a data type) all functions need to be recreated. Not very flexible.
So another approach would be to have just one function that acts as a wrapper for whatever SQL you send it, checks credentials, sanitizes the sql and sends back the response.
CREATE OR REPLACE FUNCTION runSQL(thesql text,client text, hash text) RETURNS SETOF RECORD as $$
DECLARE
sql text;
expiry time;
client_info RECORD;
m varchar;
--here go the blacklisted strings
blacklist varchar[] := array['UPDATE ','INSERT ', 'ALTER ', 'DROP ', 'TRUNCATE', 'DELETE '];
BEGIN
expiry:=INTERVAL '1 hour';
sql:= 'SELECT * FROM auth WHERE client_id = $1 AND client_hash = $2 AND created_at > (NOW() - $3)';
EXECUTE sql using client, hash, expiry INTO client_info ;
-- check if it's a valid user and has an up to date token
IF client_info IS NULL THEN
RAISE EXCEPTION 'Authorization failed for client %', client;
END IF;
sql:= thesql;
-- sanitize the sql
FOREACH m IN ARRAY blacklist
LOOP
IF position(m in upper(thesql)) = 1 OR position(concat(' ', m) in upper(thesql))>0 THEN
RAISE EXCEPTION 'Not enough privilleges';
END IF;
END LOOP;
--send back results
RETURN QUERY EXECUTE sql ;
END;
$$ LANGUAGE plpgsql
SECURITY DEFINER;
And usage would be:
SELECT * FROM runSQL('SELECT cartodb_id, the_geom FROM sometable', 'client_id', 'token') as f(cartodb_id int, geom geometry)
My concern here is not opening a huge security hole in my database. Do you think it would be enough just to check for potentially dangerous strings in the query (like INSERT, DELETE, DROP, ALTER, GRANT, REVOKE...) or are there other security issues with this approach?
Any other ideas on how to have a SECURITY DEFINER function that only allows certain operations like read, or access to just some tables?
Thanks!
David
