How to safely manage normalized permissions in Postgres with RLS

I like using a separate table to manage RLS permissions, but normalization creates a challenge when inserting new data. The general idea is that no access control logic is built into the items table, (such as with an owner_id column), it all comes from an adjacent item_permissions table that allows for multiple owners, and even multiple role types if we'd like. The general problem is that we need to ensure permissions are automatically created alongside new items, and we don't have a big window for setup if a user runs an insert ... returning.

Consider the following tables:

create extension if not exists "uuid-ossp";

create table items (
  id uuid not null primary key
);
alter table items enable row level security;

create table item_permissions (
  id uuid not null primary key,
  item_id uuid references items(id) on delete cascade,
  permitted_id uuid not null
);
alter table item_permissions enable row level security;

We could build a lot more into those permissions, such as adding a role type, but this is sufficient to reproduce the crux of the problem.

When a new item is inserted, we'll also insert a permission:

create or replace function insert_permission()
  returns trigger
  as $$
begin
  insert into item_permissions (item_id, permitted_id) values (
    new.id,
    auth.uid()
  );
  return new;
end
$$ language plpgsql;

create trigger insert_permission_trigger
after insert
on items
for each row
execute procedure insert_permission();

Here are the policies I'm working with:

-- the big, important policy that handles our item access control
create policy manage_item
on items
for all
using (
  exists(
    select item_id
    from item_permissions
    where items.id = item_id
    and permitted_id = current_setting('user_id')::uuid
  )
)
with check (
  exists(
    select item_id
    from item_permissions
    where items.id = item_id
    and permitted_id = current_setting('user_id')::uuid
  )
);

-- so that anyone can insert new items
create policy insert_items
on items
for insert
with check (true);

-- allows users to access and delete their own permissions
create policy manage_permissions
on item_permissions
for all
using (
  permitted_id = current_setting('user_id')::uuid
)
with check (
  permitted_id = current_setting('user_id')::uuid
);

The problem is when a user uses insert ... returning to create a new item: since insert_permission_trigger runs after insert there is no permission yet and the manage_item policy fails. To fix this we can add a special case policy:

create policy return_new_item
on items
for select
using (
  not exists(
    select item_id
    from item_permissions
    where item_id = items.id
  )
);

This fixes the problem, but I'm worried it might cause a leak since anyone might be able to leverage this policy if they run a select before the trigger completes. And that is the question. Are triggers run atomically such that there isn't a window for Bob to come along and select Alice's new item before her permission record is created? Does this seem like a reasonable approach or am I missing something?

Everything that occurs during the insert, including the trigger actions, is still within a single transaction and is not visible to any queries outside of that transaction. Therefore, there isn't an opportunity for anyone else to select the newly inserted rows until after the transaction has been committed.

The insert_permissions trigger can also be implemented using BEFORE INSERT if the foreign key constraint is defined as DEFERRABLE INITIALLY DEFERRED.

Using an external table to record ownership is a reasonable approach. It can be argued that neither ownership nor access are intrinsic attributes of the the concept represented by items and thus they shouldn't be part of an item's definition.