Re: [HACKERS] Row Level Security Bug ?

As suggested from Joe i changed the db definition as enclosed.
Now when i do:

select * fom schools

all works fine

but when i do:

select * from rls_test

select * from _rls_test_security_barrier

select * from public._rls_test_with_check_local

select * from _rls_test_with_check_local_cascade

always i get the error:

permission denied for relation schools
SQL state: 42501

Someone has some suggestion ?

Best regards
Andrea Adami

--
-- PostgreSQL database dump
--

-- Dumped from database version 9.6.4
-- Dumped by pg_dump version 10.0

-- Started on 2017-11-14 05:51:56

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET row_security = off;

CREATE ROLE rls
NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;

CREATE ROLE rls_owner_table
NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;

CREATE ROLE rls_owner_view
NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;

CREATE ROLE "manager-a(at)scuola-1(dot)it" LOGIN
NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
GRANT rls TO "manager-a(at)scuola-1(dot)it";

CREATE ROLE "manager-c(at)scuola-2(dot)it" LOGIN
NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
GRANT rls TO "manager-c(at)scuola-2(dot)it";

CREATE ROLE "manager-e(at)scuola-28961(dot)it" LOGIN
NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
GRANT rls TO "manager-e(at)scuola-28961(dot)it";

--
-- TOC entry 2211 (class 1262 OID 12272501)
-- Name: rls; Type: DATABASE; Schema: -; Owner: rls_owner_table
--

CREATE DATABASE rls WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE
= 'it_IT.UTF-8' LC_CTYPE = 'it_IT.UTF-8';

ALTER DATABASE rls OWNER TO rls_owner_table;

\connect rls

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET row_security = off;

--
-- TOC entry 1 (class 3079 OID 12429)
-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner:
--

CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;

--
-- TOC entry 2214 (class 0 OID 0)
-- Dependencies: 1
-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner:
--

COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';

SET search_path = public, pg_catalog;

--
-- TOC entry 185 (class 1259 OID 12272550)
-- Name: pk_seq; Type: SEQUENCE; Schema: public; Owner: rls_owner_table
--

CREATE SEQUENCE pk_seq
START WITH 736220
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;

ALTER TABLE pk_seq OWNER TO rls_owner_table;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- TOC entry 186 (class 1259 OID 12272552)
-- Name: schools; Type: TABLE; Schema: public; Owner: rls_owner_table
--

CREATE TABLE schools (
school bigint DEFAULT nextval('pk_seq'::regclass) NOT NULL,
description character varying(160) NOT NULL,
processing_code character varying(160) NOT NULL,
mnemonic character varying(30) NOT NULL,
example boolean DEFAULT false NOT NULL,
behavior bigint
);

ALTER TABLE schools OWNER TO rls_owner_table;

--
-- TOC entry 191 (class 1259 OID 12272602)
-- Name: _rls_test; Type: VIEW; Schema: public; Owner: rls_owner_view
--

CREATE VIEW C AS
SELECT schools.school,
schools.description,
schools.example
FROM schools;

ALTER TABLE _rls_test OWNER TO rls_owner_view;

--
-- TOC entry 188 (class 1259 OID 12272584)
-- Name: _rls_test_security_barrier; Type: VIEW; Schema: public; Owner:
rls_owner_view
--

CREATE VIEW _rls_test_security_barrier WITH (security_barrier='true') AS
SELECT schools.school,
schools.description,
schools.example
FROM schools;

ALTER TABLE _rls_test_security_barrier OWNER TO rls_owner_view;

--
-- TOC entry 189 (class 1259 OID 12272588)
-- Name: _rls_test_with_check_local; Type: VIEW; Schema: public; Owner:
rls_owner_view
--

CREATE VIEW _rls_test_with_check_local AS
SELECT schools.school,
schools.description,
schools.example
FROM schools
WITH LOCAL CHECK OPTION;

ALTER TABLE _rls_test_with_check_local OWNER TO rls_owner_view;

--
-- TOC entry 190 (class 1259 OID 12272592)
-- Name: _rls_test_with_check_local_cascade; Type: VIEW; Schema: public;
Owner: rls_owner_view
--

CREATE VIEW _rls_test_with_check_local_cascade AS
SELECT schools.school,
schools.description,
schools.example
FROM schools
WITH CASCADED CHECK OPTION;

ALTER TABLE _rls_test_with_check_local_cascade OWNER TO rls_owner_view;

--
-- TOC entry 187 (class 1259 OID 12272566)
-- Name: usenames_schools; Type: TABLE; Schema: public; Owner:
rls_owner_table
--

CREATE TABLE usenames_schools (
usename_school bigint DEFAULT nextval('pk_seq'::regclass) NOT NULL,
usename name NOT NULL,
school bigint NOT NULL
);

ALTER TABLE usenames_schools OWNER TO rls_owner_table;

--
-- TOC entry 2205 (class 0 OID 12272552)
-- Dependencies: 186
-- Data for Name: schools; Type: TABLE DATA; Schema: public; Owner:
rls_owner_table
--

INSERT INTO schools (school, description, processing_code, mnemonic,
example, behavior) VALUES (1, 'Istituto comprensivo ''Andromeda''',
'ZZIC80000Z', 'IC ANDROMEDA', true, NULL);
INSERT INTO schools (school, description, processing_code, mnemonic,
example, behavior) VALUES (2, 'Istituto Tecnico Tecnologico "Leonardo da
Vinci"', 'ZZITT0000Z', 'ITT DAVINCI', true, NULL);
INSERT INTO schools (school, description, processing_code, mnemonic,
example, behavior) VALUES (3, 'Istituto comprensivo "Voyager"',
'ZZIC00001Z', 'IC VOYAGER', true, NULL);

--
-- TOC entry 2206 (class 0 OID 12272566)
-- Dependencies: 187
-- Data for Name: usenames_schools; Type: TABLE DATA; Schema: public;
Owner: rls_owner_table
--

INSERT INTO usenames_schools (usename_school, usename, school) VALUES (1, '
manager-a(at)scuola-1(dot)it', 1);
INSERT INTO usenames_schools (usename_school, usename, school) VALUES (2, '
manager-c(at)scuola-2(dot)it', 2);

--
-- TOC entry 2222 (class 0 OID 0)
-- Dependencies: 185
-- Name: pk_seq; Type: SEQUENCE SET; Schema: public; Owner: rls_owner_table
--

SELECT pg_catalog.setval('pk_seq', 736220, false);

--
-- TOC entry 2066 (class 2606 OID 12272558)
-- Name: schools schools_pk; Type: CONSTRAINT; Schema: public; Owner:
rls_owner_table
--

ALTER TABLE ONLY schools
ADD CONSTRAINT schools_pk PRIMARY KEY (school);

--
-- TOC entry 2068 (class 2606 OID 12272560)
-- Name: schools schools_uq_description; Type: CONSTRAINT; Schema: public;
Owner: rls_owner_table
--

ALTER TABLE ONLY schools
ADD CONSTRAINT schools_uq_description UNIQUE (description);

--
-- TOC entry 2070 (class 2606 OID 12272562)
-- Name: schools schools_uq_mnemonic; Type: CONSTRAINT; Schema: public;
Owner: rls_owner_table
--

ALTER TABLE ONLY schools
ADD CONSTRAINT schools_uq_mnemonic UNIQUE (mnemonic);

--
-- TOC entry 2072 (class 2606 OID 12272564)
-- Name: schools schools_uq_processing_code; Type: CONSTRAINT; Schema:
public; Owner: rls_owner_table
--

ALTER TABLE ONLY schools
ADD CONSTRAINT schools_uq_processing_code UNIQUE (processing_code,
example);

--
-- TOC entry 2075 (class 2606 OID 12272571)
-- Name: usenames_schools usenames_schools_pk; Type: CONSTRAINT; Schema:
public; Owner: rls_owner_table
--

ALTER TABLE ONLY usenames_schools
ADD CONSTRAINT usenames_schools_pk PRIMARY KEY (usename_school);

--
-- TOC entry 2077 (class 2606 OID 12272573)
-- Name: usenames_schools usenames_schools_uq_usename_school; Type:
CONSTRAINT; Schema: public; Owner: rls_owner_table
--

ALTER TABLE ONLY usenames_schools
ADD CONSTRAINT usenames_schools_uq_usename_school UNIQUE (usename,
school);

--
-- TOC entry 2064 (class 1259 OID 12272565)
-- Name: schools_fk_behavior; Type: INDEX; Schema: public; Owner:
rls_owner_table
--

CREATE INDEX schools_fk_behavior ON schools USING btree (behavior);

--
-- TOC entry 2073 (class 1259 OID 12272579)
-- Name: usenames_schools_fx_school; Type: INDEX; Schema: public; Owner:
rls_owner_table
--

CREATE INDEX usenames_schools_fx_school ON usenames_schools USING btree
(school);

--
-- TOC entry 2078 (class 2606 OID 12272574)
-- Name: usenames_schools usenames_schools_fk_school; Type: FK CONSTRAINT;
Schema: public; Owner: rls_owner_table
--

ALTER TABLE ONLY usenames_schools
ADD CONSTRAINT usenames_schools_fk_school FOREIGN KEY (school)
REFERENCES schools(school) ON UPDATE CASCADE ON DELETE CASCADE;

--
-- TOC entry 2200 (class 0 OID 12272552)
-- Name: schools; Type: ROW SECURITY; Schema: public; Owner: rls_owner_table
--

ALTER TABLE schools ENABLE ROW LEVEL SECURITY;

--
-- TOC entry 2201 (class 3256 OID 12272600)
-- Name: schools schools_pl_school; Type: POLICY; Schema: public; Owner:
rls_owner_table
--

CREATE POLICY schools_pl_school ON schools USING ((school IN ( SELECT
usenames_schools.school
FROM usenames_schools))) WITH CHECK ((school IN ( SELECT
usenames_schools.school
FROM usenames_schools)));

--
-- TOC entry 2202 (class 0 OID 12272566)
-- Name: usenames_schools; Type: ROW SECURITY; Schema: public; Owner:
rls_owner_table
--

ALTER TABLE usenames_schools ENABLE ROW LEVEL SECURITY;

--
-- TOC entry 2203 (class 3256 OID 12272599)
-- Name: usenames_schools usenames_schools_pl_usename; Type: POLICY;
Schema: public; Owner: rls_owner_table
--

CREATE POLICY usenames_schools_pl_usename ON usenames_schools USING
((usename = "current_user"())) WITH CHECK ((usename = "current_user"()));

--
-- TOC entry 2213 (class 0 OID 0)
-- Dependencies: 3
-- Name: public; Type: ACL; Schema: -; Owner: rls_owner_table
--

REVOKE ALL ON SCHEMA public FROM postgres;
REVOKE ALL ON SCHEMA public FROM PUBLIC;
GRANT USAGE ON SCHEMA public TO rls;

--
-- TOC entry 2215 (class 0 OID 0)
-- Dependencies: 185
-- Name: pk_seq; Type: ACL; Schema: public; Owner: rls_owner_table
--

GRANT ALL ON SEQUENCE pk_seq TO rls;

--
-- TOC entry 2216 (class 0 OID 0)
-- Dependencies: 186
-- Name: schools; Type: ACL; Schema: public; Owner: rls_owner_table
--

REVOKE ALL ON TABLE schools FROM rls_owner_table;
GRANT ALL ON TABLE schools TO rls;

--
-- TOC entry 2217 (class 0 OID 0)
-- Dependencies: 191
-- Name: _rls_test; Type: ACL; Schema: public; Owner: rls_owner_view
--

REVOKE ALL ON TABLE _rls_test FROM rls_owner_view;
GRANT ALL ON TABLE _rls_test TO rls;

--
-- TOC entry 2218 (class 0 OID 0)
-- Dependencies: 188
-- Name: _rls_test_security_barrier; Type: ACL; Schema: public; Owner:
rls_owner_view
--

REVOKE ALL ON TABLE _rls_test_security_barrier FROM rls_owner_view;
GRANT SELECT,INSERT,REFERENCES,DELETE,TRIGGER,UPDATE ON TABLE
_rls_test_security_barrier TO rls;

--
-- TOC entry 2219 (class 0 OID 0)
-- Dependencies: 189
-- Name: _rls_test_with_check_local; Type: ACL; Schema: public; Owner:
rls_owner_view
--

REVOKE ALL ON TABLE _rls_test_with_check_local FROM rls_owner_view;
GRANT SELECT,INSERT,REFERENCES,DELETE,TRIGGER,UPDATE ON TABLE
_rls_test_with_check_local TO rls;

--
-- TOC entry 2220 (class 0 OID 0)
-- Dependencies: 190
-- Name: _rls_test_with_check_local_cascade; Type: ACL; Schema: public;
Owner: rls_owner_view
--

REVOKE ALL ON TABLE _rls_test_with_check_local_cascade FROM rls_owner_view;
GRANT SELECT,INSERT,REFERENCES,DELETE,TRIGGER,UPDATE ON TABLE
_rls_test_with_check_local_cascade TO rls;

--
-- TOC entry 2221 (class 0 OID 0)
-- Dependencies: 187
-- Name: usenames_schools; Type: ACL; Schema: public; Owner: rls_owner_table
--

REVOKE ALL ON TABLE usenames_schools FROM rls_owner_table;
GRANT ALL ON TABLE usenames_schools TO rls;

-- Completed on 2017-11-14 05:51:59

--
-- PostgreSQL database dump complete
--

2017-11-12 20:14 GMT+01:00 Joe Conway <mail(at)joeconway(dot)com>:

> On 11/12/2017 10:17 AM, Andrea Adami wrote:
> > if i do:
> >
> > SET ROLE 'manager-a(at)scuola-1(dot)it <mailto:manager-a(at)scuola-1(dot)it>'
>
> [SELECT from table]
>
> > i see only one row (as expected)
> >
> > but when i do:
>
> [SELECT from VIEWs]
>
> > 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.
>
> See:
> https://www.postgresql.org/docs/10/static/sql-createview.html
> In particular: "Access to tables referenced in the view is determined by
> permissions of the view owner."
>
> And:
> https://www.postgresql.org/docs/10/static/ddl-rowsecurity.html
> "Superusers and roles with the BYPASSRLS attribute always bypass the row
> security system when accessing a table. Table owners normally bypass row
> security as well, though a table owner can choose to be subject to row
> security with ALTER TABLE ... FORCE ROW LEVEL SECURITY."
>
> HTH,
>
> Joe
>
> --
> Crunchy Data - http://crunchydata.com
> PostgreSQL Support for Secure Enterprises
> Consulting, Training, & Open Source Development
>
>

--

FULCRO SRL UNIPERSONALE
Viale Brennero, 27
frazione Parona
37124 Verona (VR)
ITALY
Tel: +39 (045) 8890026
Fax: +39 (045) 247 5813
web: www.fulcro.net

Il contenuto e gli allegati di questo messaggio sono strettamente
confidenziali e destinati all'uso del solo destinatatario. E' vietato la
diffusione e l'uso non autorizzato. Le opinioni ivi espresse sono quelle
dell'autore: di conseguenza il messaggio non costituisce impegno
contrattuale tra FULCRO S.r.l. ed il destinatario, e la società non assume
alcuna responsabilità riguardo ai contenuti del testo e dei relativi
allegati. Qualora il presente messaggio Le fosse pervenuto per errore la
preghiamo di distruggerlo e comunicare, il prima possibile, l' errata
ricezione al mittente o all'indirizzo: badmail(at)fulcro(dot)net(dot)

This e-mail and any attachments are strictly confidential and for use only
by intended recipient. Any use and diffusion not authorized are prohibited.
Any opinions therein expressed are those of the author: therefore its
content doesn't represent any commitment between Fulcro S.r.l. and the
recipient and no liability or responsibility is accepted by Fulcro S.r.l.
for the above mentioned content. If you are not an intended recipient
please destroy this message and notify, as soon as possible, either the
sender or the address: badmail(at)fulcro(dot)net(dot)