This repository was archived by the owner on Feb 24, 2024. It is now read-only.
This repository was archived by the owner on Feb 24, 2024. It is now read-only.
Expand on Schema Isolation #748
Open
Description
Conceptually, this makes perfect sense, but what is the best way to go about implementing this in practice? Views are a new concept to me.
...
Any further guidance or resources would be much appreciated, thank you!
A random collection of tips:
- Use at least PostgreSQL 15 and always use the
security_invoker
option for your views. Otherwise it will be really annoying to implement Row Level Security at some point. I use the following pgtap test to make sure we never miss any of the views:RETURN NEXT is_empty( $$ SELECT relnamespace::regnamespace, relname FROM pg_class WHERE relkind='v' AND relnamespace::regnamespace NOT IN ('pg_catalog', 'information_schema', 'pgtap') AND NOT ('security_invoker=true' = ANY (COALESCE(reloptions, '{}'))); $$, 'no views without security invoker' );
- See this comment for why I believe you need a data schema and three schemas for your api (exposed, extra, api).
- Don't implement access control at the api layer. Use RLS on your tables.
- Since you will surely want to use resource embedding, make sure you understand how PostgREST can infer Foreign Key relationships on your base tables "through" views. You will need to select all columns of such a relationship in both sides respectively, so that we can infer the FK connecting those two views.
- Understand when your views are automatically updateable and when you need to create INSTEAD OF triggers.
- IIRC, one limitation is that
ON CONFLICT
/ conflict resolution will only work on automatically updateable views - but not anymore once you have added any INSTEAD OF trigger.
Originally posted on PostgREST/postgrest#3188 (comment)