Postgresql – whitelist for allowed user queries in postgres

postgresqlSecurity

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.

Best Answer

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

Related Question