Hello, i have a db with a couple of tables (enclosed the script to recreate it, please have a look before to proceed) i enabled the row level security and all seem to work fine
if i do it (connected in as superuser like, usualy, postgres is): select school, description, example from schools i can see all the rows if i do: SET ROLE 'manage...@scuola-1.it' select school, description, example from school i see only one row (as expected) but when i do: select * from _rls_test select * FROM _rls_test_security_barrier select * from _rls_test_with_check_local select * from _rls_test_with_check_local_cascade I see all the rows always this way i lack all the row level security i defined is this either a bug or it's made by design ? if it's made by design why ? Is there a way to write view that respect the row level security ? For my point of view is a nonsense make a row level security that doesn't work with the view. Thanks to all the spend time to answer me. here: https://github.com/scuola247/postgresql you can have a look at the complete database Andrea Adami =============================================== =============================================== =============================================== 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','manage...@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));
