Postgres Row Level Security doesn't allow update on restrictive select policy

Question

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.

What should I do? ????‍♂️

RLS seems like overkill. Why not just provide access via a simple VIEW that does not specify WITH CHECK OPTION? — Fred
Your policy players_owner will cause an error, because the USING expression does not return a boolean. — Laurenz Albe
1. The reason why I'm using RLS is because this DB will be exposed via Postgraphile, so I don't want to allow users to get all the data of the table. — Bar Bokovza
2. The condition is OK and it's working properly for any command that doesn't change deleted to true. — Bar Bokovza
"The last command will fail" because of what? Which RLS definition is offended? — madflow

klin klin · Accepted Answer · 2021-10-02T11:26:22

From the documentation:

When a mix of permissive and restrictive policies are present, a record is only accessible if at least one of the permissive policies passes, in addition to all the restrictive policies.

It means that you cannot permit in one policy something restricted in another policy. For this reason, restrictive policies should be used with extreme caution. When there is no policy defined, everything is restricted, so you should focus on permitting what should be allowed.

Simplified example:

create table my_table(
    id int primary key, 
    user_name text, 
    deleted bool);
    
alter table my_table enable row level security;

create policy rule_for_select
    on my_table
    as permissive
    for select
    to app_users
    using (not deleted);

create policy rule_for_all
    on my_table
    as permissive
    for all
    to app_users
    using (user_name = current_user and not deleted);

insert into my_table(id, user_name, deleted) values
(1, 'alice', false),
(2, 'bob', true),
(3, 'celine', true)

The user bob will see row 1. He would be able to update row 2 if deleted is false.