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 $$
sql text;
expiry time;
client_info RECORD;
m   varchar;
--here go the blacklisted strings
blacklist varchar[] := array['UPDATE ','INSERT ', 'ALTER ', 'DROP ', 'TRUNCATE', 'DELETE '];
    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
            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
$$ LANGUAGE plpgsql

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?



1 Answer 1


I've always said that the power of the security definer is that you can use the enforced datatypes in the plpgsql to help ensure that you aren't getting SQL injection. Not totally certain I would trust a blacklist like that. You also open up all tables to the user with tokens, I'm not sure that is your intended goal.

Why not just return the result of a SELECT * statement and let them run the filters outside of your function? Then, you could just pass in a tablename string and make sure that the tablename isn't anything malicious. I would do that by just hardcoding in an array of allowed tablenames to check it against and fail if not.

  • Thanks Andrew, that's a much better approach for sure. Just not so performant when you have to do table joins, but you cal allways do the join at func level. Thanks! Commented Feb 4, 2014 at 22:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.