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

On 2017/06/02 3:13, Mike Palmiotto wrote:
> 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 for creating the patch. Agree with Michael that a test would
be nice.

Thanks,
Amit