Am I right to think this could be used to "inject" limits on the number of rows returned by a user query, or otherwise restrict what users see allowed to do?

I know it sounds silly/crazy but I have a use case where I would like to allow "mostly" trusted users to access the database directly and not through an API, but I need to apply some restrictions on their output.

It can but it's not the primary goal at the moment. If you want to restrict the number of rows returned, you can rewrite the query to add a LIMIT clause. To control which rows your users can see, you can use row-level security.

One thing I was thinking of doing is generating query plans asynchronously and blocking/cancelling queries that would otherwise be expensive and cause downtime. That's on the roadmap.