Way to limit amount of rows returned by broad queries through RLS

[gc-ft]

NOTE: After I wrote this post I adjusted the function and wrote a reply, so read below please for the new version of the function!

---

Now and then I read here in the discussions or over on reddit or the like that people are wondering if there is some way to make sure with RLS that a where clause on a certain column needs to be used, so that people can not simply dump all rows they have access to from a table through the API. This is usually looked for in cases where for example there are public profiles in a system and it is completely fine if people read a specific public profile but it is quite un-wanted that someone can just scrape all profiles with easy queries.

One way to limit this is of course lowering the amount of max rows that Supabase allows in query results (which defaults to 1000), however it is not REALLY a fix since people can just hammer the API with range requests (limit / offset queries) and still dump all public records.

So therefore people have asked often if there is some way to build a RLS policy which in essence forces a where clause on a certain column in order to return any useful data. I have come up with a somewhat hack to do just that while PostgREST has not implemented anything natively (and I believe it is not very likely this will happen soon since discussions around limiting the API have been around for a few years on the PostgREST GitHub boards and such).

The hack uses the fact that we can set arbitrary headers due to PostgREST for the API reply and can also check them whenever we want. So, first step is a simple Postgres function which checks and sets a specific header x-limited-response.

see function in second post

This results in the function returning true first time this function being run during a postgREST session and false every other time after that.

If you then use this policy:

create policy "Allow only a single row to be returned from some_table"
on "public"."some_table"
as permissive
for select
to authenticated
using (allow_single_row_only());

This effectively forces all queries on some_table to only return the FIRST result, regardless of any limit/offset added! Why? Because even for limit/offset queries RLS needs to be checked from the first row on, running the query with offset 1 would result in always an empty response, because the first row that should be "skipped" still has RLS run on it.

Note that this is the first version of this little hack, there are for sure some things that it does not cover and also running many queries or having multiple tables in a query that have this security will mess up the results in the current setup, because the first row of a second query run in the same session would be blocked already since the function would return FALSE.

Thoughts on this idea would be appreciated!

Fabian

[gc-ft]

Writing this post I decided to work a bit more on the function to make it more configurable and remove the limitation of multiple tables being involved... here is the new version, it takes two variables: a suffix which is added at the end of x-limited and a max_rows to allow setting the number of rows that can be returned to something else than the first. I changed the function name slightly to reflect the change of how it functions too.

Hope this will be interesting for some, but of course if someone sees a problem with this, do please let me know!

CREATE OR REPLACE FUNCTION public.allow_limited_rows_only(suffix text, max_rows integer)
RETURNS boolean
LANGUAGE plpgsql
AS $function$
DECLARE
    headers jsonb;
    current_value integer;
    key text;
    new_header jsonb;
BEGIN
    -- Get the current response headers as a JSONB array
    headers := current_setting('response.headers', true)::jsonb;

    -- Define the key of our tracking header based on the suffix
    key := 'x-limited' || suffix;

    -- Check if the key exists in the JSON array and get its current value
    SELECT (elem->>key)::integer INTO current_value
    FROM jsonb_array_elements(headers) AS elem
    WHERE elem ? key;

    IF current_value IS NOT NULL THEN
        -- Check if the current value has reached or exceeded the max_rows
        IF current_value >= max_rows THEN
            RETURN false;
        ELSE
            -- Increment the value
            current_value := current_value + 1;
            -- Build the new header with the incremented value
            new_header := jsonb_build_object(key, current_value::text);
            -- Replace the header in the headers array
            SELECT
              jsonb_agg(
                elem
              ) into headers
            FROM (select jsonb_array_elements(headers) as elem) as data where not elem ? key;
            IF headers IS NULL THEN
              headers := jsonb_build_array(new_header);
            ELSE 
              headers := headers || jsonb_build_array(new_header);
            END IF;
        END IF;
    ELSE
        -- Initialize the header with value 1 if it doesn't exist
        current_value := 1;
        new_header := jsonb_build_object(key, current_value::text);

        IF headers IS NULL THEN
            -- If headers is null, initialize it as a JSONB array with the new header
            headers := jsonb_build_array(new_header);
        ELSE
            -- Otherwise, append the new header to the existing array
            headers := headers || jsonb_build_array(new_header);
        END IF;
    END IF;

    -- Set the updated headers back to response.headers for the transaction
    PERFORM set_config('response.headers', headers::text, true);

    RETURN true;
END;
$function$;

[niwaa]

@gc-ft thanks for sharing! I was looking for this. However, it looks like this is failing:

{
  code: '22P02',
  details: 'The input string ended unexpectedly.',
  hint: null,
  message: 'invalid input syntax for type json'
}

It works sometimes after some page refresh. Any idea why?