How can I make sure a function only SELECTs from one specific schema?

Question

What do I need to do to make sure that sandbox.execute_any_query() only SELECTs from tables in schema sandbox?

create function sandbox.execute_any_query(_query text) returns json as 
$$
declare
    _result json;
begin
    execute format('select row_to_json(t) from (%s) t', _query) into _result;
    return _result;
end
$$
language plpgsql;

Unrelated, but: the language name is an identifier. It shouldn't be quoted with single quotes. You should use `language plpgsql` instead — , Dec 07 '18 at 08:31

score 0 · Accepted Answer · answered Dec 07 '18 at 08:19

0

Mark the function as SECURITY DEFINER and make sure that the function owner does not have permissions on any schema other than sandbox.

Then the function will run in the user context of the owner, and any attempt to access other schemas will cause an error.

answered Dec 07 '18 at 08:19

Laurenz Albe

209,280
17
206
263

2

Adding a `set search_path = sandbox` to the function definition would also make sure that unqualified tables are used from the sandbox schema by default. Not really necessary but a convenience for the users calling the function. – Dec 07 '18 at 08:51
Thanks a lot, finally got it. – Dec 07 '18 at 12:19

How can I make sure a function only SELECTs from one specific schema?

1 Answers1