Row Level Security Bug ?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Row Level Security Bug ?

Andrea Adami
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 '[hidden email]'

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:
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','[hidden email]','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));
 
 
 
 
 
 
Reply | Threaded
Open this post in threaded view
|

Re: Row Level Security Bug ?

Joe Conway
On 11/12/2017 10:17 AM, Andrea Adami wrote:
> if i do:
>
> SET ROLE '[hidden email] <mailto:[hidden email]>'

[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


signature.asc (836 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Row Level Security Bug ?

Andrea Adami
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 "[hidden email]" LOGIN
  NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
  GRANT rls TO "[hidden email]";

CREATE ROLE "[hidden email]" LOGIN
  NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
  GRANT rls TO "[hidden email]";

CREATE ROLE "[hidden email]" LOGIN
  NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
  GRANT rls TO "[hidden email]";

--
-- 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, '[hidden email]', 1);
INSERT INTO usenames_schools (usename_school, usename, school) VALUES (2, '[hidden email]', 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 <[hidden email]>:
On 11/12/2017 10:17 AM, Andrea Adami wrote:
> if i do:
>
> SET ROLE '[hidden email] <mailto:[hidden email]>'

[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

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: [hidden email]

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: [hidden email]
Reply | Threaded
Open this post in threaded view
|

Re: Row Level Security Bug ?

Joe Conway
On 11/13/2017 09:09 PM, Andrea Adami wrote:
> 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 VIEW]

> always i get the error: 
>
> permission denied for relation schools
> SQL state: 42501
>
> Someone has some suggestion ?

Offhand I would say you probably need to GRANT ALL (or SELECT or
whatever needed) to the VIEW owner.

Joe

--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development


signature.asc (836 bytes) Download Attachment
Previous Thread Next Thread