0

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:

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

I want to:

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

What should I do? 🤷‍♂️

Improve this question
6
  • 1
    RLS seems like overkill. Why not just provide access via a simple VIEW that does not specify WITH CHECK OPTION? Commented Sep 30, 2021 at 23:39
  • Your policy players_owner will cause an error, because the USING expression does not return a boolean. Commented Oct 1, 2021 at 4:18
  • 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. Commented Oct 1, 2021 at 6:20
  • 2. The condition is OK and it's working properly for any command that doesn't change deleted to true. Commented Oct 1, 2021 at 6:21
  • "The last command will fail" because of what? Which RLS definition is offended? Commented Oct 1, 2021 at 12:53

4 Answers 4

Reset to default
4

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

Here's the problem: Permissive works like OR condition, and I don't have just bob as a user, I have Alice as well, and they are inherit their permission from a role named app_users. When I'm writing down those policies, I must to write TO app_users and not TO bob.
There is no problem, as I have used user_name = current_user, the example will work for app_users as well (I've edited the answer respectively). You did not catch the main issue. You can (and should) define all you need only with permissive policies because you cannot override a restrictive policy.
I've tried the combination of those 2 permissive policies. Bob is getting records with deleted = true. Doesn't achieve what I'm searching for. I don't want to get records with deleted = true, but I do want to allow the user bob (or any user in app_users) to update the value of the field deleted.
Ok, just add the condition to the policy like in the updated answer.
As you may have noticed, playing "this doesn't work" and solving puzzles don't work well. Ask a new question with the simplest possible example and describe exactly what you want to achieve.
|
0

What I can gather is, that

UPDATE "PLAYERS" SET deleted=true

will not fail for "Bob". But:

UPDATE "PLAYERS" SET deleted=true RETURNING *

will. Postgraphile will usually "return" something when doing mutations. After updating the row - you basically forbid the select statement.

The usual hack (feature) in Postgraphile land is usually creating a custom mutation and define it as security definer.

Maybe there are other ways around this - but this is the hammer->nail approach in Postgraphile, in my experience.

Improve this answer

2 Comments

1. That SELECT after the mutation, because of how Postgrapile works, it's a good point, and I'll be noticed by that. 2. When I'm doing UPDATE public."PLAYERS" SET deleted = true WHERE id = '11111'; in the Postgres Shell, it fails, so the bug is not related just for Postgraphile, it's also on the SQL itself.
That's correct @BarBokovza . I've encountered this and asked a question about it. I now think that creating this additional function with security definer to do the update, is the solution to the problem I stated. stackoverflow.com/questions/74862224/…
0

Try to create function with cursor instead of UPDATE ... WHERE. It seems that this method can bypass select policy for newly updated rows.

CREATE OR REPLACE FUNCTION update_player_deleted(n text, status boolean) RETURNS void AS $$
        DECLARE
            cur refcursor;
        BEGIN
            OPEN cur FOR SELECT * FROM players WHERE id = n FOR UPDATE;
            LOOP
                MOVE cur;
                EXIT WHEN NOT FOUND;
                UPDATE players SET deleted = status WHERE CURRENT OF cur;
            END LOOP;
            CLOSE cur;
        END;
$$ LANGUAGE plpgsql;

Usage:

 SELECT update_player_deleted('99999', true);
Improve this answer

Comments

-1

Similar problem can be seen here: Unable to update row of table with Row Level Security where I proposed a workaround by implementing a grace period:

  • instead of deleted have a deleted_at column and on deletion store the current timestamp in it (i.e. timestamp when the deletion happened)
  • in the SELECT policy check if deleted_at is NULL or the time between deleted_at and now is less than 1 second
  USING (
        teams_deleted.deleted_at IS NULL
        OR
        ABS(EXTRACT(EPOCH FROM (now() - teams_deleted.deleted_at))) < 1
  )
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.