Using Data in permissions

[andrewalex]

when setting a permission, can I refer back to a field in the initial record or insert data?

Pseudo Code

insert $storeid, $bookid, $quantity into inventory 
where 
store.manager = X-Hasura-UserId  
and store.allowed_books contains $bookid

[rikinsk]

@andrewalex can you share the schema of the inventory and store tables to help figure out what exactly we need here

[rikinsk]

Seems like the _exists operator might be what we need here

[andrewalex]

Converting this to an issue #7095

[andrewalex]

The tables here were just an example, the real thing is more complicated to explain, but here is a schema that I think represents the challenge. If you can use _exists, that would be awesome

https://dbfiddle.uk/?rdbms=postgres_13&fiddle=2362f232e8284e94b93e5946a92a0fa3

CREATE TABLE books (
   id bigint generated always as IDENTITY PRIMARY KEY,
   name text
);


CREATE TABLE stores (
   id bigint generated always as IDENTITY PRIMARY KEY,
   name text,
   managerid bigint
);


CREATE TABLE allowed_books (
   storeid bigint REFERENCES stores(id),
   bookid bigint REFERENCES books(id)
);

CREATE TABLE inventory (
   id bigint generated always as IDENTITY PRIMARY KEY,
   storeid bigint REFERENCES stores(id),
   bookid bigint REFERENCES books(id),
   quantity int
);

Essentially only the manager of this specific store can insert into inventory (Role is not suitable for this, but its easy to do), and can only insert books listed in allowed_books .. something like WHERE bookid in (SELECT bookid from allowed_books where storeid= $storeid)

The same filtering could be useful for selects as well.