[HACKERS] Row Level Security Bug ? - Mailing list pgsql-hackers

CREATE DATABASE test

  WITH OWNER = postgres

       ENCODING = 'UTF8'

       TABLESPACE = pg_default

       CONNECTION LIMIT = -1;

CREATE SEQUENCE public.pk_seq

  INCREMENT 1

  MINVALUE 1

  MAXVALUE 9223372036854775807

  START 736220

  CACHE 1;

CREATE TABLE public.schools

(

  school bigint NOT NULL DEFAULT nextval('pk_seq'::regclass), -- Uniquely identifies the table row

  description character varying(160) NOT NULL, -- Description for the school

  processing_code character varying(160) NOT NULL, -- A code that identify the school on the government information system

  mnemonic character varying(30) NOT NULL, -- Short description to be use as code

  example boolean NOT NULL DEFAULT false, -- It indicates that the data have been inserted to be an example of the use of the data base

  behavior bigint, -- Indicates the subject used for the behavior

  CONSTRAINT schools_pk PRIMARY KEY (school),

  CONSTRAINT schools_uq_description UNIQUE (description),

  CONSTRAINT schools_uq_mnemonic UNIQUE (mnemonic),

  CONSTRAINT schools_uq_processing_code UNIQUE (processing_code, example)

);

-- Index: public.schools_fk_behavior

CREATE INDEX schools_fk_behavior

  ON public.schools

  USING btree

  (behavior);

CREATE TABLE public.usenames_schools

(

  usename_school bigint NOT NULL DEFAULT nextval('pk_seq'::regclass), -- Unique identification code for the row

  usename name NOT NULL, -- The session's usename

  school bigint NOT NULL, -- School enabled for the the usename

  CONSTRAINT usenames_schools_pk PRIMARY KEY (usename_school),

  CONSTRAINT usenames_schools_fk_school FOREIGN KEY (school)

      REFERENCES public.schools (school) MATCH SIMPLE

      ON UPDATE CASCADE ON DELETE CASCADE,

  CONSTRAINT usenames_schools_uq_usename_school UNIQUE (usename, school) -- Foe every usename one school can be enabled only one time

);

-- Index: public.usenames_schools_fx_school

CREATE INDEX usenames_schools_fx_school

  ON public.usenames_schools

  USING btree

  (school);

  CREATE OR REPLACE VIEW public._rls_test AS 

 SELECT schools.school,

    schools.description,

    schools.example

   FROM schools;

   

   

CREATE OR REPLACE VIEW public._rls_test_security_barrier WITH (security_barrier=true) AS 

 SELECT schools.school,

    schools.description,

    schools.example

   FROM schools;

CREATE OR REPLACE VIEW public._rls_test_with_check_local WITH (check_option=local) AS 

 SELECT schools.school,

    schools.description,

    schools.example

   FROM schools;

CREATE OR REPLACE VIEW public._rls_test_with_check_local_cascade WITH (check_option=cascaded) AS 

 SELECT schools.school,

    schools.description,

    schools.example

   FROM schools;  

  

-- now same data 

-- now same data 

-- now same data 

INSERT INTO public.schools(school,description,processing_code,mnemonic,example) VALUES ('28961000000000','Istituto comprensivo "Voyager"','ZZIC00001Z','IC VOYAGER','t');

INSERT INTO public.schools(school,description,processing_code,mnemonic,example) VALUES ('2000000000','Istituto Tecnico Tecnologico "Leonardo da Vinci"','ZZITT0000Z','ITT DAVINCI','t');

INSERT INTO public.schools(school,description,processing_code,mnemonic,example) VALUES ('1000000000','Istituto comprensivo ''Andromeda''','ZZIC80000Z','IC ANDROMEDA','t'); 

INSERT INTO public.usenames_schools(usename_school,usename,school) VALUES ('726633000000000','manager-a@scuola-1.it','1000000000');

-- THEN ENABLE ROW LEVEL SECURITY

-- THEN ENABLE ROW LEVEL SECURITY

-- THEN ENABLE ROW LEVEL SECURITY

  

  ALTER TABLE usenames_schools ENABLE ROW LEVEL SECURITY;

 

 ALTER TABLE schools ENABLE ROW LEVEL SECURITY;

  

CREATE POLICY usenames_schools_pl_usename ON usenames_schools TO public 

 USING (usename = current_user)

  WITH CHECK (usename = current_user);

CREATE POLICY schools_pl_school ON schools TO public 

 USING (school IN (SELECT school FROM usenames_schools))

  WITH CHECK (school IN (SELECT school FROM usenames_schools));