Expand on Schema Isolation

[steve-chavez]

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 https://github.com/PostgREST/postgrest/discussions/3188#discussioncomment-8253909

[laurenceisla]

Maybe an already created database could be used as a starting point to explain the Schema Isolation (it kinda already is with the current graphic). Related to this discussion: https://github.com/PostgREST/postgrest/discussions/3201?converting=1

[steve-chavez]

Maybe pagila or other sample db could be used.