Re: BUG #14682: row level security not work with partitioned table

On Thu, Jun 1, 2017 at 2:59 AM, <fte(at)nct(dot)ru> wrote:
> The following bug has been logged on the website:
>
> Bug reference: 14682
> Logged by: Fakhroutdinov Evgenievich
> Email address: fte(at)nct(dot)ru
> PostgreSQL version: 10beta1
> Operating system: macOS Sierra 10.12.5
> Description:
>
> create table test (
> id bigserial not null,
> tm timestamp not null,
> user_name text not null,
> rem text
> ) partition by range (tm);
>
> create table test_1q (like test including all);
> create table test_2q (like test including all);
>
> alter table test attach partition test_1q for values from ('2017-01-01') to
> ('2017-04-01');
> alter table test attach partition test_2q for values from ('2017-04-01') to
> ('2017-07-01');
>
> CREATE ROLE bob; -- Normal user
> CREATE ROLE alice; -- Normal user
>
> insert into test(tm,user_name,rem)
> values
> ('2017-01-09 22:15:15','bob','bla-bla'),
> ('2017-02-09 22:15:15','alice','bla-bla'),
> ('2017-03-09 22:15:15','bob','bla-bla'),
> ('2017-04-09 22:15:15','alice','bla-bla'),
> ('2017-05-09 22:15:15','bob','bla-bla'),
> ('2017-06-09 22:15:15','alice','bla-bla');
>
> ALTER TABLE test ENABLE ROW LEVEL SECURITY;
> ALTER TABLE test_1q ENABLE ROW LEVEL SECURITY;
> ALTER TABLE test_2q ENABLE ROW LEVEL SECURITY;
>
> CREATE POLICY view_test ON test FOR SELECT USING (current_user =
> user_name);
> CREATE POLICY view_test_1q ON test_1q FOR SELECT USING (current_user =
> user_name);
> CREATE POLICY view_test_2q ON test_2q FOR SELECT USING (current_user =
> user_name);
>
> GRANT SELECT ON test TO public;
> GRANT SELECT ON test_1q TO public;
> GRANT SELECT ON test_2q TO public;
>
> set role to bob;
> select * from test;
> id | tm | user_name | rem
> ----+---------------------+-----------+---------
> 1 | 2017-01-09 22:15:15 | bob | bla-bla
> 2 | 2017-02-09 22:15:15 | alice | bla-bla
> 3 | 2017-03-09 22:15:15 | bob | bla-bla
> 4 | 2017-04-09 22:15:15 | alice | bla-bla
> 5 | 2017-05-09 22:15:15 | bob | bla-bla
> 6 | 2017-06-09 22:15:15 | alice | bla-bla
> (6 rows)
>
> select * from test_1q;
> id | tm | user_name | rem
> ----+---------------------+-----------+---------
> 1 | 2017-01-09 22:15:15 | bob | bla-bla
> 3 | 2017-03-09 22:15:15 | bob | bla-bla
> (2 rows)
>
> select * from test_2q;
> id | tm | user_name | rem
> ----+---------------------+-----------+---------
> 5 | 2017-05-09 22:15:15 | bob | bla-bla
> (1 row)

This is indeed a bug. fireRIRrules is currently skipping the RLS
policy check when relkind == PARTITIONED_TABLES, so RLS policies are
not applied. The attached patch fixes the behavior.

Thanks,
--
Mike Palmiotto
Software Engineer
Crunchy Data Solutions
https://crunchydata.com