Can we enforce the maximum number of rows returned?

[Sjoerd82]

Is there a way to enforce a LIMIT/maximum of rows returned to the user?

For example, never return more than 100 rows, regardless of the user specifiying ?limit=500, or when the user is not specifying a limit at all?

There seems no way to implement this PostgreSQL-side. Triggers don't affect SELECT, and as far as I can tell, also the RLS policy can't LIMIT number of rows of a single SELECT. A view with a LIMIT clause applies the limit before filters are applied.

I have one particularly large materialized view ("mvw") containing hundreds of thousands of first- and last-names, together with some metadata. The user should get about 20 rows of randomized names, by specifying the type of names (eg. 20 french male first names).

My first try was to not allow access to the mvw, and create an accessible view with a random order and limit clause. However, this won't reliably work, because the filters come in after the limiting has already taken place. For example, this view:
CREATE OR REPLACE VIEW lvw_onomasticon AS SELECT * FROM mvw_onomasticon ORDER BY RANDOM() LIMIT 20;
When queried:
postgrest:3000/lvw_onomasticon?ind_first=eq.true&ind_male=eq.true
Will return less than 20 rows.

Then I thought I could go around this, by instead using a function that does the query. But it turns out that the user needs access to the, mvw even when it has execute access on the function. Surprisingly this was not required when using view-on-view.

Ok, so yhis might be a bit of an edge-case, but this "force-limiting" might still be a very useful addition to PostgREST. It can act as another layer of protection to prevent vacuuming whole databases. Esp. for SPA-applications, where the code runs largely client-side,

[laurenceisla]

Hi, I think the db-max-rows config parameter is what you're looking for?

[Sjoerd82]

Had totally overlooked that parameter. Unfortunatly can't be set per table, however this might not be a huge problem. Real quick, do the headers inform the user that only a partial result was returned in this case??

[Sjoerd82]

Yes it does. :-)