2

Looking for a way to limited a user to a specific set of queries (i.e. a whitelist).

I currently need this as an extra security measure for a client app.

Is there a way to enable a table of "user allowed queries"? Anything that doesn't meet the regex pattern in that table (or however it works) receives a FATAL error.

UPDATE for clarity:

The client app only has 50 or so queries that ever execute, over and over again. So the list of expected queries is known. The only thing that changes between them is some values for id attribute in the WHERE clause.

For instance, I count these two select statements as 1 of the "50" (i.e. it's the same query, just the x value is different):

SELECT a, b, c
FROM table_a
WHERE x = 1;

The next query may be:

SELECT a, b, c
FROM table_a
WHERE x = 2;

What I'd ideally like to enforce is that all queries against the database should include a particular condition in the WHERE clause, i.e. organisation_id = x where the value of x would change depending on the user running the query.. but organisation_id exists in every table, and every query should contain this clause.

  • What kind of queries? Do you know how to use views? You can limit actions on views. – Mladen Uzelac Jul 3 '17 at 10:43
  • I've just updated the question to provide clarity around that. – Turgs Jul 3 '17 at 12:33
  • I would simply create a set of views for the allowed queries and only grant that user select privileges on those views – a_horse_with_no_name Jul 3 '17 at 12:55
  • Did you check row level security? It depends on the user – Mladen Uzelac Jul 3 '17 at 19:08
2

Looking for a way to limited a user to a specific set of queries (i.e. a whitelist).

Wrap all of your queries in a plpgsql function that takes a SQL command and executes dynamic SQL.

SELECT * FROM mystupidwrapper('SELECT * FROM foo');

It will have to return a specific type, SETOF jsonb sounds great. You can write mystupidwrapper in perl, or plv8 and it'll even be trusted. If you want to write it in plperl, you can even use SQL::Statement::Structure to do basic SQL Statement processing. That said, SQL is a query language it provides READ permissions on both table and columns but the goal is still a declarative language that provides the ability to query abstractly.

Perhaps you want something more like pg-promise which natively supports a QueryFile

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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