Postgres RLS & RBAC don't behave as expected when combined with group inheritance...

postgres_rls_rbac_inheritance_weirdness.sql

DROP TABLE IF EXISTS post CASCADE;

DROP ROLE IF EXISTS author;

DROP ROLE IF EXISTS visitor;

DROP ROLE IF EXISTS basic;


-- Create roles

CREATE ROLE basic inherit;

CREATE ROLE author noinherit;

CREATE ROLE visitor noinherit;

GRANT author TO basic;

GRANT visitor TO basic;

-- Create one table

CREATE TABLE IF NOT EXISTS post (
  id serial PRIMARY KEY,
  author_id integer NOT NULL,
  public_name text,
  private_info text -- This column should only be visible to the author of the post
);

--

GRANT SELECT (id, author_id, public_name) ON TABLE post TO visitor;

CREATE POLICY visitor_can_select_post ON post AS permissive
  FOR SELECT TO visitor
    USING (TRUE);

GRANT INSERT ON TABLE post TO author;

CREATE POLICY author_can_insert_post ON post AS permissive
  FOR INSERT TO author
    WITH CHECK (author_id = current_setting('jwt.user_id', TRUE)::int);

GRANT SELECT (id, author_id, public_name, private_info) ON TABLE post TO author;

CREATE POLICY author_can_select_post ON post AS permissive
  FOR SELECT TO author
    USING (author_id = current_setting('jwt.user_id', TRUE)::int);

GRANT UPDATE (author_id, public_name, private_info) ON TABLE post TO author;

CREATE POLICY author_can_update_post ON post AS permissive
  FOR UPDATE TO author
    WITH CHECK (author_id = current_setting('jwt.user_id', TRUE)::int);

ALTER TABLE post ENABLE ROW LEVEL SECURITY;

INSERT INTO post (author_id, public_name, private_info)
  VALUES (1, 'toto', 'private_to_1_toto'), (1, 'tutu', 'private_to_1_tutu'), (1, 'titi', 'private_to_1_titi'), (2, 'momo', 'private_to_2_momo'), (2, 'mumu', 'private_to_2_mumu'), (2, 'mimi', 'private_to_2_mimi');

-- Test
SET local ROLE basic;

SET local jwt.user_id TO 1;

UPDATE
  post
SET
  private_info = 'bad'
WHERE
  id = 4;

UPDATE
  post
SET
  private_info = 'good'
WHERE
  id = 3;

-- Problem: The table here shows private_info of rows that the current user should not be able to access

SELECT
  id,
  author_id,
  public_name,
  private_info
FROM
  post;