Question about schema isolation

[taphill]

Hello all! I'm new to postgrest and also trying to learn more about sql beyond the basic crud operations.

There is a section in the docs here about schema isolation that I am really interested in learning more about, but there is not much information in there at the moment.

Per the docs:

It is recommended that you don’t expose tables on your API schema. Instead expose views and stored procedures which insulate the internal details from the outside world. This allows you to change the internals of your schema and maintain backwards compatibility. It also keeps your code easier to refactor, and provides a natural way to do API versioning.

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.

The following seemed to work fine for me:

I created two schemas

create schema core;
create schema api;

Then created a simple table

create table core.users (id serial primary key, email text);

Then created this view

create view api.users as select * from core.users;

Finally, created a role

create role web_anon nologin;
grant usage on schema api to web_anon;
grant select on api.users to web_anon;

I was able to call the /users route and get all the rows, and was able to use all the filtering options I tried as well, but just wanted to confirm that I'm on the right path. Is this the preferred way to create a view which insulates my table? Are there any gotchas I should be aware of? What are are some of the best practices for setting up this type of architecture?

Any further guidance or resources would be much appreciated, thank you!

[wolfgangwalther]

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.

[taphill]

Thank you @wolfgangwalther!

I may have more questions as I dig in to all of these things, but this is a super helpful starting point for me. I appreciate it!