Background

I'm working these days on organizing the Postgres database of a project in my work.

EDIT: This database is connected to a NodeJS server that runs Postgraphile on it, in order to expose the GraphQL interface for the client. Therefore, I have to use RLS in order to forbid the user to query and manipulate rows that he/she doesn't have permission.

One of the tasks that I've got is to add a deleted field for each table, and using RLS to hide the records that deleted = true.

Code Example

To explain my problem, I'll add an SQL code for building a fake database:

Roles

For this example, I'll use these roles:

Superuser role named admin
Role called app_users
2 Users inherit from app_users:
- bob
- alice

CREATE ROLE admin WITH
  LOGIN
  SUPERUSER
  INHERIT
  CREATEDB
  CREATEROLE
  NOREPLICATION
  ENCRYPTED PASSWORD 'md5f6fdffe48c908deb0f4c3bd36c032e72'; -- password: admin

GRANT username TO admin;

CREATE ROLE app_users WITH
  NOLOGIN
  NOSUPERUSER
  NOINHERIT
  NOCREATEDB
  CREATEROLE
  NOREPLICATION;

CREATE ROLE bob WITH
  LOGIN
  NOSUPERUSER
  INHERIT
  NOCREATEDB
  NOCREATEROLE
  NOREPLICATION
  ENCRYPTED PASSWORD 'md5e8557d12f6551b2ddd26bbdd0395465c';

GRANT app_users TO bob;

CREATE ROLE alice WITH
  LOGIN
  NOSUPERUSER
  INHERIT
  NOCREATEDB
  NOCREATEROLE
  NOREPLICATION
  ENCRYPTED PASSWORD 'md5579e43b423b454623383471aeb85cd87';

GRANT app_users TO alice;

Database

This example will hold a database named league for a mock database for an American football league.

CREATE DATABASE league
    WITH 
    OWNER = admin
    ENCODING = 'UTF8'
    LC_COLLATE = 'en_US.utf8'
    LC_CTYPE = 'en_US.utf8'
    TABLESPACE = pg_default
    CONNECTION LIMIT = -1;

GRANT CREATE, CONNECT ON DATABASE league TO admin;
GRANT TEMPORARY ON DATABASE league TO admin WITH GRANT OPTION;

GRANT TEMPORARY, CONNECT ON DATABASE league TO PUBLIC;

Scheme: `public`

I've added some minor changes in the scheme, so in default, role app_users allow any command, type, execute function, etcetera.

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT ALL ON TABLES TO app_users;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT ALL ON SEQUENCES TO app_users;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT EXECUTE ON FUNCTIONS TO app_users;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT USAGE ON TYPES TO app_users;

Creating Tables

Table: TEAMS

CREATE TABLE IF NOT EXISTS public."TEAMS"
(
    id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    deleted boolean NOT NULL DEFAULT false,
    name text COLLATE pg_catalog."default" NOT NULL,
    owner text COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT "TEAMS_pkey" PRIMARY KEY (id)
)

TABLESPACE pg_default;

ALTER TABLE public."TEAMS"
    OWNER to admin;

ALTER TABLE public."TEAMS"
    ENABLE ROW LEVEL SECURITY;

GRANT ALL ON TABLE public."TEAMS" TO admin;
GRANT ALL ON TABLE public."TEAMS" TO app_users;

CREATE POLICY teams_deleted
    ON public."TEAMS"
    AS RESTRICTIVE
    FOR SELECT
    TO app_users
    USING (deleted = false);

CREATE POLICY teams_owner
    ON public."TEAMS"
    AS PERMISSIVE
    FOR ALL
    TO app_users
    USING (owner = CURRENT_USER);

Table: PLAYERS

CREATE TABLE IF NOT EXISTS public."PLAYERS"
(
    id text COLLATE pg_catalog."default" NOT NULL,
    deleted boolean NOT NULL DEFAULT false,
    first_name text COLLATE pg_catalog."default" NOT NULL,
    last_name text COLLATE pg_catalog."default" NOT NULL,
    team_id integer NOT NULL,
    jersey_number integer NOT NULL,
    CONSTRAINT "PLAYERS_pkey" PRIMARY KEY (id),
    CONSTRAINT fkey_team_id FOREIGN KEY (team_id)
        REFERENCES public."TEAMS" (id) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT check_player_number CHECK (jersey_number > 0 AND jersey_number < 100)
)

TABLESPACE pg_default;

ALTER TABLE public."PLAYERS"
    OWNER to admin;

ALTER TABLE public."PLAYERS"
    ENABLE ROW LEVEL SECURITY;

GRANT ALL ON TABLE public."PLAYERS" TO admin;
GRANT ALL ON TABLE public."PLAYERS" TO app_users;

CREATE POLICY players_deleted
    ON public."PLAYERS"
    AS RESTRICTIVE
    FOR SELECT
    TO app_users
    USING (deleted = false);

CREATE POLICY players_owner
    ON public."PLAYERS"
    AS PERMISSIVE
    FOR ALL
    TO app_users
    USING ((( SELECT "TEAMS".owner
   FROM "TEAMS"
  WHERE ("TEAMS".id = "PLAYERS".team_id)) = CURRENT_USER));

Test Case (Edited for better understanding)

Run this code using user bob:

INSERT INTO "TEAMS" (name, owner)
    VALUES ('Jerusalem Lions', 'bob')
    RETURNING id; -- We'll save this id for the next command

INSERT INTO "PLAYERS" (id, first_name, last_name, jersey_number, role, team_id)
    VALUES ('99999', 'Eric', 'Cohen', 29, 'linebacker', 888) -- Replace 888 with the returned id from the previous command
    RETURNING *;

-- These commands will work
SELECT * FROM "PLAYERS";

UPDATE "PLAYERS"
    SET last_name = 'Levi'
    WHERE id = '99999'
    RETURNING *; 

-- This is the command that won't work. I can't change the deleted. 
UPDATE "PLAYERS"
    SET deleted = true
    WHERE id = '99999'
    RETURNING *;

EDIT: Now, it's important to understand that The policies as defined above works when I do any query, as long as:

INSERT INTO doesn't include deleted = true (that's ok).
UPDATE that includes SET deleted = true. (This is the main issue).

I want to:

Allow bob to delete a record using deleted = true on an UPDATE command.
Hide in SELECT commands all records that deleted = true.