How to allow SyncData for more complex access

[amitaibu]

Let's take the Blog post example, and assume we have an authenticated user and an admin user.
How could we define it so that editing/ deleting would be allowed for the post author and for any admin?

[mpscholten]

A policy set like this should implement the above rules:

CREATE POLICY "Everyone can see all posts" ON posts USING (true) WITH CHECK (false);
CREATE POLICY "Post owners can edit their posts" ON posts USING (true) WITH CHECK (user_id = ihp_user_id());
CREATE POLICY "Admins can edit all posts" ON posts USING (true) WITH CHECK (select is_admin from users where id = ihp_user_id() limit 1);

Check out https://www.postgresql.org/docs/current/sql-createpolicy.html to see what policies can do. It can pretty much cover most advanced use cases. (E.g. while right now not supported in the Schema Designer, you can also have a policy that only applies to e.g. UPDATE queries or only to DELETE queries)