Проверка доступа к записи?

Question

[Богдан]

Здравствуйте. Подскажите пожалуйста. Как правильно решать такой кейс:
1) есть табличка с пользователями, но пользователи могут принадлежать другим пользователям

CREATE TABLE public."Users" (
	id serial NOT NULL,
	"parentId" int4 NULL,
	email varchar(100) NOT NULL DEFAULT ''::character varying,
       CONSTRAINT "Users_email_key" UNIQUE (email),
	CONSTRAINT "Users_pkey" PRIMARY KEY (id),
	CONSTRAINT "Users_parentId_fkey" FOREIGN KEY ("parentId") REFERENCES "Users"(id) ON DELETE CASCADE,
);

2) Есть таблица "Cartridges" которая имеет связь с таблицей пользователей по полю "userId".
Редактировать, просматривать запись может только или непосредственно сам пользователь или его родитель

CREATE TABLE public."Cartridges" (
	id serial NOT NULL,
	code varchar(9) NOT NULL DEFAULT ''::character varying,
	printed int4 NOT NULL DEFAULT 0,
	"userId" int4 NULL,
	CONSTRAINT "Cartridges_pkey" PRIMARY KEY (id),
	CONSTRAINT "Cartridges_userId_fkey" FOREIGN KEY ("userId") REFERENCES "Users"(id) ON DELETE CASCADE
);

Делаю через добавление CTE к каждому запросу, который рекурсивно создает табличку с пользователем и его родителем, а далее уже соединением проверяем есть ли он там.

WITH RECURSIVE "UsersTree" AS (
  SELECT
      id,
      email,
      "parentId"
    FROM "Users"
        WHERE id = 4

      UNION ALL

      SELECT
          "Users".id,
      "Users".email,
      "Users"."parentId"
    FROM "Users"
    INNER JOIN "UsersTree" ON "Users"."parentId" = "UsersTree".id
)
update "Cartridges" set printed = 5 
  from "UsersTree" where "Cartridges".id = 2 and "UsersTree".id = "Cartridges"."userId"
  returning *

Но как-то громоздкая выходит конструкция, которую нужно добавлять к каждому запросу. Как в таких случаях деают. Спасибо

Comments

[Сергей c0re]

1. а на уровне интерфейса не показывать чужие картриджи? так и обновлять не будет?

2. можно на триггере.
добавить в таблицу поле, типа updatedByUserId
В триггере соответственно проверять может ли этот updatedByUserId обновлять запись, если не может кидать эксепшн.

а обновлять просто

update "Cartridges"
    set printed = 5 , updatedByUserId = 4
  where "Cartridges".id = 2

[Богдан]

Сергей, спасибо,
1) а на уровне интерфейса это как?

2) триггер это конечно неплохая идея, но когда например нужно отобразить записи (банальный SELECT) он уже работать не будет.

WITH RECURSIVE "UsersTree" AS (
  SELECT
      id,
      email,
      "parentId"
    FROM "Users"
        WHERE id = 3

      UNION ALL

      SELECT
          "Users".id,
      "Users".email,
      "Users"."parentId"
    FROM "Users"
    INNER JOIN "UsersTree" ON "Users"."parentId" = "UsersTree".id
)
select * from "Cartridges" inner join "UsersTree" ON "Cartridges"."userId" = "UsersTree".id

[Сергей c0re]

Богдан,
1. ну у вас же какое-то приложение или пользователя хардкорят на SQL апдейтами ? )
просто в интерфейс приложения НЕ выдавать "чужие" записи, соответственно и изменить он их не сможет.

2. ну... попробуйте использовать вьюху

[Богдан]

Сергей,
Спасибо.
1) Ну то что в интерфейсе не будет записи не значит что через curl, postman нельзя их вставить. Поэтому как бы ради секюрности нужно как-то проверять.

2) Не понял как вьюха мне поможет, можете логику подсказать?

[Сергей c0re]

Богдан,

Но как-то громоздкая выходит конструкция

будет решен вопрос с громоздкостью конструкции ))
не надо цеплять WITH ......... к каждому запросу, а просто джойнить уже описанную вьюху.