How do I restrict which fields and operators are available on a per table basis?

[zeevo]

Is there a way to tune the API to only allow filtering on certain fields and operators?

[laurenceisla]

You can manage access to certain fields by granting/revoking permissions from the columns, e.g. for a users table with columns: id,username,password, you need to remove the access to the whole table first then grant it to the columns.

-- grant select only on id,username to "admin" user
revoke all on users from admin;
grant select (id,username) on users to admin;

You can access those columns using select (password will give an error):

curl "localhost:3000/todos?select=id,username

On the other hand, there is no way to allow/block certain operators for now. This is discussed here: #2805

[zeevo]

by "it" do you mean PostgREST as a whole? How does one protect their APIs from being issued queries that will never finish due to no indexes being created on the field/operation?

In my team, we have massive tables that won't resolve queries in a timely manner without there being underlying indexes on the columns being filtered on. I'd like to limit the API to only allowing filters on columns with indexes. Otherwise, my application could be DDOSed by queries that will take too long to finish.

[laurenceisla]

by "it" do you mean PostgREST as a whole?

No, I meant the "protecting columns from unauthorized access" feature. But, now that you mention it, I think it could apply to your use case: not wanting any unauthorized user to use those columns in any way (e.g. to select, filter, order).

How does one protect their APIs from being issued queries that will never finish.

There's a PostgreSQL setting statement_timeout that can help you with that. It can be activated by user as mentioned in an example in the docs https://postgrest.org/en/latest/references/transactions.html?#impersonated-role-settings. The Connection Pool section https://postgrest.org/en/latest/references/connection_pool.html#connection-pool can also help you with performance.

I'd like to limit the API to only allowing filters on columns with indexes

Another option would be to use a PostgreSQL function that will receive the column values as parameters and then return a JSON value directly using only the columns that you want (you may need to use aggregates like json_agg to give structure to your response).

[zeevo]

statement_timeout definitely helps for protecting against unreasonable queries. I suppose a function would work but I think having out of the box support for configuring "Supported filterable fields" is still reasonable. And of course, these unsupported fields will still be exposed in the OpenAPI spec which will be confusing for users.

Maybe a config options?

db-<table>-explicit-fields = ["author", "title", "language"]

[zeevo]

Is this related to #2442 ?

[laurenceisla]

Is this related to #2442 ?

Yes, it's almost the same issue. I linked #2805 at the beginning which mentioned that issue inside. But yeah, it's more directly related to #2442.