0

I would like to grant a Postgres user access to a public_data view and allow them to execute user defined SQL queries against it.

However, the view is quite large (5M rows), so I'd like to prevent users from executing queries that could accidentally return huge results sets.

CREATE VIEW public_data AS SELECT * FROM data limit 10; only seems to give me the first 10 results no matter what query I run against public_data.

Ideally, I'd like users to be able to run SELECT * FROM public_data WHERE ...; but somehow have LIMIT 10 automatically included prior to executing the statement.

Is there any way to do this without creating a custom function?

0

Alas, no. You cannot parameterize views like that, you need a function to do it.

I rather hope someone proves me wrong, as I much prefer views to set-returning functions. But I doubt they will.

  • Is there a safe way with a function to allow any select query, but limit the results to 10 max and not risk a SQL injection? – Andy Brewer Mar 28 at 5:27
0

You can do something that may even be better than limiting rows. Set statement_timeout for the user they connect as and any query that runs for longer than that will be aborted.

https://www.postgresql.org/docs/11/runtime-config-client.html

0

psql has a FETCH_COUNT variable to almost-transparently handle query results with cursors. The first results tend to be loaded and displayed quickly, as opposed to the whole resultset being built in memory, and the user can simply interrupt the fetching with Ctrl+C when there are too many results.

From the doc:

FETCH_COUNT

If this variable is set to an integer value greater than zero, the results of SELECT queries are fetched and displayed in groups of that many rows, rather than the default behavior of collecting the entire result set before display. Therefore only a limited amount of memory is used, regardless of the size of the result set. Settings of 100 to 1000 are commonly used when enabling this feature. Keep in mind that when using this feature, a query might fail after having already displayed some rows.

Related:

Memory usage on select for large number of rows

How to handle large result sets with psql? on stackoverflow.

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.