[HACKERS] RLS policy not getting honer while pg_dump on declarative partition

[Rushabh Lathia]

While doing some testing I noticed that RLS policy not getting honer
while pg_dump on declarative partition.

I can understand that while doing SELECT on individual child
table, policy of parent is not getting applied. But is this desirable
behaviour? I think for partitions, any policy on the root table should
get redirect to the child, thoughts?

If current behaviour is desirable then atleast we should document this.

Consider the below test:

\c postgres rushabh

CREATE USER rls_test_user1;

CREATE TABLE tp_sales
(
    visibility         VARCHAR(30),
    sales_region       VARCHAR(30)
) PARTITION BY LIST (sales_region);

create table tp_sales_p_india  partition of tp_sales for values in
('INDIA');
create table tp_sales_p_rest  partition of tp_sales for values in ('REST');

insert into tp_sales values ( 'hidden', 'INDIA');
insert into tp_sales values ( 'visible', 'INDIA');
insert into tp_sales values ( 'hidden', 'REST');
insert into tp_sales values ( 'visible', 'REST');

GRANT SELECT ON tp_sales to rls_test_user1;
GRANT SELECT ON tp_sales_p_india to rls_test_user1;
GRANT SELECT ON tp_sales_p_rest to rls_test_user1;

ALTER TABLE tp_sales ENABLE ROW LEVEL SECURITY;

CREATE POLICY dump_p1 ON tp_sales FOR ALL USING (visibility = 'visible');

\c - rls_test_user1

-- SELECT honer the policy
SELECT * FROM tp_sales;

When we run the pg_dump using user rls_test_user1, can see the hidden
rows in the pg_dump output.

./db/bin/pg_dump -U rls_test_user1 postgres --inserts

Attaching the dump output.


Thanks,
Rushabh Lathia
www.EnterpriseDB.com

--
-- PostgreSQL database dump
--

-- Dumped from database version 10beta1
-- Dumped by pg_dump version 10beta1

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;

--
-- Name: postgres; Type: COMMENT; Schema: -; Owner: rushabh
--

COMMENT ON DATABASE postgres IS 'default administrative connection database';


--
-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner: 
--

CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;


--
-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner: 
--

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


SET search_path = public, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: tp_sales; Type: TABLE; Schema: public; Owner: rushabh
--

CREATE TABLE tp_sales (
    visibility character varying(30),
    sales_region character varying(30)
)
PARTITION BY LIST (sales_region);


ALTER TABLE tp_sales OWNER TO rushabh;

--
-- Name: tp_sales_p_india; Type: TABLE; Schema: public; Owner: rushabh
--

CREATE TABLE tp_sales_p_india PARTITION OF tp_sales
FOR VALUES IN ('INDIA');


ALTER TABLE tp_sales_p_india OWNER TO rushabh;

--
-- Name: tp_sales_p_rest; Type: TABLE; Schema: public; Owner: rushabh
--

CREATE TABLE tp_sales_p_rest PARTITION OF tp_sales
FOR VALUES IN ('REST');


ALTER TABLE tp_sales_p_rest OWNER TO rushabh;

--
-- Data for Name: tp_sales_p_india; Type: TABLE DATA; Schema: public; Owner: rushabh
--

INSERT INTO tp_sales_p_india VALUES ('hidden', 'INDIA');
INSERT INTO tp_sales_p_india VALUES ('visible', 'INDIA');


--
-- Data for Name: tp_sales_p_rest; Type: TABLE DATA; Schema: public; Owner: rushabh
--

INSERT INTO tp_sales_p_rest VALUES ('hidden', 'REST');
INSERT INTO tp_sales_p_rest VALUES ('visible', 'REST');


--
-- Name: tp_sales dump_p1; Type: POLICY; Schema: public; Owner: rushabh
--

CREATE POLICY dump_p1 ON tp_sales USING (((visibility)::text = 'visible'::text));


--
-- Name: tp_sales; Type: ROW SECURITY; Schema: public; Owner: rushabh
--

ALTER TABLE tp_sales ENABLE ROW LEVEL SECURITY;

--
-- Name: tp_sales; Type: ACL; Schema: public; Owner: rushabh
--

GRANT SELECT ON TABLE tp_sales TO rls_test_user1;


--
-- Name: tp_sales_p_india; Type: ACL; Schema: public; Owner: rushabh
--

GRANT SELECT ON TABLE tp_sales_p_india TO rls_test_user1;


--
-- Name: tp_sales_p_rest; Type: ACL; Schema: public; Owner: rushabh
--

GRANT SELECT ON TABLE tp_sales_p_rest TO rls_test_user1;


--
-- PostgreSQL database dump complete
--


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers