search

supabase / supautils

PostgreSQL extension that secures a cluster on a cloud environment
https://supabase.github.io/supautils
Apache License 2.0
59 stars 13 forks source link

Max values for settings #82

Open steve-chavez opened 9 months ago

steve-chavez commented 9 months ago

Problem

Found an user doing the following for an API role:

alter role service_role set statement_timeout = '15min';

Which makes no sense because the API roles should are bounded by HTTP timeouts. Cloudflare for example enforces a 100 second timeout.

Proposal 1

Enforce max values for role settings. For this case we could do:

supautils.role_max_settings = '{"service_role": { "statement_timeout": { "max": "100s"}}}'

If the user surpasses the setting then we'd fail and show an error message.

Proposal 2

Not sure if minimum values would make sense. But if so maybe we could use a pg range to keep the config shorter:

supautils.role_settings_bounds = '{"statement_timeout": { "service_role": "[5,100)", "anon": "[5,30)" }'
soedirgo commented 8 months ago

I think this is better handled on the backend/infra side similar to RDS, since we might want to make the limits dynamic wrt instance size etc.

Role settings might be a bit tricky though.

steve-chavez commented 8 months ago

@soedirgo We already query resources for https://github.com/supabase/supautils?tab=readme-ov-file#constrained-extensions. We could do the same for this. 

supautils.settings_bounds = '{"statement_timeout": { "max": { "mem": ["(100,1G)", "(200, 2G)"]} }'

Not sure if it would make sense for statement_timeout though but maybe for work_mem?

Also, it looks it would be another feature, so it can be done later.