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
('289610','Istituto comprensivo "Voyager"','ZZIC1Z','IC
VOYAGER','t');
INSERT INTO
public.schools(school,description,processing_code,mnemonic,example) VALUES
('20','Istituto Tecnico Tecnologico "Leonardo da
Vinci"','ZZITTZ','ITT DAVINCI','t');
INSERT INTO
public.schools(school,description,processing_code,mnemonic,example) VALUES
('10','Istituto comprensivo ''Andromeda''','ZZIC8Z','IC
ANDROMEDA','t');
INSERT INTO public.usenames_schools(usename_school,usename,school) VALUES
('7266330','manage...@scuola-1.it','10');
-- THEN ENABLE ROW LEVEL SECURITY
-- THEN ENABLE ROW LEVEL SECURITY
-- THEN ENABLE ROW LEVEL SECURITY
ALTER TABLE
